While security teams often focus exclusively on the latest operating system (OS) patches, endpoint protection, firewall hardening and penetration tests, one of the oldest and most dangerous forms of security threats still slips through the cracks: SQL Injection. A single vulnerable web form or poorly written database query can hand an attacker direct access to your data. Many breaches do not begin with sophisticated zero-day exploits, but with simple input forms or query parameters that blindly trust user-supplied input data.
This post has the following sections – feel free to jump straight to the section of your interest:
What is SQL Injection?
SQL injection is a type of cyber attack where someone tricks a website or application into running unintended database commands. Imagine a website asks you to type your username into a text box. Normally, the application takes what you type and safely looks it up in the database via a SQL query or a stored procedure call. But if the application or database code is poorly written, an attacker can type carefully crafted text instead of a normal username, tricking the database into executing malicious instructions.
A successful SQL Injection atack can lead to unintended consequences like:
- gaining read or write access to sensitive data that’s not normally available to the user
- bypassing the login process completely to gain access to an application
- taking control of a server and performing disruptive actions like taking a database offline, dropping a database or running a malicious operating system command via xp_cmdshell.
Examples of SQL Injection:
I am going to provide some simplified examples of SQL Injection below. The idea is to make database and web developers aware of how SQL Injection works, so that they can code effectively. I am not trying to encourage anyone to go and exploit using these examples.
Example 1: Hijacking a simple self service stored procedure to run other commands
This is an example I had experienced in real world (obviously have simplified the example). In this case, the database engineers have provided the users a stored procedure, to enable them to create their own database for testing purposes. The users don’t have the necessary permissions to create the database themselves, but this signed stored procedure runs in an elevated context and creates the database for the users.
CREATE PROC dbo.CreateDatabase
(
@DBName sysname
)
AS
BEGIN
DECLARE @CMD nvarchar(1024)
SET @CMD =
'CREATE DATABASE ' + @DBName
PRINT @CMD
EXEC (@CMD)
END
GONow USER_1 runs the stored procdure as shown below to create a database for himself, named MyDB:
EXEC dbo.CreateDatabase 'MyDB'This successfully creates a database named MyDB – all is well and as expected.
Now a malicious user USER_2 decides to OFFLINE USER_1‘s database MyDB. USER_2 manipulates the input parameter of this stored procedure by injecting the code to offline MyDB as shown below:
EXEC dbo.CreateDatabase 'MyDB; ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE--'The first part of the input provides the database name MyDB, and the second part of the input contains the command required to OFFLINE the datatabase MyDB. As MyDB already exists, the stored proecdure raises an error saying the database already exists, but it proceeds to execute the second part of the parameter and offlines the database MyDB.
Then USER_2 decides to drop the MyDB database. He simply manipulates the input as shown below, and manages to drop the database:
EXEC CreateDatabase 'MyDB; DROP DATABASE MyDB--'So, how does this work?
After MyDB, the semi-colon (;) terminated the command, Whatever follows the semi-colon marks the next command to be executed. After the malicious command, the comment marker (–) comments out the actual code to prevent syntax errors to make sure the code works.
Whilst the intention of the database engineer was to create a self service stored procedure to enable users to create their own databases, he ended up enabling a backdoor for users to exploit his code and run various other commands – all down to poor coding practices and lack of input validation.
Example 2: Manipulating a search string to access other tables or to get all the data from a table
This is another real world example from one of my previous clients. I have simplified the example here though. Idea is to provide dynamic search capability on the website, where the user could choose which column to search. The deverloper achieved this by using a dynamically constructed SQL statement.
To set up this example, let’s create a table and insert some data into it:
CREATE TABLE dbo.Customers
(
FirstName varchar(32),
LastName varchar(32),
Email varchar(64),
HomeAddress varchar(128)
)
GO
INSERT INTO dbo.Customers (FirstName, LastName, Email, HomeAddress)
VALUES
('John', 'Donaldson', 'JD@something.com', '1 Bank St, London'),
('Will', 'Henderson', 'WH@example.com', '32 Office Road, Liverpool'),
('George', 'Austin', 'GA@domain.com', '5 High St, London'),
('Tom', 'Dixon', 'TD@email.com', '25 Well St, Manchester'),
('Sebastian', 'Walters', 'SebW@email.com', '215 Museum Rd, Birmingham')
GOLet’s create our stored procedure dbo.DynamicCustomerSearch to demonstrate the SQL Injection
CREATE PROC dbo.DynamicCustomerSearch
(
@ColumnName varchar(256),
@SearchString varchar(512)
)
AS
BEGIN
DECLARE @CMD varchar(1024)
SET @CMD =
'SELECT FirstName, Email, HomeAddress
FROM dbo.Customers
WHERE ' + @ColumnName + ' LIKE ''%' + @SearchString + '%'''
PRINT @CMD
EXEC (@CMD)
END
GOLet’s look at some genuine usage examples of this dynamic search stored procedure:
--Genuine search 1:
EXEC dbo.DynamicCustomerSearch 'FirstName', 'John'
/*
This returns the list of customers whose first name contains the word John
FirstName Email HomeAddress
John JD@something.com 1 Bank St, London
*/--Genuine search 2:
EXEC dbo.DynamicCustomerSearch 'HomeAddress', 'Liverpool'
/*
This returns all customers whose address contains the word Liverpool
FirstName Email HomeAddress
Will WH@example.com 32 Office Road, Liverpool
*/Now let’s look at some examples of how this stored procedure can be exploited with SQL Injection.
--SQL Injection example 1:
EXEC dbo.DynamicCustomerSearch 'HomeAddress', 'London'' UNION ALL SELECT name, cast(principal_id AS varchar(10)), DB_NAME() FROM sys.database_principals--'
/*
This returns all customers whose address contains the word London, as well as all the rows from sys.database_principals (which is a list of all database users/roles, their user ID, and the database name)
FirstName Email HomeAddress
John JD@something.com 1 Bank St, London
George GA@domain.com 5 High St, London
public 0 MyScripts
dbo 1 MyScripts
guest 2 MyScripts
sys 4 MyScripts
Dom1\USR1 501 MyScripts
dbSQLUsr1 552 MyScripts
*/How does the above SQL Injection work?
The input search string simply added a UNION ALL and a query to get details from sys.database_principals, making sure the columns of the malicious query matched the actual query. As there was no validation or other defence measures, the stored procedure executes the second query, joins both the resultsets together and the website displays the data, exposing the database user names.
--SQL Injection example 2:
EXEC dbo.DynamicCustomerSearch 'HomeAddress', 'London'' OR 1 = 1--'
/*
This returns ALL customers regardless of the search string - because "OR 1 = 1" is true for all rows
FirstName Email HomeAddress
John JD@something.com 1 Bank St, London
Will WH@example.com 32 Office Road, Liverpool
George GA@domain.com 5 High St, London
Tom TD@email.com 25 Well St, Manchester
Sebastian SebW@email.com 215 Museum Rd, Birmingham
*/How does this work?
The input search string simply added “OR 1 = 1” to the end of the original search string, which resulted in the WHERE condition being true for every row of the dbo.Customers table – returning all the rows from the table, there by exposing all the customer details on the website.
How to defend against SQL Injection by following best practices?
Here are some tips, tricks and best practices to follow for writing secure code and avoid becoming an SQL Injection victim.
The principle of least privilege
Make sure your applications connect to your SQL Server using the minimum required permissions.
Do not let your applications access the database with higher privileges like db_owner role membership, just because its easier. Also, do not assign blanket database level read or write permissions using db_datareader/db_datawriter role memberships.
Only grant read/write permissions on the objects that are used by the application. Better yet, do not grant any direct read/write permissions on the individual tables – instead, grant access on stored procedures only. This lets users manipulate tables via stored procedures, without direct permissions on the tables.
Create database roles and assign the minimum required permissions to those roles – then, add your application or website user to that role.
If you follow the above guidelines and run with minimum required permissions, then, even if a hacker manages to inject SQL code, the application won’t be able to execute that code, as it hasn’t got any additional permissions outside of what’s originally granted.
Be very careful with dynamic SQL
Many inexperienced database developers resort to dynamic SQL as a default option, even when its perfectly possible to meet the requirement using static SQL commands. Dynamic SQL is basically a string that stores an SQL command or query that has been dynamically generated by combining/concatenating different variables. This string is then typically executed using the EXEC command, as in: EXEC (@MyCommand)
Dynamic SQL is inherently insecure, especialy when its generated by combining input from users.
Where possible, avoid dynamic SQL. If you must use dynamic SQL, parameterise the strings, substitute the parameters with the values of the input variables, and execue the string using sp_executesql. Parameterising and executing via sp_executesql makes sure any malicious input is treated just like a parameter value, rather than execute it.
Do not trust user input and validate all input
SQL Injection primarily happens because of malicious user input into either a web form or a text box in an application.
Always limit the length of the input, to what is actually needed. For example, if a particular database column is sized varchar(16), make sure you only accept 16 characters as input in your application/web form/stored proc parameters.
Where possible, let users choose the input from a pre-populated listbox/combo box, rather than letting the users type it. This reduces the possibility of malicious input.
Validate the input to make sure it doesn’t contain any unwanted characters. For example, there is no need for a semi-colon or comment markers (like /* or –) inside the input for First Name column. If you are using dynamic SQL extensively, you might want to have an SQL Injection Check function that validates the input and rejects any dangerous input.
QUOTENAME() function is very helpful in neutralising any malicious input by putting brackets or quotes around the text, basically nullyfing the danger. For example, if you want to wrap a text input inside single quotes, do not attach the quotes before and after the string manualy. Get QUOTENAME() to do it automatically as it encloses the entire input – that is QUOTENAME(@FirstName, ””) instead of ”” + @FIrstName + ””
Here’s how to resolve the SQL Injection issue with our Example 1, using QUOTENAME() function:
CREATE PROC dbo.CreateDatabase
(
@DBName sysname
)
AS
BEGIN
DECLARE @CMD nvarchar(256)
SET @CMD =
'CREATE DATABASE ' + QUOTENAME(@DBName)
PRINT @CMD
EXEC (@CMD)
END
GOTesting is very important
In addition to the usual functionality testing, make sure testing the input columns is also an important part of your testing. Robust input handling reduces the risk of becoming an SQL Injection victim. Have your test team try to inject bad code where appropriate and check the results – address any weaknesses.
I hope this post gives you a good overview of what SQL Injection is, how it works, how to code effectively to prevent SQL Injection. Please share your thoughts by leaving a comment below.
