SQL injection

SQL injection is a well known web application vulnerability. The attacker’s aim is to execute his own SQL code on the victim’s database through a web application. How can the attacker do that? The attack is similar to XSS. The attacker inserts a special string into the web application via a form or a URL parameter. If that string is used as a dynamic part of an SQL query (e.g., a part of a WHERE condition) and not protected properly, the attacker can inject a query before it is executed.

We can divide vulnerabilities into two kinds – classic and blind:

• Classic SQL injection – the attacker can see the real error message from the SQL server.
• Blind SQL injection – the attacker sees only a general error page. These injections are harder to exploit because the attackers do not know how exactly they can inject the code. The attackers usually use enumeration in this type of attack. Then, according to the time based errors or the displayed error message can they determine which of their queries have passed and which did not.

Example of SQL injection

We have a simple web page with a textbox and a button which is used for searching users:

<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="Search" onclick="btnSearch_Click" /> 

In code behind, we have this code:

using System.Data;
using CMS.Membership;
using CMS.Helpers;

protected void btnSearch_Click(object sender, EventArgs e)
{
    DataSet ds = UserInfoProvider.GetUsers().Where("UserName LIKE '%" + txtUserName.Text + "%'");
    if (!DataHelper.DataSourceIsEmpty(ds))
    {
        Response.Write(ds.Tables[0].Rows[0]["FullName"]);
    }
} 

Now if a user inserts something like “admin”, the user gets the full name of the global administrator on the output. The SQL query looks like this:

SELECT * FROM CMS_User WHERE UserName LIKE '%admin%' 

This is a correct query which doesn’t cause any problems. But if the user inserts something like “a’; DROP table CMS_User --”, the resulting query is:

SELECT * FROM CMS_User WHERE UserName LIKE 'a%'; DROP table CMS_User --%'

The query is executed, resulting in a deleted table.

What can SQL injection attack do

With an SQL injection vulnerability, the attacker can do exactly the same operations with the database as the web application itself. For example, the attacker can read all data or the database schema, change it, edit it, etc. Also, T-SQL supports the xp_cmdshell() function, which executes operating system commands. So, the attacker can basically manage the whole server.

Finding SQL injection vulnerabilities

The first technique is based on trying. Insert strings like:

• ’whatever – basic test,
• DROP,
• something,

to all inputs/URL parameters/whatever. Do not test only the apostrophe character (in the next chapter, you will see that you can exploit an application even without the apostrophe character).

The second way is to search for vulnerabilities in code. You can search for methods executing SQL queries. Then, you can check variables which are inputs of these methods. The aim of checking is searching for protection against SQL injection.

You can also use automatic tools. There are two types of them. The first is based on trying – it simply runs an application, tries to insert some payloads to output and checks the application’s reaction. The second type is based on searching for patterns (via regular expressions) in code. These automatic tools are very inaccurate and we do not recommend using them.

Avoiding SQL injection in Kentico

There are many ways of protection against SQL injection. In Kentico we use:

• SQL Parameters
• Apostrophe escaping

Both these methods have advantages and disadvantages and we use them in different situations.

SQL Parameters

Kentico has its architecture divided into layers. One of them is the data layer, which provides operations for manipulation with database data. This includes executing SQL queries. The system uses the following method and objects for executing SQL queries:

• ExecuteQuery()
• ObjectQuery and DataQuery

See Working with database queries in the API for examples.

We recommend that you avoid making your own direct query calls and that you retrieve system objects using providers or our strongly typed collections.

Use the QueryDataParameters object in WHERE conditions of INSERT, UPDATE and DELETE query types and in stored procedures. To fill this parameter, create an appropriate object, for example:

QueryDataParameters parameters = new QueryDataParameters();
parameters.Add("@param", param);

SQL server simply replaces @param with your value. The value is treated as literal, which means that even if your value contains a piece of SQL code, the SQL server does not execute the code.

CREATE PROCEDURE injection( @param varchar(30) )
AS
SET NOCOUNT ON
DECLARE @query VARCHAR(100)
SET @query = 'SELECT * FROM ' + @param
exec(@query)
GO 

Apostrophe escaping

The second protection technique is replacing the dangerous apostrophe character with an escape sequence of two apostrophes. In code, you often build WHERE conditions for SELECT queries. When a part of a condition is a dynamically obtained string (e.g., from the database, input by a user, etc.), you must enclose it with apostrophes and perform replacing.

In Kentico, we have a dedicated method for this purpose – SqlHelper.GetSafeQueryString(string input). This method also protects the database against DoS attacks.

This is a correct solution, but only for string values. Never use this method for other types than strings. For example:

Guid guid = ... ;
string where = "SomeGUID = '" + guid.ToString().Replace("'", "''") + "'";

In this example, replacing is unnecessary because class Guid can only contain letters and numbers in a specific format. So, this code is secured without any protection.

A worse situation can occur when you believe that you are using non-string data types (for example int), but the variable you are actually using is a string:

string id = ... ;
string where = "SomeID = " + id.Replace("'", "''");

There are two reasons why this code is wrong. First of all, you don’t need the apostrophe character to drop a table. In the previous examples, you needed an apostrophe to inject a string constant inside an SQL command, but there are no enclosing apostrophes here. You may think that the attacker cannot use WHERE conditions because you escape apostrophes. And that’s the second problem. In SQL, there is the Char() function which converts numeric values to their ASCII representations. And these ASCII letters can be concatenated, so the attacker can write anything into the query.

You can solve these situations by adding enclosing characters or by converting values to correct data types. Always use the second choice (the first one causes performance issues). A correct piece of code should look like this:

string id = ... ;
string where = "SomeID = " + ValidationHelper.GetInteger(id, 0) ;

You can also use the QueryHelper.GetSQLSafeText() method to get query strings for SQL.

Summary

• Protect dynamic parts in INSERT, UPDATE and DELETE queries with SQL parameters.
• Don’t ever use the exec() function in your SQL code.
• When you build a SELECT query in code, all used strings taken from external sources must be protected with the SqlHelper.GetSafeQueryString(string input) method or use SQL parameters.
• Always escape values from array(list, …) when you are getting them and putting them into a string (typically in foreach loops).
• Never rely on JavaScript validation. JavaScript is executed on the client side so the attacker can disable validation.
• When you work with other than string types, always convert data types to that type or validate the value via regular expressions.