Learn SQL: SQL Injection

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

Emil Drkusic
Latest posts by Emil Drkusic (see all)

Author: admin

Leave a Reply

Your email address will not be published.