4

Is it possible, using the Google Cloud Spanner Java SDK, to bind to the parameters supplied to the IN portion of a query?

e.g.

List<String> names = new ArrayList<String>();
names.add("Alice");
names.add("Bob");
String sql = "SELECT * FROM people WHERE name IN (@names)";
Statement statement = Statement
                .newBuilder(sql)
                .bind("names").to(names)
                .build();

If we bind names using toStringArray, it errors. And if we set the following:

names = "'Alice','Bob'";

Then the generated SQL is:

SELECT * FROM people WHERE name IN ("'Alice','Bob'")
  • Note the extra quotations. Any idea how we can do this without %s string substitution to avoid inject attacks?
Dan McGrath
  • 41,220
  • 11
  • 99
  • 130
user3707
  • 1,470
  • 2
  • 15
  • 21
  • When you say 'it errors', what is the error? Are you doing `.bind("names").toStringArray(names)` – Dan McGrath Apr 24 '17 at 15:21
  • Hi Dan, I receive this error: INVALID_ARGUMENT: No matching signature for operator IN for argument types STRING and {ARRAY} [at 1:35] SELECT name FROM customers WHERE name IN (@names) – user3707 Apr 24 '17 at 15:54
  • What happens if you the SQL statement to `WHERE name IN UNNEST(@names)` – Dan McGrath Apr 24 '17 at 16:08
  • That works. Is this the preferred way? – user3707 Apr 24 '17 at 16:17
  • No idea, I read Spanner's server code to find that, I've pinged Spanner's tech writer to see if this just wanted documented, or if the client libs should be unrolling the array to be repeated parameters instead. – Dan McGrath Apr 24 '17 at 16:20
  • Thanks Dan. Ill hold off on answering until you've returned a comment here - or someone from Google can respond with the official way? – user3707 Apr 24 '17 at 17:19
  • Yes, this is the official, documented here: https://cloud.google.com/spanner/docs/functions-and-operators#in-operators – Dan McGrath Apr 24 '17 at 19:39

1 Answers1

4

2 changes to your code:

List<String> names = new ArrayList<String>();
names.add("Alice");
names.add("Bob");
String sql = "SELECT * FROM people WHERE name IN UNNEST(@names)";
Statement statement = Statement
                .newBuilder(sql)
                .bind("names").toStringArray(names)
                .build();

First is to make the condition IN UNNEST as we're going to be binding an array rather than repeated values.

Second is to change to to toStringArray to bind the array as you originally tried.

To better visualize this, the array that you bind is essentially this:

SELECT * FROM people WHERE name IN UNNEST(["Alice", "Bob"])
Dan McGrath
  • 41,220
  • 11
  • 99
  • 130
  • Still not quite sure why do we need to use UNNEST. After UNNEST it is not a list anymore – Stan Sep 02 '22 at 03:11