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 taken into account. By injecting SQL into the command (hence the name of the attack), the query is modified. It is therefore possible to execute arbitrary statements. For example, if the username is foo'; DELETE FROM [Users], this will delete all users from the table. The potential damage from this type of attack is significant.

#How to guard against it?

The solution is to escape all characters properly, but how do you achieve this regardless of the DBMS used?

In .NET, to run an SQL query against a DBMS (SQL Server, MySQL, or others), you use ADO.NET. This layer supports parameterized queries, which prevent SQL injections by replacing string concatenations with typed parameters.

Here is an example:

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 string concatenation. Instead, the values to be inserted (username and password) are replaced by named parameters (@username and @password). Each parameter is defined with a name and a value. ADO.NET then escapes the parameter values automatically, 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!