View source | Discuss this page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Add page
Show collection (0 pages)
Collections help

Search

HQL SQL Injection Prevention


Contents

Introduction

SQL/HQL injection is a code injection technique which provides an attacker with DB data that should not be revealed otherwise. It is based on SQL/HQL statements that are inserted into an entry field in the UI which is not filtered nor cleaned afterwards. These statements end up concatenated in the queries. As a result of this type of attack, identity spoofing, data alteration, complete disclosure of all data (personal or business related) or even gain full access to an administrator user. In general, SQL/HQL Injection is considered to have a high impact severity.

Some of the main consequences are:

Steps of an SQL/HQL injection attack

Warning.png   For teaching purposes in the following scenarios no input validation is taking place. This is very insecure and it must be avoided!

The attacks based on SQL/HQL injection can be separated into two main steps:

How applications are vulnerable to HQL/SQL injection?

Injections of this kind might occur in every query (dynamic or not) that uses user's input without previous sanitization or validation. That's why it's very important to always consider the user's direct inputs as something potentially dangerous to be especially treated before building an HQL/SQL query. In the following sections we will see methods to prevent the injection.

How can the injection be effectively prevented?

Bulbgraph.png   The best way to prevent SQL/HQL injection is by using Parameterized Queries or Prepared Statements. This method is considered absolutely safe (unless there is a bug in the JDBC connector).

The main idea of the effort to elude this kind of attack is that direct concatenation of user's input must be avoided.

HQL using parameterized queries

Inserting parameters in WHERE clause

This is the most typical scenario for a developer, where we need to use parameters in the where clause. Regardless of whether the parameters come from the user input or not, it is highly recommended to pass them to the HQL query as parameters and to avoid String concatenation.

Bad example(avoid):

....
//@formatter:off
String hqlWhere = 
        " where salesOrderLine.id = '" + oldOrderLine.getId() + "' "
        "   or orderlineRelated.id = '" + oldOrderLine.getId() + "' ";
//@formatter:on
 
OBQuery<OrderlineServiceRelation> serviceRelationQuery = OBDal.getInstance()
     .createQuery(OrderlineServiceRelation.class, hqlWhere);
....

Good example(recommended):

....
//@formatter:off
String hqlWhere =
        " where salesOrderLine.id = :salesorderlineId" +
        "   or orderlineRelated.id = :salesorderlineId";
//@formatter:on
 
OBQuery<OrderlineServiceRelation> serviceRelationQuery = OBDal.getInstance()
     .createQuery(OrderlineServiceRelation.class, hqlWhere)
     .setNamedParameter("salesorderlineId", oldOrderLine.getId());
....

Building complex dynamic queries

Sometimes we are required to build a dynamic query that affects parts that can't be passed as bind parameters, like for example the FROM clause. Because of that impossibility, they are usually directly concatenated to the String of the query, which is a known risk.

Instead of the dangerous direct concatenation (following bad example), we can safely use the Data_Access_Layer#OBCriteria or Criteria infrastructure, as they can be dynamically modified. In the following good example, the same structure is valid for executing the query using any class that extends from BaseOBObject.

Bad example(avoid):

...
// tableName, propertyName and propertyValue are direct user inputs
//@formatter:off
final String hql = 
              "select a "
              "  from " + tableName + " as a "
              "  where a." + propertyName + " = " + propertyValue ;
//@formatter:on
final Query<Object> query = OBDal.getInstance()
    .getSession()
    .createQuery(hql, Object.class);
...

Good example(recommended):

...
public <T extends BaseOBObject> List<T> getFilteredObjects(Class<T> clazz, String propertyName, String propertyValue) {
     final OBCriteria<T> obc = OBDal.getInstance().createCriteria(clazz);
     obc.add(Restrictions.eq(propertyName, propertyValue));
     return obc.list();
 }
...

SQL queries using Prepared statements

Warning.png   Remember that in Openbravo is highly recommend to avoid writing direct SQL queries, and instead you should be always using Hibernate framework through Data_Access_Layer

Prepared statements are used by the database to cache the query plan to pull the result set. They have a constant structure and use bind parameters, which makes them invulnerable to injection.

