Preventing SQL Injection attacks

Preventing SQL Injection attacks

Technical professionals responsible for the security of publicly accessible Internet servers often focus (justifiably) on maintaining the operating system and server software that they purchase from vendors. Indeed, these packages often contain significant security vulnerabilities and it is incumbent upon every security administrator to ensure that their servers are patched with the most recent vendor security patches and hotfixes.

However, that's not the extent of the administrator's responsibilities. As you probably know, the custom code developed within your organization to power dynamic Web site functionality has the potential to open significant vulnerabilities in your Web server. These flaws are especially dangerous when you're using Web applications to provide an interface to a back-end database. One particular type of attack against database-driven applications is the SQL injection. In this attack, the intruder manipulates a site's Web-based interfaces to force the database to execute undesirable SQL code.

    Requires Free Membership to View

    Login

    When you register, you'll receive targeted emails designed to keep you informed of the most relevant information on Agile development, application security, testing & QA, software requirements, and more.

    Hannah Smalltree, Editorial Director

    By submitting your registration information to SearchSoftwareQuality.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSoftwareQuality.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Eliminating SQL injection
SQL injection: Secure your Web applications 

Ways to automate SQL injection testing 

OWASP Guide to Building Secure Web Applications: Interpreter Injection

SQL injection attacks are best learned through example. Imagine a simple Web site set up by a package delivery company to provide delivery status information to anyone who knows the tracking number associated with a particular package. The application may simply ask the user for the tracking number and then look it up in a database table using the following SQL code:

SELECT * FROM Shipments WHERE TrackingID='@tracking'

Where @tracking is a variable passed in from the Web application. Under normal circumstances, this application may function perfectly normally. For example, if a user enters the tracking number 1A2123ZC2, the corresponding query would be:

SELECT * FROM Shipments WHERE TrackingID='1A2123ZC2'

That ideal situation makes one flawed assumption -- that the user will only enter a valid tracking number. Malicious individuals are not likely to be so cooperative. Suppose that the user instead enters the string shown below in the tracking number field:

1A2123ZC2' or true The corresponding query will now be: SELECT * FROM Shipments WHERE TrackingID='1A2123ZC2' or true

Which will have the unintended consequence of retrieving all of the tracking information stored in the database. Now assume that we have an even more malicious user who enters the following string:

1A2123ZC2'; DELETE FROM Shipments This would cause the database to execute the following query: SELECT * FROM Shipments WHERE TrackingID='1A2123ZC2'; DELETE FROM Shipments

Which would have the clearly undesirable result of deleting all of the tracking information from the database!

There are several steps that you can take to reduce the possibility of a SQL injection attack against your database:

  • Escape single quotation marks. Include code within your Web applications that replaces single apostrophes with double apostrophes. This will force the database server to recognize the apostrophe as a literal character rather than a string delimiter.

  • Limit the privileges available to the account that executes Web application code. In the example above, if the account only had permission to perform the intended action (retrieving records from the Shipping table), the deletion would not be possible.

  • Reduce or eliminate debugging information. When an error condition occurs on your server, the Web user should not see technical details of the error. This type of information could aid an intruder seeking to explore the structure of your database.

  • Educate your developers. Make sure that the people responsible for developing code within your organization are aware of the seriousness of the threat and the simple steps that they may take to help safeguard your servers.

  • Test your Web applications. Spot-check the work done by your developers. One simple check that you can do is to place single quotation marks within the data sent to your server. If you receive an error response of any kind, chances are you're vulnerable to an SQL injection attack.

    If you take the time to implement these simple steps, you'll be well on your way to securing your Web/database interaction.

    About the Author
    Mike Chapple, CISSP is an IT Security Professional with the University of Notre Dame. He previously served as an information security researcher with the National Security Agency and the U.S. Air Force. Mike is the author of several information security titles including the CISSP Prep Guide and Information Security Illuminated.

    This tip originally appeared on SearchSecurity.com

    This was first published in December 2005

    • Join the conversationComment

    Share
    Comments

      Results

      Contribute to the conversation

      All fields are required. Comments will appear at the bottom of the article.

      Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

      Back to top