SQL查询始终选择不匹配的行

Question

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.

Answer 1

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