airavata-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From machris...@apache.org
Subject airavata git commit: Using parameter binding to handle single quote in proj/exp names
Date Wed, 12 Jul 2017 17:44:25 GMT
Repository: airavata
Updated Branches:
  refs/heads/develop 03d691803 -> c8ee09c33


Using parameter binding to handle single quote in proj/exp names


Project: http://git-wip-us.apache.org/repos/asf/airavata/repo
Commit: http://git-wip-us.apache.org/repos/asf/airavata/commit/c8ee09c3
Tree: http://git-wip-us.apache.org/repos/asf/airavata/tree/c8ee09c3
Diff: http://git-wip-us.apache.org/repos/asf/airavata/diff/c8ee09c3

Branch: refs/heads/develop
Commit: c8ee09c3324686722702b9831194e3fe517b606d
Parents: 03d6918
Author: Marcus Christie <machristie@apache.org>
Authored: Tue Jul 11 11:35:11 2017 -0500
Committer: Marcus Christie <machristie@apache.org>
Committed: Wed Jul 12 12:43:36 2017 -0500

----------------------------------------------------------------------
 .../catalog/resources/WorkerResource.java       | 52 +++++++++++++++-----
 1 file changed, 40 insertions(+), 12 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/airavata/blob/c8ee09c3/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/experiment/catalog/resources/WorkerResource.java
