Fixes issue 30096: Cannot filter the bp field by ' symbol in selector drop down
authorCarlos Aristu <carlos.aristu@openbravo.com>
Wed, 10 Jun 2015 09:05:57 +0200
changeset 26887 ba6a63e37880
parent 26886 ee157fef9c33
child 26888 ec9a491fe07d
Fixes issue 30096: Cannot filter the bp field by ' symbol in selector drop down

The CustomQuerySelectorDatasource was not handling the parameters of the query properly. For that reason characters like ' were not replaced as expected.
Now the parameters are mapped into a list, and replaced in the query using the setParameter() method
modules/org.openbravo.userinterface.selector/src/org/openbravo/userinterface/selector/CustomQuerySelectorDatasource.java
--- a/modules/org.openbravo.userinterface.selector/src/org/openbravo/userinterface/selector/CustomQuerySelectorDatasource.java	Tue Jun 09 18:14:54 2015 +0200
+++ b/modules/org.openbravo.userinterface.selector/src/org/openbravo/userinterface/selector/CustomQuerySelectorDatasource.java	Wed Jun 10 09:05:57 2015 +0200
@@ -11,13 +11,14 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2011-2014 Openbravo SLU
+ * All portions are Copyright (C) 2011-2015 Openbravo SLU
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
  */
 package org.openbravo.userinterface.selector;
 
+import java.math.BigDecimal;
 import java.sql.Timestamp;
 import java.text.SimpleDateFormat;
 import java.util.ArrayList;
@@ -64,6 +65,7 @@
   private static final String ADDITIONAL_FILTERS = "@additional_filters@";
   private static final String NEW_FILTER_CLAUSE = "\n AND ";
   private static final String NEW_OR_FILTER_CLAUSE = "\n OR ";
