A SQL injection attack is one of the most commonly used hacking techniques. It allows hacks to access information from a database that is otherwise not publically accessible.
Owing to its simplicity, SQL injection is one of the most popular database hacking techniques.
In this article, you will see how SQL injection works with the help of examples, and also how to prevent a SQL injection attack.
Create a dummy database
The following script creates a dummy database named BookStore with one table i.e. Books. The Books table has four columns: id, name, category, and price:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE Database BookStore; GO USE BookStore; CREATE TABLE Books ( id INT, name VARCHAR(50) NOT NULL, category VARCHAR(50) NOT NULL, price INT NOT NULL ) |
Let’s now add some dummy records in the Books table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE BookStore INSERT INTO Books VALUES (1, 'Book1', 'Cat1', 1800), (2, 'Book2', 'Cat2', 1500), (3, 'Book3', 'Cat3', 2000), (4, 'Book4', 'Cat4', 1300), (5, 'Book5', 'Cat5', 1500), (6, 'Book6', 'Cat6', 5000), (7, 'Book7', 'Cat7', 8000), (8, 'Book8', 'Cat8', 5000), (9, 'Book9', 'Cat9', 5400), (10, 'Book10', 'Cat10', 3200) |
The above script adds 10 dummy records in the Books table.
What is the SQL injection attack?
A SQL injection attack takes advantage of a vulnerability in a web application that allows hackers to modify the queries that are being executed on the underlying database. Web applications that directly execute user inputs as a query are those that fall prey to SQL injections. This allows attackers to execute malicious queries, also known as malicious payloads on database servers.
Let’s see a very simple example of how a SQL injection attack can be executed on a database server. Consider a scenario where you have a web application that accesses the BookStore database that we created in the last section. Your web application has a search box where a user can enter the name of a book and, if the book exists, the id, name, and price of the book is shown on the webpage.
One way to return the book searched by a user is via sp_executesql stored procedure that executes the SQL query in the form of a string. Here is an example of that:
|
1 2 3 4 5 |
DECLARE @BookName NVARCHAR(128) DECLARE @SQL_QUERY NVARCHAR (MAX) SET @BookName = '''Book6''' SET @SQL_QUERY =N'SELECT id, name, price FROM Books WHERE name = '+ @BookName EXECUTE sp_executesql @SQL_QUERY |
Here we declare two variables @SQL_QUERY and @BookName. The name of the book entered by the user in the search box of the web application is stored in the @BookName variable. The @SQL_QUERY contains the select query which returns the book name where the name is equal to the value in the @BookName variable. Finally, the sp_executesql query is used to execute the @SQL_QUERY. Here is the result.
We get the desired result but the above query is vulnerable to SQL injection. There are different ways in which SQL injection can be executed via the above query.
We will discuss two of the simplest and most commonly used ways below.
1. SQL injection via 1 = 1
SQL injection can be executed by concatenating 1 = 1 using the OR clause in a search query.
Let’s see an example of this:
|
1 2 3 4 5 6 |
DECLARE @BookName NVARCHAR(128) DECLARE @SQL_QUERY NVARCHAR (MAX) SET @BookName = '''Book6'' OR 1 = 1' SET @SQL_QUERY =N'SELECT id, name, price FROM Books WHERE name = '+ @BookName EXECUTE sp_executesql @SQL_QUERY |
In the above script, the search query is “Book 6 or 1 = 1”. This query will return true for all the rows in the Books table since 1 = 1 will return true for all the rows. Therefore all the results in the Books table will be displayed. Here is the output:
This might not be a problem for the Books table but if you have a table containing the user names and passwords of your users, the retrieval of all of their user names and passwords is a business-critical issue.
2. SQL injection via “” = “”
Another common way to execute a SQL injection attack is by appending “”=”” using an OR clause at the end of the search query. Again, this will also always return true.
Here is an example:
|
1 2 3 4 5 |
DECLARE @BookName NVARCHAR(128) DECLARE @SQL_QUERY NVARCHAR (MAX) SET @BookName = '''Book6'' OR '''' = ''''' SET @SQL_QUERY =N'SELECT id, name, price FROM Books WHERE name = '+ @BookName EXECUTE sp_executesql @SQL_QUERY |
Preventing SQL injection
The best way to prevent a SQL injection is to use parameterized queries rather than directly embedding the user input in a query string. Let’s see how an SQL injection can be prevented via a parameterized query:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @BookNameValue NVARCHAR(128) DECLARE @SQL_QUERY NVARCHAR (MAX) DECLARE @PARAMS NVARCHAR (1000) SET @BookNameValue = 'Book6' SET @PARAMS = '@BookName NVARCHAR(128)' SET @SQL_QUERY =N'SELECT id, name, price FROM Books WHERE name = @BookName' EXECUTE sp_executesql @SQL_QUERY ,@PARAMS, @BookName = @BookNameValue |
In the above script, the value entered by the user is stored in the @BookNameValue variable.
Next, in the @SQL_QUERY string, the @BookName parameter is used to search the book. The @PARAMS variable defines the list of parameters. In this case, we only have one parameter i.e. @BookName. Finally, while executing the query via sp_executesql stored procedure, the @SQL_QUERY, the @PARAMS variable, and the value for the @BookName parameter are passed at runtime, in the output you will see the record of the book named “Book6”.
Let’s now try to execute a SQL injection on this parameterized query. We will append “OR 1 = 1 ” at the end of the @BookNameValue variable to see if we can retrieve all the records from the Books table:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @BookNameValue NVARCHAR(128) DECLARE @SQL_QUERY NVARCHAR (MAX) DECLARE @PARAMS NVARCHAR (1000) SET @BookNameValue = 'Book6 OR 1 = 1' SET @PARAMS = '@BookName NVARCHAR(128)' SET @SQL_QUERY =N'SELECT id, name, price FROM Books WHERE name = @BookName' EXECUTE sp_executesql @SQL_QUERY ,@PARAMS, @BookName = @BookNameValue |
If you execute the above query, you will see that no records will be returned.
This is because in the @PARAMS variable we defined that the type of @BookName should be NVARCHAR, therefore the sp_executesql stored procedure expects a single string value for the @BookName parameter, without any OR/AND conditions, etc. Thus, SQL injection is prevented.
Conclusion
SQL injection attack is one of the most commonly exploited hacking techniques to access private database records. This article explains how SQL injection attack works and how you can use parameterized SQL queries to avoid SQL injection attacks.
View all posts by Ben Richardson
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021