Welcome to our Secure Development Tips blog

Every other week, we provide expert tech tips on how to build and deploy secure applications.  These best practices, derived from Security Innovation’s assessments of the worlds’ most dominant software applications,  are taken directly from our TeamMentor product, which includes more than 3,500 guidance assets and articles on secure software design, coding and testing.    

Recent Security Innovation Blog Post:

Subscribe by Email

Your email:

Secure Development Tips

a blog with tips relating to secure application development, from Security Innovation's eknowledge database, TeamMentor

Current Articles | RSS Feed RSS Feed

Use Parameterized APIs for Data Access

  
  

Applies to

Applications written using Servlets or JSP that interact with a database.

What to Do

Use Prepared Statements to execute SQL statements. Do not generate SQL statements directly from user input.

Why

Parameterized Queries mitigate the number one risk for database-facing Web applications: SQL injection. This powerful attack is language independent, as it depends on the algorithm used to generate SQL strings passed to the database and affects the database engine, not the actual application.

It exploits the creation of the SQL string, in which user input is embedded manually with escape sequences (such as the single quote (') or double quote (") characters). Using this method, an attacker inserts valid SQL statement fragments as part of user input so they, in turn, are embedded within the SQL query that is submitted to the database engine for execution. Successful exploitation allows an attacker to potentially compromise the entire database and often the underlying operating system.

Parameterized Queries offer a simple yet efficient mitigation since they automatically contain user-input in a non-escaped form, "compiling" the query and passing the input as parameters, much like a function call. There is no known method of injecting custom SQL over Parameterized Queries.

Additionally, using Parameterized queries offers two significant advantages:

  • Greater Efficiency: The database engine needs to "compile" the statement only once, allowing for greater efficiency in query invocation.
  • Greater Security: The database engine automatically handles the parsing and embedding of user input, effectively negating any potential for SQL injection due to attackers taking advantage of delimiter characters such as a single quote (') or a double quote (").

When

Type-safe SQL parameters should be used whenever SQL statements need to be executed by the database.

How

One way of making the application less susceptible to SQL injection attacks is by using stored procedures. Stored procedures are subroutines stored in the database that are available to applications for accessing the data.

Just using stored procedures isn't a complete remedy for SQL injection. The important thing to do is use parameters with stored procedures, or else your stored procedures can be susceptible to SQL injection if they use unfiltered input. Stored procedures provide several security advantages:

  • By parameterizing input parameters and type-enforcing them, user input is effectively filtered.
  • Stored procedures are allowed by most databases to execute under different security privileges than from the database user, thereby restricting the ability of the application to do anything beyond the actions specified in the stored procedures. For instance, an application would have execute access to a stored procedure but no access to the base tables.

To use type-safe SQL parameters, follow these steps:

  1. If possible, use stored procedures. Stored procedures are pre-compiled statements that are stored on the database server. Using stored procedures improves your application's performance and security.
  2. Identify the SQL queries.Locate all SQL queries throughout your application. Example:
Connection cn = MyApp.getDBConnection();
Statement st = cn.createStatement();
String query = "select login_attempts from myappUsers where user = \"" + user
             + "\" and pass = \"" + new String(passDigest) + "\";";
ResultSet rs = st.executeQuery(query);
  1. Identify the parameters in each SQL query. After locating all SQL queries, identify the parameters in each query. Understand the format and type of each parameter's data.
  2. Use PreparedStatement.Java supports parameterized queries via its PreparedStatementclass. PreparedStatement is mapped by most databases to a parameterized query or a stored procedure. Although the implementation may differ among databases, the functionality is the same. Turn all SQL queries into parameterized queries. To accomplish this, turn all Statementobjects intoPreparedStatement objects. For example, the SQL query from Step 1 would be:
Connection cn = MyApp.getDBConnection();
PreparedStatement st = cn.prepareStatement("select login_attempts from myappUsers"
                     + " where user = ? and pass = ?;");
st.setString(1, user);
st.setString(2, new String(passDigest));
ResultSet rs = st.executeQuery();

For more information, consult the documentation for Class PreparedStatement in the Java SDK.

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)
{
      // Hash the credentials before querying the credential store
      // The application crafts the SQL query based on user's input
      Connection cn = MyApp.getDBConnection();
      Statement st = cn.createStatement();
      String query = "select login_attempts from myappUsers where user = \""
                    + user + "\" and pass = \"" + passHash + "\";";
      ResultSet rs = st.executeQuery(query);
      // Execute the rest of the authentication steps
}

Solution Example

The following code validates the user based on 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 usesPreparedStatement, 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)
{
      // Hash the credentials before querying the credential store
      // The application uses parameterized queries
      Connection cn = MyApp.getDBConnection();
      PreparedStatement st = cn.prepareStatement("select user from myappUsers"
                            + " where user = ? and pass = ?;");
      st.setString(1, user);
      st.setString(2, passHash);
      ResultSet rs = st.executeQuery();
      // Execute the rest of the authentication steps
}

Comments

Currently, there are no comments. Be the first to post one!
Post Comment
Name
 *
Email
 *
Website (optional)
Comment
 *

Allowed tags: <a> link, <b> bold, <i> italics