SQL Injection: Examples & How to Defend Against It
SQL injection is one of the most common and dangerous security vulnerabilities facing Web sites and applications today. But what is it about SQL injection that’s so dangerous, and is there any way to properly defend against it?
SQL Injection is a form of code injection used primarily against database-driven Web applications. The vast majority of Web applications in use today are comprised of either 3-tier or n-tier application architecture, but what they all have in common is the use of a database server located at the backend from which the logic or application tier opens a connection to and passes an SQL script for it to execute. SQL is the query language used to retrieve from and insert information into the majority of commercially-used databases. SQL injection, then, is an attempt by the attacker to manipulate the query that is sent to the database server by inserting code into presentation-tier input fields, such as username and password text fields representing a login screen, or into HTTP requests via the browser, which can result in the database server executing unintended commands or retrieving private data.
To put it succinctly; SQL injection is an attempt to manipulate the application so that input data is parsed as code rather than treated as data.
SQL injection is an ongoing security issue for all database driven Web applications, and indeed some of the most damaging attacks in history have been as a result of SQL injection, such as the 2009 attack by hackers on Heartland Payment Systems in which 130 million credit card numbers were compromised. The Open Web Application Security Project, a non-profit global organisation designed to provide standards for keeping Web applications safe from attackers, has ranked Injection (including SQL injection) as its number one security risk for Web applications in 2013.
There are a number of scenarios in which applications that use a back-end database may be vulnerable to SQL injection attack. The first, and most easily exploited, of these, is when the application does not properly handle escape characters, such as the single quote symbol ( ‘ ). This is because SQL databases interpret the escape symbol (usually the quote character) as the demarcation between the data and the code. If an attacker can trick an SQL query into unintentionally escaping data, they can append code to the query. A simple example below shows PHP server-side source code to store a dynamic SQL query that does not handle escape symbols and thus is vulnerable to SQL injection.
$SQL = “SELECT UserID FROM users WHERE username = ‘$_GET[“username”]’;”;
The user at the client side could potentially cause an injection attack if they were to enter the below line into the ‘username’ text field corresponding to the variable retrieved from $_GET[‘username’]:
‘ DROP TABLE Users --
The resulting SQL query to be run on the database then becomes:
SELECT UserID FROM users WHERE username = '' DROP TABLE users --';
The user in the above example has commented out the final quote to avoid an SQL exception. The above query gives the user unintended access to the database and (if the permissions allow it) allow the user to delete entire tables from the database, obviously a completely unintended outcome from the application developer’s perspective.
Another vulnerability to SQL injection comes when the application sends SQL exceptions or server errors as responses directly to the client. This allows the user to test through trial and error for vulnerabilities in the application; for example, by guessing field names of tables through injected queries. If the application shows server error responses to the user, they can determine whether a particular field name exists or whether the application is vulnerable to escape characters. For this reason, Web applications should always show a generic, non-specific error page or message when an input returns an error.
Many Internet security bodies and professionals recommend SQL Query parameterisation as a key defence mechanism to combat SQL injection attacks.
Parameterised statements are those in which user input is not embedded directly into the SQL statement, but rather assigned to parameters or placeholders which are only ever parsed as a value of the given type of the table field. For example, in the above example, the user input ‘ DROP TABLE Users – would be assigned to a String parameter and matched against the UserID field. This would return no results, unless of course there were actually a user in the database called ‘ DROP TABLE Users –.
All major development platforms support their own version of parameterised statements.
Article by Tom Sprudzans