Emil Drkusic
the data model

Learn SQL: SQL Injection

November 2, 2020 by

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 data model

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.

dynamic sql example

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.

sp_executesql example

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.

dynamic sql create table

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.

dynamic sql drop table

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.

dynamic sql insert into

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

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query?
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions
Learn SQL: SQL-Related Jobs
Learn SQL: Non-Equi Joins in SQL Server
Learn SQL: SQL Injection
Learn SQL: Dynamic SQL
Learn SQL: How to prevent SQL Injection attacks
Emil Drkusic
Latest posts by Emil Drkusic (see all)
SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. 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

168 Views