Proper Etiquette for using MySQL in C# (Part of 2 of 3) – Parameterized Queries

In the first installment of this three part series I covered why properly managing resources related to database activity is so important. In this second part, I will discuss how using parameterized queries (also known as prepared statements) will improve the performance as well as increase the security of your application. Again the examples are written using MySQL Connector/NET, but the concepts and principles should ultimately be applicable to the other ADO.NET Data Providers such Microsoft SQL Server, SQLite, Oracle, etc. – The code snippets presented here should be minimally different if one was using one of the other ADO.NET drivers.

For those who wish to follow along with this series – here is the layout:

1. Use ‘using statements’ on your disposable objects which interface with the database.
2. Parameterize your queries (aka Prepared Statements)
3. Ensure that your Data Reader code is readable

* Note that parameterized queries and prepared statements are referring to the same thing.

So what is a parameterized query/ prepared statement exactly? A parameterized query is a template SQL statement in which variables are plugged into the statement prior to executing the query. Typically a parameterized query is constructed in the following way – The query is first “prepared” by having the application construct the statement template where certain parameters (also known as bind variables) are left unspecified and then the statement template is sent to the RDBMS. The RDBMS then parses, compiles, and performs query optimization on the statement template and then the result is stored without any execution since there are no values currently supplied to the parameters. The application then binds values for the parameters in the template statement and the RDBMS then executes the statement. It is worth noting that the application may execute the statement as many times as it desires (binding new values to the parameters each time).

What you get in exchange for using parameterized queries are two things: better performance and better security.

You get better performance because the overhead of having the database engine compile and optimize the SQL statement only needs to happen once. So if you are inserting multiple rows, then you are saving some computational cost. As a disclaimer not all query optimization can occur when the parameterized query is compiled – During execution additional query optimization may be applied due the specific parameter values or over time the best query plan may change due to the changing landscape of the database such as changing tables and indexes. Now this may seem like a micro-optimization and you may be thinking back to that famous Donald Knuth quote: “Premature optimization is the root of all evil.” Well the security holes created by not using paramertized queries are the biggest reason to use parameterized SQL.

The gains in security come from protecting your software from SQL injection attacks. SQL injection occurs when SQL code is injected into a SQL command. This is done by using an entry field to supply SQL code which will be run alongside the intended SQL command. SQL injection attacks are considered to be among the top web application vulnerabilities. The heart of the SQL injection attack issue is mixing SQL code with data. Parameterized queries protect your software from SQL injection by first sending the template SQL statement to the database server, then in a separate request we send the data to the database server for binding values to the parameters of the template SQL statement. In this way the data can only be interpreted as just data of a given type and never as executable SQL code.

SQL injection attack example:

Say we have two tables in our database “user_account” and “list_of_fears” which support a fictional application which allows users to write down their fears. Here are the two tables…

Now for simplicity and to focus on the point of the exercise, the log in functionality is controlled via a RadioButton. The application also has no add functionality and just an option that allows the user (Hank or Frank) to search their fears, perhaps in order to confront them. To easily showcase the insecurity caused by unparameterized queries and how parameterized queries protect us from SQL injection attacks – there is also an option to switch between each. Note that this example application is available for download at the end of this article.

Now Hank has logged onto the fictional FearWriter application and decides to be a naughty boy – He plays around with the option that allows him to search through his fears and realizes that it must be checking to see if the text he inputs is contained in the description of any of his fears. So he constructs an injection attack – he submits “%’ OR 1=1 OR ‘%” into the search box and just like that he has access to the personal information of other user accounts. Not only is this a violation of your user’s privacy, there is also the chance that this leaked information could seriously hurt your users (imagine if this was banking information!).

Here is the code which allowed this event to occur:

private List<string> GetFearsUnparameterized(int user_account_id, string fearsContains)
{
    var fears = new List<string>();
    using (var conn = new MySqlConnection(ConnectionString))
    {
        conn.Open();
        using (MySqlCommand cmd = conn.CreateCommand())
        {
            // Please DO NOT write your MySQL queries this way!!
            // This is just an example of WHAT NOT TO DO...
            cmd.CommandText = 
                 "SELECT fear_description FROM list_of_fears WHERE user_account_id =" + 
                 user_account_id + " AND fear_description LIKE '%" + fearsContains + "%'";
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    fears.Add(reader.GetString("fear_description"));
                }
            }
        }
    }
    return fears;
}

Now say we are in an alternate universe where the developers of the FearWriter application used parameterized queries. Would Hank’s attack have been successful? The answer is no – it would not be. When the SQL query is parameterized, the contents of the search box are treated as just text. There is no possibility that the contents of the search box will be treated as SQL code and instead are always treated as just data. Hank’s attack ends up just being a query which checks to see if any of Hank’s fears contain the string “%’ OR 1=1 OR ‘%” in their description (none of them do – hence the empty result).

Below is the code which prevented the SQL injection attack:

private List<string> GetFearsParameterized(int user_account_id, string fearsContains)
{
    var fears = new List<string>();
    using (var conn = new MySqlConnection(ConnectionString))
    {
        conn.Open();
        using (MySqlCommand cmd = conn.CreateCommand())
        {
            // Use parameterized queries (prepared statements) like this...
            cmd.CommandText = 
                "SELECT fear_description FROM list_of_fears WHERE " +
                "user_account_id = @user_account_id AND fear_description LIKE @fearsContains";
            cmd.Parameters.AddWithValue("@user_account_id", user_account_id);
            cmd.Parameters.AddWithValue("@fearsContains", string.Format("%{0}%", fearsContains));
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    fears.Add(reader.GetString("fear_description"));
                }
            }
        }
    }
    return fears;
}

Not only do parameterized queries offer potential performance benefits, they more importantly protect you from the huge security threat posed by SQL injection attacks. Also in my personal opinion, parameterizing the SQL command makes the application code much more readable. So given this, I firmly believe that everyone should parameterize their SQL. If you see someone passing in data into their SQL and they are not parameterizing it, be a pal and show them the way to a prepared paradise.

You may download the source of the SQL injection attack example application used in this article here.