What to Do

Validate all input before it is passed to the database.

Why

Unvalidated input can lead to persistent cross-site scripting, SQL injection, and other vulnerabilities which would allow an attacker to gain unauthorized access to sensitive data.

When

If your application uses a backend database server.

How

To properly validate input passed to database:

1. Identify application inputs. Potential sources of input include:

   • URL based parameters
   • Form based parameters
   • Hidden fields
   • Cookies
   • Local filesystem
   • Javascript variables

2. Identify database entry points. Find all the locations in the system where data is pushed into the database, either directly via queries, or indirectly (via log entries, etc). Trace all database entry points to find all the paths which can contain external input.

3. Create a set of validators. Build a set of validators that work with the data in question and fit into the system architecture, including your general validation scheme for other data and the way the system interacts with the database. Using the Validate Input for Length, Range, Format, and Type guideline, determine the appropriate validation at each validator. Centralize the validators as it helps strengthen your code by limiting the amount of scattered validation code throughout the system, and allows for easy auditing. Place the validators along the database input paths, ideally as close to the initial data input as possible. All data should pass through the validators before breing transmitted to the database servers.

4. Use Type-Safe parameters in SQL statements. Stored procedures and Parameterized queries improve your application's robustness against SQL injection based attacks. Consult the guideline Use Type Safe SQL Parameters When Constructing SQL Queries.

5. Audit your entry paths. Before going into production, have a reviewer who was not responsible for the development of the code in question check all the input paths to ensure that the validators are correctly implemented, that they are correctly applied, and that all input paths are checked.

Problem Example

The following code validates the user based on a given username and password. It hashes the password with a random salt. Then it compares the username and the produced hash against the data stored in the backend database. Unfortunately, the SQL query is dynamically crafted by using the user's input. If there is no proper input validation, an attacker can access the application's database server through the use of SQL injection.

public boolean validateUser(String user, char[] pass)
{
      byte[] salt = MyApp.generateSalt();
      String passHash;
      SqlConnection cn;
      SqlCommand sqlCommand;
      String query;
      passHash = hashCredentials(pass, salt);
      // The application crafts the SQL query based on user's input
      cn = MyApp.getDBConnection();
      sqlCommand = new SqlCommand("select user from myappUsers where user = \""

      + user + "\" and pass = \"" + passHash + "\" and login_attempts < 3;", cn);
      SqlDataReader reader = sqlCommand.ExecuteReader();
      updateLastLoginAttempt(user);
      ...
}

If a user submits a login request with a username of 'foo\" or 1 = 1; drop table *; --', the system will happily first authenticate the user and then drop all the tables in the database.

Solution Example

The following code validates the user based on a given username and password. It hashes the password with a random salt. Then it compares the username and the produced hash against the data stored in the backend database. Because the application uses the Parameters property, it is much more difficult for an attacker to gain access to the application's database server through the use of SQL injection.

public boolean validateUser(String user, char[] pass)
{
   byte[] salt = MyApp.generateSalt();
   String passHash; 
   SqlConnection cn;
   SqlCommand sqlCommand;
   String query;     
   passHash = hashCredentials(pass, salt);
   // The application crafts the SQL query based on user's input
   cn = MyApp.getDBConnection();
   sqlCommand = new SqlCommand("select user from myappUsers where user = @user and 
                    + pass = @passHash and login_attempts < 3;", cn);
   sqlCommand.Parameters.Add("@user", SqlDbType.VarChar, user.Length).Value = user;
   sqlCommand.Parameters.Add("@passHash ", SqlDbType.VarChar, passHash.Length).Value = passHash;
   SqlDataReader reader = sqlCommand.ExecuteReader(); 
   updateLastLoginAttempt(user);  
   ...
}

Even if the user passes in an arbitrary string for a username or a password, they will not be able to alter the query or otherwise effect the database. An even better solution would involve using a stored procedure instead of a parameterized command, because that would permit the database to be locked down further.

Adapted from Microsoft patterns & practices guidance.