This page has been robot translated, sorry for typos if any. Original content here.

Some vulnerabilities in web resources using SQL

This post covers a SQL vulnerability related to penetration into the body of a query.

The essence of the vulnerability

Consider the work of the simplest system with a WEB-interface that allows users to save also change information about themselves. Such systems are one of the most common in the network. This may also include a guest book, also a chat, also a photo gallery, and a postal service.

The generated query to the database is forced to have the login and password entered by the user. For these fields, the database should find the corresponding entry in the table. After finishing the request, the database gives the found information about the user, which the PHP script draws in the form of HTML also gives to the user.

Let us analyze a fairly typical fragment of a PHP script that uses a SQL query to access the database:

<? php
$ result = mysql_db_query ("database", "select * from userTable where login = '$ userLogin' and password = '$ userPassword'");
while ($ row = mysql_fetch_array ($ result)) {
echo $ row ["fullname"];
echo $ row ["email"];
echo $ row ["password"];
mysql_free_result ($ result);

As we observe, the login and password entered by the user are contained in the $ userLogin variables and $ userPassword . The contents of these variables are included in the request to filter information about this particular user. The filter is set using the where option of the select SQL command. In this case, the query looks like this: select * from userTable where login = '$ userLogin' and password = '$ userPassword' where userTable is the name of the table containing the necessary information. If the login variables of the password also contain the values ​​of vanya and vasya respectively, then the query sent to the database will look like this: select * from userTable where login = 'vanya' and password = 'vasya' . It is clear that if in the database there is no record in which the login is equal to vanya, however the password is vasya , right up to the request it will not send a single line from the database, nor will the script produce anything. Thus, the given system provides access to the information only of the user who has an error-free password and login.

It would seem that such a system contains no flaws. In actual practice, this is not the case. The logic of the programmers who created the above example implies that the username and password entered by the user will be contained within single quotes that are hard-coded in the request body. However, let's see what happens if the password is personal, the user entered contains a quotation mark. Let him own the value of vas'ya , while the request will look like: select * from userTable where login = 'vanya' and password = 'vas'ya' . When executing such a request, an error will certainly occur, since the quote from the password closed the opening quote from the request, also the end of the password ya ' remained "to hang" outside the conditional expression.

And if you insert such a line as a password: 'or 1 = 1' , then the query will become like this:: select * from userTable where login = 'vanya' and password = '' or 1 = 1 '' also will not have syntax errors . But the logical expression will become identically correct, also in objection to this query, SQL will return the entire database of users 8-).

Thus, using the apostrophe character, we can also insert the body of the SQL query so that the condition being checked is true. If we are interested in a specific user vanya , then to get information about him, it is allowed to use the following password string: 'or login =' vanya . This will make the request: select * from userTable where login = 'vanya' and password = '' or login = 'vanya' . As you understand, as a result we will receive information about vanya .

The described vulnerability of SQL-based services is in no way limited to unauthorized receipt of information. Since in such systems, as a rule, MySQL is used, it is possible in any way not only to modify the conditional expression in the select command, but also to go beyond this command, as well as execute another DB command. Since MySQL allows multiple commands in a single query, separated ; , then we can execute any of these commands by entering the following code in the password field: '; <SQL command> in which place it is allowed to specify any valid command as <SQL command> . So for example the following code: '; drop table 'userTable will simply destroy the userTable from the database.

Practical use of vulnerability

Despite the simplicity, the practical use of SQL query errors is very difficult.

In this head, we consider the following problems that arise when using the described vulnerability:
  • Determining whether SQL is used in the system.
  • Identify the presence of a vulnerability. Clarification of the script's reaction to errors.
  • Definition of field names in the table.
  • Identify the names of existing tables.

    The considered vulnerability is inherent in all SQL queries, regardless of the script or program from where they are called. Nevertheless, we will consider systems based on PHP. This is due to the fact that the PHP error stream as a position (by default) is sent to the end user. While Perl or Exe applications usually do not inform the user about the nature of the errors.

    Determining whether SQL is used in the system.

    When examining a particular system, you first need to find out if it uses SQL.
    This fact can be revealed either indirectly (by looking at the names of the files used, references to the tools used, etc.), or directly - by causing the SQL to manifest itself. If we work with PHP, then there is only one way to determine uniquely the use of SQL - to cause an error in its execution. If an error occurs during the execution of the request, however, the PHP script does not explicitly handle the errors of any execution, then the PHP error message will be sent directly to the user’s page.
    How to cause an error in the execution of an SQL query depends on the particular device of the system in question. In most cases, the error is allowed to call entering incorrect data into the system.

    Identify the presence of a vulnerability. Clarification of the script's reaction to errors.

    The most unsophisticated means of detecting the presence of a vulnerability, but at the same time also the fact of using SQL, is the following: In any field that is supposedly involved in generating an SQL query (for example, the Login or Password field), enter a single quote mark. The remaining fields are filled with any valid data (or leave empty, if allowed by the system). Having sent the given forms, we look at the reaction of the system. If, as a result, the PHP script gives us an SQL error, then we can congratulate ourselves: the system uses SQL, and the script does not filter the single quote in any way. That eat the system contains a vulnerability.
    The SQL query error will then look like this on the page:

    Data entry form:

    The result is an SQL query error:

    If there is no SQL error code on the page, then this may mean the following:
    1) The system contains a vulnerability, but the PHP script handles errors. In this case, the system is allowed to be hacked, but you will have to act "by touch", since we will not know at any time at which time the SQL syntax will be correct, but at what time it will not.
    2) The script filters the quotation also because errors do not arise. In this case, the system does not contain any vulnerabilities.
    3) The general system does not use SQL.
    In the last couple of cases, it is clear that further exploration of SQL has no meaning at all.

    Definition of field names in the table.

    In order to get information from the database with specific data, we need to determine the values ​​of some fields in the request (for example, set the user login). Yet for this you need to know the name of the corresponding fields. Directly find out these names is not possible. Therefore, it is necessary to search for these names using the search method. These names may coincide with the names of the fields in the form sent to the server, but they may also not coincide. The good thing is that the names of the fields as a position are also standard variants of writing them in no way so much. So, for example, the name of the field for the username is likely to be login or user or nick . Similar to the password: password either pwd or pass .
    To determine the existence of a specific field, we propose an unsophisticated method: Suppose we want to check whether the pwd field exists in the table. Let's enter in any form field the following string 'pwd =' . If the pwd field exists in the table, then SQL correctly processes the request, if there is no such field, then the execution error of SQL will occur again. Thus, substituting different values ​​of the field names also examining the result of finishing the query, we can find out which fields exist in the table, but which ones do not.

    Identify the names of existing tables.

    Similar to the method of finding the names of fields in tables, it is also allowed to search for the names of existing tables in the database. Suppose we want to find out if the adminList table exists in the database. To do this, enter the following string in the form field with the speaker '; select * from adminList . If, as a result, the SQL error does not start at all, then the adminList table exists. For the correctness of this test, you must enter this string in the field that appears final in the SQL query. This is necessary to ensure that the syntax error is not caused due to the remaining "tail" of the original request, which becomes present later select * from adminList . Note that if the view for the request is owned by a pair of the Login field also the Password , then most likely the password field will appear last in the request.


    Using this vulnerability, the site was hacked (more precisely, its photo gallery is also a chat). We have posted a vulnerability for site owners. The hole was fixed. But now we checked the chat form once more. Plus found all that blah blah vulnerability 8-).