This is the first time I've seen this issue. I'm building up an SQL array to run through sanitize_sql_array and Rails is adding extra, unnecessary single quotes in the return value. So instead of returning:
SELECT DISTINCT data -> 'Foo' from products
it returns:
SELECT DISTINCT data -> ''Foo'' from products
which of course Postgres doesn't like.
Here is the code:
sql_array = ["SELECT DISTINCT %s from products", "data -> 'Foo'"]
sql_array = sanitize_sql_array(sql_array)
connection.select_values(sql_array)
Note the same thing happens when I use the shorter and more usual:
sql_array = ["SELECT DISTINCT %s from products", "data -> 'Foo'"]
connection.select_values(send(:sanitize_sql_array, sql_array))
Ever seen this before? Does it have something to do with using HStore? I definitely need that string sanitized since the string Foo is actually coming from a user-entered variable.
Thanks!
You're giving sanitize_sql_array a string that contains an hstore expression and expecting sanitize_sql_array to understand that the string contains some hstore stuff; that's asking far too much, sanitize_sql_array only knows about simple things like strings and numbers, it doesn't know how to parse PostgreSQL's SQL extensions or even standard SQL. How would you expect sanitize_sql_array to tell the difference between, for example, a string that happens to contain '11 * 23' and a string that is supposed to represent the arithmetical expression 11 * 23?
You should split your data -> 'Foo' into two pieces so that sanitize_sql_array only sees the string part when it is sanitizing things:
sql_array = [ 'select distinct data -> ? from products', 'Foo' ]
sql = sanitize_sql_array(sql_array)
That will give you the SQL you're looking for:
select distinct data -> 'Foo' from products