This post is part of the series 'Vulnerabilities'. Be sure to check out the rest of the blog posts of the series!
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 more nasty?
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.
It's simple: just escape all the characters 😃 But how to do this whatever the DBMS used?
In dotnet 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. In fact the concept is going to be to replace the concatenations by parameters.
Let us see an example without further delay:
using (IDbConnection connection = new SqlConnection(connectionString))
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 ' --";
// Second parameter: @password
IDbDataParameter passwordParameter = command.CreateParameter();
passwordParameter.ParameterName = "@password";
passwordParameter.Value = "bar";
// Execute query
As you can see, there is no longer a string concatenation. Instead, the values to be inserted (username and password) are replaced by parameters (
@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 on Twitter or by email!