Q: How to write MySQL multilines condition with RubyOnRails? I want to use the "?" char for a search in a MySQL request in rails.
For example, classical way is:
Model.where("name = ?", '#{@search}')

My question is about long queries and multilines condition.
If I want to build a condition manually:
where = ""
where << " status = 1 "
where << " AND MATCH (name) AGAINST (? IN BOOLEAN MODE), @search " if @search
@records = Model.where(where)

Of course, it won't work.
So how to use the "?" (for security and simplicity) with a multilines conditions ?
A simple way is to do that:
where << " MATCH (name) AGAINST ('#{@search}' IN BOOLEAN MODE) "

But I will lose security (SQL injection) and can have problems with quotes if @search contains quotes.
Thanks,

A: You're getting a bit confused about the contents of where: you're putting variable names inside the string, which won't work: "@search" inside a string becomes literally the word "@search" and not a variable.
The best way to think of the arguments to where is as an array of objects, and you can build it like this. The first object is the query string (with ? symbols) and the other elements are the values for the ? symbols, which will be sanitized and translated by rails.
eg
User.where(["first_name = ? and last_name = ?", "John", "Smith"])

you can pass other things to where, like a hash of values, or a single string, but the array is the most flexible, especially in your case.
Bearing that in mind, you can do something like this to build a dynamically-created, complex query: i use this pattern a lot as it's very flexible and also very readable.
condition_strings = []
condition_values = []

condition_strings << "status = ?"
condition_values << 1 #or some dynamic data

condition_strings << "MATCH (name) AGAINST (? IN BOOLEAN MODE)"
condition_values << @search

conditions = [condition_strings.join(" AND ")] + condition_values
# => ["status = ? AND MATCH (name) AGAINST (? IN BOOLEAN MODE)", 1, "foo"]

#now we can use the array as an argument to `.where`:
@records = Model.where(conditions)

