CODEWITHSUNDEEP
phpmysqlmd5compare
vitto
vitto

Reputation: 19476

Check a field in MySql from a php md5 string

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!

Upvotes: 2

Views: 8482

Answers (5)

Rick
Rick

Reputation: 31

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

Upvotes: 0

shufler
shufler

Reputation: 932

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.

Upvotes: 0

tplaner
tplaner

Reputation: 8461

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.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425361

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.

Upvotes: 0

Kzqai
Kzqai

Reputation: 23062

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;";

Upvotes: 7

Related Questions