A discussion of common tactics used by developers to prevent SQL injection attacks on their systems/databases, and some misconceptions around these practices.
There are a set of best practices that can be used to build security into applications that use databases to send, retrieve, and store data including appropriate input validation and the use of prepared statements. You can also increase database security by suppressing database error messages, reducing the attack surface of the database application, using the least privilege principle to deploy databases, and employing defense in depth – not to mention keeping the database system up‐to‐date with software patches.
However, there a number of common assumptions or mistaken practices that should be debunked because they promote a false sense of security and leave database systems open to attacks. These include:
The technique of “quoting quotes” (also called “escaping quotes”) is commonly used by developers to prevent SQL injection attacks. This technique works by replacing each single quote with two single quotes. The resulting SQL statement has an imbalance of single quote characters and is rejected by the database engine, thereby preventing SQL injection attacks. This works most of the time, as long as the un-trusted input uses single quotes.
When/if you use this technique, be sure to understand its limitations and that it is not a cure-all for SQL injection attacks. It uses the error-prone blacklist input approach, which checks inputs against a set of known attack inputs or patterns. With this approach, you need to list all possible ‘bad’ inputs—however, the set of all bad inputs is infinite, so it is impossible to block them all. This is why I always recommend using the whitelisting approach – checking inputs against a finite set of expected data formats and patterns.
Using more comprehensive techniques, such as white list input validation and using prepared statements, can effectively reduce the risk of SQL injection attacks.
Although Microsoft is not exempt from security problems, this is simply not true. SQL injection is a threat to any database that uses SQL, including non-Microsoft databases such as MySQL, Oracle, and SQLite. For evidence that “database attacks only apply to Microsoft SQL Server” is a myth, open a browser to www.osvdb.org. The Open Source Vulnerability Database (OSVDB) website provides an online searchable record of known vulnerabilities. A quick search for vulnerabilities associated with Oracle, for example, shows that security should be a concern for all database developers, regardless of the platform they are using.
Here is an example of a SQL injection attack that does not require knowledge of the table name. In this example, the malicious user inserts a shutdown command, which on certain databases causes the entire database service to shut down.
Example:
A common myth is that if a database function requires authentication and can be accessed only by trusted users, you need not worry about protecting it. However, this does not take into account that an attacker may gain access to valid user credentials or take over a valid user session; or that a flaw in the application may allow an untrusted third party to interact with the database.
