Page Comparison

A1 Injection

...

1.1. Safe Java Prepared Statement ExampleThe following code example uses a PreparedStatement, Java's implementation of a parameterized query, to execute the same database query.

 String custname = request.getParameter("customerName"); // This should REALLY be validated too
// perform input validation to detect attacks
String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, custname);
ResultSet results = pstmt.executeQuery( );

h5. 1.2. Hibernate Query Language (HQL) Prepared Statement (Named Parameters) Examples

 First is an unsafe HQL Statement

 Query unsafeHQLQuery = session.createQuery("from Inventory where productID='"userSuppliedParameter"'");

...

Defense Option 3: White List Input Validation

Various parts of SQL queries aren't legal locations for the use of bind variables, such as the names of tables or columns, and the sort order indicator (ASC or DESC). In such situations, input validation or query redesign is the most appropriate defense. For the names of tables or columns, ideally those values come from the code, and not from user parameters. But if user parameter values are used to make different for table names and column names, then the parameter values should be mapped to the legal/expected table or column names to make sure unvalidated user input doesn't end up in the query. Please note, this is a symptom of poor design and a full re-write should be considered if time allows. Here is an example of table name validation.

 String tableName;
switch(PARAM):
case "Value1": tableName = "fooTable";
break;
case "Value2": tableName = "barTable";
break;
...
default      : throw new InputValidationException("unexpected value provided for table name");

The tableName can then be directly appended to the SQL query since it is now known to be one of the legal and expected values for a table name in this query. Keep in mind that generic table validation functions can lead to data loss as table names are used in queries where they are not expected.

For something simple like a sort order, it would be best if the user supplied input is converted to a boolean, and then that boolean is used to select the safe value to append to the query. This is a very standard need in dynamic query creation. For example:

 public String someMethod(boolean sortOrder) {

 String SQLquery = "some SQL ... order by Salary " + (sortOrder ? "ASC" : "DESC");
 ...

Any time user input can be converted to a non-String, like a date, numeric, boolean, enumerated type, etc. before it is appended to a query, or used to select a value to append to the query, this ensures it is safe to do so.

Input validation is also recommended as a secondary defense in ALL cases, even when using bind variables as is discussed later in this article. More techniques on how to implement strong white list input validation is described in the Input Validation Cheat Sheet.

Defense Option 4: Escaping All User Supplied Input

...

String principal = "cn=" + getParameter("username") + ", ou=Users, o=example";
String password = getParameter("password");
env.put(Context.SECURITY_AUTHENTICATION, "simple");
env.put(Context.SECURITY_PRINCIPAL, principal);
env.put(Context.SECURITY_CREDENTIALS, password);
// Create the initial context
DirContext ctx = new InitialDirContext(env);


//Instead, implement code for LDAP as follows: 
 
//if the username contains LDAP specials, stop now
if ( containsLDAPspecials(getParameter("username")) ) {
    throw new javax.naming.AuthenticationException();
}
String principal = "cn=" + getParameter("username") + ", ou=Users, o=example";
String password = getParameter("password");
env.put(Context.SECURITY_AUTHENTICATION, "simple");
env.put(Context.SECURITY_PRINCIPAL, principal);
env.put(Context.SECURITY_CREDENTIALS, password);
// Create the initial context
DirContext ctx = new InitialDirContext(env);

...