我有一个名为sqli
的PostgreSQL数据库,有两个表,定义为:
create table articles(id int, content text);
create table users(username varchar(32), password varchar(32));
我正在做一个执行SQL注入的库,这个库应该支持的一个用例是:
- 未执行聚合(
string_agg
) - 每次查询最多检索N个字符
不应该使用聚合的原因是string_agg
只在PostgreSQL 9.0
和更高的版本上工作(一些用户可能需要在旧版本上使用该库)。
如果输出长度设置为3,我的库将运行以下查询来获取表的名称:
(select count(1)::text from information_schema.tables where table_schema='public')
=> 2, expect two rows
(select substr(coalesce(table_name::text,'')::text,1,3) from information_schema.tables where table_schema='public' limit 1 offset 0)
=> returns "use"
(select substr(coalesce(table_name::text,'')::text,4,3) from information_schema.tables where table_schema='public' limit 1 offset 0)
=> returns "rs", length < 3, move to next row
(select substr(coalesce(table_name::text,'')::text,1,3) from information_schema.tables where table_schema='public' limit 1 offset 1)
=> returns "art"
(select substr(coalesce(table_name::text,'')::text,4,3) from information_schema.tables where table_schema='public' limit 1 offset 1)
=> returns "icl"
(select substr(coalesce(table_name::text,'')::text,7,3) from information_schema.tables where table_schema='public' limit 1 offset 1)
=> returns "es", length < 3, stop
Result:users
andarticles
.
我不明白的是:当尝试使用相同的算法检索列名时,结果如下:
(select count(1)::text from information_schema.columns where table_name='users' and table_schema='public')
=> 2
(select substr(coalesce(column_name::text,'')::text,1,3) from information_schema.columns where table_name='users' and table_schema='public' limit 1 offset 0)
=> "pas"
(select substr(coalesce(column_name::text,'')::text,4,3) from information_schema.columns where table_name='users' and table_schema='public' limit 1 offset 0)
=> "swo"
(select substr(coalesce(column_name::text,'')::text,7,3) from information_schema.columns where table_name='users' and table_schema='public' limit 1 offset 0)
=> "me" !! THIS IS WRONG
(select substr(coalesce(column_name::text,'')::text,1,3) from information_schema.columns where table_name='users' and table_schema='public' limit 1 offset 1)
=> "use"
(select substr(coalesce(column_name::text,'')::text,4,3) from information_schema.columns where table_name='users' and table_schema='public' limit 1 offset 1)
=> "rna"
(select substr(coalesce(column_name::text,'')::text,7,3) from information_schema.columns where table_name='users' and table_schema='public' limit 1 offset 1)
=> "rd" !! THIS IS ALSO WRONG
结果是passwome
和usernard
。me
来自username
,rd
来自password
。
我知道如果不使用具有唯一值的列的ORDER BY
子句,就没有保证的顺序稳定性,但我事先不知道数据库模式,并且该算法适用于我测试过的任何其他数据库管理系统,知道为什么它不工作吗?或者对如何得到正确的结果有什么建议吗?
如您所说,没有ORDER BY
就不能保证结果行排序。与其他数据库系统进行比较是没有意义的,因为每个数据库系统实现查询执行和存储内部都不同。
但您的是一个没有问题:information_schema.columns
有一个列ordinal_position
,用于确定列的顺序。你应该用它