Protect Your Data: Prevent SQL Injection

For a few years, since I joined different technical community forums, I always see code provided by the thread starter which is prone to SQL injection attacks. 

I know there are gazillions of articles that highlight the prevention of SQL Injection attacks but still vulnerable codes exist everywhere in various forums or even in the articles and blogs. I think some of the main reasons for this are:

1. Experienced developers keep providing those vulnerable codes which beginners will follow.
2. They don’t understand what they are doing (code).
3. They don’t mind about the code as long as it works for them.
4. They are afraid to learn the right way because they might break their existing “working” code.
5. They are just lazy.

For beginners, if you have been redirected to this article then you must have done  something wrong with your code. This article will cover a few examples of how vulnerable code can break your data and how you can prevent it.

What is SQL Injection?

According to  documentation, SQL injection is a code injection technique which is used to attack data-driven applications where nefarious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker). These attacks allow attackers to spoof identity, tamper with existing data, cause repudiation issues such as voiding transactions or changing balances, allow the complete disclosure of all data on the system, destroy the data or make it otherwise unavailable and become administrators of the database server.

Uh… What?

If that doesn’t make sense to you then let’s take a look at an example in ASP.NET. Suppose we have the following table data:


                     Figure 1 - Sample Data

Example 1

Let’s assume that you want to search for some values in the column name “Field1” and then display the result in a data control like GridView. In most cases, you will see the following code below that will help to retrieve some records from a SQL database:

protected void btnSearch_Click(object sender, EventArgs e) {
            SqlConnection conn = new SqlConnection(@"Data Source=ServerName\SQLEXPRESS;Initial Catalog=DemoDB;Integrated Security=SSPI;");
            SqlCommand cmd = new SqlCommand("Select * from GridViewDynamicData where Field1= '" + txtSearch.Text +"'", conn);
            conn.Open();
            SqlDataAdapter ad = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            if(dt.Rows.Count > 0)
            {
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }

            conn.Close();
}

The code above is commonly used to search for a database record based from a TextBox value. It uses ADO.NET to connect to the database, execute SQL and extract data from SQL Server database. The result will then be filled into a DataTable and then binds it to your GridView. At runtime, the value entered by the user is merged dynamically with the SQL string to create a valid SQL command as shown in the figure below:


                              Figure 2 - Showing SQL Command text

As you have seen in the text visualizer above, the value “Test 1” supplied by the user was merged with the core SQL to complete the command. Running the code above will give you the expected result as shown in the figure below:


                                                   Figure 3 - Output

The app runs smoothly and you get the expected result. Now look at the following figure below when a hacker inputs a malicious value.


                                 Figure 4 - Showing SQL Command text

From the figure above, I just entered ';Drop Table Members - - into the TextBox and the values were appended into the core SQL. The result is definitely a valid SQL command that will be fetch records from SQL database and this can result in deleting your Members table. The single quote from the first character value represents a string delimiter as far as T-SQL in concerned and if you allow users to enter these without managing them then your data will be at risk. The double dash/hyphen (- -) character in the last part are basically used to comment out the preceding text in SQL. Now you might want to ask if, how a hacker knows your database table names? Well chances are they don’t but you should think about how you name your database tables. They are bound to be common sense names that reflect their purpose and it doesn’t take long to guess what they were, especially if you are using ASP.NETDB.mdf database which is publicly available to anyone. Renaming your database table names to something obscure (really-hard-to-guess-name) won’t solve the issue as someone can easily use random string generators.

Example 2

Another common example is validating user credentials from a database using the following code:

protected void btnLogin_Click(object sender, EventArgs e) {
            SqlConnection conn = new SqlConnection(@"Data Source=ServerName\SQLEXPRESS;Initial Catalog=DemoDB;Integrated Security=SSPI;");
            SqlCommand cmd = new SqlCommand("Select * from SYSUser where LoginName= '" + txtUserName.Text + "' and PasswordEncryptedText='" + txtPassword.Text + "'", conn);
            conn.Open();
            SqlDataAdapter ad = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            if (dt.Rows.Count > 0)
                Response.Write("OK");
            else
                Response.Write("Failed");

            conn.Close();
}

Again, the code above will just work fine if you supply proper valid credentials. If the LoginName and the Password values matched a row from a database then it will display OK otherwise Failed. Now if I enter ' or 'hacked' = 'hacked in both LoginName TextBox and Password TextBox then your SQL command query will now result to this:



Appending those malicious values will always match at least one row, so the dt.Rows.Count will always be > 0.

Another scenario is that if the hacker knows your LoginName, for example your LoginName is “Admin”, they can simply append the value '- - and your SQL query will now become something like this:

Select * from SYSUser where LoginName= 'Admin'--' and PasswordEncryptedText=''

If you have noticed the remaining condition in your WHERE clause, it was commented out because of the injected SQL syntax in order to disregard the remaining condition. So if the LoginName “Admin” exists in your database then your dt.Rows.Count will be > 0 granting the hacker access to your website.



