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 39C60200CB5 for ; Wed, 12 Jul 2017 19:44:27 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 3846A169829; Wed, 12 Jul 2017 17:44:27 +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 5A045169823 for ; Wed, 12 Jul 2017 19:44:26 +0200 (CEST) Received: (qmail 8039 invoked by uid 500); 12 Jul 2017 17:44:25 -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 8029 invoked by uid 99); 12 Jul 2017 17:44:25 -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; Wed, 12 Jul 2017 17:44:25 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 76C24DFC28; Wed, 12 Jul 2017 17:44:25 +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 Message-Id: <6c04869c8aae459e8b9e2d5da7d3b1a8@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: airavata git commit: Using parameter binding to handle single quote in proj/exp names Date: Wed, 12 Jul 2017 17:44:25 +0000 (UTC) archived-at: Wed, 12 Jul 2017 17:44:27 -0000 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 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) {