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
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)
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"))
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
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:
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: