SQL Injection Protection and Kentico
Securing websites should be second nature to developers. Ever since the first sites launched, people have been trying to hack them using any means possible. With the proliferation of mobile technologies, machine learning, and nearly limitless computing power at hackers’ disposal, it’s more important than ever to ensure your sites are safe and not vulnerable. In this blog, I’ll cover how to lock down your site from one of the most widely known and utilized attacks: SQL Injection.
There’s nothing new about SQL Injection. Over the last 17+ years of web development, I have encountered this threat time and again on every project. Because the entry point of the attack is usually easy to find, hackers love to exploit this vulnerability and wreak havoc on systems by executing code against your database.
While Kentico’s documentation has a great page dedicated to this type of attack, I wanted to dig into each technique to help you understand where (and how) you can fortify your applications.
You can find details on SQL Injection here.
A SQL Injection Primer
SQL Injection is all about sending code to the backend database and executing it. This is often done through a form or interface that the hacker can access. Another popular route is query strings, which allows the hacker to put whatever they like in the parameter and send it to the URL. Protecting the inroads to your application is essential when thwarting a SQL Injection attack and is your first line of defense.
Basic Protection Tips
While I’m going to discuss some specific techniques and functions, there are a number of basic concepts and tips you can use.
Always validate form values
Wherever you are allowing the user to enter data, you should be validating all input to ensure it’s the format and type you are expecting.
Don’t use query strings to post data
The best protection is to close a vulnerability completely. Query strings are notoriously susceptible to attack, so limiting their use will reduce your exposure and the threat of someone exploding your site.
Don’t use inline SQL in your code
This one should be an automatic one for developers, but if not, here it is again – do not use inline SQL in your code! While this is often the quickest way to execute something, it leaves so many areas open for manipulation and is just not good practice. Instead, leverage stored procedures, query data parameters, and the Kentico APIs.
Leverage the Kentico APIs where possible
Speaking of APIs, Kentico has put a lot of work into protecting their code from SQL Injection attacks. When you use these within your site, you benefit from this protection and lock down your code from attackers.
DataQuery and ObjectQuery
When it comes to securing a Kentico site from SQL Injection, one of the best methods to use is DataQuery and ObjectQuery. These built-in providers automatically strip out SQL Injection nastiness by using the WHERE conditions when you use them to filter your form values and parameters. If the API does encounter some foreign values, they will be sanitized to ensure no malicious code is sent to the server.
Here are some examples of how to use the DataQuery and ObjectQuery APIs.
// DataQuery example
var usersQuery = UserInfoProvider.GetUsers();
var users1 = usersQuery
.WhereEquals("FirstName", txtFirstName.Text)
.Where("Lastname", QueryOperator.Like, txtLastName.Text);
// ObjectQuery exmaple
var users2 = UserInfoProvider.GetUsers()
.WhereEquals("FirstName", txtFirstName.Text)
.Where("Lastname", QueryOperator.Like, txtLastName.Text);
There are a lot more variations of the WHERE conditions in the API. You can check out the other options here.
Query Objects
Sometimes you need to get a little more hands-on with your code, such as creating a query object behind the scenes. When manually executing the query, you can utilize Query Object in the CMS.DataEngine namespace. With this API, you can build up your query and clean the values as you go.
Here are a few examples of how to use Query Objects to validate your data.
//Query objects example
// Builds a WHERE condition for loading documents
string wherecond = new WhereCondition()
.WhereEquals("FirstName", txtFirstName.Text)
.Where("Lastname", QueryOperator.Like, txtLastName.Text).ToString(true);
QueryDataParameters / SQL Parameters
When manually calling a Kentico query or procedure, you can utilize the QueryDataParameter class to add your form values to the call.
// QueryDataParameters example
QueryDataParameters parameters = new QueryDataParameters();
parameters.Add("@fname", txtFirstName.Text);
parameters.Add("@lname", txtLastName.Text);
If you’re not a big fan of using the Kentico APIs (which you should be), you should be implementing standard SQL Injection protection on all your database calls. This includes utilizing stored procedures and SQL parameters. With these calls, you can use the SqlParameter class to ensure your values are passed to your procedures properly.
// SqlParameter exmaple
string connectionString = "[Connectionstring]";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(
"SELECT * FROM CMS_User WHERE LsstName LIKE @LastName", connection))
{
command.Parameters.Add(new SqlParameter("LastName", txtLastName.Text));
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
// ...
}
}
}
Escaping Quotes
If none of the above methods works for you, you can always fall back on the tried-and-true method of escaping quotes. Seasoned .NET developers will remember formatting all of their SQL calls with this method to ensure that any text sent to the DB was wrapped in the correct characters.
To help with this type of protection, the Kentico API has some helpful functions to ensure your values are safe to post. These include the EscapeQuotes and EscapeLikeText in the SQLHelper namespace.
// Escaping equals example
string strWhereEquals = "FiratName = N'" + SqlHelper.EscapeQuotes(txtFirstName.Text) + "'";
// Escaping LIKE example
string strWhereLIKE = "LastName LIKE N'%" + SqlHelper.EscapeLikeText(SqlHelper.EscapeQuotes(txtLastName.Text)) + "%'";
ValidationHelper
One of your last lines of defense includes the ValidationHelper class. Using this built-in collection of functions, you can ensure the proper values are passed to your database. This is especially important when dealing with non-string data types and values.
//ValidationHelper example
string strWhereAge = "Age = " + ValidationHelper.GetInteger(txtAge.Text, 0);
You should be combining the
ValidationHelper methods with the above concepts wherever you are accepting values.
K#
Web parts and controls will often contain a dynamic WHERE condition or other property, populated by an ID passed to the page. This is often accomplished by using K# to get the value and populate the field.
//ValidationHelper example
string strWhereAge = "Age = " + ValidationHelper.GetInteger(txtAge.Text, 0);
In these cases, it’s important to validate the values being passed in. You can use standard K# syntax to validate inputs.
ArticleID = {%ToInt(QueryString.GetValue("ID"), 0)%}
We strongly recommend to use predefined WHERE methods instead of general WHERE method. The reason is that all predefined methods mitigate the SQL injection using the SQL parameters behind the scene.
Wrapping Up
As you can see, Kentico provides a lot of tools for developers to help them make their code secure. Through the use of the API and helper classes, you can lock down every part of your custom code and validate input throughout the site. Good luck!
You can find out a lot of other great SQL Injection protection information in Juraj Komlosi's blog here.