Protect Your Data: Prevent SQL Injection

Introduction

In many years, since I joined different technical community forums, I always see code provided by the asker/thread starter which is prone to SQL injection attacks. Though there are some fellow contributors who will continue to guide beginners to code against them, still there are few folks who will still provide vulnerable code to the asker. It's a sad thing and that's why I'm urged to write this article.

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 articles and blogs. I think some of the main reasons for this are:

1. Experienced developers keep providing those vulnerable code in which beginners will follow.
2. They don’t understand what they are doing (code).
3. They don’t mind about the code for as long as it works for them.
4. They’re afraid to learn the right way because they might break their existing “working” code.
5. They’re just lazy.
6. Yeah, they’re simply 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 few examples of how vulnerable code can break your data and how you can prevent it.

What is SQL Injection?

Taken from the documentation: SQL injection is a code injection technique, used to attack data-driven applications, in which 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 that we have the following table data:

Example 1

Let’s assume that you want to search for some values in a 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 search 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 and execute a SQL against a 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:

As you’ve 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:

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

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 executed against a 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 is concerned. The double dash/hyphen (--) character in the last part are basically used to comment out the preceding text in SQL, and if you allow users to enter these without managing them, then your data will be at risk. Now you might want to ask 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 ASPNETDB.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, thus allowing the hacker to enter your secured site.

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 was commented out because of the injected SQL syntax, thus disregarding the remaining condition. So if the LoginName “Admin” does exist 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 was able to access your secured pages. Once they are inside your secured site, they can potentially start defacing your site, or they might break some data to 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 attack can be a value from forms, cookies and querystrings wherein additional SQL command can be injected automatically to your core SQL command to change the behavior.

The Solution

Just to let you know that 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 the 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 to this:

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 that 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 place the code within the using statement, we don’t need to explicitly dispose the object in 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 disposes an IDisposable object in the finally 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’ve 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 something like this:

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

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

Using Stored Procedures

If you do not want your SQL query embedded in your C# code for some reasons, 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 the CommandType of SqlCommand to StoredProcedure and provide the name of your stored procedure 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. So using them will provide protection against SQL Injection attack without an extra effort on your part. Using these data access mechanisms can also save you lots of trouble because you can just program against the conceptual application model instead of programming directly against your database. So you don’t have to deal with those typos and SQL syntax. Here’s a sample snippet 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 expects a numeric value and you are passing a string type, then your application will throw an error.
• Make sure to validate ranges, values to be expected and length of characters to be entered from your input controls.

Outro

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

To forums contributors, especially to the experienced ones, 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 guiding them to the right way.

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

CodeProject