Check a field in MySql from a php md5 string

Question

hello all!
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

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 3

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 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.