SQL Injection vs. Lethal Injection / Protection Against SQL Injection

SQL Injection and Lethal Injection... They are both dangerous and they can be easily fatal. But how? What is SQL Injection and how it can effect my project? The answers are in this blog post.

1/29/2011 8:37:00 PM 6 comments 9061 times

Writing a software, web application code is a real deal. It requires a good quality of talent on programing languages, appropriate analectic approach and most of all, a good prescience on your project. The things I have mentioned are very important and basic features of a qualified programmer.

I am not a student of a computer related course and I haven’t been but I support that educational background on computer science makes a difference on the quality of programmer. But the diploma or the course certificate is not enough. Little mistakes could be unforgivable in programming world and your diploma or certificate cannot get those mistakes back or cover them.

As for our topic, SQL injection is one of the most important topic on programming security. I have seen couple of developer’s “handy” work for last several months and I decided to write this blog post and I would like say all of the developers, with no offense;

Please, if you are creating a project with database structure, for the sake of programming, be aware of the SQL injection and its effects. It is not a shame that you haven’t heard of that term. What the shame is to write lines of codes creating the proper connection with your database without considering the effects of SQL injection !

NO OFEENSE !

What is SQL Injection?

Well, some of you might want to know what the SQL injection is. I won’t explore the world from scratch, so here is the clear explanation that I quoted from Wikipedia;

SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks.

So the definition supposed to clear the question marks but it might not. Let’s demonstrate.

Imagine that you have a web application running on the web and it aims to provide an user interface to your customers to view their account details.

The demo application is pretty small and we will only create 2 pages with one database table. Page 1 will be the wrong scenario and 2nd one will be the right.

In this application, we will see how the end user can easily display the sensetive data you migh have in your database.

"I would like to say this, in a nutshell, nobody (I mean a programmer who knows what he/she is doing) developed a kind of application for that kind of purpose but to demonstrate the topic, I have done something like that. The project is not supposed to be a real world example."

Our database structure looks like this;

I won’t dive into details, I will post the project code so your could download and dig it letter.

SQL Injectable Page

I have used GridView to list the data and here is what the user page looks like;

The code has been use to provide the data is as exactly below;

        protected void butn_click(object sender, EventArgs e) {

            GridView1.DataSource = DataProvider(txt1);
            GridView1.DataBind();
        }

        private static DataSet DataProvider(TextBox mytext) {

            string connectionString = WebConfigurationManager.ConnectionStrings["SampleConnectionString"].ConnectionString;
            string sql = "SELECT * FROM Customers WHERE ([TCKimlikNo] = '" + mytext.Text + "')";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter MyAdapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet("MyDs");
            MyAdapter.Fill(ds, "MyDs");

            return ds;
        }

“DataProvider()” static method connects to the database and executes some SQL against a SQL Server database that returns the number of rows where the user data supplied by the user matches a row in the database. If the result is one matching row, that row will be displayed as you can see;

Let’s put a break point on the 10th line and hit it;

The value supplied above for TCKimlikNo is 34265128731. As we can see in the image, the code works perfectly fine and the value is on the place that we wanted. Now let’s do some evil things;

Now the query explains itself pretty clearly. The evil user put this;

hi’ or ‘1’ = ‘1

And the logic fits. Method will return all the rows inside the database table. Look at the result;

Boom, you have been hacked ! This is the SQL Injection my friends. This thing is easy to apply and the worse part, this mistake is being made often.

Here is a quote from Mike’s blog;

This is SQL Injection. Basically, additional SQL syntax has been injected into the statement to change its behavior. The single quotes are string delimiters as far as T-SQL is concerned, and if you simply allow users to enter these without managing them, you are asking for potential trouble.

What is the Prevention?

Easy ! Just do not create the world from scratch.

If you are a ASP.Net user, use parameters instead of hand made code. Review the following code and compare it with the previous one;

            string connectionString = WebConfigurationManager.ConnectionStrings["SampleConnectionString"].ConnectionString;
            string sql = "SELECT * FROM Customers WHERE ([TCKimlikNo] = @IDParameter)";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@IDParameter", SqlDbType.VarChar);
            cmd.Parameters["@IDParameter"].Value = mytext.Text;

            SqlDataAdapter MyAdapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet("MyDs");
            MyAdapter.Fill(ds, "MyDs");

            return ds;

Done ! You will be good to go Smile But what happened there? Here is a good quote from Mike’s blog again;

Parameter Queries

Parameters in queries are placeholders for values that are supplied to a SQL query at runtime, in very much the same way as parameters act as placeholders for values supplied to a C# method at runtime. And, just as C# parameters ensure type safety, SQL parameters do a similar thing. If you attempt to pass in a value that cannot be implicitly converted to a numeric where the database field expects one, exceptions are thrown

Paramaters will protect your data if your building your project this way but another safe way is to use LINQ to SQL and Entity Framework to protect your project against SQL Injection.

Comments

#2062

re: SQL Injection vs. Lethal Injection / Protection Against SQL Injection

by Tugberk on 01/30/11 20:27:50 Sunday (UTC +00:00)

In this situation, Replace() Method could be used as well. Some folks e-mailed me about why I didn't mention that method. It is not complately protected and you have to spend extra time to build a system like that. You need to escape all he characters and you will need a proper exception handler system as well.

Well, parameters alrready does that. So, why is the extra effort?