Rails ActiveRecord: How to use bind variables with

Question

I have a postgres jsonb query as follows:

Blog.where("upload_data @> '[ { \"name\": \"#{name}\" }]'")

Which works but breaks build because the excellent brakeman points out the possible sql injection risk. If I use bind variables:

Blog.where("upload_data @> '[ { \"name\": ? }]'", name)

It creates a sql query like:

WHERE upload_data @> '[ { "name": 'name' }]'

Note the single quotes - which is an invalid query

If I use single quotes I get:

WHERE upload_data @> "[ { 'name': 'name' }]"

which is invalid

I have tried a few other things but what I want is for the bind variable to evaluate to a string with double quotes:

WHERE upload_data @> '[ { "name": "name" }]'

Or a different solution - other than getting brakeman to skip the file.

Answer 1

You can't put parameter placeholders inside quoted strings.

The fact that Rails allows you to do that and substitutes a single-quoted string inside the single-quoted string indicates that Rails has failed (as usual) to understand rules of SQL.

But you can put a parameter placeholder in an expression, with other strings. I am not a regular PostgreSQL user, but I assume you can concatenate strings together to form a complete JSON literal:

Blog.where("upload_data @> '[ { \"name\": \"' || ? || '\"}]'", name)

You might find it makes your code more clear if you parameterize the whole JSON value. Use %Q() to avoid needing to backslash the literal double-quotes.

Blog.where("upload_data @> ?", %Q([ { "name": "#{name}" } ]))

Or to make sure to generate valid JSON, I'd put the expression in Ruby syntax, and then convert to JSON:

Blog.where("upload_data @> ?", JSON.generate( [{name: name}] ))