NINJA BLOG

Relational database, SQL, SELECT

SQL is a language designed for the manipulation of relational databases and for the retrieval of information from that database. A relation most commonly looks like a chart, which can be, for example, an article.

ARTICLES

The columns are called attributes and the lines are called records. In the example above, the attributes are the ID, Text and Title, while the records are the contents of the articles. We can think about a relational database as a set of relations (these are called tables) and the connections between them.
We can request data from the database with SELECT, which syntax is :

• SELECT [ *optional number of literal or attribute names, divided by comma ] FROM [name of the table] WHERE [condition ( logical expression ) ];
• The „*” includes all the existing attributes, while the literal can be for instance a number or a word.

If you would like to know all attributes of those articles, where it is true that their title is „DoS”, you need to use the following:

SELECT *
FROM   articles
WHERE  title = „dos”;

The result will be the following chart:

If we would like to know the attributes of the articles where the ID is „2”, we need to use this :

SELECT title,
text
FROM   articles
WHERE  id = 2;

The outcome:

Vulnerabilities in the SQL request

Let’s observe the following requests in which we replace the $param with the input given by the user.

1. SELECT *FROM   articlesWHERE  id = $param;
2. SELECT *FROM   articlesWHERE  id = ‘$param’;
3. SELECT *FROM   articlesWHERE  id = “$param”;

The vulnerability may be exploited by a malicious hacker who adds a value as a parameter that changes the behaviour of the whole request – if the programmer doesn’t validate the user input. For instance, if we examine the  request with the following value given for the $param variable:

„42 OR 1=1 — ”
SELECT *
FROM   articles
WHERE  id = 42
OR 1 = 1 — ;

This request will not only list the articles with 42 ID, but all of them. The condition will be true if at least one operand of the OR operator is true. In this case, the expression is true if either ID=42 is true, 1=1 is true or both. 1=1 is true in all cases,  that is why the condition will be met for every record, so the result will always include all articles from the table.  The „- -„ means comment, meaning that the MySQL will ignore everything coming behind it.
This makes the whole vulnerability even more significant, because the hackers may comment out parts of the original query. As an example:

SELECT *
FROM   articlesk
WHERE  id = $param
AND id > 30;

If the value of $param is “OR 1=1”, the query will look like the following:

SELECT *
FROM   articles
WHERE  id = 42
OR 1 = 1
AND id > 30;

The fully parenthesized form of the condition is this:

(ID = 42 OR (1=1 AND ID>30))

The value of the AND operator is only true if both of its operands are true. Here 1=1 is constantly met, however ID>30 becomes true only when the ID of the articles is bigger than 30.
If we use “OR 1=1 –” as the value of the $param variable, we can comment out the AND operator and it’s second operand which would give the following query:

SELECT *
FROM   articles
WHERE  id = 42
OR 1 = 1 — AND ID>30;

Here the database management system will ignore AND ID>30 , so the request is equivalent with this:

SELECT *
FROM   articles
WHERE  id = 42
OR 1 = 1

And this will show us all of the articles.
This vulnerability can be exploited in several ways:

• Data leakage:The results of the queries often get rendered onto web pages or change the behavior of the web page. You may say that viewing article isn’t really harmful but with more advanced queries, an attacker may access information from other tables as well. For example, he or she may access usernames, password hashes (an important note: never store your passwords as plain text.), credit card information or other sensitive information. If you using MySQL, even the database schema is accessible.
• Unauthorized access:Even though vulnerable login forms are the “hello world” examples for SQL injection, they are still quite common in a production system. This could allow an attacker to log in as any of the users of your site.Unauthorized changes in the data. SQL injection vulnerabilities can also appear in UPDATE and DELETE queries. In these cases, the attacker may be able to edit or remove any record in the table being modified.
• DoS attack:With proper queries, an attacker may make the DBMS service unusable, which will prevent your site to work well.Code injection: It is possible to write the result of a query into the file system. When using a suitable query, the files written may contain backdoor or other kinds of malicious code, which may be executed.

Looking for MySQL vulnerabilities