+  private static final String ALIAS_PREFIX = "alias_";
 
   @Override
   protected int getCount(Map<String, String> parameters) {
@@ -78,6 +80,7 @@
     final SimpleDateFormat xmlDateFormat = JsonUtils.createDateFormat();
     final SimpleDateFormat xmlDateTimeFormat = JsonUtils.createDateTimeFormat();
     final List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
+    final List<Object> typedParameters = new ArrayList<Object>();
     // Defaulted to endRow + 2 to check for more records while scrolling.
     int totalRows = endRow + 2, rowCount = 0;
 
@@ -99,12 +102,15 @@
       Hibernate.initialize(fields);
 
       // Parse the HQL in case that optional filters are required
-      String HQL = parseOptionalFilters(parameters, sel, xmlDateFormat);
+      String HQL = parseOptionalFilters(parameters, sel, xmlDateFormat, typedParameters);
 
       String sortBy = parameters.get("_sortBy");
       HQL += getSortClause(sortBy, sel);
 
       Query selQuery = OBDal.getInstance().getSession().createQuery(HQL);
+      for (int i = 0; i < typedParameters.size(); i++) {
+        selQuery.setParameter(ALIAS_PREFIX + Integer.toString(i), typedParameters.get(i));
+      }
       String[] queryAliases = selQuery.getReturnAliases();
 
       if (startRow > 0) {
@@ -180,7 +186,7 @@
    */
 
   private String parseOptionalFilters(Map<String, String> parameters, Selector sel,
-      SimpleDateFormat xmlDateFormat) {
+      SimpleDateFormat xmlDateFormat, List<Object> typedParameters) {
     String HQL = sel.getHQL();
     if (!HQL.contains(ADDITIONAL_FILTERS)) {
       return HQL;
@@ -238,14 +244,15 @@
           if (StringUtils.isNotEmpty(defaultValue)) {
             defaultExpressionsFilter.append(NEW_FILTER_CLAUSE);
             defaultExpressionsFilter.append(getWhereClause(operator, defaultValue, field,
-                xmlDateFormat, operatorvalue));
+                xmlDateFormat, operatorvalue, typedParameters));
           }
         } catch (Exception e) {
           log.error("Error evaluating filter expression: " + e.getMessage(), e);
         }
       }
       if (field.isFilterable() && StringUtils.isNotEmpty(value)) {
-        String whereClause = getWhereClause(operator, value, field, xmlDateFormat, operatorvalue);
+        String whereClause = getWhereClause(operator, value, field, xmlDateFormat, operatorvalue,
+            typedParameters);
         if (!hasFilter) {
           additionalFilter.append(NEW_FILTER_CLAUSE);
           additionalFilter.append(" (");
@@ -296,7 +303,7 @@
    * @return a String with the HQL where clause to filter the field by the given value.
    */
   private String getWhereClause(String operator, String value, SelectorField field,
-      SimpleDateFormat xmlDateFormat, String[] operatorvalue) {
+      SimpleDateFormat xmlDateFormat, String[] operatorvalue, List<Object> typedParameters) {
     String whereClause = "";
 
     if (operator != null && operator.equals(AdvancedQueryBuilder.EXISTS_QUERY_KEY)) {
@@ -312,28 +319,33 @@
         .getDomainType();
     if (domainType.getClass().getSuperclass().equals(BigDecimalDomainType.class)
         || domainType.getClass().equals(LongDomainType.class)) {
-      whereClause = field.getClauseLeftPart() + " = " + value;
+      whereClause = field.getClauseLeftPart() + " = "
+          + getTypedParameterAlias(typedParameters, new BigDecimal(value));
     } else if (domainType.getClass().equals(DateDomainType.class)) {
       try {
         final Calendar cal = Calendar.getInstance();
         cal.setTime(xmlDateFormat.parse(value));
-        whereClause = " (day(" + field.getClauseLeftPart() + ") = " + cal.get(Calendar.DATE);
+        whereClause = " (day(" + field.getClauseLeftPart() + ") = "
+            + getTypedParameterAlias(typedParameters, cal.get(Calendar.DATE));
         whereClause += "\n and month(" + field.getClauseLeftPart() + ") = "
-            + (cal.get(Calendar.MONTH) + 1);
-        whereClause += "\n and year(" + field.getClauseLeftPart() + ") = " + cal.get(Calendar.YEAR)
-            + ") ";
+            + getTypedParameterAlias(typedParameters, cal.get(Calendar.MONTH) + 1);
+        whereClause += "\n and year(" + field.getClauseLeftPart() + ") = "
+            + getTypedParameterAlias(typedParameters, cal.get(Calendar.YEAR)) + ") ";
       } catch (Exception e) {
         // ignore these errors, just don't filter then
         // add a dummy whereclause to make the query format correct
         whereClause = "1 = 1";
       }
     } else if (domainType instanceof BooleanDomainType) {
-      whereClause = field.getClauseLeftPart() + " = " + value;
+      whereClause = field.getClauseLeftPart() + " = "
+          + getTypedParameterAlias(typedParameters, new Boolean(value));
     } else if (domainType instanceof UniqueIdDomainType) {
-      whereClause = field.getClauseLeftPart() + " = '" + value + "'";
+      whereClause = field.getClauseLeftPart() + " = "
+          + getTypedParameterAlias(typedParameters, value);
     } else if (domainType instanceof ForeignKeyDomainType) {
       // Assume left part definition is full object reference from HQL select
-      whereClause = field.getClauseLeftPart() + ".id = '" + value + "'";
+      whereClause = field.getClauseLeftPart() + ".id = "
+          + getTypedParameterAlias(typedParameters, value);
     } else if (domainType instanceof StringEnumerateDomainType) {
       // For enumerations value can be in two formats:
       // 1- VAL: in this case the expression should be property='VAL'
@@ -349,7 +361,8 @@
 
       if (values == null) {
         // format 1
-        whereClause = field.getClauseLeftPart() + " = '" + value + "'";
+        whereClause = field.getClauseLeftPart() + " = "
+            + getTypedParameterAlias(typedParameters, value);
       } else {
         // format 2
         whereClause = field.getClauseLeftPart() + " IN (";
@@ -358,7 +371,7 @@
             whereClause += ", ";
           }
           try {
-            whereClause += "'" + values.getString(i) + "'";
+            whereClause += getTypedParameterAlias(typedParameters, values.getString(i));
           } catch (JSONException e) {
             log.error("Error parsing values as JSONArray:" + value, e);
           }
@@ -367,11 +380,17 @@
       }
     } else {
       if ("iStartsWith".equals(operator)) {
-        whereClause = "lower(" + field.getClauseLeftPart() + ") LIKE '"
-            + value.toLowerCase().replaceAll(" ", "%") + "%'";
+        whereClause = "lower("
+            + field.getClauseLeftPart()
+            + ") LIKE "
+            + getTypedParameterAlias(typedParameters, value.toLowerCase().replaceAll(" ", "%")
+                + "%");
       } else {
-        whereClause = "lower(" + field.getClauseLeftPart() + ") LIKE '%"
-            + value.toLowerCase().replaceAll(" ", "%") + "%'";
+        whereClause = "lower("
+            + field.getClauseLeftPart()
+            + ") LIKE "
+            + getTypedParameterAlias(typedParameters, "%"
+                + value.toLowerCase().replaceAll(" ", "%") + "%");
       }
     }
     return whereClause;
@@ -573,4 +592,10 @@
       return null;
     }
   }
+
+  private String getTypedParameterAlias(List<Object> typedParameters, Object value) {
+    String alias = ":" + ALIAS_PREFIX + (typedParameters.size());
+    typedParameters.add(value);
+    return alias;
+  }
 }