OWASP Top Ten Project 2013 No 1 – Injection

Jens Kleinschmidt Blog, Security

To avoid SQL injection vulnerabilities only prepared statements with parameter binding should be used:

String custname = request.getParameter("customerName");
String query = "SELECT account_balance FROM user_data WHERE user_name = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, custname);
ResultSet results = pstmt.executeQuery();

Intershop Specifics – ORM Layer

The best way to avoid SQL injection vulnerabilities are to use the CAPI interfaces created by the ORM layer and to avoid the execution of getObjectsBySQLWhere()/getObjectsBySQLJoin(). This can be achieved by defining alternate object keys and relations in the EDL as shown in the following example:


namespace com.intershop.component.customer.orm.internal.^orm
{
  orm class CustomerPO extends ExtensibleObjectPO table "CUSTOMER"
  {
    alternate key (customerNo, domainID);

    attribute customerNo: string<256> required searchable;
    attribute customerTypeID: string<256> required;
    attribute approvalStatus: int;
    attribute profileID: uuid required;

    relation customerBasicProfileAssignments: CustomerBasicProfileAssignmentPO[0..n] inverse customer;
    relation customerCompanyProfile: CompanyProfilePO[0..1] inverse companyCustomer;

    dependency customerBasicProfile: BasicProfilePO
    {
      foreign key(profileID);
    }
  }
}

In this example the DomainId and CustomerNo form an alternate (semantic) key for the CustomerPO object. With the corresponding object factory it is possible to look up with plain java, just as getting the related company profile:

...
final CustomerPOAlternateKey customerKey = new CustomerPOAlternateKey(customerNo, currentDomain);
final CustomerPO customer = customerPOFactory.getObjectByAlternateKey(customerKey);

final CompanyProfilePO companyProfile = customer.getCustomerCompanyProfile();
…

In case this is not sufficient and still the getObjectsBySQLWhere()/getObjectsBySQLJoin() need to be used, the object factory’s API with parameter binding should be used:

Collection foos = fooFactory.getObjectsBySQLWhere("name=?", new Object[]{"bla"});
Collection bars = barFactory.getObjectsBySQLJoin("Foo f", "this.uuid=f.barID and this.name=?", new Object[]{"bla"});

Intershop Specifics – Query Framework

When using the file base query framework from Intershop, make sure only the default bind parameter processing is used for parameter assignment:

<template-variable value="ProductTable" />

or

<template-variable value="ProductTable" processing="bind"/>

But do not use text as processing type:

<template-variable value="ProductTable" processing="text"/>

For more details please see the reference to the query file syntax Reference – Query File Syntax.

Co-Authors: Thomas Bergmann, Nils Breitmann and Intershop Consulting Stuttgart

OWASP Top Ten Project 2013 No 1 – Injection
Tagged on: