Preventing SQL Injection Using Parameterized Queries

Allison Tharp

2016-01-05

How To

MySQL, vb

SQL Injection is a technique used to attack SQL databases. The general theory behind this technique is that malicious statements are inserted into a field that they were not intended for. It is really easy to write code that is vulnerable to SQL Injection but it is certainly bad practice.

What Not To Do

I have quite a bit of examples of what not to do – my first instinct for coding my gunsmith app was not SQL Injection proof. In that example, I have a GUI to insert information about guns into a firearms database. In the database is a table called customer that stores information on each customer including their name, address, gun license number, etc. Each of these table inputs is a textbox in the GUI, as shown below. Capture The initial code I used for pushing the data from this GUI to the MySQL database is below: [vb] Dim firstname As String = Cfname.Text Dim lastname As String = Clname.Text query = “INSERT INTO customer (firstname, lastname) VALUES (‘“ + firstname + “‘, ‘“ + lastname + “‘);” [/vb] The variable query was pushed to the MySQL database and worked as expected – the text within the textboxes was placed into the correct columns for each individual customer in the customer table. However, what if a user typed something like the following into either of the textboxes? Allison'); DROP DATABASE IF EXISTS firearms; The final MySQL query would be: [SQL] INSERT INTO customer (firstname, lastname) VALUES (‘Allison’); DROP DATABASE IF EXISTS firearms; [/SQL] MySQL would add ‘Allison’ to the firstname column and then proceed to delete the entire database! One of the major concerns when designing a GUI, particularly when someone other than yourself will be using it, is to always scrub the data as much as possible. This means we want to prevent users from making simple mistakes like typos and we want all of our data to be consistent (so if we are dealing with dates, we may choose to have a calendar to have the user pick the date instead of type the date) but we also want to prevent malicious users from manipulating our data. Parameterized Queries are one way to prevent malicious users from manipulating our data.

What To Do

Thankfully, the solution is pretty easy to implement (though it did mean that I had to go through and rewrite the majority of my GUI). There are several options for defending against SQL Injection, but this post focuses on using parameterized queries. The safer way to query the database for the example above would be: [vb] Dim firstname As String = Cfname.Text Dim lastname As String = Clname.Text Dim query As String = “INSERT INTO customer (firstname, lastname) VALUES (@firstname, @lastname)” Dim cmd = New MysqlCommand(query, conn) cmd.Parameters.AddWithValue(“@firstname”, firstname) cmd.Parameters.AddWithValue(“@lastname”,lastname) cmd.ExecuteNonQuery() [/vb] In this way, the query is static so the MySQL database receives the query INSERT INTO customer (firstname, lastname) VALUES (@firstname, @lastname) After the query is received, the variables will be added. The system already has the query that it will execute, so if a user tries to add an additional command to the query by using SQL Injection, the database will output an error. In other words, when we use a parameterized query, we send the intent of the query to the database followed by the variable data. If a user were to try to sneak a different intent to the database through the variable, the database would not accept it. If you want to receive an output from your query to VB, the method for forming the query is slightly different. An example is below: [vb] Dim query As String = “SELECT firstname FROM customer WHERE city LIKE @city” Dim cmd = New MySqlCommand(query, conn) cmd.Parameters.AddWithValue(“@city”, city.text) Dim reader as MySqlDataReader = cmd.ExecuteReader() While reader.Read() Dim my_output As String = reader.GetString(0) End While reader.Close() [/vb] The key to getting an output from MySQL is to use the MySqlDataReader function, particularly in a While reader.Read() loop. This will allow you to get the output from a SELECT statement as a variable in your VB code. Parameterized queries are not the only options for defending SQL Injection. Another option is to use stored procedures. With this method, SQL statements are defined and stored within the database itself and then called from the application. When implemented correctly, this prevents users from inserting SQL commands from the application. Another defense option is to escape all user supplied input. This method is much riskier than the others, so it is not advised. Each DBMS has at least one character escaping scheme for different kinds of queries. If the user input is escaped using the correct scheme for that database and query, the DBMS will understand what is user supplied input and what the intended SQL command is.