Determine Which Part(s) of WHERE Statement Failed

Question

Let's say I have a SQL statement like this that checks a user login:

SELECT * FROM users 
WHERE username='[email protected]', password='abc123', expire_date>=NOW();

Is there a way in SQL to determine specifically which WHERE conditions fail, without having to separate each condition into its own query and test individually?

In this specific example it would allow the developer to tell users exactly the reason why their attempt to login failed.

For my purposes I'm using PHP/MySQL.

Answer 1

Well, one thing you could do is change your query so it only matches on the username. Then in the code you check the password and the expiration date, returning appropriate errors.

Also, I hope your example query is a simplification; Certainly you should be salting/encrypting/hashing your passwords, and you should include something that limits the number of failed attempts within a certain timeframe, etc...

As far as your actual question (as opposed to the results you are looking for), there isn't a way to get that information from the where clause. The closest you could do would be something like:

SELECT *,
    CASE WHEN Password = 'asdf' THEN 1 ELSE 0 END AS IsPasswordMatch,
    CASE WHEN Expiration >= NOW() THEN 1 ELSE 0 END AS IsActiveAccount
FROM Users
WHERE Username = 'user'

Answer 2

No, the where-clause is applied as a block, and various techniques are used so that not all rows have to be scanned. Also, how would you know which row was the one that was desired?

Additionally, you probably don't want to tell the user too much about why a login attempt failed. Saying too much allows for exploits such as account mining and password attacks.

edit If you truly do want to display this to your user, then split your logic into different parts:

1. Validate identity
   Action: Fetch the corresponding user row from the database
   Result:
   • If no such row exist => invalid account
   • If row is returned, continue to step 2.
2. Validate credential
   Action: Check the stored credential (password, hash of password or encrypted password) against the supplied password treated in the same way the credential is stored.
   Result:
   • No match => Invalid password / credential
   • Match => Successful login attempt
3. Login user
   Action: Add data to session etc.

Answer 3

You probably just need to separate the parts of the where clause with 'AND'

SELECT * FROM users 
WHERE username='[email protected]'
   And password='abc123'
   And expire_date>=NOW();

Answer 4

In MySQL you can put boolean expressions in the select-list. Boolean expressions evaluate to the integer 1 when true, or the integer 0 when false.

SELECT password = 'abc123' AS is_authenticated,
       expire_date >= NOW() AS is_not_expired
FROM users
WHERE username='[email protected]';

note: If you need to write a query that works on other brands of RDBMS, keep in mind this use of boolean expressions is nonstandard. Use the CASE syntax that other folks have posted.

PS: This is a tangent from your question, but I urge you not to store passwords in plaintext. Store a hash digest of the salted password. See http://stackoverflow.com/questions/420843/need-some-help-understanding-password-salt