Tuesday, August 11, 2009

Safe SQL LIKE conditions in Ruby On Rails

This is a problem that was driving me a little nuts. In this discussion of how to write a Ruby on Rails query without leaving myself open to a SQL Injection, I found this passage:

"Building your own conditions as pure strings can leave you vulnerable to SQL injection exploits. For example, Client.first(:conditions => "name LIKE '%#{params[:name]}%'") is not safe. See the next section for the preferred way to handle conditions using an array."
However, they never show how to actually write the exact query to replace the unsafe one safely. Here it is:

escaped_query = params[:name].gsub('%', '\%').gsub('_', '\_')
@posts = Post.find(:all, :conditions=> ["subject like :eq or name like :eq", {:eq => "%" + escaped_query + "%"}])

5 comments:

  1. Dear Timothy,

    My compliments. I spent a couple of hours trying to pass a "Like condition" to a "find.all". It about drove me crazy. The RAILS DOCUMENTATION simply says NOTHING about how to incorporate a "like condition" into a "find.all". Very poor documentation.

    Very fine work. How did you figure it out?

    Thanks,

    Ken Wagner (vonhutch@att.net)

    ReplyDelete
  2. Ken, I did this a while back so I'm not sure-- I probably googled it and saw something similar. You're right; the Rails documentation was weak on this. I'm glad this worked out for you!

    ReplyDelete
  3. I think this is a better technique:

    http://asciicasts.com/episodes/25-sql-injection

    ReplyDelete
  4. I could be mistaken, but :conditions => ["subject like :eq or name like :eq", {:eq => "%" + escaped_query + "%"}] is what protects the query from injection. Your gsub before that just allows the user to search for an actual percent and underscore in the string rather than accidentally using them for their special search pattern matching, with the caveat that allowing them to do arbitrary pattern matching does indeed allow them to write queries that are potentially very resource intensive, which is itself a potential attack. It just doesn't, as far as I can tell, allow injection. Maybe that's what you meant to get across, but it wasn't obvious if that's the case.

    ReplyDelete
    Replies
    1. Thanks! It's hard to believe I wrote this five years ago-- to be honest, I haven't worked in Ruby on Rails at all lately; I'm finishing up an MS in Stats and that's taking all my time. I'll have to have a look at this in a few months after I graduate! Thanks again.

      Delete

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.