Exploitable SQL commands and syntax

We have already seen that the main problem that makes applications and systems vulnerable to SQL injection is the lack of controls on user-provided input. By default, input sources need to be considered as untrusted so that everything sent to our application or system is verified prior to processing. You may now ask: how exactly could a malicious user insert an instruction within our code? SQL, being a very powerful language, permits many different operations on a database; tricking an application into executing arbitrary commands could lead to someone who wants to damage or obtain access to a system being given a serious advantage. In this section, we will see the main enablers for SQL injection, underlining how important correctly preprocessing input is, thus saving our systems and applications from being compromised through a simple input string.

SQL injection-enabling characters

Before dealing with SQL statements and constructions, we should first examine what, given the lack of controls on the input, makes inserting arbitrary instructions possible in the first place.

In a similar fashion to what happens in most machine-interpreted languages, SQL maps some specific characters to corresponding purposes within code text. The most trivial character a person would try to exploit is the single quote (') or the double quote ("), as it is used as a delimiter for text values within queries.

One example is the semicolon character (;) that is used to separate different commands (the same as in most programming languages).

Another quite abused character in SQL injection is the comment separator, which in most syntaxes corresponds to the – sequence, because it can render the next part of a SQL query useless, as the system will consider it a comment.

Think, just as a general example, of a text input used in a naïve SELECT query for the color of an object, for which a regular user would have inserted red, as follows:

SELECT color, shape FROM objects WHERE color='red'

Things could go quite differently if, instead of red, the following were inserted as text input:

red'; DROP TABLE objects --

This would result in the query looking like this:

SELECT color, shape FROM objects WHERE color='red'; DROP TABLE objects –-'

User-provided input, not having been sanitized, would trick the system into processing, alongside the command that uses an expected syntax, another SQL command that removes the objects table entirely from the database. The addition of the comment separator removes the second single-quote character automatically inserted by the application, thus making it possible for a malicious user to insert any SQL command they like while keeping the syntax correct.

To better understand the level of manipulation that would be possible in the case of unchecked input, we will see how SQL statements are usually constructed within an application.

SQL statement construction

As we mentioned earlier, the most common SQL statements used in applications are SELECT statements. Many times, when you encounter a web form used for searching an item, it is linked to a database running within the application so that data can be accessed in a structured and reliable way.

Think of a regular login form, made of the text inputs of a given username and password. What the application does is match the information inserted to see if the provided username and password pair exists within the same record of a database (linked to a single user). Thus, if the result exists, the application knows that the user has access to it and grants the necessary permission.

If we inserted Overlord as the username and pass as the password, the resulting SELECT statement would look like this:

SELECT * FROM users WHERE username='Overlord' AND password='pass'

The application inserts the strings collected, stored as variables, as text within another text string that constitutes the general body of the query. Of course, those variables could be acquired as input in various ways in a web context (as parameters in HyperText Transfer Protocol (HTTP) GET requests—which we would not recommend: it's not the 90s anymore—or in POST requests). For simplicity, in this example, we will consider variables acquired through a GET request to the (made-up) address sqlexample.com/login.php?username=Overlord&password=pass.

Therefore, the application code would look something like the following PHP: Hypertext Preprocessor (PHP) example:

$user=$_GET[username]; //$_GET extracts data from parameters $pass=$_GET[password]; //in the address (after the "?") $query="SELECT * FROM users WHERE username='" + $user +"' AND password='" + $pass + "'";

By constructing the query in this way, it becomes apparent how a statement could be altered using user-provided input, as described earlier. This is why being careful when handling user-provided input is important not only when using SQL but in general, as we can't assume anybody to have benign intentions.

We will now examine some examples of SQL injection commands and their purpose for attackers.