I’m sure that you’ve seen several SQL injection attempts while browsing the incident logs on our Dashboard. I have recently found an SQL injection vulnerability scan and I thought that it would be useful to write a step-by-step analysis about it to give you some insight into how such vulnerability scanners behave and generally, how does SQL injection work. The screenshots you see contain incidents of a real-life attack.
During the explanation, I’ll use the queries I’ve introduced in the previous section to help understand the topic better with examples. Naturally, the actual query on the attacked site is most probably different, however, it’s unfortunately not known. Let’s say that the value of the $param variable is the value of the id parameter, we’ll place this value into our queries.

id=2490′”
SELECT *
FROM   articles
WHERE  id = 2490

This request has a syntax error in it. The page rendered using this query can be used as later as the basis of comparison along with the page rendered when using a syntactically correct query which returns a result and when using a syntactically correct query which doesn’t return anything. Comparing the results of the later queries could show if the query is syntactically correct, incorrect and whether it returns any result.This request is syntactically faulty, as it does not contain the closing ’ , only the opening ’ sign, moreover, there is no operator between the 249 and the ’ sign.

SELECT *
FROM   articles
WHERE  id = “2490′””;

id=24902121121121212.1

SELECT *
FROM   articles
WHERE  id = 24902121121121212.1;

The query above is syntactically correct and can provide numerous useful information to the attacker.
First of all, it the ID field in the database has a string type with a capacity of 4 characters, the characters other than the first four will be removed from the query by the DBMS, which will give the same result as a normal request.
Secondly, most probably there is no article in this database which has this ID, therefore if there is no restriction to the length of the string, the query shouldn’t return anything.

id=2490 AND 1=1

First, let’s assume that the query looks like this:

SELECT * FROM articles WHERE id = $param;

If we substitute $param with ‘2490 and 1=1’, the following query will get executed:

SELECT * FROM articles WHERE id = 2490 and 1=1;

As 1=1 is always true, this returns the exact same result as the “normal” query, where the parameter is simply 2490. If the rendered page is the same in the two case, the application is most probably vulnerable and can be exploited using requests with this format.
However, if the query looks like this:

SELECT * FROM articles WHERE id = '$param';

We’ll have the following query after substitution. SELECT * FROM articles WHERE id = ‘2490 and 1=1’;
Note that the condition states that the value of the id attribute must be the ‘2490 and 1=1’ string. Depending on the type of id, the result of the query would be either the same as the “normal” query mentioned before  – in this case, further tests are required – or it would be empty. The latter shows that the query can not be exploited using payloads with this format.
The scanner runs several other tests which contain different variants of the parameter above. The changes include adding apostrophes, quotation marks, comment symbols, using a different logical operator, specifying different expressions. By comparing the rendered pages of these queries, the scanner may be able to find a vulnerability and the way it can be exploited.

Blind SQL Injection

id=2490 AnD SLeeP(3)
In some cases, the result of the query doesn’t get displayed directly on the page or the page constantly changes in a way that prevents using the previous method for scanning. As the attacker can not rely on the content of the page, he/she needs to come up with a different method to find out if the application is vulnerable or not.
In these cases, it is often possible to ask a yes/no question from the DBMS in the queries and try to find changes in different properties of the served page or the communication. This technique is called blind SQL injection. For example, if the result of the vulnerable query affects the result of another query, it might be possible to see differences on the page – even though it is not possible to view the result of the vulnerable query. In other cases, the query parameter doesn’t change the appearance of the page at all.
One of the things which can be measured is the average time while the site arrives to the HTTP client. This time contains the time needed to run the query, therefore if an attacker can prolong the execution of a query – and consequently the time of arrival of the response from the server -, he/she will be able to conclude that the application is vulnerable.
It is possible to pause the execution of a query for a given amount of time using the SLEEP function. For example,

SELECT * FROM articles WHERE id = 2490 AND SLEEP(3)

will wait 3 seconds before returning. The BENCHMARK function can also be used for similar purposes.


Now, we are familiar with the way how hackers can look for vulnerabilities. If they exploit them they can easily find our saved passwords, usernames or other sensitive data in our database. Security is an issue which couldn’t get bigger importance today, so if you would like to have your servers protected against different kinds of cyberattacks, including SQL Injection, try our free trial, without liabilities.
Did you like this article?
Stay Tuned, the next part is coming soon.