SQL Injection

1. Introduction Background Techniques Prevention Demo Conclusions Questions SQL Injection Anthony Brown March 4, 2008

2. Introduction Background Techniques Prevention Demo Conclusions Questions Outline • Background of SQL Injection • Techniques and Examples • Preventing SQL Injection • Demo • Wrap-Up • Questions

3. Introduction Background Techniques Prevention Demo Conclusions Questions Background of SQL Injection

4. Introduction Background Techniques Prevention Demo Conclusions Questions Databases: Where are they now?

5. Introduction Background Techniques Prevention Demo Conclusions Questions Why is SQL a standard? Relational Database Runtime Interpretation Platform Independence Loose Semantics

6. Introduction Background Techniques Prevention Demo Conclusions Questions Flexibility = Vulnerability • Simple Injection • Decoding Error Messages • Blind Injection • Encoding Exploits • Stored Procedures - - - • Programmer Error(Faulty Logic)

7. Introduction Background Techniques Prevention Demo Conclusions Questions SQL Injection Techniques

8. Important Symbols ‘  “Hack” --  “Comment Out” ;  “End Statement” % , *  “Wildcards”

9. SQL Injection Definition The input field is modified in such a way that the Database returns unintended data. Sql: SELECT <column name> FROM <Table name> WHERE <logic expression>

10. Introduction Background Techniques Prevention Demo Conclusions Questions Example: Database Schema • Table Users • Has columns “username” and “password” • Accessed when users log in • Table Customers • Has column “phone” • Users can look up other customer phone numbers by name • Application does no input validation

11. Introduction Background Techniques Prevention Demo Conclusions Questions Returning Extra Rows with “union” • Query: SELECT phone FROM Customers WHERE last_name = ‘<name>’ • Input:x’ UNION SELECT username FROM users WHERE ‘x’ = ‘x

12. Introduction Background Techniques Prevention Demo Conclusions Questions Modifying Records • Application has password changing page • SQL: UPDATE users SET password = ‘<newpassword>’ WHERE username = ‘<username>’ • Input: newpassword’ WHERE username LIKE ‘%admin%’ --

13. Introduction Background Techniques Prevention Demo Conclusions Questions MS SQL Server • Default SQL Server setup • Default system admin account “sa” enabled • No password!!! • Supports multiple queries • “Extended stored procedures”: C/C++ DLL files • Read/write external files • Access command line

14. Introduction Background Techniques Prevention Demo Conclusions Questions Exploiting SQL Server • Use phone look-up query again: SELECT phone FROM customers WHERE last_name = ‘<name>’ • Input:'; exec master..xp_cmdshell 'iisreset'; --

15. Introduction Background Techniques Prevention Demo Conclusions Questions Preventing SQL Injection

16. Introduction Background Techniques Prevention Demo Conclusions Questions Preventing SQL Injection • Input Validation • Input Checking Functions • Access Rights • User Permissions • Variable Placeholders • Stored Procedures

17. Introduction Background Techniques Prevention Demo Conclusions Questions Input Validation • Checks • Type • Size • Format • Range • Replace quotation marks “All input is wrong and dangerous”

18. Introduction Background Techniques Prevention Demo Conclusions Questions Input Checking Functions • Built in character rejection $sql = “SELECT * FROM Users WHERE ID = ‘” . $_GET[‘id’] . “’”; $sql = “SELECT * FROM Users WHERE ID =” . mysql_real_escape_string($_GET[‘id’]); $result = mysql_query($sql);

19. Introduction Background Techniques Prevention Demo Conclusions Questions Access Rights Web User vs. System Administrator – ‘sa’

20. Introduction Background Techniques Prevention Demo Conclusions Questions User Permissions • Limit query access rights • SELECT • UPDATE • DROP • Restricted statement access • Global-specific • Database-specific • Table-specific

21. Introduction Background Techniques Prevention Demo Conclusions Questions Variable Placeholders (?) • Defense from String Concatenation • Enforcing database data types PreparedStatement prep = conn.prepareStatement("SELECT * FROM USERS WHERE PASSWORD=?"); prep.setString(1, pwd);

22. Introduction Background Techniques Prevention Demo Conclusions Questions Stored Procedures • Use error checking variables • Buffer direct database access

23. Introduction Background Techniques Prevention Demo Conclusions Questions Demonstration

24. Introduction Background Techniques Prevention Demo Conclusions Questions Conclusions • SQL Injection continues to evolve with new technologies • Dangerous Effects • Access to critical information • Updating data not meant to be updated • Exploiting DBMS to directly affect the server and its resources • Prevention of SQL Injection • Input Validation and Query Building • Permissions and Access Rights • Variable Placeholders (Prepare) and Stored Procedures

25. Introduction Background Techniques Prevention Demo Conclusions Questions Questions • 1) What could prevent the ‘Students’ table from being dropped? • 2) What is another way to prevent Injection?

26. Introduction Background Techniques Prevention Demo Conclusions Questions Questions?

27. Introduction Background Techniques Prevention Demo Conclusions Questions References • Achour, Mehdi, Friedhelm Betz, Antony Dovgal, et al. "Chapter 27. Database Security." PHP Manual. 13 January 2005. PHP Documentation Group. 07 Apr. 2005 <http://www.php- center.de/en-html-manual/security.database.sql- injection.html>. • Dewdney, A. K. The New Turing Omnibus. New York: Henry Holt, 1989. 427-433. • "Exploits of a Mom." xkcd.com. 4 Mar. 2008 <http://xkcd.com/327/>. • Finnigan, Pete. " SQL Injection and Oracle, Part One ." SecurityFocus 21 November 2002. 07 Apr 2005 <http://www.securityfocus.com/infocus/1644>. • Harper, Mitchell. "SQL Injection Attacks: Are You Safe?." Dev Articles. 29 May. 2002. 07 Apr. 2005 <http://www.devarticles.com/c/a/MySQL/SQL-Injection- Attacks-Are-You-Safe/2/>. Questions