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)
AS
SELECT * FROM Users WHERE username = @username AND password = @password
GO
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
AS
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. 🙂

Conclusion

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:

SQLSecurity.com

NGSSoftware