Note: This guide describes Kentico CMS version 7. Unfortunately, we cannot support this guide from version 8 forward. Go to latest documentation

Skip to end of metadata
Go to start of metadata

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" /> 
On this page

In code behind, we have this code:

protected void btnSearch_Click(object sender, EventArgs e)
{
	DataSet ds = UserInfoProvider.GetUsers("UserName LIKE '%" + txtUserName.Text + "%'", null);
	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 CMS

There are many ways of protection against SQL injection. In Kentico CMS we use SQL parameters and apostrophe escaping. Both these methods have advantages and disadvantages and we use them in different situations.

Kentico CMS 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 GeneralConnection.ExecuteQuery() method is the most frequently used method for executing SQL queries in Kentico CMS. This method has many overloads. We will use the following overload:

ExecuteQuery(string queryName, QueryDataParameters parameters, string where, string orderBy, int topN, string columns)

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

SQL Parameters

The second parameter of ExecuteQuery() is a QueryDataParameters object. 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. An important fact is that the value is treated as literal. It means that even if your value contains a piece of SQL code, the SQL server doesn't execute it.

Parameters are almost 100% secure. But if you build a query, which is executed with the built-in exec() function, the parameters are processed the standard way (the query is executed with them, even if they contain malicious SQL code). This typical example shows how not to do it:

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

Parameters are used in Kentico CMS mainly in the INSERT, UPDATE and DELETE query types, and also in stored procedures.

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 CMS, we have a dedicated method for this purpose – SqlHelperClass.GetSafeQueryString(). 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:

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

Wrong
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 SqlHelperClass.GetSafeQueryString() 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.