SQL INJECTION

1. SQL INJECTION EC500 Lecture Made By: Jiaxi Jin, Rashmi Shah, Ludovico Fontana Boston University

2. OUTLINE • SQL Background • SQL Syntax • What is SQL Injection? (What?) • What kind of information can we get from it? (why?) • Real world examples • How does it work (How?) • SQL Injection Types • Scripts - Tools • Detection Techniques • Prevention Techniques

3. SQL Background • SQL stands for Structured Query Language • a programming language designed for managing data in relational database management systems (RDBMS). • Allows us to define and manipulate data in a database. • Used for relational databases. • ANSI and ISO standard computer language Although the standards have been enhanced several times, SQL portability between major RDBMs differ on implementation.

4. SQL Background • There are many different versions of the SQL language • They support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others). • Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

5. Database Table A database contains one or more tables Each table is identified by a name. E.g. customers, orders Table contains records (rows) with data Example of a table contains “customers” and passwords:

6. SQL Syntax : Keywords SELECT UPDATE DELETE INSERT INTO UNION

7. SQL Syntax : SELECT SELECT first_name, last_name FROM users Users: Result:

8. SQL syntax : SELECT * SELECT * FROM users WHERE username = ‘johns’ Users: Result:

9. SQL Syntax : Insert INTO Insert INTO orders VALUES (3, 67, ‘Kindle Touch’, 99) orders: Result:

10. SQL SYNTAX : UPDATE Update users SET first_name = Kenneth, last_name = Lee WHERE user_id = 3 Users: Result:

11. SQL Syntax : DELete DELETE FROM users WHERE user_id = 1 Users: Result:

12. SQL SYNtax : Union SELECT first_name, last_name FROM users WHERE user_id = 1 UNION SELECT product_name, price FROM orders WHERE user_id = 1

13. Union Select : Example Users: Orders: Result: Maintains header from first query but appends results from the second query.

14. SQL SYNTAX : SYMBOLS COMMENTS: # or -- Example: SELECT * FROM ‘table’ #selects everything LOGIC: ‘a’=‘a’ Example: SELECT * FROM ‘table’ WHERE ‘a’=‘a’ MULTI STATEMENTS: S1; S2 Example: SELECT * FROM ‘table’; DROP TABLE ‘table’;

15. What Is SQL Injection? • Code Injection Technique • placing SQL codes in the user input • Exploits Security Vulnerability • Website/server 's software is not safe • Targets User Input Handlers • Incorrect type handling: supplied field is not strongly typed / not checked for type constraints

16. What Can We Get From SQL Injection? • We can execute queries against a database to: • retrieve data from a database • update records in a database • delete records from a database • insert new records in a database • Attacker can then do through SQL Injection: • Reveal others' usernames and/or passwords • Collect personal/corporate information (credit card, etc.) • Change/Delete information (sabotage) • Gain access to the host • Plant data/code • Create backdoors

17. Example Attack: Basic Product Search: blah‘ OR ‘x’ = ‘x • This input is put directly into the SQL statement within the Web application: • $query = “SELECT prodinfo FROM prodtable WHERE prodname = ‘” . $_POST[‘prod_search’] . “’”; • Creates the following SQL: • SELECT prodinfo FROM prodtable WHERE prodname = ‘blah‘ OR ‘x’ = ‘x’ • Attacker has now successfully caused the entire database to be returned.

18. SQL Injection: Advanced • What if the attacker had instead entered: • blah‘; DROP TABLE prodinfo; # • Results in the following SQL: • SELECT prodinfo FROM prodtable WHERE prodname = ‘blah’; DROP TABLE prodinfo; --’ • Note how comment (--) consumes the final quote • Causes the entire database to be deleted • Depends on knowledge of table name • This is sometimes exposed to the user in debug code called during a database error • Use non-obvious table names, and never expose them to user • Usually data destruction is not your worst fear, as there is low economic motivation

19. Real World Examples • On August 17, 2009, the United States Justice Department charged an American citizen Albert Gonzalez and two unnamed Russians with the theft of 130 million credit card numbers using an SQL injection attack. • In 2008 a sweep of attacks began exploiting the SQL injection vulnerabilities of Microsoft's IIS web server and SQL database server. Over 500,000 sites were exploited. • More examples available at: http://en.wikipedia.org/wiki/SQL_injection#Known_real-world_examples

