14 May 2013
Recently I’ve been working on a library that would allow direct CRUD operations of any object to the database. The library would detect the types and values of the properties of the classes involved then create Insert, Update, GetAll, and GetById procedures depending on what they find.
It was working great and I was happy with the result. However when I showed it to a friend he pointed out that there might be security concern for building such queries. I researched about it a little and I found he was correct: my code was vulnerable to SQL injection attacks.
A SQL injection attack is an attempt to pass customized queries to the database through exposed fields, such as textboxes. For example, consider the following code that forms part of a query template:
string query = "INSERT INTO Employees ( Name ) VALUES ( " + txtName.Text + " )";
It’s a simple query that inserts into a single column of a table, with the input coming from a textbox.
Now imagine that the user inputs the following into the textbox:
'' ) DELETE FROM Employees --
The resulting query would now look like this:
INSERT INTO Employees ( Name ) VALUES ( '' ) DELETE FROM Employees -- )
You can see that instead of inserting into the table, the query now deletes the entire table! This is very, very bad. Aside from this kind of attack (deleting), an attacker can also cause other sorts of trouble such as inserting malicious information or retrieving sensitive data.
The most common ways to prevent SQL injection attacks are using stored procedures and using parameterized queries.
Using stored procedures forces you to use parameters. These parameters can be checked for malicious script, either in the procedure itself or by the framework. The principle is the same for using parameterized queries: the value gets wrapped in a SqlParameter object and can be cleansed for malicious scripts.
Here is a parameterized version of the query above:
string query = "INSERT INTO Employees ( Name ) VALUES ( @Name )"; SqlCommand cmd = new SqlCommand(query, connection); // 'connection' is an open connection cmd.Parameters.AddWithValue("@Name", txtName.Text);
Using such parameterized queries or stored procedures will definitely make your application more secure.