Rails 3 LIKE query raises exception when using a double colon and a dot

Question

Hi,

In rails 3.0.0, the following query works fine:

Author.where("name LIKE :input",{:input => "#{params[:q]}%"}).includes(:books).order('created_at')

However, when I input as search string (so containing a double colon followed by a dot):

aa:.bb

I get the following exception:

ActiveRecord::StatementInvalid: SQLite3::SQLException: ambiguous column name: created_at

In the logs the these are the sql queries:

with aa as input:
Author Load (0.4ms)  SELECT "authors".* FROM "authors" WHERE (name LIKE 'aa%') ORDER BY created_at
Book Load (2.5ms)  SELECT "books".* FROM "books" WHERE ("books".author_id IN (1,2,3)) ORDER BY id

with aa:.bb as input:
SELECT DISTINCT "authors".id FROM "authors" LEFT OUTER JOIN "books" ON "books"."author_id" = "authors"."id" WHERE (name LIKE 'aa:.bb%') ORDER BY created_at DESC LIMIT 12 OFFSET 0
SQLite3::SQLException: ambiguous column name: created_at

It seems that with the aa:.bb input, an extra query is made to fetch the distinct author id_s.

I thought Rails would escape all the characters. Is this expected behaviour or a bug?

Best Regards,

Pieter

Answer 1

The "ambiguous column" error usually happens when you use includes or joins and don't specify which table you're referring to:

"name LIKE :input"

Should be:

"authors.name LIKE :input"

Just "name" is ambiguous if your books table has a name column too.

Also: have a look at your development.log to see what the generated query looks like. This will show you if it's being escaped properly.