In code behind, we have this code:
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:
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:
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,
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:
We recommend that you avoid making your own direct query calls and that you retrieve system objects using providers or our strongly typed collections.
The second parameter of ExecuteQuery() is a QueryDataParameters object. To fill this parameter, create an appropriate object, for example:
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:
Parameters are used in Kentico CMS mainly in the INSERT, UPDATE and DELETE query types, and also in stored procedures.
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:
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:
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:
You can also use the QueryHelper.GetSQLSafeText() method to get query strings for SQL.
- 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).
- When you work with other than string types, always convert data types to that type or validate the value via regular expressions.