DOCX

Quick Security Reference - SQL Injection

By Sam Spencer,2014-12-24 15:14
6 views 0
Quick Security Reference

    Quick Security Reference: SQL Injection

Updated November 5, 2010

    http://www.microsoft.com/sdl. For the latest information, please see

    This document is provided “as-is.” Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it. Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred.

    This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.

     2010 Microsoft Corporation. All rights reserved. ?

    Licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported

    Quick Security Reference: SQL Injection

Table of Contents

    OVERVIEW ................................................................................................................................. 3 WHAT ARE PERSONAS ............................................................................................................ 3 Business Decision Maker ........................................................................................................................ 4 Architect .................................................................................................................................................. 4 Developer ................................................................................................................................................ 4 Tester/QA ................................................................................................................................................ 4 UNDERSTANDING SQL INJECTION FOR THE BUSINESS DECISION MAKER ....................... 4 Risk ......................................................................................................................................................... 4 Business Impact ...................................................................................................................................... 5 Fixing the Code ....................................................................................................................................... 6 Resources and Training for Business Decision Makers .......................................................................... 7 UNDERSTANDING SQL INJECTION FOR THE ARCHITECT/PM .............................................. 7 Identifying the Problem ........................................................................................................................... 7 Common SQL Injection Attacks .............................................................................................................. 7 Designing a Fix ....................................................................................................................................... 7 Tools for Designing Software That Prevents SQL Injection Vulnerabilities ............................................. 9 Resources and Training for Architects/PMs ............................................................................................ 9 UNDERSTANDING SQL INJECTION ATTACKS FOR THE DEVELOPER ................................. 9 Example of SQL Injection 9 Example of a SQL Injection by Truncation Attack 10 Writing Secure Code ............................................................................................................................. 12 Constrain Input 12 Use Parameterized SQL Queries 13 Use Proper Escaping Techniques to Handle Special Input Characters 14 Calculate Buffer Lengths Properly 15 Additional Considerations ..................................................................................................................... 16 Use a Least-Privileged Database Account 16 Avoid Disclosing Detailed Error Information 16 Tools and Libraries 17

    Quick Security Reference: SQL Injection 1

    Resources and Training for Developers ................................................................................................ 17 UNDERSTANDING SQL INJECTION VULNERABILITIES FOR THE TESTER/QA .................. 17 Map Out the Site and Its Functionality 17 Start Testing and Pay Attention to the Output 18 Techniques for Finding Various Types of SQL Injection Vulnerabilities ................................................ 18 Code Reviews 18 Building Improvements into Black Box Testing 19 Tools You Can Use ............................................................................................................................... 19 Resources and Training for Testers ...................................................................................................... 19 THE MICROSOFT SDL AND PREVENTING SQL INJECTION ATTACKS ................................ 20 Long-Term Solutions ............................................................................................................................. 20 CONCLUSION .......................................................................................................................... 21 ACKNOWLEDGMENTS ............................................................................................................ 21

    Quick Security Reference: SQL Injection 2

OVERVIEW

    SQL injection attacks have become one of the most common and dangerous Web application security issues on the Internet. SQL injection vulnerabilities occur when an application takes user content data and

    uses it to construct SQL (Structured Query Language) statements without first properly validating or sanitizing that content. SQL injection attacks take advantage of SQL injection vulnerabilities to steal

    sensitive data from the database, modify or destroy the stolen data, execute administrative commands on the database, or in some cases take control of the whole machine. In recent years, SQL injection attacks have been used to store malware in databases and then distribute them through Web sites that are hosted on these compromised databases.

    SQL injection attacks can occur using a variety of techniques. The diagram below describes a simple attack to drop a SQL database table from a Web page that accepts user input from a text box.

