Imposing a Query or SQL Injection

Although different SQL implementations might differ in minor details, the main SQL commands are generally the same. They are outlined in Table 28.1.

Table 28.1: Main SQL commands

Command	Description
CREATE TABLE	Create a new table.
DROP TABLE	Delete an existing table.
INSERT INTO	Add a new field with the specified value to the table.
DELETE FROM ... WHERE	Delete all records that meet the WHERE condition from the table.
SELECT * FROM ... WHERE	Select all database records that satisfy the WHERE condition.
UPDATE ... SET ... WHERE	Update all database fields that meet the WHERE condition.

A typical scenario of interaction with a database appears as follows : The user enters some information into the query fields. Specialized script retrieves this information from the query fields and converts it into the database query string, after which it is passed to the server for execution (Listing 28.3).

Listing 28.3: A typical method of forming the database query

 $result = mysql_db_query("database", "select * from userTable                          where login = '$userLogin' and password = '$userPassword' "); 

 

Here $userlogin is the variable containing the user name , and $userPassword is the variable containing the user password. Note that both variables are placed within the text string enclosed within quotation marks. This is unusual for C but typical for interpreted languages such as Perl or PHP. Such a mechanism is known as string interpolation and allows automatic substitution of variables with their actual values.

Assume that the user enters KPNC/passwd. The query string will appear as follows: "select * from userTable where login = 'KPNC' and password = 'passwd'" (the user input is in bold). If such a combination of login and password is present in the database, the function returns the result identifier; otherwise , it returns the FALSE value.

Now assume that the intruder wants to log into the system under the name of another user and knows the login name of that user but not the password. How is it possible to achieve this? The first idea that comes to mind is that the interpolation mechanism allows the attacker to influence the query string, changing it as needed. For instance, consider what would happen if, instead the password, the following sequence is entered: "foo' or '1' = '1" (naturally, without quotation marks): "select * from userTable where login = 'KPNC' and password = 'foo' or '1' = '1'" . Note that the quotation mark that follows the foo has terminated the user password, and all further input has fallen into the logical expression implied by the attacker to the database. Because one is always equal to one, the query will always be considered accomplished, no matter which password has been supplied. Thus, the SQL server will return all records from the table (not only the ones related to the KPNC login).

