SQL
Injec*ons
Security
2012
João
Paulo
Barraca
jpbarraca@ua.pt
Current
Web
Environment
• Current
“web
pages”
are
really
web
applica*ons
– Front
end
which
may
run
in
browser
– Server
provides
execu*on
environment
– Backend
which
provides
services
– Database
for
persistent
storage
• Interfaces
connect
the
different
subsystems
– E.g.
HTTP,
REST,
WebSocket,
SQL,
etc..
• Mul*ple
technologies
and
languages
used
– E.g.
Javascript,
PHP,
HTML,
CSS
Current
Web
Environment
• Each
subsystem
may
be
vulnerable
to
aUacks
– En*re
applica*on
may
be
compromised
if
single
breach
is
found
• SQL
Injec*ons
are
just
one
case
– Focus
in
applica*ons
using
SQL
servers
– There
are
many
other
aUacks
What?
• Conjunc*on
of
several
things:
– Specially
craXed
input
– Lack
of
sanity
checks
in
code
• Injec*on
of
an
SQL
statement
into
another
SQL
statement,
changing
its
purpose
• Most
frequent
vector:
AUacker
injects
special
SQL
statement
into
text
field
SQL
Injec*on
Form
provides
two
values:
login
and
password
Typical
valida*on
query:
!SELECT user FROM users WHERE user=‘$login’ !
!AND password=‘$password’!
For
login=admin
and
password=1234,
query
becomes:
!SELECT user FROM users WHERE user=‘admin’ !
!AND password=‘1234’!
SQL
Injec*on:
Detec*on
Form
provides
two
values:
login
and
password
What
if
password
is
a
single
quote?
‘
For
login=admin
and
password=‘,
query
becomes:
!SELECT user FROM users WHERE user=‘admin’ !
!AND password=‘’’!
SQL
Injec*on:
Detec*on
SQL
Injec*on:
Detec*on
hUp://assets.devx.com/ar*clefigs/17059.jpg
SQL
Injec*on:
Bypass
Simple
Password
• Form
data
is
used
to
create
an
SQL
statement
– Without
valida*on!
– SQL
code
in
form
can
be
injected
• What
if…
password
is
‘
or
‘1’=‘1
SELECT user FROM users WHERE user=‘admin’ !
AND password=‘’ or ’1’=‘1’!
• SQL
Statement
is
valid
and
always
returns
1
row
if
the
user
exists.
It
is
also
possible
to
find
username.
SQL
Injec*on:
Bypass
Simple
Password
SQL
Injec*on:
Bypass
Complex
Passwords
• SQL
can
store
passwords
in
a
ciphered
format
– Uses
the
PASSWORD
func*on
– Password
stored
in
database
cannot
be
obtained
• Typical
valida*on
query:
– SELECT user FROM users WHERE user=‘$login’ AND
password=PASSWORD(‘$password’)!
• For
login=admin
and
password=‘)
OR
(‘1’=‘1,
query
becomes:
– SELECT user FROM users WHERE user=‘admin’ AND
password=PASSWORD(‘‘)
OR
(‘1’=‘1’)!
Guess
Password
• More
complex
statement
can
be
included
in
form
fields.
• Frequently,
the
only
requirement
is
that
they
start
and
end
with
single
quote
(‘).
• Does
the
password
starts
with
an
‘a’?
‘ OR EXISTS(SELECT user FROM users WHERE
user=‘admin’ and password LIKE ‘a%”) AND
‘’ =‘!
Guess
Password
SELECT user FROM users WHERE user=‘admin’ !
AND password=‘‘ OR EXISTS(SELECT user FROM users
WHERE user=‘admin’ and password LIKE ‘a%”) AND ‘’
=‘’!
Guess
Password
SELECT user FROM users WHERE user=‘admin’ !
AND password=‘‘ OR EXISTS(SELECT user FROM users WHERE
user=‘admin’ and password LIKE ‘p%”) AND ‘’ =‘’!
!
!
!
!
!
!
• Then
we
could
try:
pa%
or
pa%a%,
etc..
Other
possibili*es
• Find
table
name:
– Is
there
a
users
table
in
the
current
db?:
'
OR EXISTS(SELECT *
FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_SCHEMA='test' AND TABLE_NAME=’users') AND
''=’
– Is
there
any
table
star*ng
by
“p”
in
any
db?
:' OR (SELECT
COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_SCHEMA LIKE ‘p%')>1 AND ''='!
• Find
database
name
– Starts
by
t?:
'
OR EXISTS(SELECT 1 FROM users WHERE
database() LIKE ’t%') AND ''=‘!
• Find
columns,
get
columns
by
index,
etc…
SQL
Injec*on:
Terminate
Query
• Two
characters
are
par*cularly
important
– ;
Terminates
current
query
• Allows
mul*ple
queries
in
same
request
– -‐-‐
terminates
processing
of
all
queries.
• Ignores
syntax
errors
which
may
appear
! Query
1
SELECT user FROM users WHERE
user=‘admin’ AND password=‘!
’; DROP TABLE user; --’!
Query
2
Ignored
aXer
-‐-‐
Mi*ga*on:
Sani*ze
Input
Data
• Sani*ze
form
input
data
– Filter
out
dangerous
characters
• Username
can
only
have
leUers
• Passwords
can
only
have
leUers
and
numbers
• Emails
must
comply
with
RFC
2822
– Escape
dangerous
characters
• Avoid
this.
• Browser
using
Javascript
– Can
be
bypassed
doing
direct
queries
or
using
tampering
proxies
(e.g.
WebScarab).
– Automated
tools
such
as
WebCruiser
can
easily
detect
and
bypass
such
methods
• Server
– Higher
load
in
server
– Much
more
effec*ve!
Mi*ga*on:
Sani*ze
Input
Data
• Sani*zing
input
data
based
on
quotes
is
insufficient!
• If
form
is
numeric,
no
quote
is
required.
– E.g.
PIN
valida*on
!
SELECT user FROM users WHERE user=‘admin’
AND pin=12 or 1=1!
!
• Valida*on
must
take
in
considera*on
actual
data.
Sani*ze
as
much
as
possible
Mi*ga*on:
Sani*ze
Input
Data
• Escaping
doesn’t
really
help
in
all
cases
– E.g.
typical
escape
is
‘
-‐>
‘’
• Providing
‘
OR
‘1’=‘1
results
in:
!
SELECT user FROM users WHERE user=‘admin’ AND
password=‘‘’ OR ‘’1’’=‘‘1’!
!
• As
double
quote
is
ignored
by
SQL,
no
harm
done.
• What
about
\’;
DROP
TABLE
users;
-‐-‐
– \’
is
expanded
to
\’’,
‘\’’
is
a
valid
string
with
just
one
character
(the
single
quote).
Table
is
dropped!
• MySQL
provides
own
sani*za*on
methods:
mysql_real_escape_string()
Mi*ga*on:
Prepared
Queries
• Instead
of
building
query
string,
let
SQL
libraries
compile
the
query.
– Separa*on
between
Query
and
Parameters
• Three
steps
required:
– Prepara*on
– Bind
parameters
– Execu*on
Mi*ga*on:
Prepared
Queries
• Query Preparation:!
– $s = mysql->prepare(“SELECT user FROM users
WHERE user= ? AND pin= ?”)!
• Parameter binding:!
– $s->bind_param(“s”,$login);!
– $s->bind_param(“i”,$password);!
• Query
execu*on
– $s-‐>execute();
Mi*ga*on:
Others
• Limit
data
permissions
according
to
user
needs
– Do
not
grant
DROP,
or
Write
methods
for
read
only
applica*on
• Use
stored
procedures
• Isolate
servers
to
reduce
compromise
of
neighbor
hosts
• Configure
error
repor*ng
appropriately
– Detailed
error
repor*ng
for
developers
– Limited
error
repor*ng
for
users
