Understanding SQL Injection Attacks & How They Work. Identify SQL Injection Code & PHP Caveats

Article Reads:12671

SQL Injections have been keeping security experts busy for over a decade now as they continue to be one of the most common type of attacks against webservers, websites and web application servers. In this article, we explain what a SQL injection is, show you SQL injection examples and analyse how these type of attacks manage to exploit web applications and webservers, providing hackers access to sensitive data.

Additional interesting Web Hacking and Web Security content:

What Is A SQL Injection?

Websites operate typically with two sides to them: the frontend and backend. The frontend is the element we see, the rendered HTML, images, and so forth. On the backend however, there are layers upon layers of systems rendering the elements for the frontend. One such layer, the database, most commonly uses a database language called SQL, or Structured Query Language. This standardized language provides a logical, human-readable sentence to perform definition, manipulation, or control instructions on relational data in tabular form. The problem, however, is while this provides a structure for human readability, it also opens up a major problem for security.

Typically, when data is provided from the frontend to the backend of a website – e.g. an HTML form with username and password fields – this data is inserted into the sentence of a SQL query. This is because rather than assign that data to some object or via a set() function, the data has to be concatenated into the middle of a string. As if you were printing out a concatenated string of debug text and a variable’s value, SQL queries work in much the same way. The problem, however, is because the database server, such as MySQL or PostgreSQL, must be able to lexically analyse and understand the sentence’s grammar and parse variable=value definitions. There must exist certain specific requirements, such as wrapping string values in quotes. A SQL injection vulnerability, therefore, is where unsanitized frontend data, such as quotation marks, can disrupt the intended sentence of a SQL query.

How Does A SQL Injection Work?

So what does “disrupt the intended sentence of a SQL query” mean? A SQL query reads like an English sentence:

Take variable foo and set it to ‘bar’ in table foobar.
Notice the single-quotes around the intended value, bar. But if we take that value, add a single quote and some additional text, we can disrupt the intended sentence, creating two sentences that change the entire effect. So long as the database server can lexically understand the sentence, it is none the wiser and will happily complete its task. So what would this look like?

If we take that value bar and change it to something more complex – bar’ in table foobar. Delete all values not equal to ‘ – it completely disrupts everything. The sentence is thus changed as follows:

Take variable foo and set it to ‘bar’ in table foobar. Delete all values not equal to ‘’ in table foobar.

Notice how dramatically this disrupts the intended sentence? By injecting additional information, including syntax, into the sentence, the entire intended function and result has been disrupted to effectively delete everything in the table, rather than just change a value.

What Does A SQL Injection Look Like?

In code form, a SQL injection can find itself in effectively any place a SQL query can be altered by the user of a web application. This means things like query strings e.g: example.com/?this=query_string, form content (such as a comments section on a blog or even a username & password input fields on a login page), cookie values, HTTP headers (e.g. X-FORWARDED-FOR), or practically anything else. For this example, consider a simple query string in PHP:

Request URI: /?username=admin

1. $user = $_GET['username'];
2. mysql_query("UPDATE tbl_users SET admin=1 WHERE username='$user'");

First, we will break this down a bit.

On line #1, we set the value of the username field in the query string to the variable $user.

On line #2, we insert that variable’s value into the query string’s sentence. Substituting the variable for the value admin in the URI, the database query would ultimately be parsed as follows by MySQL:

UPDATE tbl_users SET admin=1 WHERE username='admin'

However, a lack of basic sanitization opens this query string up to serious consequences. All an attacker must do is put a single quote character in the username query string field in order to alter this sentence and inject whatever additional data he or she would like.

Here is an example of what this would look like:

Request URI: /?username=admin' OR 'a'='a

1. $user = $_GET['username'];
2. mysql_query("UPDATE tbl_users SET admin=1 WHERE username='$user'");

Now, with this altered data, here is what MySQL would see and attempt to evaluate:

UPDATE tbl_users SET admin=1 WHERE username='admin' OR 'a'='a'

Notice, now, that if the letter A equals the letter A (basically true=true), all users will be set to admin status.

Ensuring Code is Not Vulnerable to SQL Injection Vulnerabilities

If we were to add a function, mysql_real_escape_string() for example, on line #1, that would prevent this particular variable from being vulnerable to a SQL injection. In practice, it would look like this:

Request URI: /?username=admin' OR 'a'='a 1. $user = mysql_real_escape_string($_GET['username']);
2. mysql_query("UPDATE tbl_users SET admin=1 WHERE username='$user'");

This function escapes certain characters dangerous to MySQL queries, by prefixing those characters with backslashes. Rather than evaluate the single quote character literally, MySQL understands this prefixing backslash to mean do not evaluate the single quote. Instead, MySQL treats it as part of the whole value and keeps going. The string, to MySQL, would therefore look like this:

UPDATE tbl_users SET admin=1 WHERE username='admin\' OR \'a\'=\'a'

Because each single quote is escaped, MySQL considers it part of the whole username value, rather than evaluating it as multiple components of the SQL syntax. The SQL injection is thus avoided, and the intention of the SQL sentence is thus undisrupted.

Caveat: For these examples, we used older, deprecated functions like mysql_query() and mysql_real_escape_string() for two reasons:

1. Most PHP code still actively running on websites uses these deprecated functions;
2. It allows us to provide simple examples easier for users to understand.

However, the right way to do it is to use prepared SQL statements. For example, the prepare() functions of the MySQLi and PDO_MySQL PHP extensions allow you to format and assemble a SQL statement using directive symbols very much like a sprintf() function does. This prevents any possibility of user input injecting additional SQL syntax into a database query, as all input provided during the execution phase of a prepared statement is sanitized. Of course, this all assumes you are using PHP, but the idea still applies to any other web language.

SQL Injection Is The Most Widely Exploited Vulnerability

Even though it has been more than sixteen years since the first documented attack of SQL Injection, it is still a very popular vulnerability with attackers and is widely exploited. In fact SQL Injection has always topped the OWASP Top 10 list of most exploited vulnerabilities.