Rails ActiveRecord:如何在jsonb上使用带双引号的绑定变量 [英] Rails ActiveRecord: How to use bind variables with double quotes on jsonb

问题描述

我有一个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上使用带双引号的绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！