Postgres Querying ILIKE and "%#{}%"

Question

Can I get an explanation to me how the "?" prevents sql injection?

Candy.where("taste ILIKE ?", "%#{term}%")

Also why is "%{term}%" used as opposed to just #{term}? What do the percentages represent?

Answer 1

Percentages are wild card characters that matches the string in any part of the value

Answer 2

You've actually asked two different questions there.

Neither of them are particularly related to Rails, so I am going to answer them generically (also because I'm not that familiar with Ruby!).

How does using '?' prevent SQL Injection

SQL Injection occurs when you use values provided from outside your program - user provided values - directly in SQL statements. For example, suppose you had this pseudo code:

sql="SELECT foo FROM bar WHERE name='"+name+"'"

where perhaps name was a variable containing user inputted data. However, if name contained a single quote (') then the SQL engine would think that single quote was the end of the value and continue parsing the remainder of the variable as SQL text.

Using placeholders (such as '?') avoid this because the value inside the placeholder does not need to be quoted - all of the content of the placeholder is treated as part of the value, none of it will be parsed as SQL, regardless of any embedded quotes.

Incidentally, the actual form of the placeholders used is somewhat dependent on the actual DB engine used and/or the client framework. Natively, Postgresql uses the $1, $2, etc for placeholders. Many frameworks extend this to allow '?' and other placeholder syntaxes.

Why is "%#{term}%" used as opposed to just #{term}

The SQL ILIKE operator uses '%' signs as wildcards. An expression such as:

taste ILIKE '%apple%'

would match 'apple', 'rottenApple', 'applesauce' or any other string containing 'apple' using a case insensitive match.

Note that the '%' signs are part of the right hand operand to ILIKE, within the quotes, so you cannot use placeholders like this:

Candy.where("taste ILIKE %?%", "#{term}")

An alternative would be:

Candy.where("taste ILIKE '%' || ? || '%'", "#{term}")

That works because || is the concatenation operator, so it concatenates the literal value % with the value of the placeholder and then the trailing literal value %.

Answer 3

When you use ? Rails will escape SQL control characters by itself.