我正在使用rails-4.2.1,并试图在单个查询中从两个表主题和elective_subjects表中获取数据。由于rails 4不支持UNION,我写了一个原始sql查询。我想在两个表中按名称搜索。我的代码如下
query = "(SELECT id as id, name as name, reference as reference from subjects where name like '#{search}') UNION (SELECT id as id, name as name, null as reference from elective_subjects where name like '#{search}')"
@subjects = ActiveRecord::Base.connection.execute(query)
它正在工作,但是当我在搜索中提供'时,查询中断。那么我怎样才能把它变成一个准备好的声明。这样就可以避免SQL注入
这个问题超级老了,不再关心了,但我认为这是一个有效的问题,所以这就是答案:
query = "(SELECT id as id, name as name, reference as reference from subjects where name like $1)
UNION
(SELECT id as id, name as name, null as reference from elective_subjects where name like $1)"
binds = [ActiveRecord::Relation::QueryAttribute.new('name', search, ActiveRecord::Type::Text.new)]
result = ApplicationRecord.connection.exec_query(query, 'SQL', binds, prepare: true)
@subjects = result.rows
这就是您在 rails 中创建和使用预准备语句的方式。
我已经使用以下语句转义搜索字符串解决了这个问题。
search = Mysql2::Client.escape(search)