I need helping creating a query to salt and md5 a password then confirm the login on a MySQL Table.

Question

I've created a view of a table on a MySQL database to enable another application to use our existing (centralized) clients table.

Our passwords are stored as

md5(password + salt) + ":" + salt

Normally I decode this via a programming language of the given app we're connecting to it but...

This time it's a third party app and I only have SQL and one query to authorize a user.

Can you help me create a valid SQL query to authenticate?

The logic is straight forward:

• Get salt for the given user, (everything after the colon)
• combine the password and the salt
• MD5 the password and salt
• then compare the resulted md5 hash

the default sql query for this app is

select * from users where userName=? and userPass=?

Thanks in advance.

Answer 1

select * from users where userName='MyUser' and userPass=concat(md5('MyPass'), ':', salt)
where salt=(
  select substring(userPass FROM (instr(userPass, ':')+1)) from users
    where userName='MyUser'
)

Answer 2

I tried this and it works:

SELECT * FROM users
WHERE userPass = CONCAT( 
  MD5(CONCAT('xyzzy', (@salt := SUBSTRING_INDEX(userPass, ':', -1)))), 
  ':', @salt)
AND userName = 'bkarwin';

I know you probably don't have the freedom to change anything, but FWIW, MD5() is not considered strong enough encryption for passwords. It's recommended to use SHA-256, which is available through the SHA2() function in MySQL 6.0.5.