SQL INJECTION
by Fabrizio d’Amore
faculty of Ingegneria dell’Informazione
Università di Roma “La Sapienza”
WHAT IT IS, WHAT IT IS NOT
December 2009
¢ capability of giving SQL commands to a database
engine exploiting a pre-existing application
not exclusive to Web applications, but widespread
SQL Injection, by F. d'Amore
¢
vulnerability in Web sites
vulnerabilities exist in 60% of Web sites they have tested
(from: OWASP, Open Web Application Security Project)
¢ not due to inadequate development of Web
applications, nor a fault of the Web / RDBMS server
developers not yet sufficiently aware
low-quality info in the Web on how to prevent the problem
detailed info in the Web on how to exploit vulnerabilities
2
WHAT APPLICATIONS ARE VULNERABLE?
December 2009
¢ in practice, all databases based on SQL
MS SQL Server, Oracle, MySQL, Postgres, DB 2,
Informix etc.
SQL Injection, by F. d'Amore
¢ databases accessed thru applications based on
most of modern (and non-modern) technologies
Perl, CGI, ASP, PHP, XML, Javascript, VB, C, Java,
Cobol etc.
3
HOW IT WORKS
December 2009
¢ client injects SQL code into the input data of an
application
SQL Injection, by F. d'Amore
typical scenario: application dynamically creates SQL
query using altered data (obtained from outside),
without good validation of such data
¢ target of the attack: server of an application
¢ goal: allow the client to access the database used
by the attacked server
4
EXAMPLE
December 2009
¢ if the following query returns data...
SELECT * FROM users
SQL Injection, by F. d'Amore
WHERE login = 'damore'
AND password = 'qwerty'
¢ example of login syntax ASP/MS SQL Server
var sql = "SELECT * FROM users WHERE login = '" +
formusr + "' AND password = '" + formpwd + "'";
¢ if
formusr = ' or 1=1 --
formpwd arbitrary
¢ query becomes into SELECT * FROM users
WHERE login = '' or 1=1 5
-- AND password = ...
SQL INJECTION ATTACK
December 2009
¢ attacker can access database in read/write/admin
depends on the vulnerability of the specific DBMS
SQL Injection, by F. d'Amore
¢ impact of the attack is potentially HIGH
6
POSSIBLE HTML FORM
December 2009
¢ from Wikipedia
(http://it.wikipedia.org/wiki/SQL_injection)
SQL Injection, by F. d'Amore
<form action='login.php' method='post'>
Username: <input type='text' name='user' />
Password: <input type='password' name='pwd' />
<input type='submit' value='Login' />
</form>
7
POSSIBLE LOGIN.PHP FILE
December 2009
<?php
//Prepares query, in a variable
$query = "SELECT * FROM users WHERE
SQL Injection, by F. d'Amore
user='".$_POST['user']."' AND
pwd='".$_POST['pwd']."'";
//Execute query (suppose a valid connection to
database is already open and its state is stored
in $db)
$sql = mysql_query($query,$db);
//Count number of lines that have been found
if(mysql_affected_rows($sql)>0) {
//authenticated!
}
8
?>
CONSEQUENCES
December 2009
¢ if script does not make input analysis and
validation, user can send
SQL Injection, by F. d'Amore
user = blah
pwd = ' OR user=‘blah'
¢ we get the query
SELECT * FROM users
WHERE user=‘blah' AND pwd='' OR user=‘blah'
¢ if at least one tuple does exist, attacker obtains
authenticated access
9
OTHER (WORSE) CONSEQUENCES
December 2009
¢ symbol ';' is exploited, it allows to concatenate
commands
SQL Injection, by F. d'Amore
pwd = ' OR user=‘blah'; DROP TABLE users;
¢ or
pwd = ' OR user=‘blash'; INSERT INTO
users (...) VALUES (...);
10
LINKS
December 2009
¢ examples
http://www.owasp.org/index.php/SQL_Injection
http://www.unixwiz.net/techtips/sql-injection.html
SQL Injection, by F. d'Amore
¢ Sqlninja: example of tool for supporting attacks
http://sqlninja.sourceforge.net/
it tries to use SQL injection on applications based on
MS SQL Server
its goal is to obtain an interactive shell on the remote
DB server
¢ WebScarab: example of tool for prevention
http://www.owasp.org/index.php/Category:OWAS
P_WebScarab_Project
11
powerful, good prevention, even against other types of
attack
PREVENTING SQL INJECTION
December 2009
¢ input validation
client side
SQL Injection, by F. d'Amore
to be considered within the wider subject of software
correctness and robustness
¢ parameterized queries
based on predefined query strings
¢ use of stored procedures
subroutines that are defined at server side, available
to applications accessing the RDBMS
12
can validate input at server side
INPUT VALIDATION AT CLIENT SIDE
December 2009
¢ use scripts, e.g., Javascript
SQL Injection, by F. d'Amore
¢ can be made weaker by the security settings of the
browser
¢ in some cases, can be bypassed thru suitable
change of the HTML source code
13
PARAMETERIZED QUERIES
December 2009
¢ avoid the traditional dynamic query string, where
pre-defined substrings have to be replaced by user
defined text
SQL Injection, by F. d'Amore
¢ based on pre-defined query strings, where suitable
parameters have to be inserted
¢ example: Java Prepared Statement
14
JAVA PREPARED STATEMENTS
December 2009
¢ see Sun tutorial on JDBC
(http://java.sun.com/docs/books/tutorial/jdbc/basics
/index.html)
SQL Injection, by F. d'Amore
¢ technique based on Java class PreparedStatement
initially proposed for improving the speed of
frequently executed queries
¢ when PreparedStatement is instantiated, an SQL
query is built (and compiled): it may contain the
symbol '?' to denote possible parameters necessary
to query
¢ query structure is fixed
15
PRACTIC EXAMPLE
December 2009
// define query schema
String selectStatement = "SELECT * FROM User WHERE
userId = ? ";
SQL Injection, by F. d'Amore
// instantiate PreparedStatement object by means of
purposed method of db connector (class Connection)
PreparedStatement prepStmt =
con.prepareStatement(selectStatement);
// provide parameter thru setXXX
prepStmt.setString(1, userId); // 1 -> first
parameter
// execute query
ResultSet rs = prepStmt.executeQuery(); 16
VULNERABILITIES IN PREPARED STATEMENTS
December 2009
¢ Java prepared statements, if not carefully packed,
may be vulnerable to SQL injection
SQL Injection, by F. d'Amore
¢ example
String strUserName =
request.getParameter("Txt_UserName");
PreparedStatement prepStmt =
con.prepareStatement("SELECT * FROM user
WHERE userId = '+strUserName+'");
¢ a prepared statement is built, using a non-
validated input parameter!
17
STORED PROCEDURES: WHAT AND WHY
December 2009
¢ compiled procedures (subroutines) made available
at server side to build/support batches operating
on DB
SQL Injection, by F. d'Amore
¢ code is optimized, but DB server incurs higher
processing costs
also improve code readability
¢ they help to limit SQL injection attacks
but they are not exempt from vulnerabilities
18
USE OF STORED PROCEDURES
December 2009
¢ also known as proc, sproc, StoPro or SP, belong to
data dictionary
SQL Injection, by F. d'Amore
¢ typical uses
data validation
access control mechanisms
centralization of logic that was initially contained
inside the applications
¢ similar to the user-defined functions, but with
different syntax
functions can appear everywhere in SQL strings, this
is not true for stored procedure calls
19
DATA VALIDATION THRU SP
December 2009
A few controls (partial list)
¢ format (e.g., digits or dates)
¢ types (e.g., if text has been inserted when digits are
SQL Injection, by F. d'Amore
expected)
¢ range (check data that should belong to an admissible
interval)
¢ mandatory data
¢ parity control
¢ orthography and grammar
¢ consistence M/F, S/P
¢ cross-system consistence (data on several systems;
e.g., name + surname vs. surname + name)
20
¢ existence of referred files
