From 9efb8ef131a2795982a056f79ca42b3b25354e82 Mon Sep 17 00:00:00 2001
From: Aron Roberts <aron@socrates.berkeley.edu>
Date: Thu, 28 Mar 2013 14:48:10 -0700
Subject: [PATCH] CSPACE-5943: For simple cases where we just want to populate
 a prepared statement's parameters with a set of String values, created a
 subclass of the prepared statement builder, and call that new subclass from
 RepositoryJavaClientImpl.

---
 .../storage/PreparedStatementBuilder.java     |  8 +++---
 .../PreparedStatementSimpleBuilder.java       | 25 +++++++++++++++++++
 .../client/java/RepositoryJavaClientImpl.java | 23 +++++++++--------
 3 files changed, 42 insertions(+), 14 deletions(-)
 create mode 100644 services/common/src/main/java/org/collectionspace/services/common/storage/PreparedStatementSimpleBuilder.java

diff --git a/services/common/src/main/java/org/collectionspace/services/common/storage/PreparedStatementBuilder.java b/services/common/src/main/java/org/collectionspace/services/common/storage/PreparedStatementBuilder.java
index 7b8f09d67..e99d70404 100644
--- a/services/common/src/main/java/org/collectionspace/services/common/storage/PreparedStatementBuilder.java
+++ b/services/common/src/main/java/org/collectionspace/services/common/storage/PreparedStatementBuilder.java
@@ -1,11 +1,11 @@
-package org.collectionspace.services.common.storage;
-
-import java.sql.Connection;
-import java.sql.PreparedStatement;
 /**
  * Per http://stackoverflow.com/a/7127189
  */
 
+package org.collectionspace.services.common.storage;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
 import java.sql.SQLException;
 
 public class PreparedStatementBuilder
diff --git a/services/common/src/main/java/org/collectionspace/services/common/storage/PreparedStatementSimpleBuilder.java b/services/common/src/main/java/org/collectionspace/services/common/storage/PreparedStatementSimpleBuilder.java
new file mode 100644
index 000000000..a71f9ec14
--- /dev/null
+++ b/services/common/src/main/java/org/collectionspace/services/common/storage/PreparedStatementSimpleBuilder.java
@@ -0,0 +1,25 @@
+package org.collectionspace.services.common.storage;
+
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.util.List;
+
+public class PreparedStatementSimpleBuilder extends PreparedStatementBuilder {
+
+    private List<String> params;
+
+    public PreparedStatementSimpleBuilder(final String sql, final List<String> params) {
+        super(sql);
+        this.params = params;
+    }
+
+    @Override
+    protected void preparePrepared(final PreparedStatement preparedStatement)
+            throws SQLException {
+        int i = 0;
+        for (String param : params) {
+            i++;
+            preparedStatement.setString(i, param);
+        }
+    }
+}
\ No newline at end of file
diff --git a/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java b/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java
index a4be744c9..de39486aa 100644
--- a/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java
+++ b/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java
@@ -43,7 +43,7 @@ import org.collectionspace.services.common.context.ServiceContext;
 import org.collectionspace.services.common.query.QueryContext;
 import org.collectionspace.services.common.repository.RepositoryClient;
 import org.collectionspace.services.common.storage.JDBCTools;
-import org.collectionspace.services.common.storage.PreparedStatementBuilder;
+import org.collectionspace.services.common.storage.PreparedStatementSimpleBuilder;
 import org.collectionspace.services.lifecycle.TransitionDef;
 import org.collectionspace.services.nuxeo.util.NuxeoUtils;
 
@@ -919,6 +919,13 @@ public class RepositoryJavaClientImpl implements RepositoryClient<PoxPayloadIn,
         
         MultivaluedMap<String, String> queryParams = ctx.getQueryParams();
         final String partialTerm = queryParams.getFirst(IQueryManager.SEARCH_TYPE_PARTIALTERM);
+        
+        // FIXME: Look into whether this performance concern specific to query
+        // planning with prepared statements may be affecting us:
+        // http://stackoverflow.com/a/678452
+        // If that proves to be a significant concern, we can instead use
+        // JDBCTools.executeQuery(), and attempt to sanitize user input
+        // against potential SQL injection attacks.
 
         // FIXME: Replace this placeholder query with an actual query resulting
         // from CSPACE-5945 work
@@ -955,16 +962,12 @@ public class RepositoryJavaClientImpl implements RepositoryClient<PoxPayloadIn,
         // FIXME: We might also consider skipping the JOIN on the common schema table
         // in the '_ALL_' case, where we are not restricting by inAuthority value
         
-        PreparedStatementBuilder jdbcFilterBuilder = new PreparedStatementBuilder(sql){
-            @Override
-            protected void preparePrepared(PreparedStatement preparedStatement)
-                throws SQLException
-            {
-                preparedStatement.setString(1, partialTerm + JDBCTools.SQL_WILDCARD);
-            }};
-
+        List<String> params = new ArrayList<>();
+        params.add(partialTerm + JDBCTools.SQL_WILDCARD);
+        PreparedStatementSimpleBuilder jdbcFilterQueryBuilder = new PreparedStatementSimpleBuilder(sql, params);
+        
         List<String> docIds = new ArrayList<>();
-        try (CachedRowSet crs = JDBCTools.executePreparedQuery(jdbcFilterBuilder,
+        try (CachedRowSet crs = JDBCTools.executePreparedQuery(jdbcFilterQueryBuilder,
                 dataSourceName, repositoryName, sql)) {
 
             // If the response to the query is null or contains zero rows,
-- 
2.47.3