There was a young man who knew SQL ¹

And who thought that he had no equal

But then there was she

With her apostrophe

And her password which was ';DROP TABLE;²

An "SQL Injection" is a technique used to hack into a database by using a web-based data entry form to alter a database query. It can be used to gain access to user accounts, administrative accounts, or to cause damage to the data itself.

Background

SQL stands for "Structured Query Language" and is used to send queries or commands to a database, either to retrieve data for display on a web site, or to enter data as when changing a password.

Typically, a web site will present a form for a someone to fill out. This may be nothing more than a simple "sign in" page or a search box. Whatever the person types into the the web form is sent to the database as part of the SQL query.

A simple query has three parts: a verb, such as "SELECT" or "UPDATE"³, a reference to the table and fields where the data is stored, and a "WHERE" clause that defines exactly which data to act on, all ending with a semicolon. For example, a query to sign in to a web site may look something like this:

SELECT firstname, lastname FROM login.userdata WHERE username='cat' AND password='meow';


The words in between the single-quote marks (the apostrophes) represent the text that would have been typed in the username and password boxes of the web site's "sign in" page.

SQL can also perform simple calculations, comparisons, or do practically nothing at all, for instance:

SELECT ( 8 * 6 );


would return a result of "48", while

SELECT ('So long and thanks for all the fish');


would merely echo back "So long and thanks for all the fish".

The Problem

If someone should type a word or phrase containing an apostrophe (or in some cases a double-quotation mark) into a web form, the apostrophe becomes part of the "WHERE" clause and truncates the SQL query. Continuing with the example above, if instead of the username "cat", we had the username "Don't Panic", the WHERE clause would end up looking like this:

WHERE username='Don'


The "t" and everything following from the "Don't" ends up outside the search term, becoming part of the SQL query itself. In this example it would generate a harmless error, because "t Panic" is not a valid SQL term.

This becomes a serious issue when a hacker types an apostrophe followed by a valid SQL phrase, thereby "injecting" a new SQL query into the system, and changing the outcome.

Examples

In its simplest form, a hacker can inject a second SQL command to display additional information about the database, using the "SHOW" and "DESC" verbs. Instead of the harmless "Don't Panic", the term typed into the username or search box would begin with the apostrophe and a semicolon, followed by a second complete SQL query, for example " '; SHOW TABLES;" The apostrophe and semicolon would immediately end the first query, and the second query would potentially generate a list of all the data tables. The hacker could then use that information to create increasingly specific queries, until he or she extracts passwords, credit card numbers, or other personal information. Using other verbs such as "UPDATE", "DELETE" or (as in the above limerick) "DROP" could result in the the hacker changing data or entirely destroying the web site.

Often the goal of the hacker is to obtain undetected access to the system, so instead of generating a second query they attempt to alter the original query in a way that tricks it into returning a "successful" login. Many sign-in systems do a simple check - if what the person types in the username and password boxes matches what is stored in the database, the person is allowed access to the system. There are a couple of common methods to check if the passwords match:

1. Allow the database to do the check, this is illustrated in the first SQL example above - if "cat" and "meow" are found in the database, the database would indicate "success" and return the requested data, and the person would have access to the system. To defeat this, the hacker merely changes the SQL to check the password "OR" something that is always true, such as '1=1'. With the "OR" clause, only one of the conditions needs to be successful, so the database happily indicates "success" and the hacker now has access to the system.
   
2. 
   Request the password from the database, using only the username in the "WHERE" clause, then use a programming language to check if it matches the typed-in password. In this case the hacker uses SQL's ability to do nothing but echo back what is typed; he or she injects a "SELECT ('fakepassword')" into the query. The database will echo back the fake password and the hacker naturally uses the same fake password on the web form, resulting in a match.
   

Prevention

Stopping a SQL Injection is simply a matter of the web developer correctly and consistently dealing with the apostrophes and quotation marks. There are three commonly used techniques.

1. 
   Use stored procedures. Instead of writing SQL queries directly into the web application, the queries are stored in the database itself as "procedures" that can be called as necessary. When using this technique, it is important to understand how your database software handles the information passed to the procedures, since an incorrectly written procedure may still be vulnerable to a SQL Injection.
   
2. 
   Prepared Statements. With a prepared statement, the SQL remains in the web application programming, but the query is constructed by "binding" variables to the query instead of using apostrophes or quotation marks. Again it is important to understand how the programming language itself behaves, but the languages most commonly used on the web, such as PHP, automatically protect against SQL Injection when using prepared statements.
   
3. 
   In cases where you cannot use stored procedures or prepared statements, always "escape" any text that has been typed into a web form before using it in a SQL statement. Depending on the database that you are using, escaping involves placing another character before the apostrophe or quotation mark. For a MySQL database, use a backslash "\", for MSSQL use another apostrophe. Programming languages often provide a function to do this for you. The escaped apostrophes are never interpreted as starting or ending a quotation, they remain a part of the quotation.
   

In all cases, you want to avoid constructing a SQL query by concatenating strings, and where that is unavoidable always use an escape function to alter the apostrophes. It also helps to check any text entered through a web page before using it in a web application, and apply reasonable constraints (for instance, if a username cannot be longer that 10 characters, then check that it is no more than 10 characters before using it in your SQL). Finally, check the database output before you send it to your web page, to ensure that it reasonably matches what you expect to get from the database - if you write SQL that asks for a single bit of information but get several rows of data in the result, it's a sure sign that something is wrong.