SQL injections

This post is part of the series 'Vulnerabilities'. Be sure to check out the rest of the blog posts of the series!

• Impersonation and security
• SQL injections (this post)
• How to prevent CSRF attacks
• ASP MVC and XSRF
• Cross-site scripting (XSS)
• ASP MVC: Mass Assignment
• Regex - Deny of Service (ReDoS)
• Deserialization can be dangerous
• Prevent Zip bombs in a .NET application
• Prevent Zip Slip in .NET
• How to protect against XML vulnerabilities in .NET

#What's SQL injection?

This is a typical example of a bad code that may lead to SQL injection:

public bool IsValidUser(string username, string password)
{
    string query = "SELECT * FROM [Users] WHERE username = '" + username + "' AND password = '" + password + "'";
    // Execute request
}

Note that the security of the passwords is not covered in this post.

• What happens if the username contains a simple quote?
• What if the user is nastier?

For example, if the user uses the username foo' -- and any password. The SQL command executed is:

SELECT * FROM [Users] WHERE username = 'foo' --' + " AND password = 'bar'

You can see that the password is not take into account. In fact you injected SQL into the command (hence the name of the attack) to modify the query. It is therefore possible to execute any request. For example if the username is foo'; DELETE FROM [Users] this will remove all users from the table. I'll let you imagine all that can be done with this kind of attack.

#How to guard against it?

It's simple: just escape all the characters 😃 But how to do this whatever the DBMS used?

In .NET to run an SQL query on your DBMS (SQL Server, MySQL or other) you use ADO.NET. This access layer is very well done and avoids SQL injections by using so-called parameterized queries. The concept is going to be to replace the concatenations with parameters.

Let us see an example without further delay:

using (IDbConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    IDbCommand command = connection.CreateCommand();

    // Query without string concatenation
    // Instead you have placeholder for the values
    command.CommandText = "SELECT * FROM [Users] WHERE username = @username AND password = @password";

    // First parameter: @username
    IDbDataParameter nameParameter = command.CreateParameter();
    nameParameter.ParameterName = "@username";
    nameParameter.Value = "foo ' --";
    command.Parameters.Add(nameParameter);

    // Second parameter: @password
    IDbDataParameter passwordParameter = command.CreateParameter();
    passwordParameter.ParameterName = "@password";
    passwordParameter.Value = "bar";
    command.Parameters.Add(passwordParameter);

    // Execute query
    command.ExecuteReader();
}

As you can see, there is no longer a string concatenation. Instead, the values ​​to be inserted (username and password) are replaced by parameters (@username and @password). The value of these parameters is then specified by specifying a name and a value. Using this principle ADO.NET escapes the value of the parameters for us and this regardless of the DBMS used.

In conclusion, to avoid SQL injections you must use the parameterized queries.

Do you have a question or a suggestion about this post? Contact me!