Misc

SQL Injection Attacks

Every day I see messages on various forums asking for help with SQL. Nothing wrong with that. People want to understand how something works, or have a partial understanding but something is keeping them from completing their task. However, I frequently also see messages that have SQL statements being built in C# or VB.NET that are extremely susceptible to injection attack. Sometimes it is from the original poster and, while they really need to learn to defend their systems, that is fine as they are trying to learn. Nevertheless there is also a proportion of people responding to these questions that give advice that opens up gaping security holes in the original poster’s system, if they follow that advice.

Consider this following example:

C#

static DataSet GetCustomersFromCountry(string countryName)
{
    SqlConnection conn = new SqlConnection("Persist Security Info=False;"+
        "Integrated Security=SSPI;database=northwind;server=(local)");
    string commandText = string.Format("SELECT * FROM Customers WHERE Country='{0}'",
        countryName);
    SqlCommand cmd = new SqlCommand(commandText, conn);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
}

VB.NET

Function GetCustomersFromCountry(ByVal countryName As String) As DataSet
    Dim conn As SqlConnection = New SqlConnection("Persist Security Info=False;" + _
        "Integrated Security=SSPI;database=northwind;server=(local)")
    Dim commandText As String = String.Format( _
        "SELECT * FROM Customers WHERE Country='{0}'", _
        countryName)
    Dim cmd As SqlCommand = New SqlCommand(commandText, conn)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    GetCustomersFromCountry = New DataSet
    da.Fill(GetCustomersFromCountry)
End Function

What happens here is that what ever the value of countryName is will be inserted (injected, if you prefer) directly into the SQL string. More often than not I see examples of code on forums where there has been absolutely no checking done and the developer has used countryNameTextBox.Text directly in the string format or concatenation statement. In these cases just imagine what the effect of various unrestricted text box entries might be.

For instance, imagine the values a malicious user might put in the text box on a web form. What if they type ';DROP TABLE Customers;-- ?

That would expand the full SQL Statement passed by the .NET application to be

SELECT * FROM Customers WHERE Country='';DROP TABLE Customers; -- '

So, no more customers (at least in the database… But how long in real life?)

Some people might then say, sure, but who in their right mind would give that kind of access on a SQL Server to the ASP.NET account? If you ask that question then you cannot have seen the number of people who post code with the connection strings clearly showing that, firstly, they are using the sa account for their web application and, secondly, by posting their problem to a forum they have given to the world the password of their sa account.

Some others might say, yes yes yes, but wouldn’t an attacker would have to know what the overall SQL statement is before they can successfully inject something? Not so, I say. If you look at code posted on forums it becomes obvious that the vast majority of values from textboxes are inserted right after an opening apostrophe, like the example above. Based on that assumption, all an attacker needs to do is close the apostrophe, add a semi-colon and then inject the code they want. Finally, just to make sure that any remaining SQL from the original statement is ignored they add a couple of dashes (comment markers in SQL)

These defenders-of-bad-SQL-because-you-can-never-completely-secure-your-system-anyway-so-why-bother will often follow up with, okay okaay! But the attacker would have to know the structure of the database as well! Well, maybe not. Normally there are common table names. I’m sure most people that have been dealing with databases for a few years will have come across many with tables with the same names. Customers, Users, Contacts, Orders, Suppliers are common business table names. If that doesn’t work it may be possible to inject an attack on sysobjects. Often an attacker just gets lucky or notices a quirky output when entering something unusual and uses that to work on cracking the web site or database.

So here I present three tips for improving the security of your SQL Server database. In no particular order, they are: Use parameterised queries. Login using an appropriate account and grant only the permissions necessary. Use stored procedures.

* Using parameterised queries is really very simple, and it can make your code easier to read, and therefore to maintain. Parameters also have other advantages too (for instance you can receive values back from parameters, not just use them for sending information into the query). The previous code example can be changed very easily to use parameters. For instance:

C#

static DataSet GetCustomersFromCountry(string countryName)
{
    SqlConnection conn = new SqlConnection("Persist Security Info=False;"+
        "Integrated Security=SSPI;database=northwind;server=(local)");
    string commandText = "SELECT * FROM Customers WHERE Country=@CountryName";
    SqlCommand cmd = new SqlCommand(commandText, conn);
    cmd.Parameters.Add("@CountryName",countryName);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
}

