How does SQL injection work through URL?

Question

I wonder how SQL injection works through URL with SQL in the parameter. Assume I have a database with a table named mytable. When I get an ID from method POST I put it in this query:

SELECT * FROM `mytable` WHERE id='POST[id]'

Even if the user puts SQL like:

SELECT * FROM 'mytable'

It became something like this:

SELECT * FROM `mytable` WHERE id='SELECT * FROM 'mytable'

So it just returns:

You have an error in your SQL syntax.

So how does this kind of attack work? How can the attacker change the query without causing a syntax error?

Answer 1

The point of SQL injection is to modify the query where the variable is.

Let's use the query you wrote as an example. If the variable id contains something like this and isn't sanitized properly:

' OR 1=1#

It would return every single row because it has been modified to this:

SELECT * FROM `mytable` WHERE id='' OR 1=1#'

The trick to avoid the syntax error is to comment the rest of the query (The character # does it in this situation.

Answer 2

Exploiting SQL injections is the art of providing parameters that, when incorporated into an SQL statement, result in a valid SQL statement syntax while changing the semantics intended by the developer to some that are profitable for an attacker.

Depending on the DMBS and the statement type, in which the injection is possible, the results of a successful SQL injection vary from information disclosure (reading arbitrary data, local files), via data manipulation (inserting, deleting, or altering arbitrary data, writing local files), through to arbitrary command execution.

The DBMS and statement does also affect which techniques can be used during the exploitation. If the connection API does not allow the execution of multiple statements (so called ‘stacked queries’), the often cited Robert';DROP TABLE Students; -- would have no effect. In that case you would also be limited to the side effects of the particular statement type the injection occurs in: If it’s a SELECT statement, it’s most likely only possible to read data, but not to write or delete data.

And finally, it also depends on the feedback that is given to the attacker about the result of the executed statement. Error messages, especially those generated by a connection API or programming language, are often quite technical and verbose and can reveal critical information, e.g., parts of the failed statement like “You have an error in your SQL syntax near '…'”. Such messages can give attackers handy information on the context the injection happens in (statement type, clause type, parentheses nesting level, etc.).

In your example a simple id=' OR '1'='1 would result in the following statement:

SELECT * FROM `mytable` WHERE id='' OR '1'='1'

With the latter expression '1'='1' all rows will get selected. An extension to this would be using a sub-query to read arbitrary data in a blind manner:

SELECT * FROM `mytable` WHERE id='' OR EXISTS (SELECT * FROM users WHERE name='admin' AND password LIKE 'a%') AND '1'='1'

Here all rows would only be selected if there exists a user admin whose password begins with a, otherwise no row would be selected. This technique is called ‘boolean-based blind’.