Databases
StackOverflow2013
Postsdbo
POSQL query consistently selects a non-matching row
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL query consistently selects a non-matching row
    primarykey
    data
    text
    <p>I'm storing an md5 hash in both a cookie and a database. I have a function, <code>validate_cookie()</code>, that reads this cookie and queries the database to find the user associated with this hash. </p> <p><code>validate_cookie()</code> calls another function <code>get_user_data()</code> to do the actual querying.<code>get_user_data()</code> gets called by a few other functions and scripts, so the query it sends is pretty general. Here's the function:</p> <pre><code>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!";} } </code></pre> <p>The important query in this case is inside of the <code>if(!source){}</code> block. With the way <code>validate_cookie()</code> calls <code>get_user_data()</code> this query becomes (and I have tested that it does become this):</p> <pre><code>SELECT * FROM `users` WHERE `id` = '8sd8sdvsasdliwerhnbzo823' OR `email`='8sd8sdvsasdliwerhnbzo823' OR `cookie`='8sd8sdvsasdliwerhnbzo823' </code></pre> <p>Because <code>id</code> is an int field and everything in <code>email</code> goes through validation, the only row that should be selected from this query are those with a matching <code>cookie</code> field.</p> <p>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 <code>NULL</code> in it's <code>cookie</code> field. Changing the hash slightly (so that it shouldn't be matching anything at all) still selects this same test case.</p> <p>Is my query malformed in a way I don't grasp? Are there some arcane uses of <code>OR</code> that allows <code>NULL</code> fields to be matched? Any help would be appreciated.</p> <p>P.S. Before I get told that I should really be using <code>mysqli</code>, yes, I know that. <code>mysql</code> is the boss's orders.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USKOMON
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL query consistently selects a non-matching row
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL query consistently selects a non-matching row
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe test case has `NULL` for all 3 of those fields, or just the cookie field? Given that any input results in the same test case output, there must be a condition that is being met. `OR` requires that one of the 3 conditions you have listed is met.
      singulars
      1. POSQL query consistently selects a non-matching row
      1. USHart CO
    2. COUse limit 1, young one.
      singulars
      1. POSQL query consistently selects a non-matching row
      1. USHackerman
    3. COyou said it had `cookie=null` but what's in the `id` and `email` field of the incorrectly matched row?
      singulars
      1. POSQL query consistently selects a non-matching row
      1. USgloomy.penguin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload