Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id DF8EF200CD9 for ; Thu, 3 Aug 2017 18:01:23 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id DE2DB16BDF4; Thu, 3 Aug 2017 16:01:23 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 1D58716BDEE for ; Thu, 3 Aug 2017 18:01:20 +0200 (CEST) Received: (qmail 1121 invoked by uid 500); 3 Aug 2017 16:01:19 -0000 Mailing-List: contact commits-help@airavata.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@airavata.apache.org Delivered-To: mailing list commits@airavata.apache.org Received: (qmail 99606 invoked by uid 99); 3 Aug 2017 16:01:18 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 03 Aug 2017 16:01:18 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 0229DF1820; Thu, 3 Aug 2017 16:01:17 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: machristie@apache.org To: commits@airavata.apache.org Date: Thu, 03 Aug 2017 16:01:43 -0000 Message-Id: In-Reply-To: References: X-Mailer: ASF-Git Admin Mailer Subject: [27/50] [abbrv] airavata git commit: Using parameter binding to handle single quote in proj/exp names archived-at: Thu, 03 Aug 2017 16:01:24 -0000 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/master Commit: c8ee09c3324686722702b9831194e3fe517b606d Parents: 03d6918 Author: Marcus Christie Authored: Tue Jul 11 11:35:11 2017 -0500 Committer: Marcus Christie 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 result = new ArrayList(); EntityManager em = null; try { + Map 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 result = new ArrayList(); EntityManager em = null; try { + Map 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) {