SQL Injection Myths and Fallacies
Bill Karwin, Percona Inc.
Me
Software developer C, Java, Perl, PHP, Ruby SQL maven MySQL Consultant at Percona Author of SQL Antipatterns: Avoiding the Pitfalls of Database Programming
[Link]
What is SQL Injection?
SELECT * FROM Bugs WHERE bug_id = $_GET['bugid']
user input
[Link]
What is SQL Injection?
SELECT * FROM Bugs WHERE bug_id = 1234 OR TRUE
unintended logic
[Link]
Worse SQL Injection
UPDATE Accounts SET password = SHA2('$password') WHERE account_id = $account_id
[Link]
Worse SQL Injection
UPDATE Accounts SET password = SHA2('xyzzy'), admin=('1') WHERE account_id = 1234 OR TRUE
changes password for all accounts changes account to administrator
[Link]
Myths and Fallacies
MYTH FALLACY
Based on a grain of truth, but derives a wrong conclusion
Based on a false assumption, but derives a logical conclusion
[Link]
MYTH
SQL Injection is an old problemso I dont have to worry about it.
Myth
[Link]
Identity Theft
130 million credit card numbers Albert Gonzalez used SQL Injection to install his packet-sniffer code onto credit-card servers Sentenced 20 years in March 2010 Cost to victim company Heartland Payment Systems: $12.6 million
[Link] [Link]
[Link]
Other Recent Cases
(April 2011) [Link] and [Link] attacked by blind SQL Injection attack, revealing portions of the sites databases, including usernames and passwords.
[Link] [Link] [Link]
(April 2011) LizaMoon scareware campaign infected hundreds of thousands of websites via SQL Injection.
[Link]
[Link]
Experts Agree
2009 Data Breach Investigations Report, Verizon Business RISK Team
When hackers are required to work to gain access, SQL injection appears to be the uncontested technique of choice. In 2008, this type of attack ranked second in prevalence (utilized in 16 breaches) and first in the amount of records compromised (79 percent of the aggregate 285 million).
[Link]
[Link]
MYTH
Escaping input prevents SQL injection.
Myth
[Link]
Escaping & Filtering
backslash escapes special characters
UPDATE Accounts SET password = SHA2('xyzzy\'), admin=(\'1') WHERE account_id = 1234
coerced to integer
[Link]
Escaping & Filtering Functions
<?php $password = $_POST["password"]; $password_escaped = mysql_real_escape_string($password); $id = (int) $_POST["account"]; $sql = "UPDATE Accounts SET password = SHA2({$password_escaped}) WHERE account_id = {$id}"; mysql_query($sql);
[Link]
Escaping & Filtering Functions
<?php $password = $_POST["password"]; $password_quoted = $pdo->quote($password); $id = filter_input(INPUT_POST, "account", FILTER_SANITIZE_NUMBER_INT); $sql = "UPDATE Accounts SET password = SHA2( {$password_quoted} ) WHERE account_id = {$id}"; $pdo->query($sql);
[Link]
Identifiers and Keywords
<?php $column = $_GET["order"]; $column_delimited = $pdo->FUNCTION?($column); $direction = $_GET["dir"]; $sql = "SELECT * FROM Bugs ORDER BY {$column_delimited} {$direction}"; $pdo->query($sql);
keywords get no quoting no API to support delimited identifiers
[Link]
MYTH
If some escaping is good, more must be better.
Myth
[Link]
Overkill?
<?php function sanitize($string){ $string = strip_tags($string); $string = htmlspecialchars($string); $string = trim(rtrim(ltrim($string))); $string = mysql_real_escape_string($string); return $string; } $password = sanitize( $_POST["password"] ); mysql_query("UPDATE Users SET password = '$password' WHERE user_id = $user_id");
real function from a users project
[Link]
FIRE EVERYTHING!!
[Link]
Just the One Will Do
<?php $password = mysql_real_escape_string( $_POST["password"] ); mysql_query("UPDATE Users SET password = '$password' WHERE user_id = $user_id");
[Link]
MYTH
I can write my own escaping function.
Myth
[Link]
Please Dont
addslashes() isnt good enough in a multibyte world Example:
[Link] OR 1=1 --
$account = addslashes($_REQUEST(account)); Function sees a single-quote (%27) and inserts backslash (%5c). Result: %bf%5c%27 OR 1=1 -single-quote valid multi-byte character in GBK:
[Link]
Grant Access to Any Account
Interpolating: SELECT * FROM Accounts WHERE account = '{$account}' AND password = '{$password}' Results in: SELECT * FROM Accounts WHERE account = ' ' OR 1=1 -- ' AND password = 'guess'
[Link] [Link]
[Link]
Solutions
Use driver-provided escaping functions:
mysql_real_escape_string() mysqli::real_escape_string() PDO::quote()
Use API functions to set the client character set:
mysql_set_charset() mysqli::set_charset()
[Link]
Use UTF-8 instead of GBK, SJIS, etc. Use SQL query parameters (more on this later)
[Link]
MYTH
Unsafe data comes from usersif its already in the database, then its safe.
Myth
[Link]
Not Necessarily
$sql = "SELECT product_name FROM Products"; $prodname = $pdo->query($sql)->fetchColumn(); $sql = "SELECT * FROM Bugs WHERE MATCH(summary, description) AGAINST ('{$prodname}')";
not safe input
[Link]
FALLACY
Using stored procedures prevents SQL Injection.
Fallacy
[Link]
Static SQL in Procedures
filtering by data type is a good thing
CREATE PROCEDURE FindBugById (IN bugid INT) BEGIN SELECT * FROM Bugs WHERE bug_id = bugid; END CALL FindByBugId(1234)
[Link]
Dynamic SQL in Procedures
CREATE PROCEDURE BugsOrderBy (IN column_name VARCHAR(100), IN direction VARCHAR(4)) BEGIN interpolating arbitrary strings = SQL injection SET @query = CONCAT( 'SELECT * FROM Bugs ORDER BY ', column_name, ' ', direction); PREPARE stmt FROM @query; EXECUTE stmt; END CALL BugsOrderBy('date_reported', 'DESC')
[Link]
Worthy of TheDailyWTF
CREATE PROCEDURE QueryAnyTable (IN table_name VARCHAR(100)) BEGIN SET @query = CONCAT( 'SELECT * FROM ', table_name); PREPARE stmt FROM @query; EXECUTE stmt; END CALL QueryAnyTable( '(SELECT * FROM ...)' )
[Link]
[Link]
MYTH
Conservative SQL privileges limit the damage.
Myth
[Link]
Denial of Service
SELECT * FROM Bugs JOIN Bugs JOIN Bugs JOIN Bugs JOIN Bugs JOIN Bugs
100 bugs = 1 trillion rows
[Link]
Denial of Service
SELECT * FROM Bugs JOIN Bugs JOIN Bugs JOIN Bugs JOIN Bugs JOIN Bugs ORDER BY 1
still requires only SELECT privilege
[Link]
Just Asking for It
[Link] query=SELECT%20*%20FROM %20Bugs
[Link]
FALLACY
Its just an intranet applicationit doesnt need to be secure.
Fallacy
[Link]
Just Ask This Manager
[Link]
What Stays on the Intranet?
You could be told to give business partners access to an internal application
UPDATE Accounts SET password = SHA2('$password') WHERE account_id = $account_id
[Link]
What Stays on the Intranet?
Your casual code could be copied & pasted into external applications
UPDATE Accounts SET password = SHA2('$password') WHERE account_id = $account_id
UPDATE Accounts SET password = SHA2('$password') WHERE account_id = $account_id
[Link]
What Stays on the Intranet?
Its hard to argue for a security review or rewrite for a finished application
$$ $
UPDATE Accounts SET password = SHA2('$password') WHERE account_id = $account_id
[Link]
MYTH
My framework prevents SQL Injection.
Myth
[Link]
ORMs Allow Custom SQL
Dynamic SQL always risks SQL Injection, for example Rails ActiveRecord:
[Link]( :joins => "JOIN Accounts ON reported_by = account_id", ) :order => "date_reported DESC"
any custom SQL can carry SQL injection
[Link]
Whose Responsibility?
Security is the application developers job No database, connector, or framework can prevent SQL injection all the time
[Link]
FALLACY
Query parameters do quoting for you.
Fallacy
[Link]
Interpolating Dynamic Values
Query needs a dynamic value:
SELECT * FROM Bugs WHERE bug_id = $_GET['bugid']
user input
[Link]
Using a Parameter
Query parameter takes the place of a dynamic value:
SELECT * FROM Bugs WHERE bug_id = ?
parameter placeholder
[Link]
How the Database Parses It
SELECT expr-list *
query
FROM
simpletable
bugs bug_id
WHERE
expr
equality
= ?
parameter placeholder
[Link]
How the Database Executes It
SELECT expr-list *
query
FROM
simpletable
bugs bug_id
WHERE
expr
equality
= 1234
parameter value
[Link]
Interpolation
SELECT expr-list * bug_id query FROM simpletable bugs equality = 1234 WHERE expr OR
TRUE
SQL injection
[Link]
Parameterization
SELECT expr-list *
query
FROM
simpletable
bugs bug_id
WHERE
expr
equality
= 1234 OR TRUE
no parameter can change the tree
[Link]
Sequence of Prepare & Execute
Client Server
prepare query
send SQL parse query optimize query
convert to machinereadable form
execute query send parameters repeat with different parameters return results bind parameters execute query
[Link]
MYTH
Query parameters prevent SQL Injection.
Myth
[Link]
One Parameter = One Value
SELECT * FROM Bugs WHERE bug_id = ?
[Link]
Not a List of Values
SELECT * FROM Bugs WHERE bug_id IN ( ? )
[Link]
Not a Table Name
SELECT * FROM ? WHERE bug_id = 1234
[Link]
Not a Column Name
SELECT * FROM Bugs ORDER BY ?
[Link]
Not an SQL Keyword
SELECT * FROM Bugs ORDER BY date_reported ?
[Link]
Interpolation vs. Parameters
Scenario Example Value Interpolation Parameter
single value multiple values table name
1234
1234, 3456, 5678
SELECT * FROM Bugs WHERE bug_id = $id
SELECT * FROM Bugs WHERE bug_id = ?
SELECT * FROM Bugs SELECT * FROM Bugs WHERE bug_id IN ($list) WHERE bug_id IN ( ?, ?, ? ) SELECT * FROM $table WHERE bug_id = 1234 NO NO
Bugs
column name date_reported SELECT * FROM Bugs ORDER BY $column other syntax DESC
SELECT * FROM Bugs NO ORDER BY date_reported $direction
[Link]
Solution SOLUTION
Whitelist Maps
[Link]
Example SQL Injection
[Link] order=date_reported&dir=ASC
<?php $sortorder = $_GET["order"]; $direction = $_GET["dir"]; $sql = "SELECT * FROM Bugs ORDER BY {$sortorder} {$direction}"; $stmt = $pdo->query($sql);
SQL Injection unsafe inputs
[Link]
Fix with a Whitelist Map
application request values SQL identifiers and keywords
<?php $sortorders = array("DEFAULT" "status" "date" $directions = array( "DEFAULT" "up" "down" => "bug_id", => "status", => "date_reported" ); => "ASC", => "ASC", => "DESC" );
[Link]
Map User Input to Safe SQL
<?php if (isset( $sortorders[ $_GET["order"] ])) { $sortorder = $sortorders[ $_GET["order"] ]; } else { $sortorder = $sortorders["DEFAULT"]; }
[Link]
Map User Input to Safe SQL
<?php $direction = $directions[ $_GET["dir"] ] $directions["DEFAULT"]; ?:
PHP 5.3 syntax
[Link]
Interpolate Safe SQL
[Link]
<?php $sql = "SELECT * FROM Bugs ORDER BY {$sortorder} {$direction}"; $stmt = $pdo->query($sql);
whitelisted values
[Link]
Benefits of Whitelist Maps
Protects against SQL injection in cases where
escaping and parameterization doesnt help.
Decouples web interface from database schema. Uses simple, declarative technique. Works independently of any framework.
[Link]
FALLACY
Queries parameters hurt SQL performance.
Fallacy
[Link]
Simple Query
Proled Elapsed
0.004 0.003 0.002 0.001
MySQL MySQLi MySQLi Prep
PDO
0
PDO Prep
[Link]
Complex Query
Proled Elapsed
1.56 1.17 0.78 0.39
MySQL MySQLi MySQLi Prep PDO
0
PDO Prep
[Link]
MYTH
A proxy/firewall solution prevents SQL injection.
Myth
[Link]
Oracle Database Firewall
Reverse proxy between application and Oracle
Whitelist of known SQL queries Learns legitimate queries from application traffic Blocks unknown SQL queries Also supports Microsoft SQL Server, IBM DB2,
Sybase ASE, SQL Anywhere
[Link]
[Link]
GreenSQL
Reverse proxy for MySQL, PostgreSQL, Microsoft SQL Server Detects / reports / blocks suspicious queries:
Access to sensitive tables Comments inside SQL commands Empty password An or token inside a query An SQL expression that always returns true
[Link]
[Link]
Still not Perfect
Vipin Samar, Oracle vice president of Database Security:
Database Firewall is a good first layer of defense for databases but it won't protect you from everything,
[Link]
GreenSQL Architecture
GreenSQL can sometimes generate false positive and false negative errors. As a result, some legal queries may be blocked or the GreenSQL system may pass through an illegal query undetected.
[Link]
[Link]
Limitations of Proxy Solutions
False sense of security; discourages code review Gating factor for emergency code deployment Constrains application from writing dynamic SQL Doesnt stop SQL injection in Stored Procedures
[Link]
FALLACY
NoSQL databases are immune to SQL injection.
Fallacy
[Link]
NoSQL Injection
[Link]
<?php $map = new MongoCode("function() { emit(this." . $_GET["column"] . ",1); } "); $data = $db->command( array( "mapreduce" => "Users", "map" => $map ) ); any string-interpolation of untrusted content is Code Injection
[Link]
NoSQL Injection in the Wild
Diaspora wrote MongoDB map/reduce functions dynamically from Ruby on Rails:
def [Link](query) [Link]('$where' => "function() { return this.diaspora_handle.match(/^#{query}/i) || [Link].first_name.match(/^#{query}/i) || [Link].last_name.match(/^#{query}/i); }") end
did query come from a trusted source?
[Link]
[Link]
Myths and Fallacies
I dont have to worry anymore Escaping is the fix More escaping is better I can code an escaping function Only user input is unsafe Stored procs are the fix SQL privileges are the fix My app doesnt need security Frameworks are the fix Parameters quote for you Parameters are the fix Parameters make queries slow SQL proxies are the fix NoSQL databases are the fix
there is no single silver bullet use all defenses when appropriate
[Link]
SQL Antipatterns
[Link]
[Link]
Copyright 2012 Bill Karwin [Link]/billkarwin
Released under a Creative Commons 3.0 License: [Link] You are free to share - to copy, distribute and transmit this work, under the following conditions:
Attribution. You must attribute this work to Bill Karwin.
Noncommercial. You may not use this work for commercial purposes.
No Derivative Works. You may not alter, transform, or build upon this work.
[Link]