As we are all well aware, data is everywhere. Every organization generates and stores data and unfortunately too many bad apples are willing to exploit application weaknesses. A very popular technique used by hackers of all hats to compromise data confidentiality and integrity is the SQL injection attack. “In terms of the attack methods used by hackers, SQL injection remains the number one exploit, accounting for nearly one-fifth of all vulnerabilities used by hackers” (Howarth, 2010). Don’t believe the hype? Visit the SQL Injection Hall of Fame.
Not everyone is a DBA or a security expert but if you care about data, you need to have a basic understanding of how this attack can be used to potentially compromise your web exposed data. In 2009 infamous hacker Albert Gonzalez was indicted by grand juries in Massachusetts and New York for stealing data from companies such as Dave & Buster’s Holdings, TJ Maxx, BJ’s Wholesale Club, OfficeMax, Barnes & Noble and The Sports Authority by using SQL injection attacks. All of these attacks were enabled due to poorly coded web application software (Vijayan, 2009). He masterminded “the combined credit card theft and subsequent reselling of more than 170 million card and ATM numbers from 2005 through 2007—the biggest such fraud in history” (Wikipedia, Albert Gonzalez). As an aside, Mr. Gonzalez is serving 20 years in prison for his crimes.
In short, a SQL injection is a malicious hacking method used to compromise the security of a SQL database. Invalid parameters are entered into a user input field on a website and that user input is submitted to a web application database server for execution. A successful exploit will allow the hacker to remotely shell into the server and take control or simply obtain sensitive information from a hacked SQL SELECT statement. The exploiter may be able to further exploit SQL commands and escalate privileges to read, modify or even delete information at will.
A popular method to test the vulnerability of a site is to place a single quote character, ‘, into the query string of a URL (Krutz, R. L. & Vines, R. D., 2008). The desired response is to see an error message that contains an ODBC (Open Database Connectivity) reference. ODBC is a standard database access protocol used to interact with applications regardless of the underlying database management system. Krutz et. al (2008) offer the example of typical ODBC error message:
Microsoft OLE DB Provider for ODBC Drivers error ‘80040e14’
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
keyword ‘and’. /wasc.asp, line 68
An error message like this contains a wealth of information that an ill-intentioned hacker can use to exploit an insecure system. It would be in the best interests of a secure application to return a custom generic error response. Furthermore, it is not necessary to be an experienced hacker to take advantage of this exploit; there are automated SQL injection tools available that can make carrying out this attack fairly simple for someone with a script kiddie level of understanding.
There are ways to protect against SQL injection attacks; the most obvious way is to apply input validation. Rejecting unreasonably long inputs may prevent exploitation of a buffer overflow scenario. Programmers due to the extra work involved, sometimes skip validation steps, however the extra safety margin may be worth the cost. Encrypting the database contents and limiting privileges on those accounts which execute user input queries is also ideal (Daswani, N., Kern, C., & Kesavan, A., 2007)
From a SQL Server perspective, here are a few best practice tips shared from Microsoft TechNet to consider for input validation:
- You should review all code that calls EXECUTE, EXEC, or sp_executesql
- Test the size and data type of input and enforce appropriate limits. This can help prevent deliberate buffer overruns.
- Test the content of string variables and accept only expected values. Reject entries that contain binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.
- Never build Transact-SQL statements directly from user input.
- Use stored procedures to validate user input.
- In multitiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected and an error should be returned to the previous tier.
- Implement multiple layers of validation. Validate input in the user interface and at all subsequent points where it crosses a trust boundary. For example, data validation in a client-side application can prevent simple script injection. However, if the next tier assumes that its input has already been validated, any malicious user who can bypass a client can have unrestricted access to a system.
- Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.
Albert Gonzalez. In Wikipedia. http://en.wikipedia.org/wiki/Albert_Gonzalez
Howarth.F. (2010). Emerging Hacker Attacks. Faulkner Information Services.
Krutz, R. L. & Vines, R. D., ( © 2008). The CEH Prep Guide: The Comprehensive Guide to Certified Ethical Hacking.
Microsoft TechNet. SQL Injection. https://technet.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx
Vijayan, J. (2009). “U.S. says SQL injection caused major breaches.” Computerworld, 43(26), 4-4.