Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

MySql (mis) matching.

mysql exact matching

10:08 pm on Jun 17, 2012 (gmt 0)

New User

joined:Feb 16, 2012
posts: 7
votes: 0


I have a situation where a user clicks on a link in an email with an id at the end of the link which is also inserted into a mysql table(tmp). These 2 need to match and an email address in the same row needs to match the same address in table 1 where ultimately user inputed password must match against the password in table 1.

Things seem to be following what they should be doing until it comes to matching the password. If they match perfectly it tells you sometimes it tells you it's a match when not exact but close--I've noticed that when you change the characters at the end of the password string it has a tendency to match but not at the beginning of the string. If it makes any difference the input string is encrypted(perl encryption) in the same manner as the strings in the password field were encrypted before entry.

Here is basically what I have at this point...any ideas. Thank you.

$query="select table1.password,table1.email,tmp.email,tmp.uid from table1,tmp where binary table1.password = '$_[0]' and table1.email = tmp.email and binary tmp.uid like'$_[2]' limit 1";
($_[0]) = $dbh->selectrow_array($query);

if(defined($_[0])){print"Perfect Match";}
elsif(!defined($_[0])){print"No Match";}

$_[0] represents the password typed in by the user and $_[2] is an id from a link.

The binary thing comes from Paul DuBois' Mysql book.
12:13 pm on July 5, 2012 (gmt 0)


WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
votes: 1

binary forces the comparison to be performed byte-by-byte rather than character-by-character. This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes in the values.

More on binary:

Your query is looking for exact comparisons on the password and email address but is using a LIKE comparison on the uid. Why? Although you are limiting the result set to a single row (LIMIT 1) you still have the possibility of multiple rows from which you will select but 1.