I have a portion of a MySQL query which is designed to save a password using SHA512-CRYPT:
SELECT ENCRYPT('firstpassword', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))
I need to port the application to use PostgreSQL, and as such, the statement is not compatible. My attempt in PostgreSQL is like so:
SELECT CRYPT('firstpassword'::text, CONCAT('$6$', SUBSTRING(ENCODE(DIGEST(RANDOM()::text , 'sha1'), 'hex') FROM '.{16}$')))
When tested in component parts, each of these implementations appear identical, but as completed statements, the output differs.
I have found that the CONCAT statements (that generate the salt) appear to provide identical output.
If I try comparing the output of CRYPT or ENCRYPT using simple plaintext words, the output is identical. However, if I combine it with the output of a salt, the output differs;
MySQL:
SELECT ENCRYPT( 'firstpassword', '$6$ae73a5ca7d3e5b11' )
Produces: $6$ae73a5ca7d3e5b11$v/RbcEEx4VR37VMUF6gBnPNo2ptSyU...
PostgreSQL:
SELECT CRYPT('firstpassword'::text, '$6$ae73a5ca7d3e5b11'::text)
Produces: $6eTK2KpfoaQM
Can someone explain why these statements are diverging or suggest a better way to implement this MySQL query?
