sanitize_sql_array is adding extra, unnecessary quotes to query

Question

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!

What are you trying to do? That "data -> 'Foo'" argument doesn't make sense to me. Otherwise the sanitize_sql_array is working correctly. — Milen A. Radev
– Milen A. Radev, Commented Oct 2, 2012 at 19:20
Milen: this is hstore syntax in postgres. select distinct data -> 'Foo' from products means to select the values in the key,value store called data where the key is equal to Foo. postgres requires that the key be surrounded by single quotes. — Rob Gonzalez
– Rob Gonzalez, Commented Oct 2, 2012 at 19:57

mu is too short · Accepted Answer · 2012-10-02 19:31:16Z

4

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

answered Oct 2, 2012 at 19:31

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rob Gonzalez Over a year ago

I agree that this is not the specialty of sanitize_sql_array, but that solution doesn't work either. Postgres requires single quotes around 'Foo' and your way returns 'select distinct data -> Foo from products' which doesn't have them.

mu is too short Over a year ago

@RobGonzalez: Are you using %s or ? in the SQL?

Rob Gonzalez Over a year ago

I'm using %s. ? produces results that I don't understand at all.

mu is too short Over a year ago

? produces the results in my answer, I pasted that right out of my Rails console.

mu is too short Over a year ago

The difference is that ? takes care of all the type issues for you (strings will be quoted, numbers won't be, dates will be formatted properly, booleans will match the database's representation, ...) but %s just throws an escaped string into the SQL without quoting it and %s would probably treat everything as a string regardless of what data type it really is.

|

Collectives™ on Stack Overflow

sanitize_sql_array is adding extra, unnecessary quotes to query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related