7

I am a software developer starting studying application security and I have the following doubt related SQL injection.

I am following a video course and there are these two examples:

  1. I have an insecure SQL query like this:

    txtSql = "SELECT * FROM Users WHERE UserId = " + txtUserId
    

    If the passed value of txtUserId is something like 99 OR 1 = 1, I will obtain a query like this

    SELECT * FROM Users WHERE UserId = 99 OR 1 = 1
    

    which will return me the entire list of record contained in the Users table because 1 = 1 is always true and the OR concatenation will return true so it is like this query:

    SELECT * FROM Users WHERE TRUE
    

    returning me the entire list of records. Is this reasoning correct?

  2. Then I have this second more sophisticated example:

    There is a user login form (username and password). Behind this form there is this insecure query implementation:

    sql = 'SELECT * FROM USERS WHERE Name = "' + uName + '" AND Pass = "' + uPass + '"'
    

    If the user inserts the following data into the login form:

    uName = " OR ""="
    uPass = " OR ""="
    

    The result query will be:

    SELECT * FROM Users WHERE Name = "" OR ""="" AND Pass = "" OR ""=""
    

    So it is selecting records where

    • The Name field is empty ("") or equal to "=" (and this condition should be always false because it is pretty strange to have an empty user name or an username like "=").

    • The Pass field is empty ("") or equal to "=" (and this condition should be always false because it is pretty strange to have an empty password or a password like "=").

    So we have a conditional like:

    WHERE FALSE AND FALSE
    

    And here my doubt: FALSE AND FALSE = FALSE

Why does it say that this query returns me the entire list of records of the user table?

If I understood the logic correctly, the second query should translated to something like this:

SELECT * FROM Users WHERE FALSE AND FALSE

What is wrong in my reasoning? What am I missing?

7

You've misread the injection, specifically this part:

""=""

This isn't checking if the Name is an equal sign, but rather if an empty string is equal to an empty string. It's effectively the same thing as 1=1 (and they could have used 1=1 just as well here), and is therefore the equivalent of TRUE. So this clause:

Name = "" OR ""=""

Is the same as Name="" OR 1=1 as well as Name="" OR TRUE

It's easy to misread something like that. I'm sure that's all you need, since you otherwise understand these concepts clearly! Just to be a pedant though, the query basically boils down to:

WHERE Name="" OR TRUE AND Pass="" OR TRUE

What happens next might depend on the exact order of precedence, but it will probably be fine. Another trick you could use would be to end the username with a backslash remove Pass from the query all together (although this may not work in all flavors of SQL). Imagine injecting User=\ and Pass= OR 1=1 -- (ends in a comment). You'll end up with this query:

SELECT * FROM USERS WHERE Name="\" AND Pass = " OR 1=1 --"

Because of the backslash it will be searching for a Name of " AND Pass = (which won't match anything), but then the OR 1=1 will make it match everything. The ending comment gets rid of the last double quote which would otherwise cause a syntax error. This form gives you a bit more control over the query by effectively nuking the search on the Pass column and therefore ditching all the AND/OR clauses. For instance, you could search for an actual ID by changing your Pass condition to OR ID=10 (presuming there is an ID column).

  • Ahhh so let me check if I well understood the point: doing Name = "" OR ""="" I am checking if the PARAMETER passed for the Name field (that is empty string) is "" (an empty string) or "%" (an other way to specify the empty string). So the check is on the passed parameter. I could also do something like this to obtain my injection: SELECT * FROM Users WHERE Name = "foo" OR "foo" AND Pass = "" OR ""="" – AndreaNobili Nov 9 at 12:34
  • 2
    @AndreaNobili Not quite.Name="foo" OR "foo" wouldn't be valid syntax. The two clauses are separate:Name="" and the ""="" are completely unrelated. ""="" is perfectly valid SQL on its own, for instance you could have a query that simply says: WHERE ""="". The key is that the "column name" doesn't have to be an actual column name but can be anything, including a literal empty string. So ""="" asks, "Is an empty string equal to an empty string?" which is always true. This is effectively the same thing as 1=1 which is also always true. – Conor Mancone Nov 9 at 13:35
  • 1
    In other words, you could exchange ""="" for 1=1 and the query would operate exactly the same way, since both clauses just evaluate to True. You would even be able to see that in the query plan. – Conor Mancone Nov 9 at 13:36
  • 1
    nitpick: escaping with backslashes in a string constant is not standard sql and may not work, depending on the database being used and how it's configured – Eevee Nov 9 at 20:37
  • 1
    @ConorMancone Name="foo" OR "foo" valid syntax but invalid semantics as OR expects two boolean values as its arguments. Yet, it's valid syntax. – bash0r Nov 11 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.