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:
- Escaping quotes prevents SQL injection
- Database developer security is only required for Microsoft SQL Server
- Malicious users must know table names to conduct SQL injection attacks
- Functions that require authentication do not require protection
Myth 1: Quoting Quotes Prevents SQL Injection
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 blocklist 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 allowlisting approach – checking inputs against a finite set of expected data formats and patterns.
Using more comprehensive techniques, such as allowlist input validation and using prepared statements, can effectively reduce the risk of SQL injection attacks.
Myth 2: Database Attacks Only Apply to Microsoft SQL Server
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.
Myth 3: You Need to Know Table Names for SQL Injection
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:
- SELECT * FROM UsedCars WHERE Year = <Year>
- Malicious Input #1: 2009; shutdown;
- Resulting SQL Statement Executed By Database: SELECT * FROM UsedCars WHERE Year= 2009; shutdown;
Myth 4: Functions that Require Authentication Do Not Require Protection
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.