rails refactoring a query for correct sql injection

Question

this is the original query

where(countries.map{|c| "zones.countries LIKE '%#{c}%'"}.join(' OR '))

now i would rewrite this query with ? operator like this

where(countries.map{|c| "zones.countries LIKE ?", "%#{c}%"}.join(' OR '))

but it doesn't work. Any suggestion? thx

Try this previous SO question: stackoverflow.com/questions/4430578/… — MrDanA
– MrDanA, Commented Jul 12, 2012 at 13:21
@davide - One performance tip for the query you are writing. 1. Looping on the query is not a good idea 2. Using Like clause with multiple OR's will slow down the query. Try to convert that Like to = and fetch the records. This query wont scale on a large data set. Just throwing out this idea let me know ho it sounds to you. — Raghu
– Raghu, Commented Jul 12, 2012 at 13:30

deefour · Accepted Answer · 2012-07-12 13:35:45Z

1

where(countries.map { |c| "zones.countries LIKE ?" }.join(" OR "), *countries.map{ |c| "%#{c}%"})

You can replace the first argument of the where above with something like this to avoid one loop

("zones.countries LIKE ? OR " * countries.size).gsub(/ OR $/, '')

answered Jul 12, 2012 at 13:24

deefour

35.4k7 gold badges99 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1