20. LiVE ATTACK : INPUT

21. LIVE ATTACK : OUTPUT

22. SQL Injection Attack Types • First Order Attack • The attacker can simply enter a malicious string and cause the modified code to be executed immediately. • Blind Attack • When the attacker can't receive feedback of his actions. • Error Based Attack • When the attacker gets information through the database error messages. Ref: Oracle Learning Library - Defending Against SQL Injection Attacks

23. SQL Injection Attack Types • Second Order Attack • The attacker injects into persistent storage (such as a table row) which is deemed as a trusted source. An attack is subsequently executed by another activity. • The attacker creates functions for later use. • The attacker creates functions that will execute when a timer goes off. Ref: Oracle Learning Library - Defending Against SQL Injection Attacks

24. SQL Injection Tools • SQLMap • Open source penetration testing tool, detecting and exploiting SQL injection flaws. • SQLBrute • Brute forcing data out of databases using blind SQL injection vulnerabilities. Time based and error based exploit, written in Python. • SQLNinja • Specifically targeted to Microsoft SQL Server. • BSQL Hacker • Automatic deep blind time-based SQL injector. • Mini Mysqlat0r • Written in Java.

25. Preventions: I • SQL String Escaping • Many attacks can be thwarted by simply using the SQL string escaping mechanism • ‘ becomes \’ and “ becomes \” • mysql_real_escape_string() is a function that does that for you.

26. Preventions: II • Input Validation • Many classes of input have fixed formats. • Email addresses, dates, part numbers, etc. • Verify that the input is a valid string in the language • Sometime languages allow problematic characters (e.g., ‘*’ in email addresses); may decide to not allow these • Exclude quotes and semicolons. • Not always possible: consider the name Shaq O’Neal • Want to allow the use of single quotes in names • Use of Prepared statements (Parametrized Queries) • A function to tell the DBMS which part is fixed and which part is an user input. That way commands from the input are ignore.

27. Preventions: III • READ ONLY database access. • Configure different users in the DBMS. • Always enforce the strongest constraint at: SERVER SIDE. • Never trust any input from clients • Always Remember: IT IS NOT SAFE OUT THERE!!!

28. THANK YOU

29. REFERENCES • Oracle Learning Library: http://st-curriculum.oracle.com/tutorial/SQLInjection/html/lesson1/les01_tm_attacks.htm • SecuriTeam - SQL Injection Walkthrough : http://www.securiteam.com/securityreviews/5DP0N1P76E.html • Friedl, S. (2009, 10 26). SQL Injection Attacks by Example. • OWASP - SQL Injection: (https://www.owasp.org/index.php/SQL_Injection)

30. Lab Session Tutorial • SQL Manipulation • Modify the original SQL query by including additional queries • Inclusion of conditional statement in where clause • “Select * from Table where Username=’ ‘ and password=’ ‘” • “Select * from Table where Username=’ ‘or ‘c’=’c’ -- and password=’ ‘” • Use UNION, INTERSECT • Select * from projects where projecttype=’ ‘ • Select * from project where projecttype=’ ‘ UNION Select * from school

31. Lab Session Tutorial • Code Injection • Insert new SQL commands into the original SQL query • Select * from users where username=’ ‘can be modified to • Select * from users where username =’ ‘; drop table faculty

32. Lab Session Tutorial • Incorrect Queries • By inserting logical errors into the query, attackers get hold of the error information • The error information often reveal names of the tables and columns that caused the error • ”Microsoft OLE DB Provider for SQL Server (0x80040E07) Error converting nvarchar value ’CreditCards’ to a column of data type int.”

33. Lab Session Tutorial • Function Call Injection • An attacker can inject different database and operating system functions in a SQL statement • “Select * from Table where Username=’ ‘ and password=’ ‘” can be modified to • “Select * from Table where Username=’ ‘shutdown with nowait; -- and password=’ ‘” • SHUTDOWN WITH NO WAIT causes SQL server to shut down, stopping Windows Service