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 + "%"}])
Dear Timothy,
ReplyDeleteMy 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)
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!
ReplyDeleteI think this is a better technique:
ReplyDeletehttp://asciicasts.com/episodes/25-sql-injection