SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Injection!

By Christoffer Hedgate,

I am sure most DBAs and developers have heard of SQL injection (at least I would hope so) and may quite possibly have taken adequate steps to secure themselves against it. However, I do know that there are still a lot of vulnerable sites out there, and when I lately was contacted by an old client regarding a security breach I felt that even though there exists some good resources regarding this it is important to keep describing this in texts to make sure as many as possible are reached by this. My client thought that they were protected against SQL injection because they had taken some of the more well-known steps to secure themselves, but apparently they had not done enough.

For those who might not know exactly what SQL injection is, here is a short description. SQL injection is a variety of techniques that all have the goal of letting an attacker run whichever SQL statements they want in your database. Unlike many hacking techniques the attacker is not trying to get direct access to the database by for instance stealing or cracking a password or exploiting a bug in the DBMS, even though they may use SQL injection to set up a backdoor that would later give them direct access. They are instead trying to pass along their own SQL statements with the statements that are executed by an application using the database, by exploiting sloppy coding by the application developers. Another way to use injection is to gain access to applications that they should not have. The most well known injection technique is using a single quote to end a string and then adding your own statement. For instance, if you have a web application with a login form consisting of a user name and a password textbox, this might result in code similar to this (ASP VBScript code):

sql = "SELECT * FROM Users WHERE username = '" & Request.Form("username") & "' AND password = '" & Request.Form("password") & "'"

The problem with this is that if an attacker would enter the string "admin" (without the double quotes) as user name, and password "' OR (1 = 1 and username = 'admin') -- " (again without the double quotes), the SQL statement that would be executed in SQL Server would be this:

SELECT * FROM Users WHERE username = 'admin' AND password = '' OR (1 = 1 and username = 'admin') -- '

As you can see, 1 = 1 is always true and now the attacker is logged in as user admin. The two dashes at the end makes sure that any code following this (including the final single quote added by the ASP code) is commented and does not disrupt the injection attempt. The reason that this works is because the application developers where lazy and did not add input validation of the strings entered by the user. If they would have replaced all single quotes in the strings with two single quotes, the resulting statement would have been this instead:

SELECT * FROM Users WHERE username = 'admin' AND password = ''' OR (1 = 1 and username = ''admin'') -- '

This statement would simply have returned no rows and the injection attempt would have failed.

Defenses against SQL injection

As DBAs we can unfortunately not always be sure that the application developers that use our databases create secure code that validates all input correctly. Therefore we have to do what we can to make sure that even if some input is passed through unvalidated the potential damage that can be done is as minimal as possible, or even non-existent. One recommendation that is often given is to disallow all direct access to tables and only allow access via specific stored procedures that you create. If password in the example above would have been a parameter to a stored procedure the developers would not have needed to replace single quotes as this is automatically handled by SQL Server. However, even though this is a very good recommendation that will limit the access to the tables in your database, you still need to use it together with other recommendations, for instance to make sure that the login that the application is using to authenticate itself against the database has only minimal rights. But you still need to be aware that this is not fool-proof, as soon as an attacker can pass statements of his own choice to SQL Server he can start to try and exploit all sorts of different security holes that might exist in either SQL Server or your database and security implementation.

For instance, compare the two below examples of executing this procedure:

CREATE PROC up_Login @username varchar(255), @password varchar(25)
SELECT * FROM Users WHERE username = @username AND password = @password

ASP example #1 (simplified)
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText = "up_Login"
cmd.Parameters.Add cmd.CreateParameter("@username", adVarchar, adParamInput, 25, Request.Form("username"))
cmd.Parameters.Add cmd.CreateParameter("@password", adVarchar, adParamInput, 25, Request.Form("password"))
rst.Open cmd

ASP example #2
Set rst = conn.Execute("EXEC up_Login @username = '" & Request.Form("username") & "', @password = '" & Request.Form("password") & "'")

Both of these examples show functional code to execute the procedure, and even though both of them would stop the attacker from logging in as admin only the first will stop him from passing through statements of his choice. If he entered a password of "' ARBITRARY SQL STATEMENT -- " (without the double quotes), then his SQL statement would still be executed after the procedure was executed in the second example. So, as you can see, it is always important to make sure that all input is correctly validated and that the functions that execute the database calls use as explicit and type-safe code as possible. Any code that simply builds a string that is executed as a SQL statement is likely to be vulnerable to some kind of SQL injection.

Another variant of SQL injection

This brings me to the problem that my client encountered. They had implemented some input validation, checking and replacing single quotes. They were also using stored procedures but in many places these were executed using the simple Connection.Execute(proc-string) syntax. Luckily for them they were contacted by a person who had found a security hole in their site, so as far as they knew no intrusion had been done. Further investigation also showed this so they were lucky. The security hole was a simple SQL injection technique, but not the one above using single quotes. Consider the following procedure and execution of it:
CREATE PROC up_SumValues @someIdNumber int
SELECT SUM(somevalue) FROM SomeTable WHERE someIdNumber = @someIdNumber

ASP code:
Set rst = Connection.Execute("EXEC upSumValues @someIdNumber = " & Request.Form("someIdNumber"))

The id number to use was chosen from a drop-down box with options and posted to a receiving ASP page that would run code similar to the above. But this doesn't mean that an attacker can't send whichever id number he wants, creating an app for sending this takes less than 1 minute. And as I showed above using stored procedures does not automatically protect from attackers passing arbitrary SQL statements if you are executing strings this way. My client probably knew about these things, but the mistake they did was that they were only thinking about replacing single quotes for parameters of datatype varchar. They thought that since id number is an int then SQL injection is not possible. Wrong. Of course it is. In this scenario it is simply a matter of sending a value such as "3;ARBITRARY SQL STATEMENT" (without the double quotes) to pass a statement of an attacker's choice. The semicolon will end the statement (executing the procedure), and then the next statement will be executed by SQL Server. We solved this in a couple of steps depending on the need to quickly secure this. The first thing we did was to 'disallow' all semicolons in all strings that were executed as SQL statements. This might not always be an option but as a short-term solution we implemented a simple check for semicolons and if they existed we stopped executing. Now that we had more time we started to work through their code implementing two changes:

  • First we changed all simple string execution to instead use explicit code creating Command-objects and parameters.
  • All input that could somehow be changed or specified by users were explicitly type cast. For instance, the value that were used as the parameter for the up_SumValues proc were gathered with code similar to this:
	Dim someIdNumber
	someIdNumber = CLng(Request.Form("someIdNumber"))

If someone would try to send a value like "3;ARBITRARY SQL STATEMENT" this type cast would fail and we could handle this error (logging what we could about the suspected intrusion attempt). This meant that we set up two lines of defense and if they continue to code this way they should be safe. This also lead to a security review where we locked the server down. All in all a vastly more secure site, some valuable knowledge for my client and some good money for me. :)


So, to conclude this small rambling about SQL injection, make sure that your developers are validating all input to your database. Make sure that they use secure and explicit code to execute their statements, they should be glad to since this will also improve performance. And finally make sure that you lock down your server(s) as tight as possible so that even if an attacker would find some way in he could do as little damage as possible, possibly even none. Please check out the resources below for lots of good information about SQL injection and security in general:


Total article views: 16812 | Views in the last 30 days: 7
Related Articles




SQL Injection question

fat client injection


how to overcome sql injection

avoid sql injection in sql server2005


injection attack

injection attack to saeed


SQL Injection - Part 1

Randy says that the biggest reason we have so many injection vulnerabilities is that all the asp boo...

sql server 7