I'm storing an md5 hash in both a cookie and a database. I have a function, validate_cookie(), that reads this cookie and queries the database to find the user associated with this hash.
validate_cookie() calls another function get_user_data() to do the actual querying.get_user_data() gets called by a few other functions and scripts, so the query it sends is pretty general. Here's the function:
function get_user_data($info,$password=Null,$source=Null)
{
if(!$source)
{
$query = "SELECT * FROM `users` WHERE `id` = '".mysql_real_escape_string($info)."' OR `email` = '".mysql_real_escape_string($info)."' OR `cookie`='".mysql_real_escape_string($info)."'"; //Check to see if $info matches any column
}
else { $query = "SELECT * FROM `users` WHERE `".mysql_real_escape_string($source)."_token` ='".mysql_real_escape_string($info)."'";} //we got a social token
$result = mysql_query($query);
if($result)
{
$row = mysql_fetch_array($result);
if($password)
{
if(crypt($password, $row['password']) == $row['password']) //password matching
{
return $row;
} else {return "Password does not match!";}
}
else { return $row; }
}
else { return "Could not get result from database!";}
}
The important query in this case is inside of the if(!source){} block. With the way validate_cookie() calls get_user_data() this query becomes (and I have tested that it does become this):
SELECT * FROM `users` WHERE `id` = '8sd8sdvsasdliwerhnbzo823' OR `email`='8sd8sdvsasdliwerhnbzo823' OR `cookie`='8sd8sdvsasdliwerhnbzo823'
Because id is an int field and everything in email goes through validation, the only row that should be selected from this query are those with a matching cookie field.
However, whether calling this query from a PHP script or manually from PHPMyAdmin, this will always select both the target row and one other: a test case that only has NULL in it's cookie field. Changing the hash slightly (so that it shouldn't be matching anything at all) still selects this same test case.
Is my query malformed in a way I don't grasp? Are there some arcane uses of OR that allows NULL fields to be matched? Any help would be appreciated.
P.S. Before I get told that I should really be using mysqli, yes, I know that. mysql is the boss's orders.
http://sqlfiddle.com/#!2/0b0832/10/0
It is converting your hash to an integer. Your test ID is 3. Your hash starts with 3. It is making those equal.
select cast('3b38f280e0203d7998a0d0898095ed56' as unsigned) as x
yields 3
fix it like this:
SELECT id, email, password, cookie,
case when `email` = '3b38f280e0203d7998a0d0898095ed56' then 'email'
when `id` = '3b38f280e0203d7998a0d0898095ed56' then 'id'
when `cookie` = '3b38f280e0203d7998a0d0898095ed56' then 'cookie'
else 'else' end as wtf
FROM `users`
WHERE
cast(`id` as char) = '3b38f280e0203d7998a0d0898095ed56' or
`email` = '3b38f280e0203d7998a0d0898095ed56' or
`cookie` = '3b38f280e0203d7998a0d0898095ed56'
And that will still work if you pass in an ID = 3
If and only if you are sure that your sql statement evolves to something like:
select a,b,[c ...] from mytable where a='something' or b='something' [or ...]
with a,b [...] not NULL
you only should get a row with "a" OR "b" not null and beeing 'something'. if not, you have a problem with your database/system/diriver or what else
sometimes "the others are wrong"
sry to say that, but thats an expieriance i made (in my case php_db2 i just got no result sometimes).
especially - and again - if you are sure how your statement evolves during runtime, if the "native" tool, here mysql admin gives the same - wrong - result, there is a problem, not in your range
if (and it looks like) there is en error within your database/driver/installation you can try to use a workarround like
select from ... where (a='som' and a is not null) or (b='som' and b is not null...) and so on
think about marking that with a "TODO:waiting for a correct db install" or so
btw: