SQL injection is a form of attack where the attacker will attempt to enter T-SQL statements in fields of an application where standard user input is expected. This results in the application building valid, but unintended, harmful statements that could cause serious damage to the SQL Server environment and potentially even allow the attacker to target the wider network. All RDBMS’s (Relation Database Management Systems) are vulnerable to SQL injection attacks because of the very nature of the SQL language, but steps can be taken to mitigate the risks.
In this chapter, after building a vulnerable environment, we will explore some of the attacks that an attacker could perform using an SQL injection attack, before discussing how the risks of these attacks can be mitigated.
Preparing the Environment
In order to follow the demonstrations in this chapter, you will need to create a database called CarterSecureSafe, configure some vulnerabilities in the SQL Server instance, and create a website. This section will guide you through each of those activities.
First, you can create the CarterSecureSafe database by using the script in Listing 10-1.
Note
The script assumes that the WideWorldImporters database already exists on the same instance.
CREATE DATABASE CarterSecureSafe ;
GO
USE CarterSecureSafe
GO
CREATE TABLE dbo.Users
(
ID INT IDENTITY PRIMARY KEY NOT NULL,
UserName NVARCHAR(128) NOT NULL,
UserPassword NVARCHAR(512) NOT NULL
) ;
GO
INSERT INTO dbo.Users(UserName, UserPassword)
VALUES('Pete', 'Password1'),
('Danni', 'MyPassword'),
('Iris', 'legofriends'),
('Reuben', 'Jupiter'),
('Fin', 'Doughvinci') ;
GO
SELECT *
INTO dbo.SalesOrderDetails
FROM WideWorldImporters.Sales.Orders ;
GO
SELECT *
INTO dbo.SalesOrderHeader
FROM WideWorldImporters.Sales.OrderLines ;
GO
Listing 10-1
Create the CarterSecureSafe Database
Next, we will configure some insecure yet surprisingly common features of SQL Server by using the script in Listing 10-2. The first of these configurations is to turn on xp_cmdshell. This system-stored procedure allows administrators to interact with the operating system from within SQL Server. It is disabled by default, but a large number of DBAs enable this feature, thinking that it is secure because only members of the sysadmin fixed server role can use it. This does not stop attackers exploiting it, however.
The second activity performed by the script is to ensure that mixed mode authentication configured on the instance and that the sa account is enabled. Full details of how to secure the sa account can be found in Chapter 8. We will also configure the sa account’s password to be MyPa$$w0rd123. The sa account will then be used by the web application to authenticate to the instance. While very insecure, this is also a very common scenario that I have witnessed during my career.
Note
After running the script, you should restart the instance to ensure that the change to mixed mode authentication takes effect.
We will now create a simple aspx website, which will use the CarterSecureSafe database. The website will have two pages. Default.aspx is a simple login page, and welcome.aspx is a simple splash screen welcoming the user to the website. The aspx for the default.aspx page can be found in Listing 10-3.
Tip
aspx is a Microsoft technology for building web sites. You will need Visual Studio to follow this code example. Once the files are created in an aspx Website project, the site can be previewed.
Label2.Text = "Hello " + Session["Parameter"] + ", welcome to Carter Secure Safe!";
}
}
}
Listing 10-6
Welcome.aspx.cs Code
Figure 10-2 shows the welcome page of the website. You will notice that the user named Reuben has successfully logged in, and his welcome message is displayed.
Figure 10-2
Welcome page
The contents of the web.config file can be found in Listing 10-7.
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit http://go.microsoft.com/fwlink/?LinkId=169433
Now that we have created an environment, with some serious, yet common, security flaws, the following sections will explain the concepts of SQL injection attacks by providing examples of the nature of attacks that can be performed.
Spoofing a User Identity
The most basic form of SQL injection attack that can be performed against the CarterSecureSafe website is the spoofing of a user identity. This means that the attacker will be able to access the website using the identity of a genuine user. If the website were to expose the user’s confidential information, such as credit card details, then the attacker would have unfettered access to this information, allowing them to enact other crimes, such as identity theft.
To understand how this attack will work, we should examine the T-SQL statement, which is dynamically built by the website. This statement can be seen in Listing 10-4 but is repeated in Listing 10-8, in isolation, for convenience.
SELECT *
FROM Users
WHERE UserName='" + Login1.UserName + "' AND UserPassword='" + Login1.Password + "'
Listing 10-8
Dynamic T-SQL Statement Built by the Website
The sqlconnection object authenticates to the SQL Server instance using the sa account, and the user’s identity is then established by querying the Users table. The statement is built by passing the values entered into the UserName and Password text boxes of the Login control directly into the SQL Statement. The full statement that is executed by the website (assuming that Reuben is logging in) can be seen in Listing 10-9.
SELECT *
FROM Users
WHERE UserName = 'Reuben' AND UserPassword = 'Jupiter'
Listing 10-9
Prepaired Statement for Login by Reuben
Imagine, therefore, that instead of entering a valid user name and password, an attacker was to enter the code snippet suggested in Listing 10-10 in the user name field.
' OR 1=1--
Listing 10-10
Code Snippet to Spoof a User Identity
Because the Password field of the Login control has validation, to ensure that a user enters a value, the attacker would enter any random sequence of one or more characters. Let’s imagine that he uses the sequence qwerty. The statement executed by the website would now appear as per the statement in Listing 10-11.
SELECT *
FROM Users
WHERE UserName = " OR 1=1-- AND UserPassword = 'qwerty'
Listing 10-11
Prepared Statement for Spoofing User Identity
The -- indicates that everything to the right is a comment and will not be executed. There is obviously no UserName in the Users table with an empty string, but the 1=1 condition always evaluates to true. The net result of these facts is that all users in the Users table will be passed into the dataset object within C# and the first user returned (usually the first row in the table) will be used.
Because Pete is the first user in the Users table, entering ' OR 1=1-- into the UserName field and a random sequence of characters into the Password field will result in Pete's identity being spoofed, as you can see from the welcome message shown in Figure 10-3.
Figure 10-3
Pete’s User Identity Spoofed
Using SQL Injection to Leak Information
In order to maximize the potential of an attack, an attacker may decide to leak information about the database environment. For example, an attacker may wish to know the instance name or database name. If the attacker wishes to perform a wider attack against the network, they may wish to discover further information, such as the server name, the domain name, or even the versions of SQL Server and Windows Server being used. This would allow them to target specific vulnerabilities, without wasting their time trying to exploit vulnerabilities that have already been patched. All of these details can be leaked using SQL injection.
For example, if an attacker wished to find out the database name, they could enter the code from Listing 10-12 into the UserName field.
' AND 1 = db_name()--
Listing 10-12
Leak Database Name
This code would cause an error to be thrown in the website, which leaks the database name, as shown in Figure 10-4.
Figure 10-4
Leaked database name
To leak the server name and instance name, an attacker could enter the code from Listing 10-13 into the UserName field.
' AND 1 = (SELECT @@servername)--
Listing 10-13
Leak the Server and Instance Names
The error shown in Figure 10-5 will then be thrown.
Figure 10-5
Leaked server name and instance name
If an attacker wishes to leak the domain name, they could inject the code from Listing 10-14.
' AND 1 = default_domain()--
Listing 10-14
Leak Domain Name
This will cause the domain name to be leaked via the error message displayed in Figure 10-6.
Figure 10-6
Leaked domain name
Using the code in Listing 10-15, an attacker can leak the version of SQL Server and Windows being used, and even the service pack that is installed.
' AND 1 = (SELECT @@version)--
Listing 10-15
Leak Version Details
This will cause an error to be thrown, as displayed in Figure 10-7.
Figure 10-7
Leaked version details
In order to perform a destructive data attack, the attacker may wish to leak table names. Repeatedly injecting the code from Listing 10-16 will allow an attacker to leak table names.
' AND 1 = (SELECT TOP 1 name FROM sys.tables ORDER BY NEWID())--
Listing 10-16
Leak Table Names
This will cause the error displayed in Figure 10-8 to be thrown.
Figure 10-8
Leaked table name
In this attack, the attacker uses the ORDER BY NEWID() clause in the hope that a new table name will be leaked every time. The attacker cannot leak multiple table names in one attempt, by removing the TOP 1 clause, as the subquery will try to return multiple results and a different, non-informative error would be thrown, as shown in Figure 10-9.
Figure 10-9
Non-informative error
Destructive Attacks
An attacker can use SQL injection to perform attacks that destroy data. For example, imagine that the attacker has managed to leak the name of the Orders table. They could inject the code in Listing 10-17 to delete all data from the table.
' AND 1=1; DELETE FROM SalesOrderHeader--
Listing 10-17
Delete Data from the SalesOrderHeader Table
Tip
The semicolon is used as a statement terminator, meaning multiple commands can be injected.
From the attacker’s perspective, the website will simply display a message stating that the login attempt has failed, but a quick row count from the SalesOrderHeader table will show that the attack was successful, as shown in Figure 10-10.
Figure 10-10
Results of deletion attack
Alternatively, if an attacker had managed to leak the name of the SalesOrderDetails table, they could drop this table by using the code in Listing 10-18.
' AND 1=1; DROP TABLE SalesOrderDetails--
Listing 10-18
Drop the SalesOrderDetails Table
Again, the attacker will simply see a message stating that their login attempt failed, but if we run a query against sys.tables, we can see that the attack was successful, as shown in Figure 10-11.
Figure 10-11
Results of table drop attack
Attacking the Network
If an attacker plans to attack the wider network, then they are likely to make use of the xp_cmdshell system stored procedure, which allows administrators to run operating system commands from within SQL Server. First, an attack will want to check if the account that the website is using to connect to the database engine has sufficient permissions. They can leak this information by using the code in Listing 10-19.
' AND 1= (SELECT SUSER_SNAME())--
Listing 10-19
Leak Sysadmin Status
This will cause the account being used by the website to be leaked to an error message, as displayed in Figure 10-12.
Figure 10-12
Leaked account details
Now that the attacker knows that he has sysadmin access, he can perform subsequent attacks against the operating system. For example, injecting the code in Listing 10-20 will allow the attacker to create a local user in the operating system, which could subsequently be used for performing other attacks, such as SMB attacks, to leak the service account password.
Note
This attack assumes that the database engine service account has admin permissions in the operating system, which is a common configuration.
' AND 1=1; EXEC xp_cmdshell 'net user hacker WeakPa$$w0rd /ADD'--
Listing 10-20
Create an Operating System User
From a quick check of the Local Users and Groups, we can see that the attack was successful, as shown in Figure 10-13.
Figure 10-13
Results of create user attack
Preventing SQL Injection Attacks
There are many simple steps that can (and should) be taken on the website to mitigate the risk of SQL injection attacks, such as using regular expressions to validate the input of the UserName and Password fields to ensure that special characters, such as -- and ;, are not used. The website could also mitigate the risk of SQL injection attacks by using parameterized queries, instead of dynamically building T-SQL statements, using input from fields in the page.
As a database administrator, however, you are the last line of defense, for your organization’s data against would-be attackers. For this reason, you should never assume that the applications connecting to your database environment are secure, and you should take steps at the data-tier to prevent SQL injection attacks.
There are some simple configuration changes that you should make, such as disabling xp_cmdshell, which you can do by using the script in Listing 10-21. If you really must interact with the operating system, from inside SQL Server, then you should use more secure methods, such as creating CLR (.NET based) stored procedures to perform the required actions.
EXEC sp_configure 'show advanced option', 1 ;
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO
Listing 10-21
Disable xp_cmdshell
You should also avoid applications from connecting to SQL Server using highly privileged accounts, such as sa, or any other account that is a member of the sysadmin role, or even the db_owner database role, unless it is absolutely essential. Instead, you should assess the security requirements of each application and grant the account being used the appropriate level of permissions using the principle of least privilege.
The most important action you can take, however, is defining and enforcing hosting standards. To mitigate the risk SQL injection attacks, you should consider enforcing a hosting standard that requires applications to use an abstraction layer. You should also ensure that access to the database engine is only allowed via stored procedure, not via ad-hoc SQL statements. The following sections will discuss both of these standards in more detail.
Using an Abstraction Layer
The most common reason for allowing applications to connect to SQL Server with a highly privileged account is to reduce the administrative overhead associated with maintaining granular permissions. Using an abstraction layer provides a balance between security and administrative overhead.
When this approach is adopted, a new schema, usually called Abstraction or Client, is created that contains a series of views. The account used by the client application is granted the required permissions to the abstraction schema but is granted no other permissions within the database. Ownership chaining can then be used to ensure that the application has the required data access. This means that any attempt to drop or delete data from the underlying tables will fail with a lack of permissions.
Access via Stored Procedures Only
Enforcing a hosting standard, where access to the database is only allowed via stored procedure, as opposed to the application building its own ad-hoc SQL, can mitigate the risk of all but the most sophisticated SQL injection attack. Imagine that CarterSecureSafe website called a stored procedure, which accepted the parameters @UserName and @Password. The definition of the stored procedure may look like Listing 10-22.
CREATE PROCEDURE dbo.Authenticate
@UserName NVARCHAR(20)
, @Password NVARCHAR(512)
AS
BEGIN
SELECT *
FROM dbo.Users
WHERE UserName = @UserName
AND UserPassword = @Password
END
GO
Listing 10-22
Authentication Procedure
We can now simulate the results of the website executing this stored procedure, for a genuine login attempt, by using the script in Listing 10-23.
DECLARE @UserName NVARCHAR(20) ;
DECLARE @Password NVARCHAR(512) ;
SET @UserName = 'Reuben' ;
SET @Password = 'Jupiter' ;
EXEC dbo.Authenticate @UserName, @Password ;
Listing 10-23
Authenticate Using Stored Procedure
The results of this procedure call, shown in Figure 10-14, show that the expected results will be returned to the client application.
Figure 10-14
Results of authentication via stored procedure
Now let’s simulate what would happen, if an attacker attempted to spoof a user identity by using the script in Listing 10-24.
DECLARE @UserName NVARCHAR(20) ;
DECLARE @Password NVARCHAR(512) ;
SET @UserName = 'Reuben' ;
SET @Password = "' OR 1=1--' ;
EXEC dbo.Authenticate @UserName, @Password ;
Listing 10-24
Attempted Attck Using Stored Procedure
The results of this attempted attack, displayed in Figure 10-15, show that the dbo.Authenticate stored procedure simply returns no results.
Figure 10-15
Results of attempted attack using stored procedure
Summary
Performing an SQL injection attack involves injecting SQL statements into a field in a website or application, which will result in the intended SQL statement not being fully executed and unintended SQL statements being executed in its place. All Relational Database Management Systems can be susceptible to SQL injection attacks, and it is important that these risks are mitigated in order to protect your organization’s data.
A successful SQL injection can do terrible damage, such as deleting data or even dropping tables. An attacker may also use SQL injection to leak confidential details of the instance, server, or network, which may help them to perform further, targeted attacks against a company or organization. This is the cyber equivalent of “casing a join” that you plan to attack at a later date. SQL injection attacks can also be used to directly attack the server or network that hosts SQL Server, with the use of xp_cmdshell.
While we would all hope that a website or application has been developed to withstand SQL injection attacks, by using proper validation of input and using parameterized queries, DBAs should always be on their guard to ensure that the environment is protected from the database-tier.
DBAs can protect their environment by disabling insecure features, such as xp_cmdshell. The best chance that a DBA has of foiling the attackers, however, is to enforce hosting standards, such as an abstraction layer, or access via stored procedures only. Ensuring that applications only ever access data via stored procedure will mitigate the risk of all but the most sophisticated SQL injection attack.
