Rails宝典之第二十五式: Sql injection

Sql injection是老问题，对如下查询:

def index
  @tasks = Task.find(:all, :conditions => "name LIKE '%#{params[:query]}%'")
end

当用户输入的query条件加上单引号时很容易通过sql injection来攻击我们的Rails程序
而我们使用如下查询方式就可以避免sql注入问题:

def index
  @tasks = Task.find(:all, :conditions => ["name LIKE ?", '%' + params[:query] + '%'])
end

为什么?
先来看看active_record文档里的一段话:

  # == Conditions
  #
  # Conditions can either be specified as a string, array, or hash representing the WHERE-part of an SQL statement.
  # The array form is to be used when the condition input is tainted and requires sanitization. The string form can
  # be used for statements that don't involve tainted data. The hash form works much like the array form, except
  # only equality and range is possible. Examples:
  #
  #   class User < ActiveRecord::Base
  #     def self.authenticate_unsafely(user_name, password)
  #       find(:first, :conditions => "user_name = '#{user_name}' AND password = '#{password}'")
  #     end
  #
  #     def self.authenticate_safely(user_name, password)
  #       find(:first, :conditions => [ "user_name = ? AND password = ?", user_name, password ])
  #     end
  #
  #     def self.authenticate_safely_simply(user_name, password)
  #       find(:first, :conditions => { :user_name => user_name, :password => password })
  #     end
  #   end
  #
  # The <tt>authenticate_unsafely</tt> method inserts the parameters directly into the query and is thus susceptible to SQL-injection
  # attacks if the <tt>user_name</tt> and +password+ parameters come directly from a HTTP request. The <tt>authenticate_safely</tt>  and
  # <tt>authenticate_safely_simply</tt> both will sanitize the <tt>user_name</tt> and +password+ before inserting them in the query, 
  # which will ensure that an attacker can't escape the query and fake the login (or worse).

OK，第一种是不安全的，后两者都是安全的。因为后两者都会使用sanitize方法来escape查询条件。

评论

4 楼 xxj 2007-08-01  

呵呵，peepcode的视频是收费的，他首页上只是一个preview：）

o(∩_∩)o...谢谢推荐，我看看去

3 楼 hideto 2007-08-01  

@xxj
谢谢你分享，peepcode首页上也有下载，不过railscasts这个系列比较基础，我写完这个系列再去看peepcode吧。

p.s. Verycd上看到《奋斗》了，很好看的电视剧哦

2 楼 xxj 2007-08-01  

peepcode的视频：http://lib.verycd.com/2007/05/10/0000149317.html

幸好不在论坛上，不然肯定被隐藏了

1 楼 xxj 2007-08-01  

兄台真是高产 一天好几篇，呵呵

在观看视频的时候还有这么一份好文章解读真的不错，多谢了!

提个建议：
rorcast的系列都是短小入门的视频，pepcode的一般都有一个多小时左右的教程。推荐那个:)