CODEWITHSUNDEEP
mysqlruby-on-rails
CR7
CR7

Reputation: 710

Provide prepared statement in rails

I am using rails-4.2.1 and is trying to fetch data from two tables subjects and elective_subjects table in a single query. As rails 4 does not support UNION , I wrote a raw sql query. I want to search by name in both tables. My code is given below

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)

It is working but when I provide ' in my search the query breaks. So how can I make it a prepared statement. So that sql injection can be avoided

Upvotes: 1

Views: 1722

Answers (2)

eikes
eikes

Reputation: 5061

This question is super old and no cares anymore, but I think it is a valid question, so here's the answer:

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

That's how you create and use a prepared statement in rails.

Upvotes: 3

CR7
CR7

Reputation: 710

I have solved the issue by escaping the search string using following statement.

search = Mysql2::Client.escape(search)

Upvotes: 0

Related Questions