The result in the figure above returns “OK”. It simply means that the hacker easily bypassed your authentication and is able to access your secured pages. Once they are able to access your secured page, they can potentially start defacing your site or they might break some data from your database or make some of the data disappear.

Those examples demonstrated are just few of the typical examples of SQL Injection attack. Other avenues of the attack can be a value from forms, cookies and query strings wherein additional SQL command can be injected automatically to your core SQL command to change the behavior.

The Solution

Escaping and replacing characters from a string cannot totally prevent you from SQL Injection attacks. In order to prevent SQL Injection attack, make use of parameter queries. It is an ideal way to prevent such attacks.

Using Parameter Queries

ADO.NET parameterized query is a query in which placeholders are used for parameters. The parameter values are supplied at execution time. When parameterized queries are sent to SQL Server, they are executed via system stored procedure sp_executesql.

In example 1, we can rewrite the code like the one shown below:

protected void btnSearch_Click(object sender, EventArgs e) {
            DataTable dt = new DataTable();
            using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
            {
                string sql = "SELECT * FROM GridViewDynamicData WHERE Field1 = @SearchText";
                using(SqlCommand sqlCmd = new SqlCommand(sql,sqlConn))
                {
                    sqlCmd.Parameters.AddWithValue("@SearchText", txtSearch.Text);
                    sqlConn.Open();

                    using(SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd)){
                        sqlAdapter.Fill(dt);
                    }
                }
            }

            if(dt.Rows.Count > 0)
            {
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
 }

If you have noticed, there are few changes in the code above which makes the code more clean, maintainable and secured. First is wrapping the SqlConnection, SqlCommand and SqlDataAdapter objects within the using statement. Since these objects implement IDisposable, putting them within the <using>  statement will automatically dispose and close the connection of the object,  after it is being used. In other words, if we use the <using> statement, we don’t need to explicitly dispose the object from the code because the <using> statement will take care of it. As an additional note, a <using> statement uses a try and finally block under the hood which helps in disposing an IDisposable object from the finaly block. Second is moving the connection string in a web.config file and reference it using the System.Configuration.ConfigurationManager class. Third is moving the SQL query in a separate string variable called “sql”. Within that query, you’ll see the parameter: @SearchText which replaces the concatenated TextBox value. All SQL parameters should be prefixed with the @ symbol. Every parameter declared in your SQL query would expect a corresponding value so in this case, we have added the line sqlCmd.Parameters.AddWithValue("@SearchText", txtSearch.Text). The SqlParameterCollection.AddWithValue method basically adds a value to the end of the SqlParameterCollection.

Again, the SQL parameter query will be sent to SQL Server and then executed by sp_executesql command. Based on our example, the query will be issued like this:

exec sp_executesql N'SELECT * FROM GridViewDynamicData WHERE Field1 = @SearchText', N'@SearchText varchar(50)',@SearchText='Test 3'

When the command executes, the parameters and the query text are treated separately. Any SQL syntax that the value of string might contain will be treated as a part of the literal string and not as a part of SQL statement. This is actually how SQL Injection is prevented.

Using Stored Procedures

If you do not want your SQL query to be embedded in your C# code then you can also use stored procedures with parameter queries. An example would be pretty much the same as what I have previously demonstrated except that you just need to set CommandType of SqlCommand to StoredProcedure and provide the name of your stored procedure name as the CommandText,

DataTable dt = new DataTable();
using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
{
    string sql = "YourStoredProcedureName";
    using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
    {
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.Parameters.AddWithValue("@SearchText", txtSearch.Text);
        sqlConn.Open();
        using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd))
        {
            sqlAdapter.Fill(dt);
        }
    }
}

Using Object/Relation Mapping Framework (ORM)

ORM like Microsoft Entity Framework and NHibernate will issue parameterized SQL statements when an operation is executed. Using them will provide protection against SQL Injection attack without an extra effort. Using these data access mechanisms can also prevent you from trouble because you can just program against the conceptual application model instead of programming directly against your database. You don’t have to deal with those typo and SQL syntax. Here’s a sample snippet on how the code would look like,

using (DemoDBEntities db = new DemoDBEntities())
            {
                var result = db.GridViewDynamicData.Where(o => o.Field1.Equals(txtSearch.Text));
                if (result.Any())
                    return result.ToList();
}

Other Tips 

• Make sure to do validations for all input types before passing the values to the parameters. This is because if your SQL parameter type expect a numeric value and you are passing a string type then your application will throw an error.
  
  
• Make sure to validate ranges and length of the characters to be entered from your input controls.

Now that you know about SQL Injection attack, how it can potentially harm your website and data; I hope you will start using parameterized queries to protect your site from such attacks. Hence stop being lazy because you really have no excuse.

To forums contributors', especially to the experienced one, please make it a habit to provide parameterized query code to beginners when you see code that is prone to SQL injection attack. We are a community so let’s help folks by guiding them to the right way.

Again, make it a habit to always use parameterized queries. I hope someone will find this post useful.