Now consider another example: "SELECT * FROM userTable WHERE msg = '$msg' AND TD = 669" . Here, msg is the number of the message to be retrieved from the database, and ID is the identification number of the user, which the script automatically substitutes into the query string, and which is not directly related to the user input (a constant was used to make the example more illustrative ; in real script, a construct like ID = '$userID' will be used). To gain access to other fields of the database (not only the ones, for which ID is equal to 669 ), it is necessary to eliminate the last logical condition. This can be achieved by inserting the comments symbols ( -- and /* for Microsoft SQL Server and MySQL, respectively) into the user input string. The text following the comment symbols is ignored. If instead of the message number the attacker inserts "1' AND TD = 666 --", the query string will appear as follows: "SELECT * FROM userTable WHERE msg = '1' and ID = 666 --' AND ID = 669" (the comment text is in bold). Consequently, the attacker will gain the possibility of independently forming an ID by reading messages intended for other users.

Manipulations are not limited only to changing the SELECT query fields and there is a threat of breaking beyond its limits. Some SQL servers support the possibility of specifying several commands within one string by separating them with a semicolon, which allows the attacker to execute practically any SQL commands. For example, the sequence such as " ' ; DROP TABLE 'userTable' --" , entered instead of the user name or password, removes the entire userTable .

In addition, the attacker can save part of the table into the file by feeding the database a query like "SELECT * FROM userTable INTO OUTFTLE ' FileName "' . The corresponding URL of the vulnerable script might appear, for example, as follows: www.victim.com/admin.php?op=login&pwd=123&aid=Admin'%20INTO%20OUTFILE%20'/path_to_file/pwd.txt , where path_to_file is the path to the pwd.txt file, into which the administrator's password will be written. It is a convenient means of stealing the data, isn't it? However, the main issue is saving the file in a location, from which it can later be easily copied ” for instance, in one of the public World Wide Web directories. In this case, the fully qualified path to the file might appear approximately as follows: ../../../../www/myfile.txt . Note that exact format of the query depends on the server configuration. This isn't all! The possibility of creating files on the server allows an attacker to send custom scripts to the target machine (this might be a script providing the remote shell: <? passthru ($cmd) ?> ). The maximum size of the script is limited by the maximum length of the user input form field; however, this limitation often can be bypassed by manually forming the request to the URL or using the INSERT INTO SQL command, which adds new records to the database table.

The corrected URL query might appear as http://www.victim.com/index.php?id=12' or as http://www.victirn.corn/index.php?id=12+union+select+null,null,null+from+table1/* . The latter query operates only on MySQL 4. x or newer , which supports union , the combination of several queries within the same string. Here, table1 is the name of the table whose contents it is necessary to output to the screen.

Attacks of this type are called SQL injections . They are a particular case of more general attacks based on filtering errors and string interpolation. The hacker "injects" the command into the database query, thus piercing the body of a vulnerable script (hence the term "injection"). In contrast to common opinion, this isn't a Microsoft SQL Server bug. This is a script-implementation error. An expertly designed script must check the user input for the presence of potentially dangerous characters , such as single quotation marks, semicolons, double dashes, and (for MySQL exclusively) asterisks ” including their hexadecimal equivalents specified using the % prefix, namely, %27, %2A , and %3B . The code of the double dash mustn't be filtered, because it doesn't belong to the group of metacharacters supported by the browser. If at least one of the filtering conditions isn't checked for all cases, for which it is applicable (for instance, if URL strings or cookies remain unfiltered ), then the script contains a security hole, through which intruders can attack it.

However, such an attack isn't easy to implement. To carry out such an attack successfully, the hacker must have an experience with Perl or PHP programming, know how the specific query form might appear, and know how the table fields are typically named; otherwise, interpolation won't produce any useful result. The hacker cannot directly determine the names of tables and fields; thus, to succeed, the hacker must undertake a blinding attack.

Fortunately for intruders, most administrators and Webmasters are too lazy to develop all the required scripts on their own. They often prefer to use ready solutions, the source codes of which are freely available on the Internet. Such scripts usually have more holes than a leaky bucket. For instance, consider PHP-Nuke, where new holes are detected constantly (Fig. 28.2).

Figure 28.2: Fragment of PHP-Nuke responsible for formulating the query to the database

The strategy of searching for bugs and holes is approximately as follows: First, it is necessary to download the source code of PHP-Nuke (or any other portal system), install it at the local computer, and search all files globally, recording all attempts at accessing the database (in other words, all calls like mysql_query or mysql_db_query ). Then, scroll the code up and look for the string containing the database query (for example, $query = "select user_email, user_id FROM $ {prefix}_users WHERE user_id = '$cookie [0]'" ). Determine the names of variables to be substituted into the database, find the code responsible for passing the user input parameters, and analyze filtering conditions.

For example, consider one of the numerous PHP-Nuke 7.3 vulnerabilities, related to news processing. The corresponding URL appears as follows: modules.php?name=News&file=categories&op=newindex&catid=1 . By this URL, it is possible to assume that the catid value is passed directly in the database query string. Consequently, if the script developer has forgotten about filtering, the hacker obtains the opportunity to modify the script as he or she chooses. To check whether this assumption is true, replace the value from 1 to, say, 669. The server would immediately display a blank screen in response. Now add the following construct to this URL: 'or'1'1='1 (the complete URL will then appear as follows: modules.php?name=News&file=categories&op=newindex&catid= 669'or'1'='1) . The server will obediently display all news of the section, thus confirming that the SQL injection has worked.

Also, it is possible to cause an SQL error by issuing a knowingly-incorrect query (for example, a single quotation mark character). After this, the server will provide lots of interesting information. If there are no errors, this doesn't necessarily mean that the script filters the user input. It is possible that the script simply traps error messages, which is a normal practice for network programming. Furthermore, the response code 500 may be returned in case of an error, or redirection to the home page may take place. Such ambiguity considerably complicates the process of searching for vulnerable servers but doesn't make it impossible .

Analysis has shown that filtering errors are encountered in many scripts, including commercial ones, and such errors remain unfixed for years . Holes in the main input fields were eliminated long ago; therefore, it would be naive to hope for quick success. Queries passed using the POST method are tested less often, because they are passed secretly from the user and cannot be modified directly from the browser. This prevents the army of beginner "hackerz" from active hacking. It is possible to communicate with the Web server using netcat or telnet , forming POST queries manually.

In general, SQL injections again have demonstrated that there are no bug-free programs. However, you shouldn't overestimate their importance. Administrators and developers are aware of the threat, and vulnerable servers become fewer in number every day. Real power over the system can be obtained only by using principally new techniques, which are not widely known to the public community. True hackers work to discover them. Every hacker strives for striking discoveries of something new and unknown.