Target. Home Depot. NASA. U.S. Army. Anthem. Wall Street Journal. MarketWired. Ashley Madison. What do they all have in common? They are all recent victims of cyber-attacks – several of these are confirmed to involve SQL Injection. Even though the exact method used to penetrate the other systems has not been released, experts believe that SQL Injection was involved in all of these cases. Take a look at https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/ for places that are known victims of SQL Injection.
The part that is really astonishing is that we have known how to stop SQL Injection for more than a decade. Completely. Dead. In. Its. Tracks. Which means that the problems happening today are because of careless coding techniques, and as such, they can be prevented.
The “Funky query”
A few years ago, I was working for a company that developed and sold software. One day, my supervisor sends me an email titled “Funky query”. Hmm, that sounds interesting… I’ll open this up right away, instead of ignoring it until I finish what I’m currently working on (like I would usually do).
Wayne… one of our customer support engineers sent us this from the application error log of a customer. They were running a report. Any ideas on how this funky query came to be?
Query from error log (genericized… because I can’t use the real query):
SELECT * FROM sys.objects WHERE name = ‘O’Malley’
Details from the error log:
Incorrect syntax near 'Malley'. Unclosed quotation mark after the character string ''.
Me: Umm, boss… Does this report allow users to enter search criteria?
Boss: But of course!
Me: Well, I really hate to tell you this, but we have a SQL Injection problem.
And after a bit of back and forth where the developers were insisting that no way was there a SQL Injection problem, I sat down with the dev team lead and the boss and proved it to them. We created a dummy table in the database, went to the report criteria form, and I dropped the table.
Wayne: +1000
Development Team: -1000
Defining SQL Injection
Before we go into how SQL injection occurs, let’s first examine what it is: a code injection technique that allows SQL code that the user enters in the front-end to be run against the back-end database.
Please note a few things about this definition:
- SQL Server was not mentioned. Any and all database platforms are susceptible to SQL injection. SQL Server. Oracle. PostgreSQL. DB2. It doesn’t matter – all database platforms are susceptible.
- A generic front-end is mentioned. This is anything that connects to a back-end database, be it a web form or your favorite application. Anything that connects to a database can allow it.
- Improperly written code within the database (such as a stored procedure) can allow it. It’s all about how the query is created and run against the database.
- There are other forms of injection attacks that can occur. Other forms include Cross-site scripting, Web Parameter Tampering, LDAP Injection and XPath Injection (as I write this, WordPress 4.3.1 is being released to fix two Cross-site scripting issues).
SQL injection can happen in three different ways:
- Classic – this is when a user enters SQL code, and can immediately view the results on screen.
- Blind – this is essentially a classic attack, but the user does not see the results. Things may still happen, and the user may be able to send results to files that can be downloaded, but the results are not shown on screen.
- Second Order – the user-supplied SQL code is actually stored in the database, and at a later point in time it is run.
Creating SQL Injection
SQL injection happens when a SQL statement is created from the data supplied by the user, and it is run by the application or other process. Obviously the intention of the application is to only run the queries that it supplies. Consider a login form with user name and password data entry fields. Assume that the user enters “Mike” as the user, and “1234” as the password. In this case, the application essentially performs:
DECLARE @UserName VARCHAR(50) = 'Mike';
DECLARE @Password VARCHAR(50) = '1234'
DECLARE @SQLCMD VARCHAR(MAX);
SET @SQLCMD = 'SELECT UserName, Password
FROM dbo.Users
WHERE UserName = ''' + @UserName + ''' AND Password = ''' + @Password + '''';
EXECUTE (@SQLCMD);This will create the SQL statement:
SELECT UserName, Password
FROM dbo.Users
WHERE UserName = 'Mike'
AND Password = '1234'And when we run this, we get the expected results. But now, a hacker becomes the user. Being wise to SQL injection, the hacker enters for the password the string
1234' OR 1=1;--This results in the following SQL Statement being generated:
SELECT UserName, Password
FROM dbo.Users
WHERE UserName = 'Mike'
AND Password = '1234' OR 1=1; --Since every row matches the “OR 1=1” predicate, every username and password will be returned.
Hacker: +1
Developer: -1
Notice that I showed this attack with T-SQL code – this means that you can have SQL injection vulnerabilities within your T-SQL code (such as stored procedures), as well as any SQL commands that the application might construct and send to the database. The application might be properly handling for SQL injection, but if it is calling SQL code that is improperly coded, you can still be vulnerable. Everything has to be done properly – all it takes is one point where injection is possible to become the latest victim.
What can a hacker do with a vulnerability?
At this point, the hacker knows that the application is susceptible to SQL injection, and can now issue any SQL statement desired. Depending on the access level that the application has to connect to the database, the hacker can:
- Get a list of all of the databases in the instance.
- Get a list of all of the tables in the current database, or any other database.
- Query the data in any of the other tables that can be seen.
- Modify (insert / update / delete) data in any of the tables that can be seen.
- Create / modify / drop any object in any database on the instance.
- Create elevated permissions at the SQL Server instance level. This will allow the hacker to do anything they desire on this instance. Drop tables? Yes. Query any and all data? Yes.
- Create elevated permissions at the Operating System level.
- Install programs on the server.
- Access any server on the network, and perform all of the above on those servers.
Scary? Absolutely. However, as I mentioned before, we know how to prevent this. And it turns out that it’s not hard. Instead of making SQL Statements that include data (either as directly supplied from the user, or from the database), pass that data to the SQL statement through parameters.
If we take the previous example, and modify it to use parameters, we would have:
DECLARE @UserName VARCHAR(50) = 'Mike';
DECLARE @Password VARCHAR(50) = '1234'' OR 1=1;--'
SELECT UserName, Password
FROM dbo.Users
WHERE UserName = @UserName
AND Password = @Password;What are the chances that you have a password of “1234′ OR 1=1; –” in your system? Assuming that you don’t, this query will return zero rows. SQL injection has been prevented.
So now you want an example of the difference between classic and blind SQL injection? Well, I’m not going to show you an example… because this will normally come down to how the application handles the data being returned. In the “OR 1=1” example, if the application only shows the first record, then the hacker won’t see the results of the SQL injection (unless they notice that it’s showing the wrong data). Or the application may have been coded to expect only one row, and if more are returned then it won’t display any.
Second Order SQL Injection
But how about a second order injection? Let me explain that with an example. Imagine a form for entering new users. The development team has learned about injection on the forms, and they are now using parameters. When the hacker enters for the password:
1234′ OR 1=1;–
The generated SQL statement is:
DECLARE @UserName VARCHAR(100) = 'Sam';
DECLARE @Password VARCHAR(100) = '1234'' OR 1=1;--';
INSERT INTO dbo.Users (UserName, Password)
VALUES (@UserName, @Password);When the data is queried:
SELECT UserName, Password
FROM dbo.Users
WHERE UserName = 'Sam';We get the following results:
UserName Password
-------- ----------------
Sam 1234' OR 1=1;--'The data has been stored in the database, and we are now primed for a second order injection attack. Some other process comes along (for example, a month-end report), and it creates dynamic SQL by building the SQL statement from the data. And the second order SQL injection has just been released…
What have we learned so far?
- SQL Injection can happen to any database system.
- SQL Injection can be within SQL code (stored procedures), or it can be in the statements passed to the database from the application.
- Parameters will prevent the SQL code from being executed immediately, thereby preventing Classic and Blind SQL Injection attacks.
- The SQL Injection attempt can still be stored in the database, priming yourself for a Second Order SQL Injection attack.
Preventing SQL Injection
Whenever you are creating a SQL statement, it must never create the statement with data (either from the database, or from what the user may pass). Instead, all SQL statements must use parameters. Even the month-end report.
Within SQL Server, have your queries reference the parameters (ColumnName = @VariableName). If you must use dynamic SQL to build your statement, use sp_executesql – it allows for the building of SQL statements with parameters, and then defining and passing those parameters in as the procedure is called.
In your application, there are several component choices. Some directly execute SQL, and others can use parameters. You want to always use the components that use parameters, and you must use those parameters.
Learning more
The Open Web Application Security Project is a community-driven, non-profit organization where experts analyze web application security issues and then compile a list of what the biggest issues are. For the last several years, the category “Injection” has been the biggest issue, and this includes SQL Injection.
This isn’t all that they do. They also have educational resources to not only instruct you on how these issues manifest themselves, but what to do about them. I strongly encourage everyone to take this resource back to your company, and to start identifying and addressing security issues in your code.
Another issue is that developers don’t really know about SQL Injection. In the workshops that I have given where this topic is presented, very few attendees have seen SQL Injection in a test environment. Most can barely give an adequate description of what it is. Those that have seen it are usually those who have been a victim of it. So I also recommend getting your company to arrange for SQL Injection training for the developers and DBAs. It would be good to send your QA team to this also.
