SQL and Other Injection

1. SQL and Other Injection

2. A little background dirt… • jim@manicode.com • @manicode • Former OWASP Global Board Member • Project manager of theOWASP Cheat Sheet Series andseveral other OWASP projects • 20+ years of softwaredevelopment experience • Author of "Iron-Clad Java,Building Secure Web Applications”from McGraw-Hill/Oracle-Press • Kauai, Hawaii Resident

3. WARNING: Please do not attempt to hack anycomputer system without legal permission to do so.Unauthorized computer hacking is illegal and canbe punishable by a range of penalties includingloss of job, monetary fines and possible imprisonment. ALSO: The Free and Open Source Software presented in these materials are examples of good secure development tools and techniques. You may have unknown legal, licensing or technical issues when making use of Free and Open Source Software. You should consult your company's policy on the use of Free and Open Source Software before making use of any software referenced in this material.

4. Injection: Where are we going? SQL Injection Attack Types Parameterized Queries Database Configuration Security Command Injection

5. So… What is SQL Injection?

6. Anatomy of SQL Injection Attack sql = "SELECT id, uname FROM user_table WHERE username = ' " & Request("username") & " ' AND password = ' " & Request("password") & " ' "; What the developer intended:username = chippassword = P@ssw0rd1 SQL Query:SELECT id, uname FROM user_table WHERE username= 'chip'AND password = 'P@ssw0rd1'

7. Anatomy of SQL Injection Attack sql = "SELECT id, uname FROM user_table WHERE username = ' " & Request("username") & " ' AND password = ' " & Request("password") & " ' "; What the developer did not intend is parameter values like:username = johnpassword = blah' or '1'='1' limit 1 -- SQL Query:SELECT id,uname FROM user_table WHERE username = 'john' AND password = 'blah' or '1'='1' limit 1 -- ' Since 1=1 is true and the AND is executed before the OR,all rows in the users table are returned!

8. SQL Injection • Applications that insert untrusted data into database queries via string building allows attackers to execute arbitrary queries against back-end databases • New malicious commands are added to application,hence the term "injection" • Injected SQL queries will run under the context of the application account, allowing read and/or write access to application dataand even access to the database schema metadata! • OWASP Top Ten 2010 #1 Risk to Web Applications and Services

9. SQL Injection Impact Allow an attacker to extract data from the database.Exploits can include modifying the record selection criteriaof the SQL query or appending a user-specified query usingthe SQL UNION directive. This type of exploit can alsobe used to bypass poorly designed login mechanisms Data Retrieval Data Modification Allow attacker to write to database tables.Can be used to modify or add records to the database.NOTE: Very dangerous and could result in data corruption! Database-Specific Exploits Involve exploiting database-specific functionality.Can potentially be used to execute arbitrary commandson the database server operating system. (Command Injection)

