And Or conditions in SQL, but not properly nest them

Question

I'm trying an SQLi attack.

The compiler executes the following query despite warnings:

select * from users where username='A' or 'B' and password='C';

Because the query executes, the attack is successful. Why does this query works and what is it doing? Can I assume that the value 'B' is taken as 'True' in the boolean sense? How do the logical operators work with each other? Is there any specific order like BODMAS for mathematics? Note that 'B' is standalone and not a boolean condition.

A query of the above format works fine in mysql database and returns the same result as the query:

select * from users where username='A';

No syantax errors are returned.

mysql> select * from authentication where user='A' or 'B' and password='C';
+------+----------+
| user | password |
+------+----------+
| A    | B        |
+------+----------+
1 row in set, 1 warning (0.00 sec)

The warning is as follows:

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'B' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

Answer 1

If 'B' is not a boolean then you would need to specify what you are comparing against, which in this case is probably username.

Something like;

select * from users where username='A' or username='B' and password='C'

This would evaluate username='B' AND password='C' and then OR username='A'

You can also refer to this StackOverflow post.

You can find the SQL operator ordering here.

Answer 2

UPDATE

Based on your updated question and comments, it would appear that you are aware of the error in your syntax and want to know specifically how MySQL handles this query.

It would appear the MySQL first converts the character string to an integer value (producing a warning).

MySQL treats any non-zero number as TRUE, so 'B', aftering being converted to an integer, would evalute as TRUE.

---

To begin with, I don't think your query would be valid in any DBMS I am aware of. Since you say that 'B' is not a boolean condition, then I have to think that you mean:

select * from users where username IN ( 'A', 'B' ) and password='C';

which would, of course, invalidate your question about BODMAS. So, to answer what appears to be your core question, I am going to change your SQL to this:

select * from users where username='A' or username='B' and password='C';

It's going to depend on the DBMS and how it chooses to parse the SQL. Generally the AND should take precedence over OR, but obviously software developers are free to implement SQL however they'd like, so I'm sure you could find counter-examples if you looked hard enough (perhaps a DBMS that processes boolean operators in order, for example).

SQL Server will process the AND before OR

http://technet.microsoft.com/en-us/library/ms190276.aspx

username='A' or ( username='B' and password='C' )

MySQL will also process the AND before OR

http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html

username='A' or ( username='B' and password='C' )

Sybase will process the AND before the OR

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug102.htm

username='A' or ( username='B' and password='C' )

DB2, like the others, will process AND before OR

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_precedenceofoperations.htm

username='A' or ( username='B' and password='C' )

Even MS Access, one of the least standards-compliant SQL implementations I am aware of, preserves AND-before-OR precedence:

http://office.microsoft.com/en-us/access-help/HV080756432.aspx

username='A' or ( username='B' and password='C' )

Answer 3

When you write a query like that the query analyzer will throw you a syntax error.

SQL does not allow the syntactical construction of a list of values separated by "OR", mu must "ORize" all the conditions:

select * from users where (username='A' or user_name='B') and password='C';

The parenthesys here are needed because the AND operator takes precedence over the OR operator, so the query without parenthesys is interprested as username='A' or (user_name='B' and password='C')

One extra advice: do NOT store passwords in database. Database should not deal with plain text passwords and user credential validation should be managed at application level, not at database level. Store crypted passwords hashes, retrieve them from application level and validate there. Morover, if working in a Web environment, it's strongly adviseable to encrypt passwords in the client (browser) layer or use SSL.

Answer 4

This article has a decent table showing the order of operations for T-SQL

Assuming you fix the minor syntax error in your query, you'd return anything that:

• Has a username of 'B' and a password of 'C'
• Has a username of 'A'

I believe your syntax should look like this

 select * from users where username='A' or username = 'B' and password='C';