I've made some posts about translating user input from a search box into an ActiveRecord Parameterized statement, which may be vulnerable to SQL injection, but I think I may have a solution.
Here's how the search works. The user enters something like this into the search box:
name="hello" AND NOT address.town="Villa"
query = ["name LIKE ? AND address.town NOT LIKE ?", "hello", "villa"]
query = convert_search_to_query search
# rescue Exception ...
ActiveRecord will prevent any SQL injection attacks, AS LONG AS you are using the parameterized form. As a rule of thumb, ALL information coming from the user should be a parameter.
In your example you mention converting the user query into:
where(["name LIKE ? AND address.town NOT LIKE ?", "hello", "villa"])
In this case ActiveRecord will protect
villa from SQL injection, but it will NOT protect
name LIKE ? AND address.town NOT LIKE ?. ActiveRecord assumes that
name LIKE ? AND address.town NOT LIKE ? is being generated either by the developer (hard coded) or by the application, either way it assumes it's safe to execute.
So if any part of
name LIKE ? AND address.town NOT LIKE ? is coming from the user your app could be vulnerable to SQL injection attacks.
The proper way to do it would be to use a language parser to completely decompose the user query and then re-generate it as a safe query. Using Regex to match and replace could be a naive approach unless you are a master in Regex and security.