sql injection attackss3-eu-west-1.amazonaws.com/colinmackay.co.uk/... · · 2011-06-28sql...

colinmackay.co.uk @ColinMackay

SQL Injection Attacks

& Some Tips On How To Prevent Them

SQL Server User Group

Edinburgh 29th June 2011


Code Examples

• All the code examples can be found on my blog:

colinmackay.co.uk/blog/category/ sql-injection-attack-talk/


What is a SQL Injection Attack?

• Manipulative user input

– Tricks application

• First order

– Immediate activation of payload

• Second order

– Delayed activation of payload


A warning from history

• Vendor spent $83K on “firewall products”

• App connected to SQL Server as “sa”

• Attack retrieved results via email

• Was also able to shut down the SQL Server

http://blog.colinmackay.net/archive/2007/06/24/75.aspx

http://blog.colinmackay.net/archive/2007/06/24/75.aspx


Securing your database

• Multifaceted approach

– Firewall settings are not enough!

• Application interaction

– Code that communicates with the DB

• Database protection

– Setting security on the database

"Security is a process, not a product." -- Bruce Schneier, Secrets and Lies.


An example

SELECT ProductName, QuantityPerUnit, UnitPrice FROM Products WHERE ProductName LIKE 'G%'

string sql = "SELECT ProductName, QuantityPerUnit, UnitPrice "+ "FROM Products " + "WHERE ProductName LIKE '"+this.search.Text+"%'; SqlDataAdapter da = new SqlDataAdapter(sql, DbCommand); da.Fill(productDataSet);


An example

' UNION SELECT TABLE_NAME, 1, 1 FROM INFORMATION_SCHEMA.TABLES;--

SELECT ProductName, QuantityPerUnit, UnitPrice FROM Products WHERE ProductName LIKE '' UNION SELECT TABLE_NAME, 1, 1 FROM INFORMATION_SCHEMA.TABLES;--%'

string sql = "SELECT ProductName, QuantityPerUnit, UnitPrice "+ "FROM Products " + "WHERE ProductName LIKE '"+this.search.Text+"%'; SqlDataAdapter da = new SqlDataAdapter(sql, DbCommand); da.Fill(productDataSet);


DEMO

• Example web application

– ASP.NET MVC

• But could be any framework – Web / Windows / Services / WPF / Silverlight / etc.

• Or OS – Windows / Linux / iOS / MacOS / Android / etc.

– SQL Server 2008

• But could be any RDBMS really – Oracle / MySQL / etc.


Prevention Tips

• Lock down access to xp (eXternal Procedures)

• Run SQL service under least privilege

– So, if the xp is run it can’t do to much damage

• Minimised possibility of damage beyond SQL Server


Escaping key characters

• e.g. ' becomes ''

– User types O'Brien SQL is O''Brien

• Don’t trust myself to always remember

• Can be differences between databases

• Can be easy to get it wrong


Prevention Tip

• Use parameterised queries

– Separates the data from the command

• Cuts out most of the ability to attack

– Nothing being “injected” any more

• Not a complete defence!

– Good first step tho’


Second Order Attacks

• Malicious user hides payload

– Possibly in a trusted area

• Payload is then detonated later

• More difficult to detect


Demo

• Second Order Attack

– User creates a “favourite”

– Favourite contains the payload

– Payload activated later


Prevention Tip

• Trust NOTHING!

– Well, almost nothing

http://xkcd.com/327/

http://xkcd.com/327/


Stored Procedures

• Provide an extra level of security

– Revoke access to tables, grant to sprocs

• Allow programmatic checks

– Data validation


Prevention Tip

• Don’t connect as a user in the sysadmin role

– e.g. The sa user

• You know who else connected as the sa user?

Godwin Approved


Stored Procedure Caveat

• Can still access sprocs and be vulnerable SqlCommand cmd =

new SqlCommand("EXECUTE myStoredProc '" + myTextBox.Text + "'");

• It could create its own injection scenario


Demo

• Second order attack using Stored Procedure


Dynamic SQL in sproc

• Sometimes it IS necessary

– Not often, tho’

– Dynamic tables or columns

– SQL Server 2000 requires it for varying SELECT TOP values

• Can use sp_executesql

– Built in Stored Proc

– Allows parameters to be passed

http://blog.colinmackay.net/archive/2009/09/21/If-you-really-must-do-dynamic-SQLhellip.aspx















Dynamic SQL in sproc

• Validate inputs

– INFORMATION_SCHEMA.TABLES

– INFORMATION_SCHEMA.COLUMNS

• Use QUOTENAME to escape names properly
















sp_executesql Example

CREATE PROCEDURE GetData @Id INT, @TableName sysname, @ColumnName sysname AS BEGIN SET NOCOUNT ON; IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName) BEGIN DECLARE @sql nvarchar(max) = 'SELECT ' + QUOTENAME(@ColumnName) + ' FROM ' + QUOTENAME(@TableName) + ' WHERE Id = @Identifier'; EXEC sp_executesql @sql, N'@Identifier int', @Identifier = @Id END END GO


O/RM

• Abstracts away the persistence layer

• Will generate correct (safe) SQL

• Still a need to understand database

– And the implications of actions on the DB

– Need to shape the access to the tables


Error Messages

• Hide error messages from the user

– They can contain details about your system


Error Messages

• Log the errors

– NLog

– Elmah

– log4net

• Display a friendly message to the user


SQL Injection Attacks

Question Time

colinmackay.co.uk/blog/category/sql-injection-attack-talk/

sql injection attackss3-eu-west-1.amazonaws.com/colinmackay.co.uk/... · · 2011-06-28sql...

Documents