VB.NET

Function GetCustomersFromCountry(ByVal countryName As String) As DataSet
    Dim conn As SqlConnection = New SqlConnection("Persist Security Info=False;" + _
        "Integrated Security=SSPI;database=northwind;server=(local)")
    Dim commandText As String = "SELECT * FROM Customers WHERE Country=@CountryName"
    Dim cmd As SqlCommand = New SqlCommand(commandText, conn)
    cmd.Parameters.Add("@CountryName", countryName)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    GetCustomersFromCountry = New DataSet
    da.Fill(GetCustomersFromCountry)
End Function

* The application should be set up to use a specific account when accessing the SQL Server. That account should then be given access to only the things it needs. For instance:

GRANT SELECT ON Customers TO AspNetAccount

It is generally unwise to GRANT permission ON someObject TO PUBLIC because then everyone has the permission.

* My final tip is to use only stored procedures for selecting and modifying data, because then the code that accesses the tables is controlled on SQL server. You then do not need to grant access directly to the tables, only the stored procedures that are called. The extra protection then comes by virtue of the fact that the only operations that can be performed are those that the stored procedures allow. They can perform additional checks and ensure that relevant related tables are correctly updated.

NOTE: This was rescued from the Wayback Machine. The original post was dated Saturday, 25th September 2004.

Tags:


Original posts:

Excellent post Colin. I’d always wondered what a SQL Injection attack was without actually bothering to Google for it – and now I know.

9/26/2004 6:08 AM | Rob

this is great job, colin. i’ve just know the words ‘SQl injection’ but dont know what it exactly means. now i’ve got it. i can defend my own database.

thanks

10/3/2004 3:41 PM | Fired Dragon

I’ve seen people who’ve read this article thinking they can’t do it because you’ve only given .NET syntax. You should probably emphasize that ‘classic’ ADO also has Command objects with a Parameters collection, as does the more obscure underlying OLE DB object model. You can also use parameters with ODBC. There’s no excuse – parameters are cleaner, more secure, and less prone to error than building a SQL string. The slight disadvantage is that the code becomes a little less portable – SQL Server uses the @param notation, Oracle uses :param, and the OleDbCommand and OdbcCommand both use positional parameters marked with a ?

12/18/2004 1:01 AM | Mike Dimmick

I just can’t get this to work. I do exactly as the example shows, and the query doesn’t replace the parameters with the values to search for. The query came up with nothing, until I entered a row in the database with the name of the parameter as a field. It finds that, so it just doesn’t parse the command. I don’t get it.

7/27/2005 10:46 AM | Vesa Ahola

Since I cannot see your code I cannot see what is going wrong. However, I wrote a longer article about the subject over on codeproject.com. Perhaps that may help. See:

http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp

7/27/2005 4:28 PM | Colin Angus Mackay
Misc

UPDATE: Sql Injection Attacks

As a follow up to my post on preventing SQL Injection Attacks a couple of months ago I just found this little nugget, I Made a Difference[^], and it shows what can be achieved if you don’t secure against SQL Injection attacks – and with only 3 hours of effort. Obviously, if you have access to the source code you will be able to launch an attack much quicker.

The original link seems to have disappeared. See the Wayback Machine for an archived copy, or the quoted section below:

Two weeks ago, I taught a Guerilla .NET course for DevelopMentor in Boston. Two or three days ago, a student who listened to me rant about SQL Injection attacks during the Code Access Security module lecture sent us (myself and the other two instructors) the following. It’s obviously been edited to protect the guilty:

“Hi, Ted. I want to thank you for the short primer on SQL injection attacks at the Guerrilla course in Woburn this month. We have a vendor who supplies us with electronic billing and payment services. (We send them billing data, and they present the bills to our customers and take the payments for us.) The week after the Guerrilla class I began to lose confidence in their application for various reasons, like seeing errors that included partial SQL statements, and in one case, a complete SQL statement that was accidentally left on a page from a debugging session. I told our company’s business manager that I was 80% confident that I could hack into their site using SQL injection. He called the vendor, who swore up and down that after spending $83,000 on firewalls that no one could ever hack into their site, and that we should go ahead and try.