Figure 1. Example of a basic SQL injection attack

    This Quick Security Reference (QSR) paper can be used by each member of your engineering organization to gain a more thorough understanding of how to address SQL injection attacks. By better understanding these vulnerabilities, you can more easily and efficiently deal with existing issues and implement ongoing solutions that better protect your software, Web sites, and users.

    WHAT ARE PERSONAS

    Every person from the business decision maker, to the architect, to the developer, and tester/quality assurance (QA) must play a role in addressing this issue. However, each person must approach the problem from a different perspective to ensure that you have a clear plan for fixing the SQL injection issue, successfully verifying your solution, and mitigating the problem in the future.

    Quick Security Reference: SQL Injection 3

This paper uses four basic personas in an engineering organizationbusiness decision maker, architect,

    developer, and tester/QA. The goal of this paper is to address the key questions that each persona might ask regarding a SQL injection attack and to provide direction for each persona on how to address the issue. The personas are defined in the following paragraphs.

    Business Decision Maker

    ; In charge of delivering the development project from start to finish, on time, and within budget.

    ; Aware of the fact that security is a customer-perception and cost-management issue.

    ; Needs to understand security issues at a high level to make overall project planning decisions,

    including budget and justification of time and resource investments toward security development. Architect

    ; In charge of the technical design of the development project.

    ; Cares about security from an overall integrity standpoint.

    ; Understands security concepts in enough detail to avoid creating a blueprint that would result in

    security issues.

    ; Responsible for creating the core principles that developers use to implement the functional goals

    of the project, managing awareness of security issues as they relate to the specific solution, and

    tracking and verifying fixes to potentially costly security mistakes in code leading up to release. Developer

    ; In charge of taking the architectural blueprints for the development project and implementing

    them in code.

    ; Fixes security issues from coding errors.

    ; Understands basic secure coding best practices in detail and gathers supporting technical content,

    such as approved libraries, to ease the ability to write secure code correctly and consistently. Tester/QA

    ; In charge of acting as the quality gatekeeper and managing the last step before a development

    project is completed.

    ; Ensures that any security issues that may have slipped through previous steps are caught before

    the application is released.

    ; Executes security testing (different than functional testing) to attempt to make the application do

    things that it was not designed to do. If successful, these tests may represent potential

    vulnerabilities.

    ; Maintains a detailed understanding of attack techniques and a bit of the creative “attacker‟s

    mindset.”

    UNDERSTANDING SQL INJECTION FOR THE BUSINESS DECISION MAKER

    Risk

    SQL injection attacks have become the favorite way to attack a database and steal sensitive information. They have gained popularity and effectiveness because this type of attack can effectively compromise data layers despite the existence of firewalls and intrusion detection systems. According to Verizon

    Quick Security Reference: SQL Injection 4

Business, in 2008 SQL injection attacks ranked first in the number of records compromised79 percent of 1the aggregate 285 million.

    Figure 2. Types of hacking by number of breaches (black) and percent of records (red) According to breach.com, SQL injection attacks were the most common type of security incident resulting 2in security breaches in 2008.

    Figure 3. Breakdown of security breaches for 2008 by type of security incident

    In recent years, attackers have been using SQL injection attacks as a way to distribute malware onto SQL servers. The risk of SQL injection attacks today is much greater than a few years ago because the probability of an attacker exploiting a SQL injection vulnerability is more likely. Business Impact

    There have been several recent high-profile SQL injection attacks that had a direct effect on the day-to-day operations, customer-facing solutions, and business practices of companies.

1 Adapted from http://www.verizonbusiness.com/resources/security/reports/2009_databreach_rp.pdf.

    2 Adapted from http://www.breach.com/resources/whitepapers/downloads/WP_WebHackingIncidents_2008.pdf.

    Quick Security Reference: SQL Injection 5

