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:
- Escaping of apostrophe characters (') – typically when working with String data type values
- 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%}
| 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.