我有一个postgres jsonb查询,如下所示:
I have a postgres jsonb query as follows:
Blog.where("upload_data @> '[ { \"name\": \"#{name}\" }]'")这是可行的,但是会破坏构建,因为出色的刹车员指出了可能的sql注入风险.如果我使用绑定变量:
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)它会创建一个SQL查询,如:
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' }]"这是无效的
我已经尝试了其他一些方法,但是我想要的是使bind变量求值为带双引号的字符串:
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.
Rails允许您执行此操作并在单引号引起来的字符串中替换单引号字符串,这一事实表明Rails无法像往常一样理解SQL规则.
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.
但是您可以将参数占位符与其他字符串一起放在表达式中.我不是普通的PostgreSQL用户,但是我假设您可以将字符串连接在一起以形成完整的JSON文字:
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)如果参数化整个JSON值,您可能会发现它使您的代码更加清晰.使用%Q()避免反斜杠文字双引号.
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}" } ]))或者为确保生成有效的JSON,我将表达式放入Ruby语法中,然后转换为JSON:
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}] ))更多推荐
Rails ActiveRecord:如何在jsonb上使用带双引号的绑定变量
发布评论