sanitize_sqlを使う。 - ザリガニが見ていた...。

ひとまず、人情的に取り計らってくれるフィルターになったが、まだ重大な欠陥がある。それは、悪意のあるフィルター文字列を入力する人に対して、無防備なのだ。

例えば、今の状態で以下のようにやってみると...（オレンジ色の文字がフィルター文字列として入力された部分）: ファイル　が　' OR id LIKE '%　...と同じ
出来上がったSQL文字列はこのようになる: "( file_name LIKE '' OR id LIKE '%' )"

ヤバいです。全てのデーターを検索可能な状態だ...。といっても、csv_serverは現状、ファイル名リストには閲覧制限をかけていないので、今はまだ問題ないのだが、予定外のことを許す環境は、作り手として不安が残る...。

安全なSQLとして出力するためには、findやpaginateのconditionsオプションを指定する時には、以下のようにやれば良いのだが、それ以外の場所ではどうするべきか悩んだ。


conditions => ["file_name LIKE ?", "
#FF9900;">' OR id LIKE '%"]

sanitize_sqlの利用

Railsにはsanitize_sqlというメソッドがあることを知り、さっそく使ってみることにしたが、なんと、コントローラーでは使えないのだ...。sanitize_sqlは、ActiveRecord::BaseのProtectedなクラスメソッドとして定義されていた。なるほど、Railsでは安全なSQLに変換するのは、モデルの担当になっているのか。それでは、human_compair_sql、start_at_period、end_at_periodメソッドは、モデルに書き直すことにする。ちょうど、filter_sqls変換テーブルと連携するためのFilterSqlモデルが存在するので、それを利用した。

app/models/filter_sql.rb

気をつけるのは、クラスメソッドとして利用するので、メソッド定義はdef self.human_compair_sql(column, item, id)のように「self.」を追記したこと。
オレンジ色の部分が追記したところ。~~ほとんど変更なしだ。~~考え直して修正した。
- 今まで数字2桁までの入力は「月」と認識して変換していたが、「日」と認識することにした。「/」で終わる時は「月」と認識する。
  - 例：4 　→　2007/04/04 00:00:00〜2007/04/04 23:59:59
  - 例：4/　→　2007/04/01 00:00:00〜2007/04/30 23:59:59
- 日付の区切り文字に「-」も許していたつもりが、うまく機能していなかったので修正した。
- よく使いそうな日付のマッチを先に行うことにした。（時間のマッチは後回し）

class Filtersql < ActiveRecord::Base
  has_and_belongs_to_many :filtersets
  
  def self.human_compair_sql(column, item, id)
    f = Filtersql.find(id)

    # 必ず、option = 、item = の順に実行すること。
    # eval("AND '#{end_at_period(item)}'")の場合はitemの値に影響されるため。
    option = eval(f.option || '')
    item = eval(f.item || '') || item || '%'

    #"(#{f.not_op} #{column} #{f.operator} '#{f.wild1}#{item}#{f.wild2}' #{option})"
    sql = "(#{f.not_op} #{column} #{f.operator} ? #{option})"
    sanitize_sql([sql, "#{f.wild1}#{item}#{f.wild2}"])
  end

  # 日付を表現する文字列を、期間と認識して、その期間の始まりを'%Y-%m-%d %H:%M:%S'形式で返す。
  def self.start_at_period(str)
    str.gsub!(/-/, '/')
    case str
    when /^\s*\d{1,2}\s*$/ #日 3
      Time.parse("#{Time.now.month}/#{str}")
    when /^(\d{4}[\/\-])?\d{1,2}[\/\-]\d{1,2}\s*$/ #日 ~3/3
      Time.parse(str)
    when /^\d{4}[\/\-]?$/ #年 2007
      Time.parse(str.sub(/[\/\-]$/,'') + '/1/1')
    when /^(\d{4}[\/\-]?)?\d{1,2}[\/\-]?$/ #月 ~3/, 2007/3
      Time.parse(str.sub(/[\/\-]$/,'') + '/1')
    when /(^|\s)\d{1,2}:\d{1,2}:\d{1,2}\s*$/ #秒 ~7:57:05
      Time.parse(str)
    when /(^|\s)\d{1,2}:\d{1,2}\s*$/ #分 ~7:57
      Time.parse(str)
    when /(^|\s)\d{1,2}:$/ #時 ~7:
      Time.parse(str + '0')
    else
      #Time.parse(str)
    end.strftime('%Y-%m-%d %H:%M:%S') rescue nil
  end

  # 日付を表現する文字列を、期間と認識して、その期間の終わりを'%Y-%m-%d %H:%M:%S'形式で返す。
  def self.end_at_period(str)
    str.gsub!(/-/, '/')
    case str
    when /^\s*\d{1,2}\s*$/ #日 3
      Time.parse("#{Time.now.month}/#{str}").tomorrow - 1.second
    when /^(\d{4}[\/\-])?\d{1,2}[\/\-]\d{1,2}\s*$/ #日 ~3/3
      Time.parse(str).tomorrow - 1.second
    when /^\d{4}[\/\-]?$/ #年 2007
      Time.parse(str.sub(/[\/\-]$/,'') + '/1/1').next_year - 1.second
    when /^(\d{4}[\/\-]?)?\d{1,2}[\/\-]?$/ #月 ~3/, 2007/3
      Time.parse(str.sub(/[\/\-]$/,'') + '/1').next_month - 1.second
    when /(^|\s)\d{1,2}:\d{1,2}:\d{1,2}\s*$/ #秒 ~7:57:05
      Time.parse(str)#.since(1)
    when /(^|\s)\d{1,2}:\d{1,2}\s*$/ #分 ~7:57
      Time.parse(str).since(1.minute) - 1.second
    when /(^|\s)\d{1,2}:$/ #時 ~7:
      Time.parse(str + '0').since(1.hour) - 1.second
    else
      #Time.parse(str)
    end.strftime('%Y-%m-%d %H:%M:%S') rescue nil
  end
end

app/controllers/csvs_controller.rb: コントローラーは以下のようになる。FilterSql.を追記しただけ。

class CsvsController < ApplicationController
...（途中省略）...
  def list
...（途中省略）...
    # :conditionsの設定
    @filter_column = params[:filter_column] || @column_titles[0]
    filter_item = params[:filter_item]
    filter_id = params[:filter_id] || 20
    @conditions = FilterSql.human_compair_sql(@filter_column, filter_item, filter_id)
...（途中省略）...

結果確認

以上で出力されるSQLを確認してみると...: "( file_name LIKE ''' OR id LIKE ''%' )"

キーボードから入力したシングルクォーテーションは、二重になり、単なる文字列として検索されることになっている。（sqlite3の場合）

うまくいった！それに、SQLに変換する仕事はFilterSqlモデルが担当することになったので、今後は同じような変換が必要な時には、このモデルをコピーすれば簡単に対応できる！ついでなので、db:fixtures:saveを実行して、現在のfilter_sqlsテーブルの状態を保存しておいた。