• Parameterized SQL Queries in C#

    by  • October 8, 2010 • Programming • 0 Comments

    Introduction

    Parameterizing your SQL queries is a technique that I mentioned I used in my code last post. Now, I’m not expert in SQL (I’m not even sure the UPDATE command I’m going to show is considered a ‘query’), but from I know from my class in software security that using this technique is a great way to protect against SQL injections. I’m not sure if they offer any performance benefits, but they are a lot cleaner to use when you are looping over data as opposed to concatenating strings to generate your query. And as I mentioned before, cleaner code = faster development time, so that’s always a huge plus in my book.

    Example

    Below is some code demonstrating how to use a parameterized query to update some rows in a table. The example code is taken straight out of my project, ReplayParser.NET, thus some of the column names in the query.

    using (SQLiteConnection connection = 
             new SQLiteConnection("Data Source=" + FileName))
    {
      connection.Open();
    
      using (SQLiteCommand command = new SQLiteCommand(connection))
      {
         command.CommandText = "BEGIN TRANSACTION";
         command.ExecuteNonQuery();
    
         command.CommandText = "UPDATE war3replays " +
             "SET Path = $newpath, ReplayName = $newname " +
             "WHERE ReplayName = $oldname";
    
         for (int i = 0; i < oldFilePaths.Count; i++)
         {
            command.Parameters.AddWithValue("$oldname", Path.GetFileName(oldFilePaths[i]));
            command.Parameters.AddWithValue("$newpath", newFilePaths[i]);
            command.Parameters.AddWithValue("$newname", Path.GetFileName(newFilePaths[i]));
            command.ExecuteNonQuery();
         }
    
         command.CommandText = "END TRANSACTION";
         command.ExecuteNonQuery();
      }
    }
    

    Explanation

    On a high level, the query is iterating over all of the replays in the table, and updating both the path and name associated with each row. The parameters ($newpath, $newname, $oldname) can be named anything, but it is a good idea to use some sort of token to differentiate them from the rest of the query. In my code I chose to use a $ to differentiate them simply because I read some other examples doing the same thing.

    Within the for loop is where the real magic happens. Each parameter is replaced with the actual value that is going to be sent to the database. AddWithValue() is what is protecting the query from an injection. Even if a malicious user is able to manipulate the string that is being inserted, the query will still just recognize the malicious input as a string (or any other data type depending on what is being inserted), and it won't be able to change the fundamental purpose of the query. After each parameter has been set, the query is sent to the database (or non-query in this case because no data is returned).

    Lastly, notice that the for loop is surrounded by the commands "BEGIN TRANSACTION" and "END TRANSACTION". If these commands aren't sent, the queries will be treated as individual transaction, and take a MUCH longer time to execute. A good read for the basics on transactions in a SQL database can be found here: http://www.sqlteam.com/article/introduction-to-transactions.

    And that's all there is to parameterized queries. I hope my example helps - if you have any additional questions please leave a comment.




    About

    Leave a Reply

    Your email address will not be published.