SQL Injection Basics

[Man]

Today we will talk about SQL injections – what they are, what to eat them with, and what can be done about them. We will look at the types of injections, how they can be automated, what can be achieved and what it all looks like.

Hello everyone, dear friends!
Today we will talk about SQL injections – what they are, what to eat them with, and what can be done about them. We will look at the types of injections, how they can be automated, what can be achieved and what it all looks like.

As much as I would like to touch on starting topics like “what is the danger of composing queries using concatenation” or “what does a quotation mark in a parameter mean” – I’m afraid that I can’t do without it. After all, this particular article is not intended for people who have never heard of SQL injections before, and in general – since we’re going to go in order, let’s go in order. I’m writing here for complete newbies.

It is clear that it will not be possible to cover everything here, from the basics to the last levels, otherwise the article will turn into a book, so here we will only deal with the main base on which everything is built. And to dig deeper - who knows, maybe within the framework of the new series of articles there will be something about advanced injections)

Introduction​

So, let's look at how classic SQL injections are generally allowed and what it all looks like in general.

We have a website/web application that works with data. Let's say it's some kind of social network. In this case, it absolutely needs to store data of at least one business entity - the user, since everything is built around these characters. Their gender, age, first and last name, emails, passwords and other information - all this needs to be stored somewhere.
This is what databases are for - a kind of web application that exists precisely to store data from other web applications.

If you look at the essence right away, so as not to get confused later in the terminology - a Database is a general name for some abstract thing inside which data is stored.

But there is also such a thing as a DBMS or Database Management System. And this is essentially a proper name. A DBMS is a specific program that is responsible for ensuring that access to data can be easily provided, that no one can get into it, and other important functions.

• To draw an analogy, the Database is a set of books in a library, and the DBMS is the library director who gives instructions to whom to give out what book, where not to give out anything to whom, what can be given to an orphanage and what to a school, and what books can be burned.
• And at the same time, libraries can have different names - Pushkin, Central, MSU Library, etc. But their functions are more or less the same and each of them is a library. I hope the analogy is clear.

The databases themselves are of two types - relational and non-relational. Specifically here we will be interested only in the first, relational or SQL databases.

So what are relational databases? Essentially, each database is simply a set of tables containing a set of data about a specific object. Tables can be linked to each other in order to bind data from others to one entity – for example, a list of posts from a user.

To work with this data, a special query language is used. It is called SQL. Its syntax is somewhat reminiscent of ordinary English in a commanding tone.

• For example, a standard command like:

SQL:Copy to clipboard

SELECT  ALL  FROM USERS WHERE name = 'John' ;

Everything is quite simple and intuitive, it is translated as: select all records from users where the name is John . That is, select all Johns in the table and return their data. Nothing complicated.

• So what is the problem and how does the vulnerability that so much is talked about arise?

The essence of the attack itself is that the very request through which the application accesses the database, with a certain carelessness of the developer, the user can begin to control. Let's consider how this happens in two of the most popular examples.

Bypass authorization​

Let's imagine a situation - we have an application in which authentication by login and password is implemented:

Everything is standard. In order to understand where SQL injection can be here, you need to imagine how the authentication process occurs.

And it happens something like this:

• The user provides login and password
• The application checks this data against the records in the database.
• If there is a record that contains this login and password, the program allows access
• If there is no record, there is no access.

Problems, in case of SQL injections, arise at the stage of the first two points. How to check the presence of a user in the database? It is logical that you need to create a query that will return a user with the required login and password. That is, the query will look something like this:

SELECT  *  FROM Users WHERE login = ' user ' AND password = ' 1234 ' ;

Where useris the login, and passwordis the password, entered by the user. Well, it would seem, what's the problem? We take the data from the user and simply substitute it into the request, approximately like this:

• But there is a problem. And here is what it is:

Imagine a situation where a user enters not just their name, but adds the symbol to the end of their login‘

And if we substitute his login directly into the query, the query will look like this:

SELECT  ALL  FROM Users WHERE login = ' user '' AND password = ' 1234 ' ;

See? There are now two quotes - one added by the user, and the second we added ourselves when creating the query string. The SQL language is very sensitive to such things and it will not like such a joke with an extra quote in the query. This will cause a query error to the database, the query will not be executed, and this in turn will lead to an error on the server:

So the first thing we can do with this attack is to cause an error on the server side. It's cool, but it doesn't really do any good.

What if we substitute not only a quotation mark into the query? In the SQL language, as in the PL, there are, for example, comment signs that make part of the code invisible to the processor.

