Security - Avoiding SQL Injection (SQLi)

   —   

SQL injection is one of the most dangerous threats against web applications. Therefore, it is really important to pay extra attention to avoiding SQL injection. Every successful attack may compromise sensitive data or lead to privilege escalation.

If you have never heard about SQL injection, you may find the OWASP – SQL injection guide helpful.  In my article, you will learn how to avoid SQL injection in Kentico 8.1:

  • in the Kentico API,
  • in macros.

What are the general approaches used to mitigate SQL injection?

Basically you can use two approaches to mitigate SQL injection vulnerability:

  1. Escaping of apostrophe characters (') – typically when working with String data type values
  2. Converting values to the appropriate data type – typically when working with other data types than String (for instance Integer, Double, Boolean). These data types cannot contain apostrophes.

Common rule

There are some scenarios where escaping characters may become useless. Have a look at this code snippet:

string userId = hdnUser.Value; string where = "UserID = " + userId;

 If such a where condition is a part of an SQL query, the executed SQL query may look like this one:
SELECT Username FROM CMS_User WHERE UserID = 120

The UserID value is taken from a hidden field placed on the page. If an attacker injects - 120; DELETE FROM <table_name> - into the hidden field value, all records from the table will be deleted. Remember one easy rule - convert values to the appropriate data type. The solution for the code snippet above is:
string where = "UserID = " + ValidationHelper.GetInteger(userId, 0);

If the attacker injects - 120; DELETE FROM <table_name> - into the hidden field value,  the following query will be executed:
SELECT UserName FROM CMS_User WHERE UserID = 0
 
The default value - 0 - is returned instead of the malicious part of the SQL query.
 

Kentico API

The best practice for avoiding SQL injection in Kentico 8.1 is to use Kentico API provider methods. When you decide to go this way, you don’t have to escape special characters in user inputs. Escaping of malicious inputs is done behind the scenes using parameterized queries. All Kentico API providers execute parameterized data queries in order to avoid SQL injection. Let’s have a look at this example:
Code:

using CMS.Membership; ... String avatarName = txtAvatarname.Text; // Loads avatar objects whose "AvatarName" column starts with a specific name ObjectQuery<AvatarInfo> avatars = AvatarInfoProvider.GetAvatars().WhereStartsWith("AvatarName", avatarName);

 The code snippet shows how to load avatars based on a specified avatar name. Please notice that there is a text box control – txtAvatarName – whose value is used in the Where statement. In this case you do not have to escape the text box value even though it is a user input and can contain malicious code. The AvatarName is taken as an SQL query parameter and the query is executed safely. That’s the biggest advantage of using DataQuery. The full list of available Where methods can be found in IWhereCondition reference. When creating WHERE conditions, we strongly recommend using specific Where methods for the given purpose, such as:

  • WhereContains
  • WhereNull
  • WhereEquals
  • WhereStartsWith

Apostrophe escaping

If you do not want to use DataQuery for some reason, there is another way to avoid SQL injection. The Kentico API provides methods you can use to escape malicious SQL characters. You may face a situation where you need to build a where condition manually. In this case, be very careful and escape every user input properly. I will show you how to use the mentioned methods.
 
Scenario 1 – Escaping values in equals query patterns
 

Class: SqlHelper
Method: EscapeQuotes(string text)
Usage: Use this method if you want to escape values in equals query patterns. This method escapes single quotes, but does not escape SQL LIKE wildcards.

 
Example:

string avatarName = txtAvatarname.Text; string where = "AvatarName = '" + SqlHelper.EscapeQuotes(avatarName) + "'";

Usage: SqlHelper.EscapeQuotes(avatarName)
Input: myAvatar'; DELETE FROM <table_name> --
SQL query: ... WHERE AvatarName = 'myAvatar''; DELETE FROM <table_name> --'

 
 Scenario 2 – Escaping values in LIKE query patterns
 
In SQL Server, some characters called wildcards have special meaning, such as the percentage (%), the underscore (_) and braces ( [, ] ). You can search for character strings that include one or more of the special wildcard characters.
For example, you want to get all blog comments containing the string ‘25% off’.
The SQL query:

SELECT CommentText FROM Blog_Comment WHERE CommentText LIKE '%25% off%'
returns all comments containing the value – ‘…25<arbitrary text> off…’.

An SQL Query with escaped wildcards:
SELECT CommentText FROM Blog_Comment WHERE CommentText LIKE '%25[%] off%'
returns only comments containing the value – ‘…25% off…’
 

Class: SqlHelper
Methods: EscapeQuotes(string text) – escapes single SQL quotes
EscapeLikeText(string text) – escapes wildcards used in SQL LIKE syntax
Usage: Use a combination of these methods if you want to escape values in LIKE query patterns.


Example:

string avatarName = txtAvatarname.Text; string where = "AvatarName LIKE '%" + SqlHelper.EscapeLikeText(SqlHelper.EscapeQuotes(avatarName)) + "%'";

Usage: SqlHelper.EscapeLikeText(SqlHelper.EscapeQuotes(avatarName))
Input: my_Avatar'; DELETE FROM <table_name> --
SQL query: ... WHERE AvatarName LIKE 'my[_]Avatar''; DELETE FROM <table_name> --'

 

Macros in web part properties

During website development you often face situations where using macros in web part properties is the best way to get data dynamically. By default, Kentico protects web part properties with values that are part of SQL queries. For example, WHERE and ORDER BY properties of all web parts are protected against SQL injection if they contain macros. All macro values are resolved and escaped automatically to mitigate SQL injection vulnerability.
In your custom web parts, you need to handle SQL injection manually. Kentico provides several options how to secure macros against SQL injection:

  • SQLProperties property in your web part code
  • Macro parameter
  • Macro escaping method
  • Macro data converting methods

SQLProperties property

The SQLProperties property defines a list of web part properties that should be escaped for SQL injection. By default it works for all "Where condition" and "Order by" web part properties. If you wish to enable SQL escaping for additional web part properties, you can enter web part property names into the value separated by semicolons. Let’s say you have additional web part properties - CustomWHERE and CustomORDERBY. To enable SQL escaping, add the following line of code into the SetupControl() method:

SQLProperties = "CustomWHERE;CustomORDERBY";

Now if one of the mentioned web part properties contains a macro, for instance {%QueryString.Username%} - a macro that takes the value from the query string parameter username, the value will be automatically escaped.

Example:
Vulnerable web part property

SQLProperties value: ""
CustomWHERE web part property value: Username = '{%QueryString.Username%}'
Malicious query input: andy'; DELETE FROM <table_name> --
SQL query: ... WHERE Username = 'andy'; DELETE FROM <table_name> --'
Result: All data from table <table_name> will be deleted.

 
Protected web part property

SQLProperties value: "CustomWHERE"
CustomWHERE web part property value: Username = '{%QueryString.Username%}'
Malicious query input: andy'; DELETE FROM <table_name> --
SQL query: ... WHERE Username = 'andy''; DELETE FROM <table_name> --'
Result: No data will be deleted.

 
Notice the escaping of single quote characters, which mitigates the SQL injection vulnerability.

Macro parameter

To avoid SQL injection in macros, you can use a macro parameter - handlesqlinjection. The Handlesqlinjection macro parameter is automatically enabled in all web part properties defined in the SQLProperties property. Have a look at the following examples. Consider a custom web part with custom properties customWHERE and customORDERBY:
Web part property value -> {%QueryString.Username%} -> vulnerable to SQL injection
Web part property value -> {%QueryString.Username|(handlesqlinjection)true%} -> protected against SQL injection
Be careful if you decide to go this way. You have to use the mentioned macro parameter in all macros used in "unsafe" web part properties.

Example:
Vulnerable web part property

Web part property value: Username = '{%QueryString.Username%}'
Malicious query input: andy'; DELETE FROM <table_name> --
SQL query: ... WHERE Username = 'andy'; DELETE FROM <table_name> --'
Result: All data from table <table_name> will be deleted.

 
Protected web part property

Web part property value: Username = '{%QueryString.Username|(handlesqlinjection)true%}'
Malicious query input: andy'; DELETE FROM <table_name> --
SQL query: ... WHERE Username = 'andy''; DELETE FROM <table_name> --'
Result: No data will be deleted.

 

Macro escaping method

Another way to secure macros in web part properties is a special macro method - SQLEscape(string input). This method escapes single quotes in string values.

Web part property value -> {%QueryString.Username%} -> vulnerable to SQL injection
Web part property value -> {%SQLEscape(QueryString.Username)%} -> protected against SQL injection

Example:
Unprotected web part property

Web part property value: Username = '{%QueryString.Username%}'
Malicious query input: andy'; DELETE FROM <table_name> --
SQL query: ... WHERE Username = 'andy'; DELETE FROM <table_name> --'
Result: All data from table <table_name> will be deleted.

 
Protected web part property

Web part property value: Username = '{% SQLEscape(QueryString.Username)%}'
Malicious query input: andy'; DELETE FROM <table_name> --
SQL query: ... WHERE Username = 'andy''; DELETE FROM <table_name> --'
Result: No data will be deleted.

 

Macro data converting methods

I would like to mention the common rule once more. If you expect a different data type than string, you should convert the input value to the appropriate data type. You will face SQL injection vulnerability if your custom web part property - customWHERE - contains a value like DocumentID = {%QueryString.DocumentID%}.

Example:
Unprotected web part property

Web part property value: DocumentID = {%QueryString.DocumentID%}
Malicious query input: 10; DELETE FROM <table_name>
SQL query: ... WHERE DocumentID = 10; DELETE FROM <table_name>
Result: All data from table <table_name> will be deleted.

 
Protected web part property

Web part property value: DocumentID = {%ToInt(QueryString.DocumentID, 0)%}
Malicious query input: 10; DELETE FROM <table_name>
SQL query: ... WHERE DocumentID = 0
Result: No data will be deleted.

 
This example shows that if you convert user input to an integer value, any other data type values are not accepted. The macro evaluation result is the default value - 0.

Summary

Finally, let me point out which user inputs you should be aware of:

  • GET parameters
  • POST parameters
  • Hidden fields
  • Cookies

For all the mentioned inputs, follow the rules:

  • String values – avoid SQL injection by using one of the options mentioned in this article
  • Other data type values – convert to the appropriate data type

Keep in mind that data validation does not occur when values are saved into the database. The key point is to sanitize data when you use it as part of an SQL query.

I’ll appreciate if you post your comments and questions using the form below.

Share this article on   LinkedIn Google+

Juraj Komlosi

Hi, I am Security Specialist and my blog posts will be primary focused on web application security.

Comments

Juraj Komlosi commented on

Hi Shamcs,

thank you for your feedback. "WHERE" and "ORDER BY" web part properties are protected against SQL injection by default in all Kentico web parts. It means that the macro result is properly escaped. You don't have to use SQLEscape method because it will lead to escaping the apostrophes twice.

Simply said, the WHERE condition - Where DocumentName='{%QueryString.DocumentName%}' - will transform your input - My Document's With Single Quote - into the output - My Document''s With Single Quote. And that's valid SQL syntax and it is properly escaped. There shouldn't be any errors.

Macro code doesn't support parametrized query. If it will not solve your issue, you can send me an e-mail (jurajk[at]kentico.com) and we can figure out what's wrong.

Thank you.

Shamcs commented on

How to avoid sql injection if i really want to search by DocumentName and the document name have single quotes.

Example : Where DocumentName='My Document's With Single Quote'

so this code {% SQLEscape(QueryString.DocumentName)%} will throw an error .

Is there anyway i can use the basic parameterize in Macro code like :

param.Add(new DataParameter("@DocumentName", DocumentName)); ?

anyway, good article.