How do I build a query dynamically without risking SQL injection attacks?

I have an array of names and I would like to build a query testing each name in turn. However, I never know how many names will be in the list at any one time. I could build the query by hand like so:

However, that violates an important security rule and opens the code to SQL injection attacks. How can I avoid that risk?

The secret here is that Model.where accepts an array as its second parameter.

So if you build your query using placeholders, you can then just pass the array through and ActiveRecord will safely substitute them for you.

For example:

We build a list of clauses “lower(name) like ?” – one clause for each name in the list. We then use “join” to insert “OR” operators in between each one as it’s converted to a string. Finally we pass our original list of names in as parameters to be substituted in each of those placeholders.

Easy.

Do you know what to do but not how it works?

Ever wanted to understand why Rails views work the way that they do? Why variables from your controllers are visible inside your views?

Sign up below to get a free 5 part email course on how Ruby on Rails view rendering works and gain a deep understanding of the Rails magic.

We will send you the course, plus the occasional update from this web-site. But no spam, we promise, and it's easy to unsubscribe