SQL Injection Attacks and Counter Measures

1. SQL Injection Attacks and Counter Measures S K Dhaval Kashyap (ks12f) COP5725 Advanced Database Systems

2. A Classification of SQL Injection Attacks and Countermeasures • William G.J. Halfond, Jeremy Viegas, and Alessandro Orso • College of Computing, Georgia Institute of Technology • Published year: 2006, Cited 263 times

3. Concern • Serious threat to Web applications • Unrestricted access to the DB • Potential SI contained in these DB • Various methods available for prevention • Researchers only familiar with a subset of the vulnerabilities

4. Proposal • Present an extensive review of the types of SQLIAs • Description on each attack type with e.g. • Analyze existing detection and prevention techniques • Discuss strength and weakness

5. What is an SQL Injection Attack? • Reference to a class of code-injection attacks • Data provided by the user is included in an SQL query in such a way that part of the user’s I/P is treated as SQL code

6. Background • When does SQLIAs occur? • When an attacker changes the intended effect of an SQL query by inserting new SQL keywords or operators into the query • 2 characteristics of SQLIAs used to describe an attack. • Injection Mechanism (ways by which malicious code is introduced using different I/P mechanisms) • Attack Intent (based on the goal or intent of the attacker)

7. Injection Mechanism • Injection through user input • Suitably crafted user I/P • Comes from form submission via HTTP GET or POST • Injection through cookies • Files containing state information • When cookie content used to build SQL queries • Injection through server variables • Collection of HTTP, N/W and environment variables • Used for logging stats and to identify browsing trends • Forge values, placed in HTTP and N/W headers • Query to log the variable issues, attack in the forged header is triggered

8. Second order injection • Malicious I/P used to indirectly trigger an SQLIA when the I/P is used at a later time • Rely on where the I/P will be subsequently used

9. A user registers using a seeded user name, such as “admin’ -- ”, appliction will escape the single quote to prevent the potential malicious effect • User next updates the password, for this the app might check he knows his current pwd and changes the pwd is the check is a success • The query string sent to the DB UPDATE users SET password=’newpwd’ WHERE userName= ’admin’--’ AND password=’oldpwd’ • Because “--” is the SQL comment operator, everything after it is ignored by the DB. Therefore, the result of this query is that the database changes the password of the administrator “admin” to an attacker-specified value queryString="UPDATE users SET password=’" + newPassword + "’ WHERE userName=’" + userName + "’ AND password=’" + oldPassword + "’“

10. Attack Intent • Identifying injectable parameters • Performing database finger-printing • Determining database schema • Extracting data • Adding or modifying data • Performing denial of service • Evading detection • Bypassing authentication • Executing remote commands • Performing privilege escalation

11. Servlet implementation for authentication 1. String login, password, pin, query 2. login = getParameter("login"); 3. password = getParameter("pass"); 3. pin = getParameter("pin"); 4. Connection conn.createConnection("MyDataBase"); 5. query = "SELECT accounts FROM users WHERE login=’" + 6. login + "’ AND pass=’" + password + 7. "’ AND pin=" + pin; 8. ResultSet result = conn.executeQuery(query); 9. if (result!=NULL) 10. displayAccounts(result); 11. else 12. displayAuthFailed();

12. SQLIA Types

13. Tautologies • Inject code in one or more conditional statements so that they always evaluate to true • Most common usage : Bypass authentication and extract data • Attacker exploits an injectable field in the where clause

14. Example • An attacker submits “ ’ or 1=1 - -” for the login input field (the input submitted for the other fields is irrelevant) • The resulting query is: SELECT accounts FROM users WHERE login=’’ or 1=1 -- AND pass=’’ AND pin= • The code injected in the conditional (OR 1=1) transforms the WHERE clause to tautology

15. Illegal/Logically Incorrect Queries • Gather info about type and structure of the DB • Preliminary info gathering for other attacks • Error info reveal vulnerable/ injectable fields

