In this article, we will explain what the SQL injection attack is, why it could be hazardous, and how to defend our SQL database from this attack using parameterized queries and some third-party tools.
Introduction
When poorly designed or configured, enterprise database and information storage infrastructures are susceptible to a wide range of abuses and attacks. According to a survey conducted by the Open Web Application Security Project (OWASP) in 2021, there are ten critical security risks for web applications where Broken Access control, cryptographic failures, and injection are the top.
Even if the injection attack is considered an old hacking technique, the following figure shows that the injection security risk was on the top of the OWASP security risks list in 2017 and still be on the top three vulnerabilities in 2021.
Figure – OWASP top 10 security risks (Image Source: OWASP Top 10)
Side Note: All code examples in this article are made using SQL Server 2019 and Stack Overflow 2013 database.
What is an SQL injection attack?
It is one of the most popular attacks known for several decades. As the name suggests, this attack inserts an SQL command within another command built dynamically by the application. A successful and properly exploited injection may recover sensitive data from a database or modify, delete, or insert new data.
If the text field’s contents are just passed to SQL Server and executed, then that text field can contain a completely new query that does something different.
What are the principal application vulnerabilities that allow injection?
Several bad practices make your application vulnerable to SQL injection attacks:
- While interpreting the input values, dynamic queries and non-parameterized calls are used directly without context-aware escaping.
- The application does not validate, filter, or sanitize user-supplied data.
- The input data is directly used or concatenated with the SQL command.
- The database/tables structure is available within the dynamic SQL command.
- The source code is not reviewed.
- No penetration testing was performed on the application.
In brief, an SQL injection occurs when the developer provides information about the database schema within the application code and accepts user input directly into a SQL statement without adequately filtering out dangerous characters.
Examples
Stored procedure example 1
The following example is a stored procedure created to select the users from the Stack Overflow users table, where the WHERE clause is passed entirely as one parameter and concatenated with the SELECT command:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE PROCEDURE sp_SQLInjection @SelectFilter NVARCHAR(4000) AS BEGIN SET NOCOUNT ON; DECLARE @strQuery NVARCHAR(MAX) = 'SELECT [Id] ,[AboutMe] ,[Age] ,[CreationDate] ,[DisplayName] ,[DownVotes] ,[EmailHash] ,[LastAccessDate] ,[Location] ,[Reputation] ,[UpVotes] ,[Views] ,[WebsiteUrl] ,[AccountId] FROM [StackOverflow2013].[dbo].[Users] WHERE 1=1 ' IF ISNULL(LTRIM(RTRIM(@SelectFilter)),'') <> '' SET @strQuery = @strQuery + ' AND ' + @SelectFilter EXEC(@strQuery) END |
The WHERE clause is built on the application side and sent as a parameter to the database. This bad practice is used sometimes when developers want to create one dynamic query that can be used for several purposes in their application.
To check how an SQL injection can occurs within this stored procedure, we will replace the EXEC(@strQuery) line with SELECT @strQuery to check the executed SQL command.
If the @SelectFilter parameter contains a valid filter such as “[Age] = 30”, the SQL command will look like this:
|
1 2 3 4 5 6 7 8 |
SELECT [Id] ,[AboutMe] ,[Age] ,[CreationDate] ,[DisplayName] ,[DownVotes] ,[EmailHash] ,[LastAccessDate] ,[Location] ,[Reputation] ,[UpVotes] ,[Views] ,[WebsiteUrl] ,[AccountId] FROM [StackOverflow2013].[dbo].[Users] WHERE 1=1 AND [Age] = 30 |
Let us assume that the following value is passed as input parameter “[Age] = 30;TRUNCATE TABLE [StackOverflow2013].[dbo].[Users];–”, the SQL command will look like this:
|
1 2 3 4 5 6 7 8 |
SELECT [Id] ,[AboutMe] ,[Age] ,[CreationDate] ,[DisplayName] ,[DownVotes] ,[EmailHash] ,[LastAccessDate] ,[Location] ,[Reputation] ,[UpVotes] ,[Views] ,[WebsiteUrl] ,[AccountId] FROM [StackOverflow2013].[dbo].[Users] WHERE 1=1 AND [Age] = 30;TRUNCATE TABLE [StackOverflow2013].[dbo].[Users];-- |
In this case, two SQL commands are executed: a SELECT command that returns all users aged 30 and a TRUNCATE command that erases all the data in the users’ table after executing the select command.
Figure – Stored Procedure Example 1
Stored Procedure example 2
As another example, let us consider the following stored procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE PROCEDURE sp_SQLInjection2 @Age INT, @DisplayName NVARCHAR(255), @location NVARCHAR(255) AS BEGIN SET NOCOUNT ON; DECLARE @strQuery NVARCHAR(MAX) = 'SELECT [Id] ,[AboutMe] ,[Age] ,[CreationDate] ,[DisplayName] ,[DownVotes] ,[EmailHash] ,[LastAccessDate] ,[Location] ,[Reputation] ,[UpVotes] ,[Views] ,[WebsiteUrl] ,[AccountId] FROM [StackOverflow2013].[dbo].[Users] WHERE 1=1 ' SET @strQuery = @strQuery + ' AND [Age] = ' + CAST(@Age as varchar(10)) + 'AND [DisplayName] = ''' + @DisplayName + '''' + 'AND [Location] = ''' + @location + '''' EXEC(@strQuery) END |
This stored procedure takes two input parameters for the SELECT command, the user age, and display name values. The SELECT command is built normally if normal values are sent from the application. For example, Age = 30 and DisplayName = ‘Hadi’, and [Location] = ‘Lebanon’:
|
1 2 3 4 5 6 7 8 |
SELECT [Id] ,[AboutMe] ,[Age] ,[CreationDate] ,[DisplayName] ,[DownVotes] ,[EmailHash] ,[LastAccessDate] ,[Location] ,[Reputation] ,[UpVotes] ,[Views] ,[WebsiteUrl] ,[AccountId] FROM [StackOverflow2013].[dbo].[Users] WHERE 1=1 AND [Age] = 30 AND [DisplayName] = 'Hadi' AND [Location] = 'Lebanon' |
In case that end-user entered the following value as a display name “’Hadi’ OR 1=1;TRUNCATE TABLE [StackOverflow2013].[dbo].[Users];–‘” , the execute command will look like the following:
|
1 2 3 4 5 6 7 8 |
SELECT [Id] ,[AboutMe] ,[Age] ,[CreationDate] ,[DisplayName] ,[DownVotes] ,[EmailHash] ,[LastAccessDate] ,[Location] ,[Reputation] ,[UpVotes] ,[Views] ,[WebsiteUrl] ,[AccountId] FROM [StackOverflow2013].[dbo].[Users] WHERE 1=1 AND [Age] = 30 AND [DisplayName] = 'Hadi' OR 1=1;TRUNCATE TABLE [StackOverflow2013].[dbo].[Users];--' AND [Location] = 'Lebanon' |
As we can note, the executed SQL command is composed of three parts:
- A SELECT query that returns all users aged 30 and having a display name equal to “Hadi”
- A TRUNCATE command that erases all the data in the Users table
- All remaining commands are converted into commands using the two dashes “–”
Figure – Stored Procedure Example 2
We should note that injected SQL commands are not always a TRUNCATE command – as mentioned above – attackers can inject an update, delete, or even other commands. For example, the attacker can inject a command that lists the directories in the server “EXEC [master].[dbo].xp_cmdshell ‘dir'”, or even list all the tables/columns listed in the database “SELECT * FROM INFORMATION_SCHEMA.COLUMNS”, or create and authenticate a new user…
Vulnerable C# code example
Consider the following C# console application code: the end-user passes the display name as an argument and retrieves all the relevant users from the Stack Overflow database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
static void Main(string[] args) { string conString = @"…"; using (SqlConnection connection = new SqlConnection(conString)) { connection.Open(); using (SqlCommand command = new SqlCommand( "SELECT TOP 1000 * FROM Users WHERE [DiplayName] = '" + args[0].ToString() + "' ORDER BY Age", connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { Console.WriteLine(reader.GetValue(i)); } Console.WriteLine(); } } } } } |
As we explained in the previous example, if the end-user passes a value as “‘Hadi’ OR 1=1;TRUNCATE TABLE [StackOverflow2013].[dbo].[Users];–‘”, it will erase all the data stored within the users’ table.
Parameterized queries
One of the most common practices to protect the code against SQL Injection is encapsulating and parameterizing our SQL Commands. Parameterized queries is a technique that aims to separate the SQL query from the user input values.
The user input values are passed as parameters. They can no longer contain an executable code since the parameter is treated as a literal value and checked for the type and length.
Stored procedure examples
Getting back to the examples above, we should totally avoid using the first approach we explained; it is highly vulnerable to pass the whole WHERE clause as a parameter while building the SQL query dynamically. While in the second example, as the stored procedure parameters are defined adequately, we should only change the way the SQL command is built as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE PROCEDURE [dbo].[sp_SQLParameterized] @Age INT, @DisplayName NVARCHAR(255), @location NVARCHAR(255) AS BEGIN SET NOCOUNT ON; SELECT [Id] ,[AboutMe] ,[Age] ,[CreationDate] ,[DisplayName] ,[DownVotes] ,[EmailHash] ,[LastAccessDate] ,[Location] ,[Reputation] ,[UpVotes] ,[Views] ,[WebsiteUrl] ,[AccountId] FROM [StackOverflow2013].[dbo].[Users] WHERE [Age] = @Age AND DisplayName = @DisplayName AND [Location] = @location END |
In that case, if the user passes a value like ‘Hadi’ OR 1=1;TRUNCATE TABLE [StackOverflow2013].[dbo].[Users];–‘, the SQL command is complied as the following:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [Id] ,[AboutMe] ,[Age] ,[CreationDate] ,[DisplayName] ,[DownVotes] ,[EmailHash] ,[LastAccessDate] ,[Location] ,[Reputation] ,[UpVotes] ,[Views] ,[WebsiteUrl] ,[AccountId] FROM [StackOverflow2013].[dbo].[Users] WHERE [Age] = 30 AND [DisplayName] = '''Hadi'' OR 1=1;TRUNCATE TABLE [StackOverflow2013].[dbo].[Users];--' AND [Location] = 'Lebanon' |
C# code example
In C#, we can use the SqlParameter object to pass the user input values as parameters. Considering the C# console application used above, we can use the SqlParameter class as follows to pass the display name as a parameter:
Figure – Parameterized SQL command using SqlParameter class
Performance implications
Besides preventing SQL injection attacks, parameterized queries improve the SQL command execution performance. When using dynamic queries, the entire query must be constructed and compiled by SQL Server every time it is executed. In contrast, when we use parameterized queries, SQL Server generates a query execution plan once and then plugs the parameter value into it.
Solving the quotation’s issues
Using parameterized queries helps the developers avoid syntax errors since they don’t have to keep track of single and double quotes to construct SQL commands.
Third-party tools to protect against injection attacks
Besides using parameterized queries and other techniques, there are several tools developed by leading companies to help developers and database administrators prevent SQL injection attacks.
ApexSQL Refractor
ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in that formats and refactors SQL code using eleven code refactors and more than a hundred formatting options. With it, you can encapsulate your SQL queries within parameterized stored procedures to prevent SQL injection, expand wildcards, fully qualify object names, rename SQL database objects and parameters without breaking dependencies, and much more.
ApexSQL Log
ApexSQL Log is a tool that reads and analyzes the transaction log of a SQL database. It analyzes transactions and provides an output in a human-readable format. This tool is an excellent solution for reversing inadvertent or malicious database transactions; it allows database administrators to identify SQL injection attacks immediately, isolate affected/damaged data, and reverse/repair the damage.
Summary
This article explains what a SQL injection attack is and when it can occur. In addition, we explained one of the most common practices to prevent code injection, and finally, we listed some of the third-party tools that can help protect a database against this type of attack.
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023