----------------------------------------------------------------------
diff --git a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/experiment/catalog/resources/WorkerResource.java
b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/experiment/catalog/resources/WorkerResource.java
index ab3034c..6d5ebd6 100644
--- a/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/experiment/catalog/resources/WorkerResource.java
+++ b/modules/registry/registry-core/src/main/java/org/apache/airavata/registry/core/experiment/catalog/resources/WorkerResource.java
@@ -515,13 +515,19 @@ public class WorkerResource extends AbstractExpCatResource {
         List<org.apache.airavata.registry.core.experiment.catalog.resources.ProjectResource>
result = new ArrayList<org.apache.airavata.registry.core.experiment.catalog.resources.ProjectResource>();
         EntityManager em = null;
         try {
+            Map<String, Object> queryParameters = new HashMap<>();
             String query = "SELECT DISTINCT p from Project p WHERE ";
 
             // FIXME There is a performance bottleneck for using IN clause. Try using temporary
tables ?
             if (accessibleIds != null && accessibleIds.size() > 0) {
                 query += " p.projectId IN (";
-                for (String id : accessibleIds)
-                    query += ("'" + id + "'" + ",");
+                int accessibleIdIndex = 0;
+                for (String id : accessibleIds) {
+                    String paramName = "accessibleId" + accessibleIdIndex;
+                    query += (":" + paramName + ",");
+                    queryParameters.put(paramName, id);
+                    accessibleIdIndex++;
+                }
                 query = query.substring(0, query.length() - 1) + ") AND ";
             }else if(ServerSettings.isEnableSharing() && (accessibleIds==null ||
accessibleIds.size()==0)){
                 return new ArrayList<>();
@@ -531,14 +537,17 @@ public class WorkerResource extends AbstractExpCatResource {
                 for (String field : filters.keySet()) {
                     String filterVal = filters.get(field);
                     if (field.equals(ProjectConstants.USERNAME)) {
-                        query += "p." + field + "= '" + filterVal + "' AND ";
+                        query += "p." + field + "= :" + field + " AND ";
+                        queryParameters.put(field, filterVal);
                     } else if (field.equals(ProjectConstants.GATEWAY_ID)) {
-                        query += "p." + field + "= '" + filterVal + "' AND ";
+                        query += "p." + field + "= :" + field + " AND ";
+                        queryParameters.put(field, filterVal);
                     } else {
                         if (filterVal.contains("*")) {
                             filterVal = filterVal.replaceAll("\\*", "");
                         }
-                        query += "p." + field + " LIKE '%" + filterVal + "%' AND ";
+                        query += "p." + field + " LIKE :" + field + " AND ";
+                        queryParameters.put(field, "%" + filterVal + "%");
                     }
                 }
             }
@@ -561,6 +570,10 @@ public class WorkerResource extends AbstractExpCatResource {
             } else {
                 q = em.createQuery(query);
             }
+            for (String parameterName : queryParameters.keySet()) {
+                q.setParameter(parameterName, queryParameters.get(parameterName));
+            }
+
 
             List resultList = q.getResultList();
             for (Object o : resultList) {
@@ -607,14 +620,20 @@ public class WorkerResource extends AbstractExpCatResource {
         List<ExperimentSummaryResource> result = new ArrayList();
         EntityManager em = null;
         try {
+            Map<String, Object> queryParameters = new HashMap<>();
             String query = "SELECT e FROM ExperimentSummary e " +
                     "WHERE ";
 
             // FIXME There is a performance bottleneck for using IN clause. Try using temporary
tables ?
             if (accessibleIds != null && accessibleIds.size() > 0) {
                 query += " e.experimentId IN (";
-                for (String id : accessibleIds)
-                    query += ("'" + id + "'" + ",");
+                int accessibleIdIndex = 0;
+                for (String id : accessibleIds) {
+                    String paramName = "accessibleId" + accessibleIdIndex;
+                    query += (":" + paramName + ",");
+                    queryParameters.put(paramName, id);
+                    accessibleIdIndex++;
+                }
                 query = query.substring(0, query.length() - 1) + ") AND ";
             }else if(ServerSettings.isEnableSharing() && (accessibleIds==null ||
accessibleIds.size()==0)){
                 return new ArrayList<>();
@@ -626,7 +645,9 @@ public class WorkerResource extends AbstractExpCatResource {
             }
 
             if (toTime != null && fromTime != null && toTime.after(fromTime))
{
-                query += "e.creationTime > '" + fromTime + "' " + "AND e.creationTime
<'" + toTime + "' AND ";
+                query += "e.creationTime > :fromTime AND e.creationTime < :toTime AND
";
+                queryParameters.put("fromTime", fromTime);
+                queryParameters.put("toTime", toTime);
             }
 
             filters.remove(ExperimentStatusConstants.STATE);
@@ -634,16 +655,20 @@ public class WorkerResource extends AbstractExpCatResource {
                 for (String field : filters.keySet()) {
                     String filterVal = filters.get(field);
                     if (field.equals(ExperimentConstants.USER_NAME)) {
-                        query += "e." + field + "= '" + filterVal + "' AND ";
+                        query += "e." + field + "= :username AND ";
+                        queryParameters.put("username", filterVal);
                     } else if (field.equals(ExperimentConstants.GATEWAY_ID)) {
-                        query += "e." + field + "= '" + filterVal + "' AND ";
+                        query += "e." + field + "= :gateway_id AND ";
+                        queryParameters.put("gateway_id", filterVal);
                     } else if (field.equals(ExperimentConstants.PROJECT_ID)) {
-                        query += "e." + field + "= '" + filterVal + "' AND ";
+                        query += "e." + field + "= :project_id AND ";
+                        queryParameters.put("project_id", filterVal);
                     } else {
                         if (filterVal.contains("*")) {
                             filterVal = filterVal.replaceAll("\\*", "");
                         }
-                        query += "e." + field + " LIKE '%" + filterVal + "%' AND ";
+                        query += "e." + field + " LIKE :" + field + " AND ";
+                        queryParameters.put(field, "%" + filterVal + "%");
                     }
                 }
             }
@@ -666,6 +691,9 @@ public class WorkerResource extends AbstractExpCatResource {
             } else {
                 q = em.createQuery(query);
             }
+            for (String parameterName : queryParameters.keySet()) {
+                q.setParameter(parameterName, queryParameters.get(parameterName));
+            }
 
             List resultList = q.getResultList();
             for (Object o : resultList) {


Mime
View raw message