如何清理 Rails 4 中的原始 SQL [英] How to sanitize raw SQL in Rails 4

问题描述

在 Rails 3 中，我可以使用 sanitize_sql_array 来清理原始 SQL，以便偶尔需要原始 SQL 查询.但这似乎已在 Rails 4 中删除，或者没有删除太多，而是移至 ActiveRecord::Sanitization.但是，我现在不知道如何调用 sanitize_sql_array，那么在 Rails 4 中清理原始 SQL 的最佳方法是什么?

In Rails 3 I could use sanitize_sql_array to sanitize raw SQL for those occassional moments where a raw SQL query is needed. But this appears to have been removed in Rails 4, or not so much removed, but moved to ActiveRecord::Sanitization. However, I can not figure out how to call sanitize_sql_array now, so what's the best way to sanitize raw SQL in Rails 4?

我想澄清一下，我在这里谈论的是完整的原始 SQL 查询，而不是使用 Rail 的模型.我知道这不是最佳实践，这正是我必须为这个特定查询做的事情，因为它不能用 Rails 漂亮的 ActiveRecord 接口表示(相信我，我已经试过了).

I want to clarify that I am talking about a full raw SQL query here, not using Rail's models. I'm aware that this is not best practice, this is just what I have to do for this specific query since it can't be represented by Rails's nice ActiveRecord interface (Trust me, I've tried).

这是一个示例调用，它显然比我的查询实际看起来更简单:

Here is a sample call, which is obviously simpler than what my query actually looks like:

query = "SELECT * FROM users 
LEFT OUTER JOIN posts ON users.id=posts.user_id
AND posts.topic_id = '#{topic.id}'" 
# ^- Obviously bad and very vulnerable, this is what we're trying to fix
ActiveRecord::Base.connection.select_all(query)

推荐答案

如果你真的需要编写原始 SQL 你可以使用 quote 来清理它:

If you really need to write raw SQL you can use quote to sanitize it:

conn = ActiveRecord::Base.connection
name = conn.quote("John O'Neil")
title = conn.quote(nil)
query = "INSERT INTO users (name,title) VALUES (#{name}, #{title})"
conn.execute(query)

这篇关于如何清理 Rails 4 中的原始 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！