Some high-profile SQL injection attacks include:

    ; Federal authorities have charged a previously indicted hacker with breaching additional

    corporate computers and stealing data for at least 130 million credit and debit cards, the biggest

    identity theft case ever prosecuted in the United States. Albert "Segvec" Gonzalez and two

    unnamed Russians were indicted on Monday for attacks that hit credit card processor Heartland

    Payment Systems, retailers 7-Eleven and Hannaford Brothers, and two unidentified companies.

    Documents filed in US District Court in Newark, New Jersey claim that ... the trio used SQL

    injection attacks to install sniffer software on the companies' servers to intercept credit card data

    as it was being processed. ... The breach has proved to be a major embarrassment for Heartland,

    which ... has so far allocated $12.6m to cover costs stemming from the loss of sensitive card-3holder data.”

    ; In what could be the largest data security breach to date, MasterCard International on Friday said

    information on more than 40 million credit cards may have been stolen. The breach occurred at

    CardSystems Solutions in Tucson, Ariz., a third-party processor of payment data, according to a 4MasterCard statement.

    ; In September 2004, a hacker exploited the failures set forth by using an SQL injection attack on

    respondent‟s web application and website to install common hacking programs on computers on

    respondent‟s computer network. The programs were set up to collect and transmit magnetic

    stripe data stored on the network to computers located outside the network every four days,

    beginning in November 2004. As a result, the hacker obtained unauthorized access to magnetic 5stripe data for tens of millions of credit and debit cards.

    ; Malware found on an internal database may have allowed spammers to steal names, addresses,

    phone numbers, and email addresses from as many as 6.3 million customers of TD Ameritrade, 6the brokerage firm revealed today. Carl Banzhof, vice president and chief technology evangelist

    at McAfee, told SCMagazineUS.com today that the cyber thieves likely used SQL injection tactics 7to infiltrate the database, harvesting email addresses.

    Fixing the Code

    SQL injection attacks are most effectively addressed in the design phase of your software life cycle by looking for ways to prevent them based on your solution and architectural design. Most commonly, SQL injection vulnerabilities are a result of coding vulnerabilities during the Implementation/Development phase and will likely require code changes by the same team who authored and are most familiar with the code. Ongoing security education of your developers should be a critical piece of your life cycle to equip them with the knowledge needed to effectively identify, address, and avoid these vulnerabilities in their code. Additionally, SQL injection attacks should be used in the Verification/QA/Test phase. Using SQL injection vulnerabilities will help you exercise your Web application to ensure mitigations and protections

3 Adapted from http://www.pcworld.com/article/170457/getting_serious_about_sql_injection_and_the_tjx_hacker.html.

    4 Adapted from http://news.cnet.com/Credit-card-breach-exposes-40-million-accounts/2100-1029_3-5751886.html.

    5 Adapted from http://www.ftc.gov/os/caselist/0523148/0523148complaint.pdf.

    6 Adapted from http://www.darkreading.com/security/perimeter/showArticle.jhtml?articleID=208804735.

    7 Adapted from http://www.scmagazineus.com/Phishing-scams-may-target-Ameritrade-breach-victims/article/35699/?DCMP=EMC-SCUS_Newswire.

    Quick Security Reference: SQL Injection 6

    are in place. The QA/Test team should be the most effective team at uncovering SQL injection vulnerabilities prior to releasing a solution.

    Resources and Training for Business Decision Makers

    ; Web Hacking Incidents Database 2008

    ; SQL Injection topic in SQL Books Online

    ; 19 Deadly Sins of Software Security by Michael Howard, David LeBlanc, and John Viega

    UNDERSTANDING SQL INJECTION FOR THE ARCHITECT/PM

    Identifying the Problem

    SQL injection vulnerabilities are generally categorized into three different typesfirst order, second order,

    and truncation vulnerabilities.

    ; First-order SQL injection vulnerabilities are the easiest to exploit. In this type of exploit, a page

    constructs SQL statements based on the attacker-supplied data. These are the most common

    types of attacks.

    ; In second-order SQL injection attacks, an attacker submits hostile data through a Web page that

    stores it in a file, database, or other data store, and depends on some other Web page or mid-tier

    application to use that hostile data in the construction of a SQL statement.

    ; In SQL injections by truncation, the attacker uses varying buffer lengths to truncate a delimited

    string and uses another input value to inject his commands of choice into a dynamically

    constructed SQL statement.

    Common SQL Injection Attacks

    The common SQL injection attack exploits vulnerabilities found in Web pages. The attack is executed by injecting SQL statements as part of data input to Web pages. A vulnerable Web page sends these SQL statements to a database server, which in turn executes them. These SQL statements are executed under the context of the account that the Web page uses to connect to Microsoft SQL Server?. There are two common exploit payloads types that attackers use:

     1.Use UNION SELECT statement to steal sensitive information from the database server.

     2.Batch multiple SQL commands either to tamper with data, destroy data, or execute SQL

    commands of their choice on the database server.

    Designing a Fix

    The Microsoft Security Development Lifecycle (SDL) requires all Web applications (and any type of application that executes SQL statements on SQL Server) to implement a clearly defined set of defenses against SQL injection attacks. Whether you have adopted the SDL in your development process or not, the following defenses against SQL injection attacks are vital to consider when designing your Web application:

    ; All input to the application from a user, a component, or another program should be

    validated. This helps to ensure that the input is free from characters that cause SQL injection

    attacks. It not only helps to protect against SQL injection attacks but also other attacks, such as

    XSS attacks and buffer overflows.

    Quick Security Reference: SQL Injection 7