“After three hours and a Google search on SQL injection, I was running successful queries from their login page and I had their server emailing us the query results via xp_sendmail. I was also able to confirm that the SQL Server login they use for their application has sa rights. I got a list of their clients, and was able to create tables in their databases.

“The vendor promised that by the next morning everything would be fixed. So the next morning at 8:00 am I tried again. I was no longer able to get results via xp_sendmail, but I was able to shutdown their SQL Server service by sending a shutdown command. I followed that up with a friendly call to their tech support line to let them know that they needed to restart SQL Server–I didn’t want to be too malicious. The guy at the other end of the line apparently had been there the entire night changing code and rolling out pages. He threatened to get on a plane, come to my office, and beat me up.”

“The disturbing thing about the incident is that there is enough data in the vendor’s database to allow someone to commit identity fraud or steal credit card and bank account numbers. And they are not a mom and pop shop either–their client list includes F—-, D—-, D—-, and V—-. [These are names that you would recognize, dear reader.] If I had been malicious I could have stolen data from any of those companies.”

Hunh. Three hours and a Google search was all it took. Anybody still think firewalls are the answer? “Security is a process, not a product.” — Bruce Schneier, Secrets and Lies.

NOTE: This was rescued from the Google Cache. The original date was Wednesday 17th November, 2004.

Tags:

Misc

Please please please learn about SQL Injection Attacks

Here are two more great blog entries about preventing SQL Injection Attacks

NOTE: This was rescued from the Wayback Machine. The original date was Tuesday, 30th November 2004.

Tags:


Original comments:

I think one of the problems is that there is too many source code archives and books that called their code “best practices” that are targets for SQL Injection. We will probably still see the code used for years to come.

11/30/2004 8:03 AM | Rocky Moore
Misc

Protecting Tables from SQL Injection Attack

A recent question in a forum that I view asked about how to ensure that even if one layer of security was compromised that the table would only ever return one row at a time so that an attacker would have to do more work to get a list of the users and passwords out of the database.

The way I see it, the best solution is not just to set up constraints, assuming that your database can add a constraint to only ever return the first row in a query, but to protect the table by not granting access to it directly. Then set up stored procedures to perform all the operations that you permit on the table. That way, if an attacker should get through the “outer defences” they cannot access the tables directly, and must use the stored procedures.

For example, say you have a database that has the user details for a website, this includes the user name and password. You don’t want an attacker to get a list of passwords or even one password. So you design the stored procedures so that you can pass a password in, but it will never put a password in a result set. The stored procedures for registering and authenticating a user for the website might be:

  • RegisterUser
  • VerifyCredentials
  • ChangePassword

RegisterUser takes the user name and password as parameters (possibly along with other information that would be useful for your website) and returns the UserID

VerifyCredentials would be used for logging into the site by accepting the user name and the password. If there was a match the UserID is returned, if not then a NULL value.

ChangePassword would take the UserID, the old password and the new password. If the userID and password match the password can be changed. A value that indicates success or failure is returned.

As you can see that the password is always contained in the database and is never exposed. The stored procedure could potentially generate a salted hash of the original password too so that should some layer of the database security be compromised that the password is still not readable.

You must also be careful when you call the stored procedure and ensure that you use parameterised queries. SQL Injection attacks are also possible when calling stored procedures if they are called by building up a SQL statement dynamically and executing it.

NOTE: This was rescued from the Google cache. The original date was Thursday, 6th January, 2005.

Tags:


Original comments:

Salted hash is a pretty tricky thing to do in SQL Server 2000, but should be very simple in SQL 2005 with a CLR function that wraps System.Security.Cryptography. For the moment you should probably hash the password on the client, i.e. in the web application code.

The downside, of course, to salted hashes is that you can’t ever tell the user what their password was. You have to have a facility to allow them to reset their password by supplying some other information instead (the ‘password reset question’ technique).

1/10/2005 12:04 AM | Mike Dimmick
Software Development

Oh No! More on SQL Injection Attacks

