Oracle Sqli Cheat Sheet

/ Comments off

Cheat-sheet for non-SQL.Plus commands. SQL.Plus is the granddaddy of Oracle clients. It originated many of the commands we know, and mostly love, today. I’m going to talk about the ones that we have added. You might also want to see this post on SQLcl commands being ran in SQL Developer. Oracle SQL Injection Cheat Sheet. Ingres SQL Injection Cheat Sheet. Saturday, July 7th, 2007. Ingres seems to be one of the less common database backends for web applications, so I thought it would be worth installing it and making some notes to make my next Ingres-based web app test a little easier.

SELECT banner FROM v$version WHERE banner LIKE ‘TNS%’;
SELECT version FROM v$instance;

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.
Minit game review.

Oracle Sql Functions Cheat Sheet

Current User

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
quebrará o hashes DES-based da Oracle 8, 9 e 10

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

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;

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’;

List 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



Oracle Pl Sql Cheat Sheet

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(‘’) FROM dual; — if reverse looks are slow
SELECT UTL_INADDR.get_host_address(‘’) FROM dual; — if forward lookups are slow
SELECT UTL_HTTP.REQUEST(‘;) 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(‘’) FROM dual;


Command Execution
pode ser usado para executar comandos se ele estiver instalado.
Extproc às vezes pode ser usado também.

Local File Access
à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(‘’) FROM dual; — gets hostnames

Location of DB files

Default/System Databases




  • 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 neverremember but are very useful to have somewhere written down.

Table of Contents


Last week, I suddenly had to work with an Oracle database again. Inormally use Intellij’s DataGripto connect to databases. I tried it this time, and I found I could notconnect to the schema I wanted: the schema just turned up empty. Ofcourse, everybody will recommend you use Oracle’s SQL Developer with anyOracle database you have to touch. So, after trying brew search sqldeveloper (yes, I’m on a Mac at work), coming up empty, readingthis caskrequest andfeeling the anticipation of endless frustration grow inside me, I wentto Oracle’s web site to see if I could download the program. I can,except that they want me to turn in a DNA sample first:

Of course, faking those kind of details is not impossible, but thehassle of going through something like that just for a lousy program soI can just run a couple of lousy queries puts me off. Luckily, I had aDocker container with Oracle XE lying around. It includes SQL*Plus, the“venerable” command-line tool provided by Oracle to query and administerOracle databases. Being a Arch Linux user with a predilection foranything with a command-line interface, it was not too bad, but gettingthe output formatted so that I could actually make sense of it requiredmore effort than most users are willing to put up with. So how do youfind your way around when you have SQL*Plus and no clue how it works?How do you find the schema’s you want, the tables you need to query? Howcan you check your privileges? This post aims to be a cheat sheet so youcan find your way around. Keep in mind that the results you’re gettingdepend on the privileges your user has. Getting an empty result doesnot mean that the database object does not exist. Rather, it meansyou’re a lesser god.

Getting Help With sqlplus

sqlplus does not have a man page, but provides help when you pass-h/-help:

Connecting to an Oracle Database Using SQL*Plus

The basic syntax to connect as user alice with password qwerty to adatabase FOO which is located on db.domain.tld and listens on port1521 (default port) is:

Show the Connected User

The SHOW command lets you look at the current state of your SQL*Plusenvironment: SHOW ALL shows all settings.

Show All Schema’s

Oracle sqli cheat sheet free

Return only non-empty schema’s (excluding most users who never createdany object in the database):

Excluding Oracle’s built-in schema’s:

Show All Tables/Views in a Particular Schema

Related: find all views:

Describe a Table

Show DDL of a Table

where the first argument is the type of object (e.g. 'TABLE','VIEW', 'TRIGGER'), the second is the name of the object, and thethird the schema where the object is defined.

Show the Privileges of Your User

Get More Decent Output From a Command

If you want SQL*Plus to truncate the value of a column:

otherwise it will allow the column to wrap to the next line (defaultON). Suppress all headings, page breaks, titles, the initial blankline and other formatting information:

SourceNow, if only Oracle would support G for vertical output…

Get and Alter Database Timezones

Select DATE Rows in the Last Hour

Table t has a column c_date of type DATE:

This works because you can subtract a fraction from a DATE type wherethe fraction is interpreted as a fraction of a day (1 is an entireday, 0.5 is 12 hours, etc.).

Working Around Oracle Not Having a LIMIT

Yes, Oracle does not have a LIMIT keyword, so this idiom will quicklybecome ingrained in your muscle memory:

For each row returned by a query, the ROWNUM pseudocolumn returns anumber indicating the order in which Oracle selects the row from atable or set of joined rows. The first row selected has a ROWNUM of1, the second has 2, and so on. — Oracledocumentation

You could do without the subquery, plainly writing:

but if you add a ORDER BY clause, Oracle will first select the firstten rows, and only then apply the ORDER BYclause, which might not be what you want. So that’s why it’s best toalways use the first idiom above.

Setting New Password for User

Show Output from Script

When writing a script, you may want to output some diagnostic data:

You think you’re good to go, but when you execute your script in an environmentwhere my.table does not exist, you don’t see the diagnostic message. Whatgives? SQL*Plus’s default behavior is to suppress output by default. You haveto SET SERVEROUTPUT ON first.