Question

I m trying to validate a pair of data columns on mysql from my php page across md5 function.
I ve encrypted the string "helloworld" with php md5 function and attempted to compare it with MYSQL MD5 function but it won t work.
I do this because in the database there is a pair of strings "hello" and "world" needs to be compared with my php string, so:

<?php
$str_a = "hello";
$str_b = "world";
$str_encrypted = md5 ($str_a.$str_b);

// note "first_col" is "hello" and "second_col" is "world"
$sql = "UPDATE `my_table` SET `checked_col` =  1  WHERE MD5(CONCAT(first_col,second_col)) =  $str_encrypted  LIMIT 1;";
$res = mysql_query ($sql) or die (mysql_error());

($res) ? print "true" : print "false";
?>

This code return me false, and the database don t UPDATE the column checked column, but not mysql_error problems are returned.

Could the md5 from php generate a different MD5 from MYSQL?

a similar code written by a friend worked in the same server, but i don t have to much experience to see where is the difference

can someone explain me where I m wrong?
thanks!

Answer 1

I wouldn t mix & match MD5 functions. Probably simpler to consider any md5 function a one-way street. So modify it to be:

$str_concat = $str_a.$str_b;

// note "first_col" is "hello" and "second_col" is "world"
$sql = "UPDATE `my_table` SET `checked_col` =  1  WHERE
     MD5(CONCAT(first_col,second_col)) = MD5( $str_concat ) LIMIT 1;";

Or just make the sql match exactly, for simplicity.

// Skip the php concatenation.

// note "first_col" is "hello" and "second_col" is "world"
$sql = "UPDATE `my_table` SET `checked_col` =  1  WHERE 
    MD5(CONCAT(first_col,second_col)) = MD5(CONCAT( $str_a , $str_b )) LIMIT 1;";

Answer 2

The only way that MD5 in MySQL would return a different hash then the MD5 function in PHP is if the character set in MySQL is different.

Answer 3

Try running the following query:

SELECT  MD5(CONCAT(first_col,second_col))
FROM    mytable
WHERE   first_col =  hello 
        AND second_col =  world

and make sure it returns fc5e038d38a57032085441e7fe7010b0

Also check that the case of MD5 returned by PHP and MySQL match.

Answer 4

It is likely that your $res is false because there is nothing to update. If you have previously run your SQL command and updated the row, it will not update again if checked_col is still 1. Since mysql_query doesn t update anything, it will return false.

You could include a where clause to ignore previously checked (validated) rows.

You probably don t want to use the result of your update to determine whether validation was successful, perhaps you want to use a select instead.

Answer 5

** The only solution I found is to insert the password (or update the table) using the md5 hash part of php s function library, that way when you compare the hash to the database it works. All other attempts to match an md5 hash from php with the md5 function of mySQL fail.

If anybody knows why (I believe older versions of php and mysql dont have this problem) this happens, I would love to know - thanks, rick

友情链接