Difference between revisions of "SQL Injection Cookbook - Oracle"

From OWASP
Jump to: navigation, search
(Set operators)
 
(One intermediate revision by one user not shown)
Line 3: Line 3:
 
==Tables==
 
==Tables==
 
===List table names===
 
===List table names===
===Create a table===
 
 
===List columns for a specific table===
 
===List columns for a specific table===
 
===View table permissions===
 
===View table permissions===
 
===Change table permissions===
 
===Change table permissions===
 +
===Create a table===
  
 
==Stored procedures or functions==
 
==Stored procedures or functions==
Line 34: Line 34:
 
===Operating System version===
 
===Operating System version===
 
===OS environment variables===
 
===OS environment variables===
===Execute OS shell commands===
+
===Execute OS shell command===
 
===Read file contents===
 
===Read file contents===
 
===Arbitrary file writes===
 
===Arbitrary file writes===
 
===File uploads===
 
===File uploads===
 
 
=Unique database platform features=
 
  
  
Line 47: Line 44:
 
===Valid string delimiters===
 
===Valid string delimiters===
 
===String concatenation===
 
===String concatenation===
 +
===String-based queries with no quote characters===
 
String concatenation is performed by a double pipe ('''||''').
 
String concatenation is performed by a double pipe ('''||''').
   SELECT  
+
   SELECT
  FirstName || ' ' || LastName
+
    FirstName || ' ' || LastName
 
   FROM
 
   FROM
 
     People
 
     People
 
===String-based queries with no quote characters===
 
  
 
==Query syntax==
 
==Query syntax==
 +
===Result row count limiters===
 
===Acceptable whitespace===
 
===Acceptable whitespace===
 
===Tableless queries===
 
===Tableless queries===
Line 64: Line 61:
 
     Dual
 
     Dual
 
===Query comments===
 
===Query comments===
===Query command delimiters===
+
===Command delimiters===
 
===Set operators===
 
===Set operators===
 
Set operators are used to combine the results from two different queries. The number of columns and order of column types must be identical for both queries. The general syntax is
 
Set operators are used to combine the results from two different queries. The number of columns and order of column types must be identical for both queries. The general syntax is
Line 70: Line 67:
 
   SELECT
 
   SELECT
 
     fname, lname
 
     fname, lname
   FROM  
+
   FROM
 
     employees
 
     employees
 
   '''''SET_OPERATOR'''''
 
   '''''SET_OPERATOR'''''
Line 78: Line 75:
 
     customers
 
     customers
  
====UNION====
+
'''UNION'''<br>
 
Returns the rows from both queries, removing duplicates
 
Returns the rows from both queries, removing duplicates
 +
<br><br>
  
====UNION ALL====
+
'''UNION ALL'''<br>
 
Returns the rows from both queries, duplicates are not removed.
 
Returns the rows from both queries, duplicates are not removed.
 +
<br><br>
  
====INTERSECT====
+
'''INTERSECT'''<br>
 
Returns the rows that are found in the results of both queries.
 
Returns the rows that are found in the results of both queries.
 +
<br><br>
  
====MINUS====
+
'''MINUS'''<br>
 
Returns only the rows in the first query that are not found in the second query.
 
Returns only the rows in the first query that are not found in the second query.
  
Line 106: Line 106:
 
===UPDATE statements===
 
===UPDATE statements===
  
==Timing attacks==
+
==Inference and timing attacks==
 
==SQL Tautologies==
 
==SQL Tautologies==
A tautology is something that is inherently true. SQL tautologies are used when you want to force a query to return all results, basically ignoring any WHERE conditionals. Simple tautologies like " OR 1=1" are useful, but may be filtered out by some security tools. The table below offers a number of tautologies that filter writers (even on well known commercial tools) may not have considered.
+
A tautology is something that is inherently true. SQL tautologies are used when you want to force a query to return all results, basically ignoring any WHERE conditionals. Simple tautologies like " OR 1=1" are useful, but may be filtered out by some security tools. The table below offers a number of tautologies that filter writers (even on well known commercial tools) may not have considered.
  
