Technology blog::Life hacks::Linux::Hardware::Gaming

What are SQL Injection Attacks?

By Tim Trott, 23rd July 2016 in Privacy & Security

SQL Injection is an extremely prevalent and dangerous risk to any web application and is so easy to perform. A SQLi attack can lead to sensitive information being leaked directly from the database, malware being served, data integrity compromised and so on.

How does SQL Injection Work?

Let's have a look at how a SQL Injection attack may be performed on a vulnerable website.

The website in question has a page url such as the following:

The important thing here is that we have a query string. As a developer is pretty obvious that this query string will form part of a SQL statement for retrieving a product.

  1. SELECT * FROM products WHERE prodId = 1

By changing the ID, the SQL statement changes and a different product is retrieved from that database. The value in the query string for the product is untrusted data. This untrusted data can be manipulated by the user, and is eventually passed down into the SQL statement.

So let's have a look at how this prodId can be manipulated for unintended results. If we change the query string to: or 1=1

The SQL statement then becomes

  1. SELECT * FROM products WHERE prodId = 1 OR 1=1

This will fundamentally change the structure of the SQL query. Before we would retrieve a single product. Now that the 1=1 has been added, the SQL statement will return ALL products. Knowing this, additional parameters can be crafted to reveal information, disclose server technology or even the database structure itself.

There are a few different types of SQL injection attack.

Explicit attacks are union based, that is they append a result set which is then rendered to the markup. They can also cause internal errors which cause internal information disclosure which can be used to craft further attacks.

Implicit attacks are boolean based, that is they test if a particular condition is true or false. These attacks are used when custom error pages are used. If a SQL query falls over because of an error, the test is false and the custom page is shown. If the SQL runs successfully the test is true and the page loads normally.

We'll have a look at each type in more detail.

Any source of untrusted data can be manipulated, the attack can come from query string parameters, form post data or anywhere else a database query may take a parameter from. In these examples we're not worried about where the attack comes from or which method is being used, just that a parameter can be manipulated.

One way of determining if an application is vulnerable is to break it. This is commonly done by simply adding single apostrophe to it. In most RDBMS the apostrophe is used to open and close a string. By adding an apostrophe, we can end the string early and add our own code to the statement.

Let's consider the application which takes in a product style code from a form POST and queries the database for user details. A very simple application may contain logic such as this, with the untrusted data being inserted into the blue block from a variable. At this stage we are not concerned with how the query is constructed, just the query sent to the RDBMS.

  1. SELECT * FROM products WHERE productCode = 'AB123'

Suppose we manipulate the parameter so that it becomes AB123' (note the apostrophe). This then gets appended to the SQL statement which is now invalid.

  1. SELECT * FROM products WHERE productCode = 'AB123''

One of two things can happen here, either the application will fall over with an internal exception or it will show a user friendly error page. Both of these scenarios can be used to hack the system.

In the first scenario, an internal exception message is a goldmine of information for any malicious hacker. The messages will quite often divulge the lines of code being executed and maybe even the entire SQL statement and connection strings. If the malicious hacker has access to the original SQL being constructed and executed, a specific attack can be targeted to the manipulated parameter.

ASP.Net Exception Yellow Screen of Death
ASP.Net Exception Yellow Screen of Death

In the second scenario, we can't see any of this information, so we are better protected, but the presence of an error page can still be used to attack a vulnerable system. This is called a blind attack and will be covered later on in this tutorial.

Let's go back to our manipulated parameter and see if we can get some of our own queries to run. Now, we know we can close off a string, we can add our own query and comment out the rest of the original query.

Explicit SQL Injection

Consider an example malicious parameter which may look like this:

AB123'; select * from users; --

What this is going to do is close off the productCode parameter, execute another SQL query to check the existence of a users table, then everything else after is commented out with a double hyphen. This gets inserted into the SQL statement so it becomes:

  1. SELECT * FROM products WHERE productCode = 'AB123'; SELECT * FROM users; --'

If there are no errors on this statement, the page will load and we know that there is a table called users. If the statement causes an error, the internal exception is shown with further details, or an error page is shown. At the very least we know that the table probably does not exist.

Please note, I am using SELECTS as an injection, but malicious hackers could just as easily use DROP TABLE or TRUNCATE which will have devastating effects to your application.

Typically an error message will state something like "Invalid object name 'users'". If we get an internal exception, we may get something disclosing a System.Data.SqlClient.SqlException indicating that we are working with a Microsoft SQL Server database, or we may get an error like "1146: Table 'dbo.users' doesn't exist" which may indicate a MySql database. See how we are building up a fingerprint of the server technology? As we know more about the server, we can tailor attacks specific to that technology stack.

By asking the right questions to the database we can gain a lot of information about the server, the application and the database itself.

So what request can we make so that the database server reveals the tables in the database? Well, that depends on the type of database server. In this example I'm going to look at a Microsoft SQL server, but the techniques are similar for all systems.

In SQL Server, information about the databases, tables and fields are stored in a system table. We can query this to reveal information about the database structure by manipulating the query.

  1. SELECT * FROM products WHERE productCode = 'AB123'; SELECT * FROM sys.TABLES--'