I’ve not written about this in a while becuase it seemed that people were getting the message. But today I was asked, on Code Project, “I am wondering why injecting values into the [SQL] string is considered a security risk?” Here is my response: Because if you inject strings into the SQL, especially ones that come straight from the user interface, then an attacker can produce malformed SQL and gain access to your system. (Where do you live? I can come and do one of my SQL Injection Attack presentations in your town if you want a real live demonstration where I compromise a SQL Server into divulging the inner most secrets of the server it is running on. And I mean the whole server, not just the SQL Server process.*) Lets say you have a simple bit of SQL like this:

cmd.CommandText = "SELECT * FROM Products where Name = '"+txtSearch.Text+"'";

What happens if the user types in the following?

'; DELETE FROM Products; --

The whole string becomes:

SELECT * FROM Products where Name = ''; DELETE FROM Products; --

That will return a dataset back to the application, which is what it expects, and then deletes all the products from the database. When the next customer comes to the website what is it going to show when there are no products in the database? Okay – there may be some constraints on the table (foreign key constraints) that don’t permit the rows to be deleted. How about something equally damaging to the company. Let’s set their entire inventory to a penny! The mallicious user then types:

'; UPDATE Products SET Price = 0.01; --

The word will quickly spread around the internet and the company will soon be out of business or have a huge number of very pissed off customers. If you don’t secure your system the possibilities for attack are endless. Finally, if you want to know more, I encourage you to read my article SQL Injection Attacks and Tips on How To Prevent Them

* The demonstration is done on a server box that I own. Performing a SQL Injection Attack on a system without the permission of the system owner is a breach of the 1990 Misue of Computers Act and can carry a penalty of 5 years in jail.

NOTE: This was rescued from the Google Cache. The original date was Wednesday 7th June, 2006.


Original comments: As a MCSD I was entertained by the examples that you provided. I’m relieved however that your not a chemist or a physics guru or something… Nicely done…

7/10/2006 10:03 PM | Dee Illuminati
Misc

SQL Injection Attacks and executing dynamically created SQL

There is a very important difference between EXEC[UTE] and sp_executesql that anyone who executes dynamically generated SQL statements ought to know.

Typically dynamic SQL is generated when a particular construct is not possible by using parameters alone or when certain parts are added to the statement depending on other conditions. In the latter case, sp_executesql trumps EXEC[UTE] by allowing the developer the ability to pass in parameters to the dynamic SQL statement.

For example, consider this code:

SELECT *
FROM MyTable
WHERE a = @wantedA
AND b = @wantedB

If you were dynamically building this and were using EXEC then the code to dynamically build and execute it might look like this:

DECLARE @sql NVARCHAR(4000);
SET @sql = N'SELECT * FROM MyTable '+
           N'WHERE a = '''+@wantedA+N''' AND b = '''+@wantedB+N'''';
EXEC(@sql)

As you can probably guess, without extreme care as to the values of @wantedA and @wantedB an SQL Injection Attack is possible. However, it is possible to dynamically create the SQL statement and still use parameters within it like this:

SET @sql = N'SELECT * FROM MyTable '+
           N'WHERE a = @dynWantedA AND b = @dynWantedB';
sp_executesql @sql, N'@dynWantedA varchar(100), @dynWantedB varchar(100)',
              @dynWantedA = @wantedA, @dynWantedB = @wantedB;

As you can see in the second example, instead of injecting the value of the parameters we can just write parameters directly into the dynamic SQL statement and then pass them in.

There is, of course, caution to be exercised. Certain things cannot take parameters. For example, in SQL Server 2000 the TOP keyword must be followed by a literal value. It isn’t possible to write TOP @numRows* so if that must be dynamic then the value would have to be injected into the SQL statement like this:

SET @sql = 'SELECT TOP '+CAST(@numRows AS varchar(10))+' * FROM MyTable';

So, using sp_executesql is not a panacea that will make all issues with SQL injection go away when building dynamic SQL, but it does help in certain cases.

* This is possible in SQL Server 2005, but not SQL Server 2000

NOTE: This was rescued from the Google Cache: The original date was Thursday, 26th January, 2006.