{| style="width:75%;" border="1" cellspacing="0" cellpadding="5"  
+
{| style="width:75%;" border="1" cellspacing="0" cellpadding="5"
 
! width="55%"|Statement
 
! width="55%"|Statement
 
! width="15%"|Numeric
 
! width="15%"|Numeric
Line 118: Line 118:
 
! width="15%"|Binary
 
! width="15%"|Binary
 
(0x1 = 0x1)
 
(0x1 = 0x1)
|-  
+
|-
 
| '''''a''''' = '''''a'''''
 
| '''''a''''' = '''''a'''''
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''a''''' ^= '''''b'''''
 
| '''''a''''' ^= '''''b'''''
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''a''''' < '''''b'''''
 
| '''''a''''' < '''''b'''''
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''b''''' > '''''a'''''
 
| '''''b''''' > '''''a'''''
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''a''''' <= '''''b'''''
 
| '''''a''''' <= '''''b'''''
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''b''''' >= '''''a'''''
 
| '''''b''''' >= '''''a'''''
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''a''''' <! '''''b'''''
 
| '''''a''''' <! '''''b'''''
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''b''''' >! '''''a'''''
 
| '''''b''''' >! '''''a'''''
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
| '''''a''''' ''operator'' ANY ('''''a''''', '''''b''''', '''''c''''', '''''d''''')  
+
| '''''a''''' ''operator'' ANY ('''''a''''', '''''b''''', '''''c''''', '''''d''''')
where ''operator ''is any of the  
+
where ''operator ''is any of the
 
first 9 listed comparison
 
first 9 listed comparison
 
operators; for the statement to
 
operators; for the statement to
Line 166: Line 166:
 
true for '''''a''''' and least one of the
 
true for '''''a''''' and least one of the
 
items in the list
 
items in the list
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
| '''''a''''' ''operator'' SOME ('''''a''''', '''''b''''', '''''c''''', '''''d''''')  
+
| '''''a''''' ''operator'' SOME ('''''a''''', '''''b''''', '''''c''''', '''''d''''')
 
SOME is a synonym of ANY
 
SOME is a synonym of ANY
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
| '''''a''''' ''operator ''ALL ('''''a''''', '''''b''''', '''''c''''', '''''d''''')  
+
| '''''a''''' ''operator ''ALL ('''''a''''', '''''b''''', '''''c''''', '''''d''''')
 
similar to ANY, except that the
 
similar to ANY, except that the
 
operator must return true for '''''a'''''
 
operator must return true for '''''a'''''
 
and all of the items in the list
 
and all of the items in the list
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''a''''' IN ('''''c''''', '''''b''''', '''''a''''')
 
| '''''a''''' IN ('''''c''''', '''''b''''', '''''a''''')
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''a''''' NOT IN ('''''d''''', '''''c''''', '''''b''''')
 
| '''''a''''' NOT IN ('''''d''''', '''''c''''', '''''b''''')
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''b''''' BETWEEN '''''a''''' AND '''''c'''''
 
| '''''b''''' BETWEEN '''''a''''' AND '''''c'''''
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''a''''' NOT BETWEEN '''''c''''' AND '''''d'''''
 
| '''''a''''' NOT BETWEEN '''''c''''' AND '''''d'''''
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
| align="center" | X  
+
| align="center" | X
 
|-
 
|-
 
| '''''a''''' LIKE '''''a'''''
 
| '''''a''''' LIKE '''''a'''''
 
| &nbsp;
 
| &nbsp;
| align="center" | X  
+
| align="center" | X
 
| &nbsp;
 
| &nbsp;
 
|-
 
|-
 
| '''''a''''' NOT LIKE '''''b'''''
 
| '''''a''''' NOT LIKE '''''b'''''
 
| &nbsp;
 
| &nbsp;
| align="center" | X  
+
| align="center" | X
 
| &nbsp;
 
| &nbsp;
 
|}
 
|}
 
 
=Data exfiltration=
 
=Data exfiltration=
 
==E-mail==
 
==E-mail==
 
==Web==
 
==Web==
 
==General network==
 
==General network==
 +
 +
 +
=Platform specific=
 +
==Unique database platform features==
 +
==Authoritative documentation resources==
 +
==Links==

Latest revision as of 20:07, 16 January 2007

Contents

Database objects

Tables

List table names

List columns for a specific table

View table permissions

Change table permissions

Create a table

Stored procedures or functions

List stored procedures or functions

Parameters for a stored procedure or function

Source code of a stored procedure or function

Create a stored procedure or function

System data

Users

Identify current user

List of database users

List of database administrators

Database user permissions

Create a new user

Change a user password

Delete a user

Database server

View database server settings

Change database server settings

View database server processes

Kill database server process

Host Operating System

Operating System version

OS environment variables

Execute OS shell command

Read file contents

Arbitrary file writes

File uploads

Queries

Strings

Valid string delimiters

String concatenation

String-based queries with no quote characters

String concatenation is performed by a double pipe (||).

 SELECT
    FirstName || ' ' || LastName
 FROM
    People

Query syntax

Result row count limiters

Acceptable whitespace

Tableless queries

Tableless queries aren’t supported in Oracle per se. However, a special table named "Dual" allows for similar functionality. This doesn't help much for filter evasion since it still matches the standard SELECT syntax.

 SELECT
    'This is a string'
 FROM
    Dual

Query comments

Command delimiters

Set operators

Set operators are used to combine the results from two different queries. The number of columns and order of column types must be identical for both queries. The general syntax is

 SELECT
    fname, lname
 FROM
    employees
 SET_OPERATOR
 SELECT
    fname, lname
 FROM
    customers

UNION
Returns the rows from both queries, removing duplicates

UNION ALL
Returns the rows from both queries, duplicates are not removed.

INTERSECT
Returns the rows that are found in the results of both queries.

MINUS
Returns only the rows in the first query that are not found in the second query.

Special queries

Single column queries

Single row queries

Functions, etc.

Data type casting

Query output to file

Attacks

Breaking out of a query

WHERE clauses

FROM clauses

Other parts of a SELECT

INSERT statements

UPDATE statements

Inference and timing attacks

SQL Tautologies

A tautology is something that is inherently true. SQL tautologies are used when you want to force a query to return all results, basically ignoring any WHERE conditionals. Simple tautologies like " OR 1=1" are useful, but may be filtered out by some security tools. The table below offers a number of tautologies that filter writers (even on well known commercial tools) may not have considered.

Statement Numeric

(1 = 1)

String

('a' = 'a')

Binary

(0x1 = 0x1)

a = a X X X
a ^= b X X X
a < b X X X
b > a X X X
a <= b X X X
b >= a X X X
a <! b X X X
b >! a X X X
a operator ANY (a, b, c, d)

where operator is any of the first 9 listed comparison operators; for the statement to be true, the operator must return true for a and least one of the items in the list

X X X
a operator SOME (a, b, c, d)

SOME is a synonym of ANY

X X X
a operator ALL (a, b, c, d)

similar to ANY, except that the operator must return true for a and all of the items in the list

X X X
a IN (c, b, a) X X X
a NOT IN (d, c, b) X X X
b BETWEEN a AND c X X X
a NOT BETWEEN c AND d X X X
a LIKE a   X  
a NOT LIKE b   X  

Data exfiltration

E-mail

Web

General network

Platform specific

Unique database platform features

Authoritative documentation resources

Links