Now let's imagine that we add this symbol together with the quotation mark:
test’ --

• So we get a request like this. And what happens if we try to execute the request? Unexpectedly, we are authorized:

But how did this happen if we only entered the username and the password was incorrect? Let's look at the query again - essentially, our quote symbol first closed the section where the login was, and then our comment threw away all the remaining code like this:

SELECT  ALL  FROM Users WHERE login = ' user ' -- ' AND password='1234';

We do all sorts of things with the base​

But websites/web applications go to the database not only to extract user data from there for authorization.

The second popular example is search. Let's say we have an application that stores news. And search is implemented for news. A fairly trivial example:

First, let's try to imagine what the request would look like in this case.

• And it will look something like this:

SELECT  ALL  FROM news WHERE title LIKE  '%text%' ;

Or, to draw an analogy, select all values from a table newswhere the word will be present textin any position in the title.

It is logical that the weak point in this case will be text , since it is set by the user when searching for news.

It would seem - well, what can we do here? If we try to substitute the injection from the previous example, we will achieve nothing. There is only one parameter and we do not need to close anything else. But in fact, at this stage everything is just beginning.

Let's imagine that we have substituted the injection from the previous example. The request will then look like this:

SELECT  ALL  FROM news WHERE title LIKE ' % text '   #%';

Now pay attention to the area highlighted in yellow. Before it, there is a parameter closing, and after it there is a comment that discards the rest of the request.

And here's how we can use this - in the SQL language, in one query we can write several commands - for example, extract records from one table, and then immediately from another.

Such commands within one request are separated by a semicolon. That is why it is at the end of each command.

Let's imagine that we substitute the following set of characters into the injection:

‘;#

• And our request will look like this:

SELECT  ALL  FROM news WHERE title LIKE ' % text ' ;   #%';

And again, pay attention to the area highlighted in yellow – now we can write any request there and it will be executed. Thus, we can do whatever we want with the database.

Let's look at it in order. Usually the whole attack occurs in several stages:

1. Identifying the weak spot and clearing the gap
2. Determine the number of fields that the application requests from the database
3. Defining the display of fields from the previous item on the page
4. Defining the purpose and structure of a table
5. Directly, injection

Now let's take a closer look at each of the 5 stages...

Well, now let's continue!

Step 1 – Finding a Vulnerable Spot​

The first thing we'll try to do is enter some search criteria and add a quotation mark to it:

• As we can see, this broke the site, it gave us a 500 error:

This is already the first sign that something went wrong. If we close the rest of the request with a comment, everything will work again:

This means that the space between the quote and the comment will be our “entry point”

A small "BUT":

Comments may be different for different DBMS. For example, “#” will work for MySQL, but for SQLite, which we use in our example, this will not work. For it, we use the comment symbol “ -- “ If you use it, keep in mind that the two dashes must be marked with spaces on both sides.

Step 2 – Determining the number of requested values​

Next, we will need to determine how many values the application takes from the database.

Here's what that means: this is what our database query looks like, which pulls out news entities. There is an operator here * (ALL), which means the query will result in rows containing all three fields: id, title, text, since this is the set of fields that the NEWS table contains:

But from the user's side, we don't see the request, which means we can't initially determine how many fields the application pulls out.

In order to determine this, we use such a thing as order by <x>.
This command is used for sorting, and in our case it can help to find out what we need. This is done like this - we substitute the construction into our entry point:

ORDER BY X

Where xis the expected number of fields. In our case, the correct value is x = 3, since we have already determined above that three fields are returned in our request.

If we try to pass an invalid x (greater than the number of requested fields), we will get an error:

Thus, by simply iterating over the values of x until we get an error, we can find out the number of fields we are interested in.

Once again - as long as the value order by xdoes not exceed the number of requested fields - there will be no error. As soon as we get an error - then the number will be equal to x-1

Step 3 – Defining the display​

After we have found out how many fields our application pulls out, we should find out which fields on the page display the result of which field. It doesn't sound quite clear, so let's look at the example again:

Thanks to the previous step, we know that the number of requested fields is three. And now we can form a structure like this:

union select 1,2,3 –

Let's try to execute the following query:

And what did this give us? Well, here's what: we see that where the title was displayed, the number 2 appeared, and where the text of the news was displayed, the number 3 appeared.

In order to understand what happened, let's remember what we requested from the DB - id(1 - first field), title(2 - second field), text(3 - third field). Further, the user does not need to know the first field, but the developer displayed the values of the second and third on the page.

