SQL injection – attack and defence
SQL injections occur when commands are smuggled into an SQL database. If a web application does not check user input sufficiently thoroughly, any database on any operating system is vulnerable.
Web shops, news boards, groupware and content management systems all use databases to save customer data, articles, and texts. Often, visitors to such sites face a combination of a Windows 2000 server, IIS, an MS SQL server and Active Server Pages, or, Linux together with Apache, mySQL, and PHP or Perl. In web forms, users can enter information to place orders or register as customers. The web application uses this data to create dynamic database queries. If the application does not sufficiently check the input, special strings can be smuggled in. If attackers input their data carefully, they can transfer their own parameters and commands to the database and access the content or even the system.
To write data into or read data from an SQL database, an application communicates with the database via SQL (Structured Query Language). Generally, it transfers a complete string that it puts together from commands and user input. The vocabulary of SQL commands is now quite large because there are so many different SQL database solutions, some of which support their own proprietary functions. But all of them understand the most basic commands such as SELECT, UPDATE, DELETE, INSERT, DROP, and WHERE. SQL databases can contain multiple databases, each with several tables, themselves consisting of multiple columns.
The SQL command
SELECT * FROM customer WHERE card = 'visa'
returns all the data records in the table customer that have the value visa in the column card. If the constant string visa is replaced by the variable $card, various strings are possible in connection with user input:
SELECT * FROM customer WHERE card = '$card'
As long as values such as visa, amex, or master are in this variable, the database reacts as expected. But if a malicious user enters the string ';DROP TABLE customer--, the application sends the following to the database:
SELECT * FROM customer WHERE card = '';DROP TABLE customer --'
The database sees two commands because the semicolon represents a separator:
SELECT * FROM customer WHERE card = ''
shows all of the data records that have an empty column card. The database then executes the second command that completely deletes the table customer.
DROP TABLE customer--'
The two hyphens mark the beginning of a comment, which is why the last apostrophe (quote) is ignored instead of causing an error. In this example, the attacker can wipe out entire tables from hard drives without prior authentication provided the web application has the required access rights. When a database user is created, the database administrator has to allow for deletions by means of DROP. The basic problem with SQL injections is the lack of filtering for quotes in input. A number of applications create an SQL command from string elements, which have to be delimited by apostrophes. In Java, that can look like this:
String sql = new String("SELECT * FROM customers WHERE card= '" + request.getParameter("cardname")"'")
Java expands the input cardname and adds it to the string. Any quotation marks in cardname remain intact but now appear in a different context. Other commands can also be added to the original command.