How to bind to IN parameters in Cloud Spanner Java API

Question

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?

Answer 1

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"])