This could result in a list of tables in the database, but it does depend on the account the database being run on having permissions to the system tables. Quite often developers have little knowledge or understanding of database security policy and will simply grant the database user all rights, or even use the admin superuser account! While it makes things easy for developing, it also makes things easy for attackers.

Now, these attacks rely on explicit attacks where we can see the internal exception messages. They also rely on carefully crafted parameters and a little patience. There are tools such as Havij which will automate the process.

So how do we get the database to reveal the names of tables, and have the UI show them to us?

Let's craft a parameter and see what the SQL looks like.

We know that the sys.tables table has a field called name, so we are going to select the first table.

AB123'; select top 1 name from sys.tables--

Now when run normally, this will show the first table in the sys.tables table. But this does not necessarily mean that the web UI will show this information to use. What we can do however is cause an exception to occur by converting the name to an integer value.

AB123'; select top 1 convert(int, name) from sys.tables--

The SQL now becomes

  1. SELECT * FROM products WHERE productCode = 'AB123'; SELECT top 1 CONVERT(INT, name) FROM sys.TABLES--'

When this is then executed, we cannot convert a string to an integer so the application shows an internal error message "Conversion failed when converting the nvarchar value UserProfiles to data type int".

We can now see that the first table is a table called UserProfiles. Using similar techniques which I won't go into here, we can determine all the tables in the database, their structure and the data within. It is a manual and tedious process, but there are tools which automate the process.

If an application only returns a user friendly message, that is an error message with no internal data, the process is trickier but still possible.

Blind SQL Injection

In this example we are going to manipulate a product search and sort function. Consider the following URL and how it may translate to a SQL statement.


We can see that the category is being set, the order by is set to the price column and sort is ascending.

We can validate this by manually controlling the value of the parameters. For example, changing the value 'price' to 'price1'. This will probably result in an error, which may simply state something like "Sorry, an error has just occurred, please try again or contact us for assistance." This message does not reveal anything about the error, but it does divulge the fact that an error did occur.

So what may this look like in a SQL statement. It's reasonable to assume that the values are being passed directly into a statement, so it may look a little like this.

  1. SELECT * FROM products ORDER BY 'price asc'

We can see here that there are two parameters we have control over, the order by field and the direction. We also know that a SQL error results in an error page, while a successful query results in the page to be shown.

What we can do now, is craft a query to the database which will ask questions that result in a true or false answer. True being a successful page load, false being an error page.

A crafted query may look something like this:

  1. SELECT * FROM products ORDER BY 'price, case when (select count(*) from sys.tables) = 0 then 1 else convert(int, 'x') end-- asc'

This is a very simple blind SQL test. What this is going to do is set the order by on price and then by a value of our choosing. In this case, the test is the number of tables in sys.tables being zero. If this test is true then the value 1 is sorted. If however this test is false we are going to convert the letter x to an integer, which will clearly cause an error. From this we can tell the result by the page loaded - a successful page load or an error message.

Again, this is a tedious and time consuming process, but it can be sped up by using automation tools such as Habij.

Preventing SQL Injection

So we've seen how easy a vulnerable app can be made to divulge internal information and leak sensitive information. Hopefully you can see the risks and dangers involved and how serious a SQL injection attack is. So how can you best protect against it?

There are several techniques you can use:

  • Whitelisting - This works by only allow known good values, for example in the logic you can test that the order by field is one of a known list, and the direction is either asc or desc. If either of these are false then do not construct the query.
  • Blacklisting - This works by blocking known bad values, so you can test to see if any of the parameters contain words like drop, union, select, truncate and so on. These are all common SQL injection attack patterns.
  • Validation - You should check that values coming in are realistic. For example if you are expecting an email address, check to see that the value is a valid email before it gets to the database. This can easily be done by using RegularExpressions.
  • Paramerisation - Will be different from platform to platform, but essentially involves passing untrusted data as a parameter to the database which it then sorts out internally how to work with the values. This prevents each value from being able to break the statement and there is no longer a string concatenation.

You should also lock down the permissions of the user account the database runs over. You do not want that profile to be able to have permissions to anything other than the bare minimum it needs. In theory it is possible for the SQL injection attack to delete tables or shutdown the database server, or go even further and get a remote command prompt where the sky's the limit.

Further Reading
  1. ss

    So here is an idea. Database servers take the incoming SQL query and run it through a parser resulting in a parse tree. Then they turn the tree into a plan and execute the plan.

    The essence of injection is that the parser produces a tree different from the one intended by the programmer.

    So the fix is to be able to detect unusual parse trees. Walk the tree after parsing and produce a string in canonical form minus the data values. Compute a SHA hash of the string. Keep a table of known hashes for the application/database user. Warn or abort if the server sees an unknown hash.

    Obviously, there is a startup problem. So the programmer would have to run the application in a testing mode, extract the hashes after exhaustive testing, and the load the server with the hashes on application startup. Then turn on abort-on-new-hash and no more SQL inject should be possible.

Leave a Reply

Your email address will not be published.