SELECTing in a WHERE clause, inside a SELECT statement

Question

I'm not exactly sure on the correct technical wording, so excuse my title, but here's the problem. I have a MySQL database, and in the user table I have *user_name*, a *password_salt*, and an md5 password containing the password then salt. In a program, users connect and I get one query to send to validate a user. When a user connects I need a way of selecting their user_name, and comparing the given password to the stored password, which requires retrieving the salt somewhere in the WHERE statement (I guess).

This is my hypothetical "example":

SELECT user_name FROM users WHERE user_name='$nick' AND password = md5(CONCAT('$md5pass', md5((select password_salt FROM users where user_name='$nick')))) LIMIT 1

Resolution Update: Got it working, thanks for the suggestions, a normal select sufficed, the problem was that the sql-auth api wasn't receiving the password unless the port was specified.

Why are you issuing a separate SELECT statement when you already have access to the salt in your main query? Just do WHERE password = md5(CONCAT('$md5pass', md5(password_salt))) — Zane Bien
– Zane Bien, Commented Jul 22, 2012 at 20:15
I was unsure as to whether salt was being received, in my original attempt, it was exactly like @kuba-wyrostek suggested, it seems that the api in use here isn't playing ball though. — Lee Holmes
– Lee Holmes, Commented Jul 22, 2012 at 23:44

Kuba Wyrostek · Accepted Answer · 2012-07-22 20:15:11Z

Actually you can freely use any column from table declared in "FROM" clause not only in "SELECT" clause, but also in "WHERE" clause, so I don't see a need to subquery here. Let it be simply:

SELECT user_name FROM users WHERE user_name='$nick' AND password = md5(CONCAT('$md5pass', md5(password_salt))) LIMIT 1

This way a row is selected only if it matches both: - user name is correct - the password in row matches given password

I am not sure though if I used md5() functions correctly. I copied your example.

Thanks, this is what I had in my first attempt, it didn't work which is why I looked at sub-queries, it would seem the problem is in the api, will debug and see what it's doing.

Alexander · Accepted Answer · 2012-07-22 20:15:00Z

SELECT user_name FROM users WHERE user_name='$nick' AND password = md5(CONCAT('$md5pass', password_salt)) LIMIT 1

Ross Smith II · Accepted Answer · 2012-07-22 20:15:48Z

Try this instead:

SELECT user_name FROM users WHERE user_name='$nick' AND password = md5(CONCAT('$md5pass', md5(password_salt))) LIMIT 1

Collectives™ on Stack Overflow

SELECTing in a WHERE clause, inside a SELECT statement

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related