What does the last single quote of the sql injection query: ' or "1"="1" ' mean

Question

I couldn't fully grasp why the last single quote of the sql query: ' or "1"="1" ' is necessary. So I understand the part until the last single quote '. The first single quote enables to inject the sql and the or "1"="1" part enables the query to always return true. However, I don't understand why the last single quote is necessary to inject the sql.

Answer 1

Think about where the injected string is going. It's being concatenated into a SQL query at a point that is wrapped in string delimiters. String delimiters always need to be paired; if they aren't then the DB engine will detect an unclosed string and reject the query rather than execute it.

An example:

• Original SQL "template", before concatenation: SELECT * FROM Users WHERE AccessToken=' + secretParam + ' and you control the content of secretParam.
• After concatenation with a real secret, like DEADBEEF: SELECT * FROM Users WHERE AccessToken='DEADBEEF'; this is a valid SQL query.
• When using the "correct" SQL Injection string: SELECT * FROM Users WHERE AccessToken='' or "1"="1" ''. In theory, this will return any user whose access token is an empty string (probably nobody) or for whom 1 equals 1 (everybody, the app will probably just take the first record). In practice, though, the DB might not like the empty string hanging out (the final '') after the second test (the or "1"="1") in the WHERE clause; it'll depend on the DB engine what it does with that.
• When using your shorter injection string: SELECT * FROM Users WHERE AccessToken='' or "1"="1" '. Now your query ends with a single quote to open a new string, but not only is this string in an unexpected place, it is never closed. Even a forgiving DB that doesn't mind an unexpected string (perhaps concatenating it with the preceding one) will consider this query to be syntactically invalid and reject it.

There are various other ways of dealing with this, especially for DBs that won't tolerate an unexpected string. You can incorporate the quotes in the template to do what you expect, using an injection like ' OR ''=', which would produce SELECT * FROM Users WHERE AccessToken='' OR ''=''; this has the same effect as above (swapping a test of whether the string "1" is itself for whether the empty string is itself) but leaves no dangling apostrophes/quotes or unexpected strings. It's also nice if there are additional tests or clauses after the insertion point, and you want them to be processed correctly.

Alternatively, you can terminate the query after your insertion. For example, many DB engines use -- (two hyphens) as a single-line start-of-comment indicator, similar to // in languages such as C or JavaScript, or # in shell scripts, Python, etc. Thus, you can make your attack string ' or "1"="1" -- and the generated SQL would be SELECT * FROM Users WHERE AccessToken='' or "1"="1" --'; this is a valid SQL statement containing two tests in its WHERE clause, and a single-character comment of ' at the end of the query. That's especially helpful of the query contained additional clauses or restrictions that you wanted to get rid of.