Checking username/password 2

[annasue]

I have this sql statement:

SELECT * FROM projects WHERE `username` = 'FAA' OR `username` = 'faa' AND `password` = 'ASW2004' OR `password` = 'asw2004'

in order to check for whether for the correct combination whether the user used upper or lower case. It's returning 2 rows from the database but that combination occurs only for one row. Do I have my syntax wrong?

 

[Chacalinc]

change:
SELECT * FROM projects WHERE `username` = 'FAA' OR `username` = 'faa' AND `password` = 'ASW2004' OR `password` = 'asw2004'

with:

SELECT * FROM projects WHERE UPPER(`username`) = 'FAA' AND UPPER(`password`) = 'ASW2004'

Take a look to:

http://dev.mysql.com/doc/mysql/en/String_functions.html

 

[sleipnir214]

It's probably an operator precedence problem.

You think
WHERE `username` = 'FAA' OR `username` = 'faa' AND `password` = 'ASW2004' OR `password` = 'asw2004'

means

WHERE (`username` = 'FAA' OR `username` = 'faa') AND (`password` = 'ASW2004' OR `password` = 'asw2004')

But MySQL, since AND has a higher precedence than OR, thinks the clause is:

WHERE `username` = 'FAA' OR (`username` = 'faa' AND `password` = 'ASW2004') OR `password` = 'asw2004'


A tip:

If you want your queries to be non-case-specific, don't try putting every combination into the query. Just convert everything to upper- or lower-case:

SELECT * FROM projects WHERE [blue]upper([/blue]`username`[blue])[/blue] = 'FAA' AND [blue]upper([/blue]`password`[blue])[/blue] = 'ASW2004'



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[TonyGroves]

String comparisons in MySQL are case-insensitive by default, so you could simple use:
[tt]
WHERE username = 'FAA' AND password = 'ASW2004'
[/tt]
Using the UPPER function would only slow things down, as it would prevent indexes being used.

-----
ALTER world DROP injustice, ADD peace;