....
    String strSql = "";
    strSql = strSql + 
      "      SELECT COUNT(*) " +
      "      FROM AD_ORGMODULE" +
      "      WHERE AD_MODULE_ID = ?";
 
    ResultSet result;
    String strReturn = null;
    PreparedStatement st = null;
 
    int iParameter = 0;
    try {
    st = connectionProvider.getPreparedStatement(strSql);
      QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
      iParameter++; UtilSql.setValue(st, iParameter, 12, null, AD_Module_ID);
....

Recommendations and other considerations

Warning.png   As mentioned above, prepared statements and parameterized queries using bind parameters in HQL or OBCriteria/Criteria statements are the safest possible options to avoid SLQ/HQL injection.

However, when for whatever reason these safe options can not be used, the following recommendations should be followed.

Sanitize and validate user's input

The idea is to run some custom validations on the user's input before using it in a query. There are three main ways to process the input from the user, which are the following:

Blacklist and Whitelist

They consist in defining validations in code that check if the parameter is between some valid options (whitelist) or between some invalid ones (blacklist) and throw an Exception or continue with execution of the query accordingly.

...
String tableName;
switch(parameterTableName):
  case "AD_USER": tableName = "AD_USER";
                 break;
  case "C_ORDER": tableName = "C_ORDER";
                 break;
  default : throw new InputValidationException("Nice try ;)");
...

Filters

They are already implemented for internal use and can be found in org.openbravo.base.filter package. With them, input from the user can be validated. The available filters in Openbravo are:

The following example shows how to verify that the parameter "inpId" inserted from an user actually follows a valid Id structure, to avoid any injection.

...
if (!IsIDFilter.instance.accept(id)) {
      log.error("Input: {} not accepted by filter: IsIDFilter", id);
      throw new OBException("Input: " + id + " is not an accepted input");
}
...

External Escaping Libraries

Warning.png   This is the last resource to use out of the mentioned, only applicable when no other method can be used, as they aren't very precise and the implementation highly depends on the specific database. These are external libraries and should preferably not be used in Openbravo.

The main idea of this technique is to escape characters that have a special meaning in SQL. These libraries generate a blacklist of characters that need translation. For instance, every occurrence of a single quote (') in a parameter must be replaced by two single quotes (' ') to form a valid SQL string literal, which invalidates the possibility of modifying queries like the ones shown in the "Steps of an SQL/HQL injection attack" section of this page. Example:

...
ESAPI.encoder().encodeForSQL( ORACLE_CODEC, req.getParameter("ad_user_id"));
...

Limit output information to user in case of errors

The messages shown to the user must not contain any information about variables, names (fields, tables, functions…) or even what the exact error is about in certain situations. It should inform the general idea of the mistake made by the user. Some examples could be the following.

Building SQLC queries

The queries defined in SQLC are later translated into java code, in which some injections vulnerabilities can be found depending on the type of parameter used. Remember that if no user's input is involved, then it should be safe. Otherwise, filtering the parameter (blacklist/whitelist also applicable) before inserting it into the query would be necessary.

In SQLC there are two groups of these parameters: mandatory and optional:

  1. Mandatory ones are already safe as they are validated before inserting them into any query.
  2. Optional parameters allow you to add and/or modify parts of the SQL statement at runtime, but some of them are already safe, such as parameters without type and parameters of type none. The problematic optional parameters are the following:

SQLC with optional parameters of type argument

This kind of parameter is completely ignored if the parameters' value is null or an empty String "". Otherwise the executed statement does include the fixed part of the parameter followed by the parameters value, without using a placeholder ("?"), using unsafe concatenation instead. This is why they need to be checked and cleaned before inserting them into the query. The following is an example of this, using filters (blacklist/whitelist also applicable) as input validation method:

...
<!-- xsql definition -->
<Parameter name="parSalesOrders" optional="true" type="argument" after="WHERE 1=1" text="AND C_Order_ID IN"/>
...
...
// This is how it's transformed to Java
strSql = strSql + ((parSalesOrders==null || parSalesOrders.equals(""))?"":" AND C_Order_ID IN" + parSalesOrders);
...
...
// Example of manual input sanitization
String strSalesOrder = vars.getRequiredInStringParameter("inpOrder", IsIDFilter.instance);
GenerateShipmentsmanualData.updateSelection(this, strSalesOrder);
...

SQLC with optional parameters of type replace

These parameters allow to completely replace a section of an SQL statement with a new one. Because of it, they have a high risk of injection depending on the use. The following example is a safe implementation using filtering:

...
<!-- xsql definition -->
<Parameter name="modules" optional="true" type="replace" after="AD_MODULE_ID IN " text="('1')"/>
...
...
// This is how it's transformed to Java
strSql = strSql + ((modules==null || modules.equals(""))?"":modules);
...
...
// This is the call to the query transformed into Java
String strModules = vars.getInStringParameter("inpNodes", IsIDFilter.instance);
if (strModules != null && !strModules.equals("")) {
      UpdateReferenceDataData[] data = UpdateReferenceDataData.selectModules(cp, strModules,
          strOrganization);
...

Case: Callouts

Due to their nature, they have a high risk of injection, as they are executed with every user's modification of its field. It is highly recommended to always filter (blacklist/whitelist also applicable) to validate the input. The following example belongs to a safe Callout:

...
String strQty = info.getStringParameter("inpQty", IsPositiveIntFilter.instance);
...

Case: Triggers and stored procedures

Although it's an unlikely scenario, it's very important to avoid building dynamic SQL queries inside triggers or stored procedures. This is specially important in the case of triggers where the user's input is usually directly transmitted to the trigger from the UI. In this case sanitization/validation of the input is required to be done within the trigger with the techniques explained before.

In the case of Stored procedures called from Java, it is recommended to be executed using CallStoredProcedure.java or CallProcess.java. These classes allows to safely execute the code as it binds the parameters. Example of a safe call to a stored procedure using CallStoredProcedure.java:

...
private DocumentType getDoubtfulDebtDocumentType(Client client, Organization organization) {
    final List<Object> parameters = new ArrayList<Object>();
    parameters.add(client.getId());
    parameters.add(organization.getId());
    parameters.add("DDB");
    String strDocTypeId = (String) CallStoredProcedure.getInstance()
        .call("AD_GET_DOCTYPE", parameters, null);
    if (strDocTypeId == null || "".equals(strDocTypeId)) {
      throw new OBException("@APRM_DoubtfulDebtNoDocument@");
    }
    return OBDal.getInstance().get(DocumentType.class, strDocTypeId);
  }
...

However, whenever a stored function is directly called without using CallStoredProcedure/CallProcess, input validation must be implemented beforehand.

External links with more information

  1. OWASP Cheat Sheet Series - SQL Injection Prevention Cheat Sheet [1]
  2. Netsparker- SQL Injection Cheat Sheet [2]
  3. Baeldung - SQL Injection and How to Prevent It? [3]
  4. Wikipedia - SQL injection [4]
  5. UnixWiz - SQL Injection Attacks by Example [5]
  6. Medium - Exploiting a HQL injection [6]

Retrieved from "http://wiki.openbravo.com/wiki/HQL_SQL_Injection_Prevention"

This page has been accessed 17,368 times. This page was last modified on 11 September 2020, at 10:48. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.