Working With Parameters

When you want to pass ad hoc or user supplied values to your SQL command at run time, it is important to use parameters to represent them in order to prevent the possibility of your application being exposed to SQL injection attacks.

What is SQL Injection?

SQL Injection is a technique that results in unauthorised SQL commands being executed against your database. SQL injection occurs usually as a result of taking user input and concatenating it with hard coded SQL statements that form part of an application's code base. The user input has been crafted specifically to alter the SQL that the program's designer intended to execute.

The canonical example used to illustrate the issue involves a login form. Typically, a login form is designed to accept a user name and a password that uniquely identifies an individual. If a valid combination is presented, the user is authorised to access protected parts of the application. A vulnerable SQL statement designed to check that submitted credentials against those stored in the database might look like this:

var sql = "select * from users where username = '" + username + "' and password = '" + password + "'";

The username and password variables represent the values submitted by the user. They are concatenated with the SQL to generate the command that is executed. If those values are "mike" and "pass1" respectively, the generated SQL will be:

select * from users where username = 'mike' and password = 'pass1'

The command, when executed should only return rows if a match for those values is found. However, what happens if the value submitted for the password is ' or ''='? The resulting SQL will become:

select * from users where username = 'mike' and password = '' or ''=''

This completely alters the effect of the SQL so that it returns all rows in the user table, because ''='' is always true. Additional SQL syntax has been injected into the statement to change its behaviour. The single quotes are string delimiters as far as T-SQL is concerned, and if you allow users to enter these without managing them, you are asking for potential trouble.

Often, you will see well-meaning advice from people that you should escape single quotes, which converts them to literal values instead of SQL syntax:

username = username.Replace("'","''");
password = password.Replace("'","''");
var sql = "select * from users where username = '" + username + "' and password = '" + password + "'";

Indeed, this has the desired effect. The generated SQL will no longer return all rows in the database. However, this does not mitigate against all forms of SQL injection. Consider the very common scenario where you are querying the database for an article, product or similar by ID. Typically, the ID is stored as a number - most of them are autogenerated by the database:

var sql = "select * from products where productid = " + productid;

The value for the productid variable could come from a posted form, or a query string value - perhaps from a hyperlink on a previous page. It's easy for a malicious user to amend a query string value.

Imagine that the malicious user appends ;drop table AspNetUsers-- on the end of the query string before requesting the page. Now the generated SQL becomes

select * from products where productid = 1;drop table AspNetUsers--

Any database that can manage batch commands (i.e. any worth using) will execute this and the AspNetUsers table will disappear.

Some people advise that you should validate all user input against a blacklist of SQL keywords and syntax, but the problem with this approach is that there are perfectly valid reasons for users to submit values that might include entries in the blacklist. In addition, you will have to go back and patch your blacklist every time your database provider adds new keywords to their product as they improve its feature set.

Parameters

Parameters are represented in the SQL command by placeholders, and the values are passed to the command within the DbCommand object's Parameters collection. The format of the placeholder is dependant on what the provider supports. The SqlClient provider supports named parameters, with the parameter name prefixed with an @ character. The OleDb provider supports positional parameters. Values are passed to the SQL command based on matching the order in which they have been added to the parameters collection to the order in which placeholders appear in the SQL. Parameter placeholders can be named anything, as long as the placeholder names don't match database object names (columns, tables etc).

Dapper provides support for executing parameterised queries in a number of ways.

Parameters As Anonymous Types

Parameter values can be passed to commands as anonymous types:

var parameters = new { UserName = username, Password = password };
var sql = "select * from users where username = @UserName and password = @Password";
var result = connection.Query(sql, parameters);

DynamicParameters Bag

Dapper also provides a DynamicParameters class, which represents a "bag" of parameter values. You can pass an object to its constructor. Suitable objects include a Dictionary<string, object>:

var dictionary = new Dictionary<string, object>
{
    { "@ProductId", 1 }
};
var parameters = new DynamicParameters(dictionary);
var sql = "select * from products where ProductId = @ProductId";
using (var connection = new SqlConnection(connString))
{
    var product = connection.QuerySingle<Product>(sql, parameters);
}

Or you can pass an anonymous type:

var parameters = new DynamicParameters({ ProductId = 1 });
var sql = "select * from products where ProductId = @ProductId";
using (var connection = new SqlConnection(connString))
{
    var product = connection.QuerySingle<Product>(sql, parameters);
}

Or you can pass a stub representing the object that you are looking to return:

var template = new Product { ProductId = 1 };
var parameters = new DynamicParameters(template);
var sql = "select * from products where ProductId = @ProductId";
using (var connection = new SqlConnection(connString))
{
    var product = connection.QuerySingle<Product>(sql, parameters);
}

The DynamicParameters type provides an Add method, enabling you to pass explicit parameters, specifying the datatype, direction and size:

var parameters = new DynamicParameters();
var customerId = "ALFKI";
parameters.Add("@CustomerId", customerId, DbType.String, ParameterDirection.Input, customerId.Length);
var sql = "select * from customers where CustomerId = @CustomerId";
using (var connection = new SqlConnection(connString))
{
    var customer = connection.QuerySingle<Customer>(sql, parameters);
}

Some ORMs permit the use of format strings or interpolation when building SQL strings dynamically. Dapper doesn't support this approach at all. You should always pass parameter values using one of the approaches above.

Stored Procedures

You can use any of the approaches above to pass parameter values to stored procedures e.g.

var parameters = new DynamicParameters({ 
    Beginning_Date = new DateTime(2017, 1, 1), 
    Ending_Date = new DateTime(2017, 12, 31)
});
var sql = "exec SalesByYear @Beginning_Date, @Ending_Date";
using (var connection = new SqlConnection(connString))
{
    var results = connection.Query(sql, parameters);
}
Last updated: 21/05/2019 13:53:34