SQL Injection 101: Common Defense Methods Hackers Should Be Aware Of

Database technology has vastly improved the way we handle vast amounts of data, and almost every modern application utilizes it in one way or another. But the widespread use of databases naturally invites a slew of vulnerabilities and attacks to occur. SQL injection has been around for awhile, and as such, there are many defense methods in place to safeguard against these types of attacks.

• Previously: How to Avoid Detection & Bypass Defenses During SQL Injections

User Input Escaping

Often, the first line of defense put in place by developers is user input escaping. We've seen how certain characters like string terminators (') and comment markers (--) can be abused when specific input fields allow them. If all user-supplied input is correctly escaped before the SQL query is even run against the database, injection flaws are unlikely to occur in most instances.

The character escaping scheme varies depending on the type of database system in use. For example, MySQL has two main modes of escaping user input: the first which replaces each single quote with two single quotes, and a second that uses a backslash on special characters like so:

ESCAPE SEQUENCE         CHARACTER REPRESENTED BY SEQUENCE
---------------         ---------------------------------
\0                      An ASCII NULL (X'00') character
\'                      A single quote (') character
\''                     A double quote ('') character
\b                      A backspace character
\n                      A newline (linefeed) character
\r                      A carriage return character
\t                      A tab character
\z                      ASCII 26 (Control+Z)
\\                      A backslash (\) character
\%                      A % character
\_                      An underscore (_) character

However, as we demonstrated in the previous tutorial on avoiding detection and bypassing defenses, there are certain techniques that can be used to circumvent this type of security. In order to decrease the probability of these types of attacks being successful, one must employ more secure methods to safeguard against SQL injection.

Whitelist Input Validation

A common mistake that's made when validating user input is attempting to deny, or blacklist, individual characters and expressions from being submitted to the database. This is a faulty approach as it is trivial for a determined attacker to get around these defenses. A more suitable approach is to use whitelisting as input validation.

Whitelist input validation defines precisely what is allowed to be submitted to the database. Accordingly, anything that is not explicitly specified is not authorized by default. Consistently structured data like dates, phone numbers, addresses, and email addresses can all be easily validated based on specific patterns, often utilizing regular expressions.

Whitelisting can be an effective deterrent of SQL injection, especially when used alongside other methods of defense, but it still isn't bulletproof. One of the significant drawbacks is that all of the possible allowed data must be known and accounted for when performing validation. This is particularly difficult in dynamic environments or when codebases merge and diverge. It all comes down to the separation of code and data, and more standardized validation techniques should be used when possible.

Stored Procedures

Stored procedures are batches of SQL statements stored as objects in the database system that are later called upon to be executed. These are particularly useful for developers as it makes it easier to centralize and standardize sets of queries that are commonly run — think of them as similar to functions or methods used in other programming languages. Benefits of using stored procedures include proper encapsulation of data, internal error handling, reduction of network traffic, and the concept of write once, read many.

When stored procedures are properly implemented, they are generally considered quite safe to use. However, in certain situations, such as when SQL code needs to be dynamically generated inside of a stored procedure, vulnerabilities can arise. If this absolutely cannot be avoided, it is imperative that other input validation or escaping is employed in tandem.

In some cases, the use of stored procedures can be a detriment to security rather than a benefit. Whereas many SQL queries operate with only read and write permissions, stored procedures require execute rights. Therefore, it is theoretically possible for an attacker to fully own a compromised database server if stored procedures were in use.

Prepared Statements

The best way to prevent SQL injection is through the use of prepared statements. Prepared statements use parameterized queries to define all the SQL code ahead of time, then pass each parameter to the query later on. This ensures the integrity of the statement, making it extremely difficult for an attacker to alter the code. Think of prepared statements as similar to variables in other programming languages; the variable is set and later passed to a function when it is called.

Prepared statements are arguably the best method to accurately distinguish between code and data used in SQL statements. For the most part, practically every programming language used to build web applications allows for the use of parameterized queries. Here is an example of what this could look like in PHP and Java:

PHP EXAMPLE
-----------
$stmt = $dbh->prepare("UPDATE (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

JAVA EXAMPLE
------------
String username = request.getParameter("userName");
String query = "SELECT address FROM user_info WHERE id = ? ";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, username);
ResultSet results = pstmt.executeQuery();

Least Privilege

Like a lot of other situations encountered in penetration testing and hacking, owning a system often comes down to one thing: privilege. The principle of least privilege dictates that every component of a system, whether that be a user, program, or process, must only be granted access to the minimal amount of resources and information that is required to complete its purpose. This means that ordinary users are not given any kind of special access, like superuser or administrative permissions. It also means that applications and processes running in an environment only have access to what they need to perform their function viably.

This principle applies to database systems to minimize the amount of damage if a SQL injection attack is successfully performed. It's easy for DBAs and developers to run everything with full administrative rights as often everything just works better that way, but doing so can open up huge security holes for attackers to take advantage of. If an account only needs read access to certain tables, it's important to restrict access in that way. Likewise, if an account just needs access to specific parts of a table, consider using views to limit what that account can see. It's also a good idea to have separate database users for different web apps — doing this reduces the risk of one compromised account accessing data used for other web applications.

Wrapping Everything Up

This series on SQL injection has covered the basics of databases and SQL, reconnaissance techniques, advanced attacks, signature evasion, and defense methods commonly used in the industry. It is important for you, as a white hat, to completely understand a technology from the bottom up, and now that we've covered SQL injection in depth, you can go forth and truly be a master of this craft.

• Follow Null Byte on Twitter, Flipboard, and YouTube
• Follow WonderHowTo on Facebook, Twitter, Pinterest, and Flipboard

Cover image by skeeze/Pixabay; Screenshots by drd_/Null Byte