And the important knowledge we got is that the values of fields 2 and 3 are displayed on the page. This is important and will be useful in the next step.

Stage 4 – defining the database structure.​

Next, using the knowledge we've already acquired, we need to somehow figure out a general idea of the database we now have access to. And this stage very closely intersects with the stage of defining the goal, and here's why - all further actions will depend on what exactly we want to do.

There can be many goals : extract data from a specific user or record, dump the entire database, or even drop everything to hell.

And depending on what task we face, our further actions will depend.

Let's first define that our goal in this case is to extract data about all users and then drop the entire table with users.

Where should we start in this case? Well, here's where - we need to first find out which table stores data about our users. It's already obvious to us that the table is called Users, but if we were in a black box, it wouldn't be obvious. The developers could have called the table "users_main", and "user", and God knows what else. Therefore, first we need to find out what the table that contains information about users is called .

There are several ways to do this. And each of them will depend very much on what DBMS the application works with. And this needs to be found out.

The methods for this, again, are different.

For example, our vulnerable application uses SQLite. This DBMS has a built-in function “sqlite_version()”, which is specific to it and will only work in this DBMS. Now, how do we execute it? Again, it’s very simple – we know that the second and third columns are displayed on the page. Let’s substitute the function instead of field 2, like this:

test’ union select 1,sqlite_version(),3 --

As a result, in place of field number two we will see the result of the function execution - the database version:

From this we draw two important conclusions - firstly, the DBMS that the application uses is indeed slqite, and secondly, we can use fields 2 and 3 to output the data we need.

Unfortunately, sqlite, unlike its big brothers, such as MySQL, does not contain system tables that contain information about databases. This is due to the fact that each database is a separate file.
But, there is a system table that contains information about other tables.

This is the simplest and at the same time most convenient way to extract data about what tables are in the database.

So, in SQLite there is a table –sqlite_master. It contains all the information we need.

This is what it looks like:

As you can see, this table contains a lot of useful information about tables, their names, and even SQL commands for creating these tables.

Right now we are interested in the table names. In order to extract them, we need to use the following SQL query:

SELECT name FROM sqlite_master ;

Now let's see how to insert this request into our payload so that everything fits and comes together like a constructor. This is where union select.
Let's remember what values we output to the page - these are 2 and 3. This means that instead of these values, we can output the fields we need.

test’ union select 1,name,3 from sqlite_master --

As a result, we get the names of the tables from the database:

Such tables, containing information about other tables, exist in all popular DBMS. The problem is that the administrator can (and in fact should) restrict access to these tables.

And in that case, we'll have to poke around a bit. Let's start with an assumption. We need user data, so it's logical to assume that the table is called something similar. Let's assume that the table name is user.

How do we check if our assumption is correct? Let's do it this way - with uniuon select we can select data from different tables, so let's try adding the assumed table name to the end of our union. And now our query will look like this:

union select 1,2,3 from user --

If we try to execute such a request, the server will return an error:

This happens because such a table does not exist, which means our assumption is incorrect.

Let's move on. Let's assume that the table is called Users. We do approximately the same thing, only we change the table name:

And the request is processed. We do not receive errors. And although we have not received any data from the table (yet) – the server did not return an error, which means such a table exists.

Next, we can proceed to the actual implementation of the first stage of our plan. Namely, extracting user data. Here again, a problem arises that is solved almost exactly the same way as the previous one - either by trial and error, or through metadata, namely, calculating fields.

How to find out the fields? Let's remember our table with metadata. If you look at the screenshot again, you will see that we have a sql field there, which contains a command to create such a table. And this command also contains the names of the fields. Let's try to display the table name (name) in the header, and this very command from sql in the description:

test’ union select 1,name,sql from sqlite_master --

And this is what we will see as a result:

Well, after we have learned all the necessary information, we can move on to the final stage. We will do this using the following request:

test‘ union select 1,name,password from Users --

And as a result we get a list of all users and passwords. In place of the header is the user name, in place of the text is the password. The first task is completed:

The example is, of course, greatly simplified. Sensitive data, such as passwords, are always stored in encrypted form in databases. Therefore, most likely, if you manage to pull off something like this, it will not be so easy to extract passwords, since instead you will extract password hashes, which will then have to be brute-forced.

Now let's return to the second task - to demolish the users table. Here everything is both simpler and more complicated.

Firstly, the whole structure that we built in several stages above will not be useful to us. It is needed specifically for manual data extraction. For deletion, we will use the following structure:

test’; drop table Users --

And this is what happens now when you try to access the users table:

That's it. The second task is done, we've demolished the users table.

