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.
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}] ))