What is SQL injection - With Examples & Prevention

SQL Injection is a prevalent and dangerous risk to any web application and leads to sensitive information leakage or malware being served.

SQL Injection (SQLi) 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 basic SQL Injection attack may be performed on a vulnerable website.

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

http://www.example.com/products.php?prodId=1

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. It's not inconceivable to imagine that a query would be run similar to the one below to get product details to show on the page.

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:

http://www.example.com/products.php?prodId=1 or 1=1

The SQL statement then becomes

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 attacks.

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 a 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 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.

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.

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 at the manipulated parameter.

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 queries to run. Now, we know we can close off a string, we can add our 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 user's table, and then everything else after is commented out with a double hyphen. This gets inserted into the SQL statement so it becomes:

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.

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 is stored in a system table. We can query this to reveal information about the database structure by manipulating the query.

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 is 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 running 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

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.

products.php?category=4152&orderBy=price&dir=asc

We can see that the category is being set, the order by is set to the price column and the 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.

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 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:

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 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 allowing 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 is 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 - This will be different from platform to platform, but essentially involves passing untrusted data as a parameter to the database which 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 permission to do anything other than the bare minimum it needs. In theory, the SQL injection attack can delete tables or shut down the database server, or go even further and get a remote command prompt where the sky's the limit.

Preventing SQL Injection Attacks in MS SQL Server

Using parameterized queries rather than directly inserting user input in a query string is the best technique to prevent SQL injection.

DECLARE @PageNameValue NVARCHAR(128)
DECLARE @SQL_QUERY NVARCHAR (MAX)
DECLARE @PARAMS NVARCHAR (1000)
SET @PageNameValue = 'Test Page" OR 1 = 1'
    
SET @PARAMS = '@PageName NVARCHAR(128)'
SET @SQL_QUERY =N'SELECT * FROM pages WHERE pagename = @PageName'
    
EXECUTE sp_executesql @SQL_QUERY ,@PARAMS, @PageName = @PageNameValue

In the example, the first query will output all rows in the table, whilst the second uses parameterised SQL and won't return any results because no page name exists for that parameter value.

In ADO.Net (C# and VB.Net) we can do a similar thing.

SqlCommand cmd = new SqlCommand("SELECT * FROM pages WHERE pagename = @PageName)", connect);
cmd.Parameters.Add("@PageName", SqlDbType.NVarChar).Value = Request.QueryString["pagename"].ToString();
cmd.ExecuteQuery();

Query composition is allowed in LINQ to Entities, however, it is done via the object model API. LINQ to Entities queries, unlike Entity SQL queries, are not built using string manipulation or concatenation, and they are not vulnerable to classic SQL injection threats.

Preventing SQL Injection Attacks in mySQL and PHP

There are several solutions to SQL injection in PHP including magic strings, escape and input sanitation, however, these all have their problems and introduce new ones. The best solution is to use the built-in functions to prepare your SQL query.

From PHP version 5 onwards there is support for MySql prepared statements using the mysqli library. This preparation is a function of the class, which will automatically escape and protect your SQL queries.

$db_connection = new mysqli("localhost", "user", "pass", "db");
$statement = $db_connection->prepare("SELECT pageTitle FROM pages WHERE pagename = ?");
$statement->bind_param("s", $reqPage);
$statement->execute();

So in this example in the SQL statement, each input parameter is represented by a ? character. We then provide a list of format characters, followed by a list of variables to bind them to. In this example the ? is of type s(tring) and bound to $reqPage. Other format characters include i(nteger), d(ecimal) and b(lob).

You can bind multiple parameters in the same way, on the same call.

$db_connection = new mysqli("localhost", "user", "pass", "db");
$statement = $db_connection->prepare("SELECT pageTitle FROM pages WHERE pagename = ? AND status = ? AND score - ?");
$statement->bind_param("sid", $pageTitle, $intStatus, $floatScore);
$statement->execute();

WordPress versions greater than 2.6 have an integrated prepare function to protect against injection attacks and feature a string format style parameter.

$query = $wpdb->prepare("SELECT pageTitle FROM pages WHERE pagename = %s", $reqPage);
$result = $wpdb->get_var($query);

Most other database providers provide a prepare function; please refer to the documentation for your product for exact usage.