Other Datastore Attacks

SQL injection is by far the most interesting attack that can be performed against a datastore, but it's not the only one. Other attacks might take advantage of inadequate security policies in a catalog or table. After all, if you can access someone else's personal profile by changing a URL parameter from 655321 to 24601, then there's no need to inject malicious characters or try an alternate syntax.

One of the biggest challenges with applications that rely on database access is how to securely store the credentials. On many platforms, the credentials are stored in a text file that is outside the web document root. Yet in some cases the credentials may be hard-coded in an application source file within the web document root. In this latter case, the confidentiality of the username and password relies on preventing unauthorized access to the source code.

Countermeasures

 Countermeasure    An application's database contains important information about the application and its users. It's important that countermeasures address the types of attacks that can be performed against a database as well as minimize the impact of a compromise in case a particular defense proves inadequate.

Input Validation

Filtering user -supplied data is probably the most repeated countermeasure for web applications. Proper input validation not only protects the application from SQL injection, but from other parameter manipulation attacks as well. Input validation of values destined for a database can be tricky. For example, it has been demonstrated how dangerous a single quote character can be, but then how do you handle a name like O'Berry or any sentence that contains a contraction?

Validation routines for values bound for a database are not much different from filters for other values. Here are some things to keep in mind:

• Escape characters. Characters such as the single quote (apostrophe) have a specific meaning in SQL queries. Unless you're using prepared statements or parameterized queries 100 percent of the time, make sure to escape such characters (for example, \') to prevent them from disrupting the query. Always do this if you rely on string concatenation to create queries.

• Deny characters. You can strip characters that you know to be malicious or that are inappropriate for the expected data. For example, an e-mail address only contains a specific subset of punctuation characters; they don't need the parentheses, for example.

• Use appropriate data types. Whenever possible, assign integer values to integer data types and so on for all of the user-supplied data. An attacker might still produce an error, but the error will occur when assigning a parameter's value and not within the database.

Decouple Query Logic From Query Data

Input validation can be helpful, but it doesn't address the fundamental problem with SQL injection: Use query data to modify query logic. Most databases and programming languages provide functions that enable the developer to statically define the logic of a query and drop data into the appropriate location(s). This is accomplished in the programming language with bound parameters or parameterized queries . The same methods are available in the database via stored procedures or user-defined functions .

Bound Parameters

The major benefit of using bound parameters (also referred to as parameterized queries) is that you need not worry about escaping special characters or worrying that some character will change the query's logic. While it may still be possible to generate an error by inserting invalid characters, it won't be possible to use single quotes to rewrite an arbitrary query. This security comes at a price, because the query must be initially built (prepared) and then populated with parameter values. There will be a performance impact, but whether it is a serious one depends on the application's architecture; in reality, the security benefits will largely outweigh any performance hit. On the other hand, parameterized statements can actually improve performance for queries that are executed multiple times.

The following example demonstrates bound parameters in a JDBC connection (the name variable contains the user-supplied data):

 String  query  = "SELECT * FROM table WHERE something=?"; PreparedStatement  stmt  =  connection  .prepareStatement(  query  );  stmt  .setString(1,  name  ); ResultSet  rs  =  stmt  .executeQuery(); 

Java uses question marks as a parameter value's placeholder in the query. The setString() method is used to bind a value to a placeholder. In the previous example, the name variable was bound to the first (and only) placeholder. Java has additional methods that cover several possible data types, including integers, NULLs, and timestamps. Use the one most appropriate for the data being manipulated.

The .NET platform offers bound statements, but does so in a manner that uses variable references rather than incremental placeholders, as shown in this partial C# example:

 Statement  stmt  =  connection  .CreateCommand();  stmt  .CommandText = "SELECT * FROM table WHERE something=@name";  stmt  .Prepare(); SqlParameter  name;   name  =  stmt  .Parameters.Add("@name", DbType.String);  name  .value = <  value taken from POST data>;   stmt  .Execute(); 

As with JDBC, you can assign specific data types beyond the DbType.String to a parameter.

Table 7-17 lists information for parameterized query objects and functions for several languages.

Table 7-17: Language Constructs for Creating Stored Procedures

Platform	Description
ADO.NET	Statement object Prepare, Parameters.Add methods
Java	PreparedStatement object setFoo methods (setString, SetBoolean, etc.)
Perl DBI module	Prepare, bind_param methods
PHP Data Objects (PDO)	PDO object Prepare, bindParam methods
PHP mysqli	mysqli_prepare() mysqli_stmt_bind_param() Available as an object-oriented or procedural style.
Python MySQLdb	MySQLdb object Execute method (can use placeholders and variable assignment)

The advantages of bound parameters should be evident in the way they are created. They provide several useful features:

• Avoid the use of insecure string concatenation, which could otherwise lead to easy exploitation.

• Do not require special handling of SQL syntax characters such as the single quote.

• Provide strong data type assignment.

Stored Procedures

Stored procedures represent predefined queries that are stored in the database. A stored procedure accepts input arguments and returns data based on statements defined in the procedure. Stored procedures can contain complex statements with many conditional steps and, as with parameterized statements, their query logic remains static regardless of the query data. Thus, stored procedures cannot be manipulated by user-supplied data that contains characters like single quotes, semicolons, or comment delimiters.

The syntax for stored procedures may vary slightly among databases, but the syntax is also defined in the SQL 2003 standard. Here's a very simple example of a stored procedure that checks for a combination of a specific username and password hash and returns the number of matches:

 CREATE PROCEDRE sp_FooBar(IN user VARCHAR(80), IN passwd CHAR(32),  OUT i INT) BEGIN SELECT COUNT(id) INTO i FROM UserTable  WHERE UserName=user AND Password=passwd; END 

The real benefit of stored procedures comes with more complex queries than this example, but it illustrates the basic syntax.

Tip 

Remember that using string concatenation to build stored procedures can still lead to SQL injection vulnerabilities. The security of stored procedures is based on how parameters are passed to the procedure reverting to string concatenation with unfiltered user input defeats this!

Not all databases provide full support for SQL 2003 stored procedures. PostgreSQL and the MySQL 4.x series are notable exceptions. Use bound parameters for these databases.

In addition to providing a more secure method of running queries, stored procedures also provide performance benefits because the query logic is precompiled and security benefits of role-based access to procedures.

Database Encryption

Many databases provide native functions to encrypt tables and rows of information. Table-level encryption protects the data if database files can be directly accessed. Row-level encryption can protect information so that only the data's owner can decrypt it. For example, one column may represent the user's ID number and remaining columns contain the user's personal information (social security number, bank account information, credit card number, etc.). If all but the ID column are encrypted (the user ID is necessary to perform queries) by a key that is specific to that user, then SQL injection exploits will have a more difficult time of accessing the decrypted data. An exploit that tries to use a simple SELECT or UNION statement against a different user ID will only return encrypted data.

Of course, neither table- nor row-level encryption is a perfect countermeasure. An account compromised with a user's stolen or guessed password is still vulnerable. Database encryption will only mitigate unauthorized activity (SQL injection attacks) by a user; it cannot block authorized activity (logging in, viewing a profile page, performing approved transactions, etc.) by an unauthorized user (someone with a stolen account).

Database Configuration

Finally, comprehensive database security is incomplete without a secure configuration for the database installation and its catalogs. There are many checklists written for the most popular database systems. Instead of repeating them for each database version, here is a summary of their major points:

• Separate accounts for database administration and account access

• Accounts restricted to only application- related tables

• Use of read-only accounts, where possible

• Removal of high-risk stored procedures and extended functionality

• Current patch level