Why does ActiveRecord generate parameterized queries for most operations, but not for find_by?

Question

I'm working on a basic Rails 4.0 app to learn how it works, and I've run into something that I can't seem to figure out. I've been doing queries to the default Sqlite DB via ActiveRecord, and for most queries, according to the debug output, it seems to generate parameterized queries, like so:

2.0.0-p247 :070 > file.save
   (0.2ms)  begin transaction
   SQL (0.6ms)  UPDATE "rep_files" SET "report_id" = ?, "file_name" = ?, "updated_at" = ?
   WHERE "rep_files"."id" = 275  [["report_id", 3], ["file_name", "hello.jpg"],
   ["updated_at", Mon, 09 Sep 2013 04:30:19 UTC +00:00]]
   (28.8ms)  commit transaction

However, whenever I do a query using find_by, it seems to just stick the provided parameters into the generated SQL:

2.0.0-p247 :063 > file = RepFile.find_by(report_id: "29", file_name: "1.png")
  RepFile Load (6.2ms)  SELECT "rep_files".* FROM "rep_files" WHERE
  "rep_files"."report_id" = 29 AND "rep_files"."file_name" = '1.png' LIMIT 1

It does seem to be escaping the parameters properly to prevent SQL injection:

2.0.0-p247 :066 > file = RepFile.find_by(report_id: "29", file_name: "';")
  RepFile Load (0.3ms)  SELECT "rep_files".* FROM "rep_files" WHERE
  "rep_files"."report_id" = 29 AND "rep_files"."file_name" = ''';' LIMIT 1

However, it was my understanding that sending parameterized queries to the database was considered a better option than trying to escape query strings, since the parameterized option will cause the query data to bypass the database's parsing engine entirely.

So what's going on here? Is this some oddity in the Sqlite adapter or the way that the debug output is generated? If ActiveRecord is actually working like this, is there some reason for it? I can't find anything about this anywhere I've looked. I've started looking through the ActiveRecord code, but haven't figured anything out yet.

Answer 1

If we look at find_by in the source, we see this:

def find_by(*args)
  where(*args).take
end

The take just tacks the limit 1 onto the query so we're left with where. The where method can deal with arguments in various forms with various placeholder formats, in particular, you can call where like this:

where('c = :pancakes', :pancakes => 6)

Using named placeholders is quite nice when you have a complicated query that is best expressed with an SQL snippet or a query that uses the same value several times so named placeholders are quite a valuable feature. Also, you can apply where to the ActiveRecord::Relation that you got from a where call and you can build the final query in pieces spread across several methods and scopes that don't know about each other. So, where has a problem: multiple things that don't know about each other can use the same named placeholder and conflicts can arise. One way around this problem would be to rename the named placeholders to ensure uniqueness, another way is to manually fill in the placeholders through string wrangling. Another problem is that different databases support different placeholder syntaxes. ActiveRecord has chosen to manually fill in the placeholders.

Summary: find_by doesn't use placeholders because where doesn't and where doesn't because it is easier to build the query piecemeal through string interpolation than it is to keep track of all the placeholders and database-specific syntaxes.