Of course, the possibilities of SQL injections do not end there.

But if you want to understand this attack perfectly, you will first have to understand SQL perfectly. Since your future success will depend on understanding what tricky query can be used to get the desired result. And of course, you will also have to keep your finger on the pulse of the latest technologies related to relational databases.

Let's wipe off our rose-colored glasses a bit.
The examples given above do exist, but the probability of encountering something like that is now quite low.

And first of all, because there is such a thing as blind injection (blind sql-injection)

What is it and how does it complicate our lives? Here's how:

If you recall the examples, you will understand that the server's error return played a big role. Every time the server crashed with an error, we realized that something was wrong and something had to be changed.

What if the server didn't return an error? For example, in the case of search, imagine that instead of 500, the server simply behaved as if the search was unsuccessful, i.e. simply returned a page that said nothing was found. And it would always return it

How in this case could one determine most of the attack development vectors, and in principle determine that the page is vulnerable. If you remember, the principle of determining the vulnerability of a page was an error in response to a quotation mark in a request.

And here you really have to pretend to be a blind kitten. But, as practice shows, even such types can be dealt with. For example, despite the fact that the error will not be returned, you can understand that we have a vulnerable page if you insert a quotation mark and a comment into the request, that is, by creating an empty injection:

In this case, the page will ignore all these symbols and the request will work as it should. This is already an indicator that special symbols got into the request.

Automation with SQLMAP​

Well, and according to the classics - after we've gone through the manual basics, we can move on to automation. It's clear that manual promotion of such injections is a very tedious business. Therefore, let the robots do the work.

For automating SQL injections, there is a wonderful tool that has proven itself among both mommy “hackers” and serious uncles – sqlmap .

This thing is essentially mainstream among tools of this kind and can do a lot. I suggest you try it out on the examples I gave above - authorization and search. At the same time, we will figure out how to configure the tool, how to set it on a target and what to do with all this.

Let me start by saying that in order to work with the tool normally, you will need requests that fly to the server. There are many different tools for intercepting and conveniently processing them. Even the standard developer panel in the browser will do. But I still prefer Burp, which is a little more suitable for this.

So, let's start with the search page, since it offers the most opportunities. Let's intercept the request that flies to the server after clicking the search button.

We only have one parameter here. The tool essentially only needs two things - URL and data.

Let's try to run the tool with the following command:

sqlmap -u [URL]http://127.0.0.1:8081/search[/URL] –data="search=test"

The tool performs some tests and...

It finds nothing. But we know for sure that the page is vulnerable, how did this happen? However, it is too early to get upset. If we look at the warnings, we will see some useful tips that the utility gives us, in case we do not want to calm down.

One of these tips is dbmsto specify the DBMS via the key. Let's try to check what will happen if we specify sqlite:

sqlmap -u [URL]http://127.0.0.1:8081/search[/URL] --data="search=test" –dbms=sqlite

Here we achieve more success - the tool confirms that the database is SQLite, and that, although we received a bunch of errors from the server, we achieved success.

The results can be viewed in the local folder of the tool, which the utility also kindly informs us about:

Excellent. We have our first success – let’s try to extract the database names. For this, the key is used in sqlmap–dbs

• We add it to the command:

sqlmap -u [URL]http://127.0.0.1:8081/search[/URL] --data="search=test" --dbms=sqlite -dbs

And here the tool again politely prompts in the warning that this will not work - sqlite has certain features in that the database initially represents a separate file, so it will not be possible to extract them so easily. But then sqlmap advises us to extract tables at once. Well, let's try to do this:

sqlmap -u [URL]http://127.0.0.1:8081/search[/URL] --data="search=test" --tables –dbms=sqlite

And bingo – the utility pulls the table names from the database:

Well, then it’s a matter of technique – you can extract data from the table by specifying the table itself via the key -Tand adding –dump:

sqlmap -u [URL]http://127.0.0.1:8081/search[/URL] --data="search=test" -T Users –dbms=sqlite

This command will return us the contents of the table.

Naturally, these are far from all the capabilities of the tool. It can do much more, and if we describe all its capabilities, the article risks turning into a list on a roll. The article already consists of 2 parts and turned out to be very voluminous. We are sure that it contains enough information to understand where to dig if you want to understand deeper.

Conclusion​

SQL injections have become much less common in recent times, as a large number of libraries and frameworks for a variety of languages with built-in protection contribute to this.

However, this does not mean that they have disappeared. If you look at the same reports from Bug Bounty programs, you can find a lot of interesting and far from classic.