attr_encryptedされたカラムに対してwhere likeしたかった。

2015-07-09

鶏の骨を食べていたところ、歯の詰め物が砕けました。保険があるうちでよかったですね。

MySQL側で復号することで解決できました。

Ruby - MySQLのAES_ENCRYPT／AES_DECRYPT互換の方式でActiveRecordの属性を透過的に暗号化／復号する - Qiita この記事にしたがった上で

scope :email, ->(search) { where("AES_DECRYPT(encrypted_email, 'KEY_STR') like ?", "%#{search}%") }

でいけました。ほんとうに感謝。

以下顛末。

attr_encryptedが提供してくれるfind_by_hogeは完全一致しかみてくれない

class Sec < ActiveRecord::Base
  attr_encrypted :email,
  key: 'KEY_STR'
end

class CreateSecs < ActiveRecord::Migration
  def change
    create_table :secs do |t|
      t.binary :encrypted_email
    end
  end
end

があるとして

Sec.create!(email: 'akane@example.com')

Sec.find_by_email('akane')                                                                                                                                                                                              
  Sec Load (6.0ms)  SELECT  `secs`.* FROM `secs` WHERE `secs`.`encrypted_email` = x'696f4a636665336a57444e536f794d49736e437453413d3d0a' LIMIT 1
=> nil

そうでしょうとも。

普通のwhereはできない

Sec.where('email like ?', 'akane')                                                                                                                                                                                      
  Sec Load (0.5ms)  SELECT `secs`.* FROM `secs` WHERE (email like 'akane')
Mysql2::Error: Unknown column 'email' in 'where clause': SELECT `secs`.* FROM `secs` WHERE (email like 'akane')
=> #<Sec::ActiveRecord_Relation:0x3fe4f1990c80>

:pensive:

MySQLと暗号化復号手段を合わせ、whereするまえにMySQL側で復号する

Ruby - MySQLのAES_ENCRYPT／AES_DECRYPT互換の方式でActiveRecordの属性を透過的に暗号化／復号する - Qiita この記事にならい、MySQLと暗号化複号の手段を合わせ、where時以外には何も意識せずに済むようにします。

class Sec < ActiveRecord::Base
  attr_encrypted :email,
    :algorithm => "aes-128-ecb",
    :iv => "",
    :key => :generate_key,
    :encode => false

  def generate_key()
    key = "KEY_STR"
    final_key = "\0" * 16
    key.length.times do |i|
      final_key[i % 16] = (final_key[i % 16].ord ^ key[i].ord).chr
    end
    final_key
  end
end

そしてwhere likeにおいては対象のカラムをMySQL側で復号したものを対象とします。

scope :email, ->(search) { where("AES_DECRYPT(encrypted_email, 'KEY_STR') like ?", "%#{search}%") }

5000件ダミーをぶちこんだDBでゴー。

Sec.email('akane').map(&:email)
  Sec Load (0.3ms)  SELECT `secs`.* FROM `secs` WHERE (AES_DECRYPT(encrypted_email, 'KEY_STR') like '%akane%')
=> ["akane@example.com",
 "ootsuka_akane@example.com",
 "okabe_akane@example.com",
 "kitakawa_takane@example.com",
 "matsuoka_takane@example.com",
 "nakane_ryou@example.com",
 "nakane_katsuhisa@example.com",
 "morioka_takane@example.com",
 "nakane_ryouko@example.com",
 "iwata_akane@example.com",
 "ooya_akane@example.com",
 "shimoda_takane@example.com",
 "tannba_takane@example.com",
 "tsushima_takane@example.com",
 "uematsu_akane@example.com",
 "kuwata_takane@example.com",
 "terao_takane@example.com",
 "sawajiri_akane@example.com",
 "murata_akane@example.com",
 "ookuma_takane@example.com",
 "mimura_akane@example.com",
 "matsuda_akane@example.com",
 "kawabata_takane@example.com"]

幸せになった。

ところで暗号化っているの？

自分が携わったプロジェクトではすべてが一つのサーバーで完結するものであり、SQLサーバーは外部からの接続を許していませんでした。そんなSQLサーバーにアクセスされた時点でもう試合終わってるんじゃないですか感があった。

なおかつwhereとか自由にならなかったんで、本当に暗号化が必要なのか疑問だったので質問を社の人間に投げたところ、結局社の最高責任者からも明確な回答は得られず、matzに聞けで聞けとか言い出す始末（どんな会社やねん思われるわ）。

暗号化するしないの分岐点がわかる資料などございましたらご教示ください。