; Use parameterized SQL queries. Applications accessing a database must do so using only

    parameterized queries. Creating dynamic queries using string concatenation potentially allows an

    attacker to execute an arbitrary query through the application.

    ; Use stored procedures. Using stored procedures helps to mitigate the SQL injection threat to a

    great extent because type checking is available for parameters. If the attacker supplies input that

    does not match the type constraints, the stored procedures throw an exception. In the vast

    majority of the cases, this should be properly handled within the application. However, if the

    stored procedures perform string manipulation in the code and then execute that query using the

    "exec@sql" construct, incorrect handling of user input can produce the same SQL injection

    vulnerability as would be seen at the application layer.

    Note: Stored procedures by themselves do not remove SQL injection vulnerabilities. They only

    raise the bar on the attacker by hiding much of the underlying database schema. ; Use SQL execute-only permission. This is a defense-in-depth method. This defense assumes the

    attacker has successfully found a SQL injection bug in your code. Now what? Thankfully, this

    defense stops almost every attack dead in its tracks. From the SDL, “Only grant „execute'

    permission on all stored procedures, and grant that permission only for the application domain

    group. Ensure that this group is granted execute permissions only on your stored procedures. Do

    not grant any other permission on your database to any other user or group. This is a great

    defense because if the attacker attempts to access any other database object other than through

    a stored procedure (you can use views also), the underlying database permissions model prevents

    the attack by denying the attacker access. In general, database applications should be using a

    low-privileged account that has the minimum permissions required to execute the statements

    submitted to SQL Server. One should never use high-privileged accounts like dbo or sysadmin. If

    high-privileged operations need to be performed, then wrap those operations in a stored

    procedure and sign that stored procedure with a certificate that has the required high privileges

    and grant execute permission on the stored procedure.

    When implemented, the first SQL injection requirement ensures that an application is secure by design,

    and thus protected from SQL injection attacks. The other two requirements provide security by default 8defenses to mitigate other attacks if existing protections were to fail.

    There are few additional requirements that one must follow:

    ; Use proper escaping for SQL identifiers and Data Definition Language (DDL) statements.

    There may be times when SQL identifiers (table names, column names, and so on) might be

    constructed dynamically, or there may be some DDL statements for which one cannot use

    parameterized SQL statements. In these situations, one should use proper escaping techniques to

    mitigate SQL injection attacks.

    ; Handle buffer lengths properly. If dynamic SQL statements are being constructed for the type

    of scenarios mentioned earlier, buffer lengths need to be handled properly to prevent SQL

    injections through truncation. This topic is discussed at length in the MSDN article New SQL

    Truncation Attacks and How to Avoid Them.

    8 Adapted from Michael Howard’s blog: http://blogs.msdn.com/sdl/archive/2008/05/15/giving-sql-injection-the-respect-it-

    deserves.aspx.

    Quick Security Reference: SQL Injection 8

Report this document

For any questions or suggestions please email
cust-service@docsford.com