Ruby on Rails：如何在不使用查找时清理SQL的字符串？ [英] Ruby on Rails: How to sanitize a string for SQL when not using find?
问题描述
我正在尝试清理一个涉及用户输入的字符串，而无需手动制作我自己的可能的错误正则表达式，如果可能的话，如果这是唯一的方式，我也会感谢，如果有人可以指出我的权利指向一个不太可能丢失任何东西的正则表达式。 Rails中有许多方法可以允许您输入本机SQL命令，人们如何逃避用户输入？
I'm trying to sanitize a string that involves user input without having to resort to manually crafting my own possibly buggy regex if possible, however, if that is the only way I would also appreciate if anyone can point me in the right direction to a regex that is unlikely to be missing anything. There are a number of methods in Rails that can allow you to enter in native SQL commands, how do people escape user input for those?
我问的问题是一个广泛的问题，但在特定情况下，我正在使用Postgres数据库中的一列，Rails本身不理解为据我所知，tsvector，它拥有纯文本搜索信息。 Rails能够写入和读取它，就像它是一个字符串一样，但是，与字符串不同，当我执行像模型中的vector =之类的东西时，似乎不会自动转义它。
The question I'm asking is a broad one, but in my particular case, I'm working with a column in my Postgres database that Rails does not natively understand as far as I know, the tsvector, which holds plain text search information. Rails is able to write and read from it as if it's a string, however, unlike a string, it doesn't seem to be automatically escaping it when I do things like vector= inside the model.
例如，当我做了model.name ='::'，其中name是一个字符串，它工作正常。当我做model.vector ='::'时，它出错：
For example, when I do model.name='::', where name is a string, it works fine. When I do model.vector='::' it errors out:
ActiveRecord::StatementInvalid: PGError: ERROR: syntax error in tsvector: "::"
"vectors" = E'::' WHERE "id" = 1
这似乎是由于缺少转义分号而导致的问题，我可以手动设置vector ='::'。
This seems to be a problem caused by lack of escaping of the semicolons, and I can manually set the vector='::' fine.
我也有一个明亮的想法，也许我可以调用如下：
I also had the bright idea, maybe I can just call something like:
ActiveRecord::Base.connection.execute "UPDATE medias SET vectors = ? WHERE id = 1", "::"
但是，这种语法不起作用，因为原始SQL命令没有访问权限的方法来转义和输入字符串使用？标记。
However, this syntax doesn't work, because the raw SQL commands don't have access to find's method of escaping and inputting strings by using the ? mark.
这使我像使用任何类型的用户输入调用connection.execute一样的问题，因为它们都归结为对字符串进行消毒，但是我可以' t似乎找到任何方法手动调用Rails的SQL字符串清理方法。任何人都可以提供任何建议？
This strikes me as the same problem as calling connection.execute with any type of user input, as it all boils down to sanitizing the strings, but I can't seem to find any way to manually call Rails' SQL string sanitization methods. Can anyone provide any advice?
推荐答案
将此方法添加到您的型号中：
Add this method to your model:
class Media < ActiveRecord::Base
def self.execute_sql(*sql_array)
connection.execute(send(:sanitize_sql_array, sql_array))
end
end
现在您可以执行任何SQL，如：
Now you can execute any SQL such as:
Media.execute_sql('UPDATE medias SET vectors = ? WHERE id = 1', '::')
参考
这篇关于Ruby on Rails：如何在不使用查找时清理SQL的字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