10. SQL Injection Without a Single Quote (') Attacks can occur even when variables are not encapsulatedwithin single quotes https://www.site.com/customer?AccountNum=12323 sql = "SELECT id, cc, ssn, mmn from users where custnum=" +request.getParameter("AccountNum"); https://www.site.com/customer?AccountNum=1%20or%201%3D1%0A https://www.site.com/customer?AccountNum=1 or 1=1 SELECT id, cc, ssn, mmn from users where custnum=1 or 1=1 Called "Numeric SQL Injection"

11. String Building to Call Stored Procedures • String building can be done when calling stored procedures as well • sql = "GetCustInfo @LastName=" + request.getParameter("LastName"); • Stored Procedure Code • CREATE PROCEDURE GetCustInfo (@LastName VARCHAR(100)) ASexec('SELECT * FROM CUSTOMER WHERE LNAME=''' + @LastName + '''')GO What's the issue here? • If blah' OR '1'='1 is passed in as the LastName value, the entire table will be returned! • Stored procedures need to be implemented safely. • 'Implemented safely' means the stored procedure does not include any unsafe dynamic SQL generation. Wrapped Dynamic SQL

12. Advanced SQLi: Timing Attacks • Timing Attacks Discover database schema details without explicit ODBC/JDBC errors. • SQL Server: waitfor delay http://www.site.com/index.php?id=1'waitfor delay '00:00:10' -- • SQL Server: waitfor delay • declare @del varchar(50);select @del = db_name()if (ascii(substring(@del,1,1))) = 65 waitfor delay '0:0:5'elsewaitfor delay '0:0:1'

13. Code Review: Source and Sink

14. O'Really?

15. X X X X X X X SQLi Defense O O O O O O

16. How Do We StopSQL Injection in Our Code?

17. Defending Against SQL Injection Validation using Known Good Validation should be used for all input Parameterized Queries are extremely resilient to SQL injectionattacks, even in the absence of input validation Parameterized Queries allow for safe construction of bothstandard SQL statements and callable statements(stored procedures) Performs data type checking on parameter values Automatically limits scope of user input. Attacker cannot break out of variablescope (i.e. query plans areprecompiled and cannot befurther manipulated) Make sure to configure your database connections to honor the principle of least privledge!

18. Looks Legit? jim'or'1'!='@manicode.com

19. Even Valid Data Can Cause Injection select id,ssn,cc,mmn from customers where email='$email' $email = jim'or'1'!='@manicode.com select id,ssn,cc,mmn from customers where email='jim' or '1' != '@manicode.com' 1 2 3

20. Why Manual Escaping Will Not Save You from SQLiImagine if all ' were escaped to \' select * from c where name='$name' Attempted attack 1: 'or 1=1-- Defense 1: \'or 1=1-- SQL 1: select * from c where name='\'or 1=1-- ' Attempted attack 2: \'or 1=1-- Defense 2: \\'or 1=1-- SQL 2: select * from c where name='\\'or 1=1-- '

21. Java Prepared Statement

22. HQL Injection Protection Unsafe HQL Statement Query (Hibernate) Safe version of the same query using named parameters

23. .NET Parameterized Query Dynamic SQL (Not so Good) string sql = "SELECT * FROM User WHERE Name = '" + NameTextBox.Text + "' AND Password = '" + PasswordTextBox.Text + "'"; Prepared Statement (Nice! Nice!) SqlConnectionobjConnection = new SqlConnection(_ConnectionString); objConnection.Open(); SqlCommandobjCommand = new SqlCommand( "SELECT * FROM User WHERE Name = @Name AND Password = @Password", objConnection); objCommand.Parameters.Add("@Name", NameTextBox.Text); objCommand.Parameters.Add("@Password", PasswordTextBox.Text); SqlDataReaderobjReader = objCommand.ExecuteReader(); if (objReader.Read()) { ...

24. Query Parameterization (PHP PDO) $stmt = $dbh->prepare("update users set email=:new_emailwhere id=:user_id"); $stmt->bindParam(':new_email', $email); $stmt->bindParam(':user_id', $id);

25. SQL Injection Protection for ASP.NET and RUBY ASP.NET string sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId"; SqlCommand command = new SqlCommand(sql); command.Parameters.Add(new SqlParameter("@CustomerId", System.Data.SqlDbType.Int)); command.Parameters["@CustomerId"].Value = 1; RUBY: Active Record # Create Project.create!(:name => 'owasp') # Read Project.all(:conditions => "name = ?", name) Project.all(:conditions => { :name => name }) Project.where("name = :name", :name => name) # Update project.update_attributes(:name => 'owasp') # Delete Project.delete(:name => 'name')

26. Cold Fusion and Perl Parameterized Queries Cold Fusion <cfquery name = "getFirst" dataSource = "cfsnippets"> SELECT * FROM #strDatabasePrefix#_courses WHERE intCourseID = <cfqueryparam value = #intCourseID# CFSQLType = "CF_SQL_INTEGER"> </cfquery> Perl: DBI my $sql = "INSERT INTO foo (bar, baz) VALUES ( ?, ? )"; my $sth = $dbh->prepare( $sql ); $sth->execute( $bar, $baz );

27. Insecure Stored Procedure (MSSQL) create procedure getUser_USAFE @un varchar(25) as declare @sqlvarchar(max) set @sql = ' select lastname, passbcrypt, age from Users where username= ''' + @un + ''';' exec (@sql) Go

28. Secure Stored Procedure 1 (MSSQL) create procedure getUsers_SAFE @un varchar(25) as select lastname, passbcrypt, age from Users where username = @un; Go

29. Secure Stored Procedure 1 (MSSQL) declare @sqlnvarchar(4000) declare @monthNoint declare @minAmount decimal set @sql = N' select SalesPerson from dbo.SalesData where mon = @monthNo and amount > @minAmount' set @monthNo = 2 set @minAmount = 100 exec sp_executesql @sql, N'@monthNoint, @minAmount decimal', @monthNo, @minAmount http://www.mssqltips.com/sqlservertip/2981/using-parameters-for-sql-server-queries-and-stored-procedures/

30. Secure Stored Procedure 1 (MSSQL) CallableStatement callableStatement = connection.prepareCall( "{call calculateStatistics(?, ?)}"); callableStatement.setString(1, "param1"); callableStatement.setInt (2, 123); http://www.mssqltips.com/sqlservertip/2981/using-parameters-for-sql-server-queries-and-stored-procedures/

31. SQL Injection Defense Strategies Allows database permissions to be restricted to only EXECUTE on stored procedures (permission inheritance) Promotes code re-use (less error prone and easier to maintain) Standard methods of creating stored procedure are safe Caution: Stored Procedures themselves may be injectable! Stored procedures provide several benefits Query Parameterization Needed When creating SQL When calling a a Stored Procedure When building a Stored Procedure

32. WARNING: Some variables cannot be parameterized $dbh->prepare('SELECT name, color, calories FROM ? WHERE calories < ? order by ?'); What is wrong with this picture? What does this imply?

33. Database Configuration

34. Restricting Default Database Permissions Delete or otherwise lock down all default user accounts. (change rights, passwords, etc) Ensure strong/complex passwords and consider forcing mutual-TLS for database connections. Restrict all default access permissions. The database user should either be removed from default roles (i.e. public), or the underlying role permissions should be removed. Disable dangerous/unnecessary functionality within the database server (example/ xp_cmdshell in Microsoft SQL Server).

35. Database Principle of Least Privilege Database accounts used by the application should have the minimal required privileges. If there is a SQLI vuln we may be able to limit the damage that an attacker might do.

36. CAUTION • One SQL Injection can lead to complete data loss. Be rigorous in keeping SQL Injection out of your code. There are several other forms of injection to consider as well. • VERIFY • Code review and static analysis do an exellent job of discovering SQL Injection in your code. • GUIDANCE • http://bobby-tables.com/ • https://www.owasp.org/index.php/Query_Parameterization_Cheat_Sheet • https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

37. LDAP Injection

38. What is LDAP Injection? Lightweight Directory Access Protocol (LDAP) Used for accessing information directories Frequently used in web apps to help users searchfor specific information on the internet Also used for authentication systems

39. LDAP Diagram • dc=com • dc=pisoftware • ou=Group • ou=People • uid=bmarshal • uid=jparker • cn=dev • cn=sysadmin

40. LDAP Injection Technique for exploiting web apps using LDAP statementswithout first properly validating that data Similar techniques involved in SQL injection also apply to LDAP injection Could result in the execution of arbitrary commands such as granting permissionsto unauthorized queries or content modification inside the LDAP tree Can determine how queries are structured by sending logical operators(e.g. OR, AND, |, &, %26) and seeing what errors are returned

41. LDAP Injection Example The following code is responsible to catch input valueand generate a LDAP query that will be used in LDAP database: <input type="text" maxlength="20" name="userName">Insert username</input> Underlying code for the LDAP query: String ldapSearchQuery = "(cn=" + $userName + ")"; System.out.println(ldapSearchQuery); • Variable $username is not validated • Entering "*" may return all usernames in the directory • Entering "eoin) (| (password = *) )" will generate the following code and reveal eoinspassword: ( cn = eoin) ( | (password = * ) )

42. Testing for LDAP Interaction Data input validation of all client-supplied data! Use known good validation with a regular expression Only allow letters and numbers (or just numbers) ^[0-9a-zA-Z]*$ If other characters are needed, convert them to HTML substitutes (&quote, & gt) Outgoing data validation Access control to the data in the LDAP directory

43. Anatomy of LDAP Injection Attack • LDAP Injection is an attack used to exploit web and webservice applications that construct LDAP statements based on untrusted input. • When an application fails to properly sanitize user input, it’s possible to modify LDAP statements through techniques similar to SQL Injection.. • LDAP injection attacks could result in the granting of permissions to unauthorized queries and content modification inside the LDAP tree. • Escaping any variable added to any LDAP query is the proper solution to stopping LDAP injection. • Different forms of escaping are needing – one for LDAP searching and one for LDAP distinguished names.

44. LDAP Search Escaping publicStringencodeForLDAP(Stringinput) { if( input == null ) {returnnull;} StringBuildersb = newStringBuilder(); for (inti = 0; i < input.length(); i++) { charc = input.charAt(i); switch (c) { case '\\':sb.append("\\5c");break; case '*':sb.append("\\2a");break; case '(':sb.append("\\28");break; case ')':sb.append("\\29");break; case '\0':sb.append("\\00");break; default:sb.append(c); } } returnsb.toString(); } reference: https://github.com/ESAPI/esapi-java-legacy/blob/develop/src/main/java/org/owasp/esapi/reference/DefaultEncoder.java

45. LDAP Distinguished Name Escaping public String encodeForDN(String input) { if( input == null ) { return null;} StringBuildersb = new StringBuilder(); if ((input.length() > 0) && ((input.charAt(0) == ' ') || (input.charAt(0) == '#'))) { sb.append('\\'); // add the leading backslash if needed } for (inti = 0; i < input.length(); i++) { char c = input.charAt(i); switch (c) { case '\\':sb.append("\\\\");break; case ',':sb.append("\\,");break; case '+':sb.append("\\+");break; case '"':sb.append("\\\"");break; case '<':sb.append("\\<");break; case '>':sb.append("\\>");break; case ';':sb.append("\\;");break; default: sb.append(c); } } // add the trailing backslash if needed if ((input.length() > 1) && (input.charAt(input.length() - 1) == ' ')) { sb.insert(sb.length() - 1, '\\'); } return sb.toString(); } reference: https://github.com/ESAPI/esapi-java-legacy/blob/develop/src/main/java/org/owasp/esapi/reference/DefaultEncoder.java

46. Other Injection

47. File and OS Command Injection

48. Arbitrary File Upload • Uploading malicious files to web-accessible directories can be used to compromise the underlying operating system and/or application • Malicious binaries to executable web-accessible directories (ie. /cgi-bin/) • Malicious scripts to web-accessible directories with script mappings(can be any or all directories) • Overwriting sensitive system files(/etc/passwd, /etc/shadow) • Uploading large files to the web server can be used to launch a denial-of-service attack by filling web server drives (disk flooding)

49. File Path Traversal Attacks • Calling other files via input parameters can expose the web serverto unauthorized file access Malicious binaries toexecutable web-accessible directories (ie. /cgi-bin/) • /default.jsp?page=about.jsp • /default.jsp?page=../../../../etc/passwd • Compound this issue with excessiveapp permissions: • /default.jsp?page=../../../../etc/shadow • /default.jsp/get-file?file=/etc/passwd • /default.jsp/page?page=http://other-site.com.br/other-page.htm/malicius-code.php • http://vulnerable-page.org?viewtext=../upload.php Okay NOT OKAY OH NO! OH NO! OH NO! OH NO!

50. Object Lookup Maps and Access Control htttp://manico.net/readfile/fileid?=1237