Formatting MySQL Query

PHP
1

I need to format this query to work in MySQL DB, but those ’ . ’ are throwing me off and I’m not sure how to change them. I’ve spent an hour trying. :slight_smile: Would someone help me?

UPDATE `TABLE` SET `FIELD` = sha1('SALT' . sha1('SALT' . sha1('PASSWORD'))) WHERE `username` = 'admin'

I get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE `TABLE` SET `FIELD` = sha1('SALT' . sha1('SALT' . sha1('PASSWORD')))' at line 1

Thank you

2

Hi earthgirlllc, welcome to the forum

Although PHP does OK with parsing variables inside of double quotes, mixing string literals with function calls is a bit trickier and as you’ve found out can be messy and not so easy to debug.

Since you aren’t using double quotes in the query, you should be able to do something like

"beginning of query string here " . php_function_here() . " end of query string here"

3

Is there a reason why you are not using the password hashing built into PHP and using a less secure process instead?

4

SQL statements are easier to debug if they are formatted similar to the following:


$sql = "
    UPDATE   `TABLE` 
    SET     `FIELD` = sha1('SALT' . sha1('SALT' . sha1('PASSWORD') ) ) 
    WHERE     `username` = 'admin' ";
echo '<pre>';    
   echo '$sql => ' .$sql;
echo '</pre>'; 
echo '<br />';
echo '$sql => ' .$sql;
die;

Output

$sql =>
UPDATE TABLE
SET FIELD = sha1(‘SALT’ . sha1(‘SALT’ . sha1(‘PASSWORD’) ) )
WHERE username = ‘admin’


$sql =>
"UPDATE TABLE SET FIELD = sha1(‘SALT’ . sha1(‘SALT’ . sha1(‘PASSWORD’) ) ) WHERE username` = ‘admin’ ";

5

In SQL you cannot append one string to another with a dot - this is PHP style. In MySQL you have to use CONCAT function.

6

I needed to mimic the encryption process of OpenCart via UPDATE statement.

7

Thanks all, I hired a programmer to rewrite it with CONCAT.

8

Regardless of what encryption algorithm you use it’s always better to encode the password in PHP and send it to the database already encoded. This is because in your example the password is sent to MySQL in plain text and it is possible that in certain circumstances it will log the query or save it somewhere else for a number of reasons. Simply do all the hashing stuff in PHP and put the final encoded password in the UPDATE statement.

9

It’s better but some of the hashing algorithms don’t match up exactly between the PHP and SQL versions and so using the PHP version may not produce a value to match what the SQL version produced elsewhere. Presumably the SQL version has a built in salt in addition to whatever you add yourself with those algorithms. I don’t know if sha1 has that issue or not.

10

SHA1 will produce the same result both in PHP and MySQL - otherwise something would be seriously broken. We don’t know where the salt comes from in this case - but even if it were generated with SQL somehow I would prefer to first fetch it to PHP with a SELECT, hash the password in PHP and then do the UPDATE.

11

Yes one of the hashes is known to be broken in mySQL - it can’t be relied on to match the same hash generated elsewhere. I just can’t remember which one it is.

12

This is interesting - do you have some reference links to information about buggy hash algorithms?

13

I don’t know as I’d call it “buggy”, but the PASSWORD() function changed in version 4.1 from 16 byte to 41 byte

I’ve only always used PHP for hashed fields and have never used the MySQL function, so I don’t know how well it works but it seems using OLD_PASSWORD() or running mysql_upgrade should resolve any MySQL upgrade problems At least as far as PASSWORD() is concerned.

http://dev.mysql.com/doc/refman/5.7/en/password-hashing.html

http://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html

14

With mySQL having the PASSWORD() function and PHP the password_hash() function there is no reason why anyone should be using any other functions for their passwords if they expect to keep the passwords secure.

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.