Php Security Crash Course - 4 - Sql Security

Part IV SQL Database Security

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  1

SQL Database Security

• SQL-Injection • SQL and overlong data • SQL-Transactions / -Errorhandling

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  2

SQL-Injection

• Introduction • Attack Classification • Finding SQL-Injection • Abusing SQL-Injection • Preventing SQL-Injection

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  3

SQL-Injection (I)

„SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.“ Wikipedia

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  4

SQL-Injection (II)

• SQL-Injection vulnerabilities are the result of user input that is dynamically embedded into SQL-statements without proper preparation
?>

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  5

SQL-Injection (III)

• no strict separation of control statements and data • by injecting meta characters data takes over the role of control statements

• attacker is able to modify / disable parts of a query • $l = “admin‘/*“ • $p = “foo“

➡

SELECT * FROM u WHERE login=‘admin‘/*‘ and pass=‘foo‘

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  6

SQL-Injection: Attack-Types (I)

• authentication bypass • data theft • denial of service (DOS) • data manipulation

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  7

SQL-Injection: Attack-Types (II)

• website defacement • malware distribution • triggering buffer-overflows in internal SQL functions • executing shell commands (in MSSQL)

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  8

MySQL, PHP and Multi-Query-Processing

• MySQL supports multi-query-processing SELECT * FROM x;DROP DATABASE a; DROP DATABASE b;

• PHP • disables multi-query-processing by default • mysqli_multi_query() to use it explicitly • therefore by default data manipulation only possible in case of injection into UPDATE, INSERT, REPLACE queries (or data manipulating stored procedures)

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  9

Blackbox Search for SQL-Injection (I)

• enumerating all parameters • URL parameter, form data fields, cookies, HTTP headers

• manipulating all parameters • replacing with a single quote • appending a single quote • inserting a single quote in the middle

• comparing output generated by web application

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  10

Blackbox Search for SQL-Injection (II)

• Errormessages make recognition and abuse of SQL-injection vulnerabilities easier Ungültige SQL-Abfrage: SELECT * FROM users WHERE login='adm'n' and pass='xxx' mysql error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' and pass='xxx'' at line 1 mysql error number: 1064

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  11

Blackbox Search for SQL-Injection (III)

• even without errormessages it is often possible to see a difference in the output if SQL-queries fail

• when there is no difference in the output then the SQL-injection can only be detected by timing

• if you check your own application then it is recommended to output error messages for failing SQL-queries during the time of the test

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  12

Abusing SQL-Injections

• Basic injections • UNION injections • Blind SQL-injections • ORDER BY Injections • information_schema / INTO OUTFILE

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  13

Basic Injections

• the common SQL-injection occurs in the WHERE part of SQL-queries

• a basic injection can • modify search criteria • modify sort order • limit the result-set • only search in the current table SELECT * FROM user WHERE login=‘admin‘ and pw=‘‘ or ‘‘=‘‘

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  14

UNION Injections (I)

• UNION injections inject UNION SELECT statements to add further results

• can retrieve data from arbitrary tables SELECT FROM WHERE UNION SELECT FROM WHERE

name,price products id=5 and 1=2 password,null users login=‘admin‘

• very interesting if result set is echoed out

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  15

UNION Injections (II) - Problems

• Abusing UNION injections not straight forward • unknown table names • number of parameters must match • data type of parameters must fit

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  16

Blind SQL-Injections

• no direct echo • data theft through changes in display • conditional results • conditional errors • time delays

• slow, because data is stolen bit by bit

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  17

ORDER BY Injections

• special form of bling SQL injections • injection into the ORDER BY part of a SQL-query • allows changing the sort criteria • sort criteria can be functions allowing data retrieval bit by bit

• value of bits can be derived from order of result-set SELECT * FROM user ORDER BY (id=1 && conv(substring(passwd,1,1),16,10)&1)

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  18

information_schema (I)

• information_schema is a meta-database +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | KEY_COLUMN_USAGE | | PROFILING | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | STATISTICS | | TABLES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | +---------------------------------------+

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  19

information_schema (II)

• access cannot be blocked • access to structure information makes abuse easier, especially UNION injections

• exploits first retrieve structure information and in a second step the data SELECT name,price FROM products WHERE id=5 and 1=2 UNION SELECT concat(table_schema,0x2e,table_name, 0x2e,column_name),null FROM information_schema.columns

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  20

SELECT ... INTO OUTFILE

• MySQL allows writing results into files - FILE permission • SQL-injection can create arbitrary files on the hard disk • writable directories in the document root are dangerous • arbitrary PHP code execution through dropped PHP files SELECT name,price FROM products WHERE id=3 and 1=2 UNION SELECT ““, 1 INTO OUTFILE “/var/www/htdocs/cache/foobar.php“

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  21

SQL-Injection Prevention

• Input Validation • Escaping • Prepared Statements • Handling Special Cases • Stored Procedures

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  22

Input Validation

• all user input must be validated • Validation includes • validating the data type • validating against the allowed set of characters • validating of value ranges • validating the length of strings

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  23

Escaping

• escaping describes the process to prepare strings for embedding them into dynamic SQL-queries

• escaping disarms meta characters in user input • meta characters in SQL-statements are single and double quotes

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  24

Escaping-Methods

• Backslash Escaping \ => \\ ‘ => \‘

SELECT * FROM u WHERE name=‘O\‘neil‘

“ => \“

• Duplicating Quotes ‘ => ‘‘ “ => ““

SELECT * FROM u WHERE name=‘O‘‘neil‘

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  25

Escaping-Methods in PHP

• internal PHP methods • addslashes() • magic_quotes_gpc

• database specifc methods • mysql_real_escape_string() • PDO::quote() • pg_escape_string() • sqlite_escape_string()

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  26

Escaping-Methods in PHP - Example (I)



Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  27

Escaping-Methods in PHP - Example (II)

• Attacker can no longer modify the SQL-query • $l = “admin‘/*“ • $p = “egal“

➡

SELECT * FROM u WHERE login=‘admin\‘/*‘ and pass=‘egal‘

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  28

Escaping Pitfalls - Wrong Usage (I)

• Escaping not suited for numbers $sql = “SELECT vorname FROM users WHERE id=“. escapeFunc($id);

• there are no quotes around the user input • therefore no meta characters required for injection • escaping offers no protection SELECT firstname FROM users WHERE id=-1 UNION SELECT password FROM users WHERE id=1

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  29

Escaping Pitfalls - Wrong Usage (II)

• same is true for • Names (databases, tables, columns, functions) $sql = “SELECT * FROM “ . escapeFunc($name);

• Limits $sql = “SELECT * FROM users LIMIT “. escapeFunc($limit);

• Sort criteria / order (ASC/DESC) $sql = “SELECT * FROM users ORDER BY login “. escapeFunc($dir);

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  30

Escaping Pitfalls - Late Modification (I)

• Escaped data must not be modified • especially no trimming in length • and no decoding (e.g. base64)

• Otherwise the danger of loosing the escaping exists

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  31

Escaping Pitfalls - Late Modification (II)

• Example: Length-Trimming aus wird und dann

1234567‘ 1234567\‘ 1234567\

SELECT * FROM u WHERE login=‘1234567\‘ AND PASS=‘ or id=1/*‘

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  32

Escaping Pitfalls - Encoding (I)

• escaping works on character level • escaping therefore depends on character encoding • with single-byte encoding all function work • with multi-byte encoding a multi-byte aware function like mysql(i)_real_escape_string() is required

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  33

Escaping Pitfalls - Encoding (II)

• using non multi-byte aware functions sometimes secure but not with all possible multi-byte encodings

• will lead to problems, if backslash or quotes are valid 2nd, 3rd or X. characters in the encoding • X‘ will be escaped to X\‘ • if X\ is a valid multi-byte character then escaping is no protection • for the multi-byte parser the single quote is not escaped

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  34

Escaping Pitfalls - Encoding (III)

• no problem with UTF-8 • neither backslash nor quotes are accepted succession bytes

• but real problem in several east asia encodings like GBK, SJIS, ... SELECT * FROM u WHERE login=‘X\‘ or id=1/*‘ AND PASS=‘xxx‘

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  35

Escaping Pitfalls - Encoding (IV)

„database specific escaping funktions like mysql_real_escape_string() are always preparing strings correctly, or?“

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  36

Escaping Pitfalls - Encoding Changes

• mysql(i)_query(“SET NAMES GBK“); • pro:

compatible with all PHP versions

• contra: libmysql doesn‘t recognize the change which leads to malfunction of mysql_real_escape_string()

• mysql(i)_set_charset(“gbk“); • pro:

changes encoding in a way that allows libmysql to notice the change

• contra: requires PHP >= 5.2.3

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  37

Prepared Statements (I)

• Escaping • is complicated • is prone to errors • doesn‘t solve the real problem - „mixture of data and control stream“

• Prepared Statements • originally meant for optimizing performance • solve the mixture-problem

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  38

Prepared Statements (II)

• Prepared statements allow the preparation of SQLqueries for the repeated execution with different data

• Data (numbers, strings, values) are replaced by placeholders SELECT * FROM user WHERE username=?

• and transfered separately ➡ mixture-problem is solved

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  39

Prepared Statements (III)

• In PHP prepared statements are supported by • ext/mysqli - mysqli_prepare() • ext/pdo - PDO::prepare() prepare(“SELECT id FROM users WHERE login = ? and pass = ?“); if ($sth->execute(array(“O‘Neal“, “geheim“)) === true) { $data = $sth->fetchAll(); } ?>

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  40

Prepared Statement Pitfalls

• Placeholders exist for numbers, strings and values • but not for • Names (databases, tables, columns, functions) • Limits • Sort-direction (ASC / DESC) • IN statement lists

• dynamically created prepared statements are vulnerable to SQL-injection

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  41

Handling Special Cases (I)

Escaping and prepared statements have problems with

• Names (databases, tables, columns, functions) • Limits • Sort-criteria / sort-direction • IN statement lists

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  42

Handling Special Cases (II)

• Names (databases, tables, columns, functions) •

Whitelist of allowed values



Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  43

Handling Special Cases (III)

• Limits •

values must be numbers

• Sort-direction •

Whitelist - only ASC and DESC are allowed



Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  44

Handling Special Cases (IV)

• IN statement lists - escaping

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  45

Handling Special Cases (V)

• IN statement lists - prepared statements

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  46

Stored Procedures (I)

• new features in MySQL 5.0 • defines a sub procedure with multiple SQL-queries • moves application logic into the database server • client calls stored procedure, instead of combining SQL-result-sets

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  47

Stored Procedures (II) - Example

DELIMITER $$ DROP PROCEDURE IF EXISTS getUserId$$ CREATE PROCEDURE getUserId ( IN in_login varchar(20), IN in_pass varchar(20), OUT out_id int) BEGIN SELECT id INTO out_id FROM users WHERE name=in_login AND pass=in_pass; END$$

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  48

Stored Procedures (III) - Example

• calling the stored procedure CALL getUserId(‘name‘, ‘password‘, @result); SELECT @result;

• doesn‘t solve SQL-injection problems • secure wrapper function for calling stored procedures is required

• however using only stored procedures for database access limits the damage that can be caused

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  49

SQL and overlong Data

• SQL-Injection meanwhile known by developers • they validate data types • they limit the character set • they validate against allowed value ranges • they use escaping or prepared statements

• But most of them do not validate the lenght of data ➡ New problems

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  50

Overlong Daten - max_allowed_packet

• max_allowed_packet defines the maximum packet size • if a query doesn‘t fit in a packet it will not be executed • missing length check can lead to skipped SQL-queries • allows targeted killing of e.g. logging-queries • overlong „User-Agent“ header • overlong session-id

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  51

Overlong Data - Columnsize (I)

• Colums have a defined maximum size • MySQL truncates overlong strings during insertion • aus

‘admin

x‘

• wird

‘admin

‘

• Truncation by default only triggers a warning

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  52

Overlong Data - Columnsize (II)

• normal comparison ignores spaces at the end • SELECT * FROM user WHERE login=‘admin‘ • SELECT * FROM user WHERE login=‘admin • SELECT * FROM user WHERE login=‘admin

‘ ‘

➡ security problem because there are now 2 admin users

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  53

Overlong Data - Countermeasures

• Input validation must validate the length • MySQL should be used in the STRICT_ALL_TABLES mode to ensure truncated values result in errors

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  54

Errorhandling (I)

• Programmer assume success • errors are often ignored • or handled like empty result sets • and empty result sets are often not catched

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  55

Errorhandling (II)

• Transactions don‘t exist in the world of PHP... •

race-condition problems

•

or faulty database

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  56

Errorhandling (III)

Example: Webshop Transaction - Cancelation

• Cancelation-process 1. Customer cancels order 2. Webshop marks order as canceled in billing table 3. Webshop marks order as canceled in deliver table

• Without transaction safe SQL queries the database could crash at (3) ➡

Customer gets goods but no bill

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  57

Questions ?

Stefan Esser • PHP Security Crash Course at Dutch PHP Conference 2009 •  June 2009 •  58