16. Example • Goal is to cause a type conversion error that can reveal relevant data. To do this, the attacker injects the following text into input field pin: “convert(int,(select top 1 name from sysobjects where xtype=’u’))” The resulting query is: SELECT accounts FROM users WHERE login=’’ AND pass=’’ AND pin= convert (int,(select top 1 name from sysobjects where xtype=’u’))

17. Select query attempts to extract the first user table (xtype=‘u’) from the DB metadata table • We assume that the app is running MS SQL server for which the metadata table is calledsysobjects • Trying to convert the table name into an int • Illegal type conversion and you get an error msg “Microsoft OLE DB Provider for SQL Server(0x80040E07) Error converting nvarchar value ‘CreditCards’ to a column of data type int”

18. Union Query • Exploit a vulnerable parameter to change the data set returned for a given query • Trick the application into returning data from a table different from the one that was intended by the developer • Result is the union of the results from the original query and the injected second query

19. Example SELECT accounts FROM users WHERE login=’’ UNION SELECT cardNo from CreditCards where acctNo=10032 -- AND pass=’’ AND pin= • Assuming that there is no login equal to “”, the original first query returns the null set, whereas the second query returns data from the “CreditCards” table. • In this case, the database would return column “cardNo” for account “10032.” The database takes the results of these two queries, unions them, and returns them to the application. • In many applications, the effect of this operation is that the value for “cardNo” is displayed along with the account information

20. Piggy-Backed Queries • An attacker tries to inject additional queries into the original query. We distinguish this type from others because, in this case, attackers are not trying to modify the original intended query; instead, they are trying to include new and distinct queries that “piggy-back” on the original query • SELECT accounts FROM users WHERE login=’doe’ AND pass=’’; drop table users -- ’ AND pin=123

21. Stored Procedure • SQLIAs of this type try to execute stored procedures present in the database • Inference • Here the query is modified to recast it in the form of an action that is executed based on the answer to a true/false question about data values in the database • Alternate Encodings • The injected text is modified so as to avoid detection by defensive coding practices and also many automated prevention techniques

22. SQLIA Prevention • Sanitize the input • Escape the input • Limit database permissions and segregate users • Use stored procedures for database access • Configure error reporting • Using tools

23. Defensive Coding Practices • Input type checking • SQLIAs are performed by injecting commands into either a string or numeric parameter • Reject characters other than digit for a numeric parameter • Encoding on inputs • Injection into a string parameter is usually through the use of meta character that trick the SQL parser into interpreting user input as SQL tokens • Prohibit the use of meta characters • Use function to encode a string so that the meta characters are specially encoded and interpreted by the DB as normal characters

24. Positive pattern matching • Establish input validation routines that differentiate good and bad inputs • Vs –ve validation , that looks for forbidden patterns or SQL tokens • Identification of all input sources

25. Issues • Best way to prevent SQLIAs • Problematic in practice • Prone to human error • Not as rigorous and completely applied as automated techniques • Difficult for developers to identify all sources

26. Detection and Prevention Techniques • Black Box Testing • Static Code Checkers • Combined Static and Dynamic Analysis • Taint Based Approaches • New Query Development Paradigms • Intrusion Detection Systems • Proxy Filters • Instruction Set Randomization

27. TECHNIQUES EVALUATION • Evaluation with Respect to Attack Types • Evaluation with Respect to Injection Methods • Evaluation with Respect to Deployment Requirements • Evaluation of Prevention-Focused Techniques with Respect to Defensive Coding Practices

29. “*” : Developer-based techniques • “•” : To denote that a technique can successfully stop all attacks of that type • “x” : To denote that a technique that is not able stop attacks of that type • “◦” : Denotes a technique that can address the attack type considered, but cannot provide any guarantees of completeness • “-” : Denotes technique that address the attack type considered only partially because of intrinsic limitations of the underlying approach

33. Be careful out there!