目次
この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。
rails MySQLでの文字列エスケープ
■ この記事について
今回はセキュリティ系のネタです。
Ruby on RailsではActiveRecord内部で文字列のエスケープを行うため、
SQLインジェクション対策を気にする場面が少ないかもしれません。
しかし、希にSQLのすべてや一部を生で記述する必要があったりして
文字列のエスケープに気を遣わなければならないこともあります。
そのときになって慌てないためにも、
今一度MySQLでの文字列エスケープについて考えてみたいと思います。
■ エスケープ・クォート例
まずはMySQLでの文字列のエスケープ/クォートでの囲み方の一部について例をあげます。
mysql> select 'string \'' AS `name```;
+----------+
| name` |
+----------+
| string ' |
+----------+
1 row in set (0.00 sec)
上記のSQLでは文字列リテラル「string’」を出力しています。
リテラルはシングルクォート「’」 またはダブルクォート「”」で囲み、
文字列中に含まれるクォートやバックスラッシュ「\」の前に
バックスラッシュ「\」をつけてエスケープします。
AS句でカラム名の別名をつけますが、この別名はバッククォート「`」で囲みます。
別名中にバッククォートが含まれる場合はバックスラッシュ「\」ではなく、
バッククォート「`」をふたつ続けてかきます。
mysql> SELECT `name` FROM `some_table`;
<結果省略>
上記のSQLではテーブル some_table からすべてのレコードのnameカラムを出力しています。
カラム名・テーブル名ともにバッククォート「`」で囲みます。
文字中にバッククォートが含まれる場合はバッククォート「`」をふたつ続けてかきます。
なおSELECT句の「`name`」は「’name’」としてはいけません。
「’name’」とした場合リテラルとみなされ、
レコード数だけ文字列「name」が出力されてしまいます。
mysql> SELECT `name` FROM `some_table` WHERE `name` LIKE '\%abc\_%';
<結果省略>
上記のSQLではテーブル some_tableからnameカラムが「%abc_」で前方一致するレコードのnameカラムを出力しています。
LIKEの後ろにくる「’\%abc\_%’」は文字列リテラルですが、
パーセント記号「%」やアンダースコア「_」はそのまま書くとワイルドカードとして機能します。
そのためパーセント記号「%」やアンダースコア「_」自体をヒットさせたい場合は
それぞれの文字の前にバックスラッシュ「\」をつけてエスケープします。
これを忘れると強制的に前方一致させるつもりでも、
入力値によっては部分一致検索が可能となってしまいます。
mysql> SELECT `name` FROM `some_table` ORDER BY `name` ASC;
<結果省略>
上記のSQLではテーブル some_tableからすべてのレコードのnameカラムをnameカラムの昇順で出力しています。
ORDER BY句でもカラム名はバッククォート「`」で囲むパターンです。
カラム名に続く ASC or DESCについては定数のようなもので、エスケープやクォーティングは行いません。
ASC or DESCに文字列を入力する場合は入力する文字列がASCかDESCのいずれかのみを入力させるよう注意する必要があります。
■ まとめ
以上複雑なようですが、エスケープ・クォートの方法は以下4パターンになると思います。
railsでの実装例とともにまとめてみます。
文字列リテラルパターン
エスケープ・クォート方法
シングルクォート「’」 またはダブルクォート「”」で囲み、
文字列中に含まれるクォートやバックスラッシュ「\」の前に
バックスラッシュ「\」をつけてエスケープする。
railsでの実装例
str = "test'"
puts ActiveRecord::Base.connection.quote(str)
#「'test\''」と出力
LIKE文字列リテラルパターン
エスケープ・クォート方法
文字列リテラルパターンの方法に加えて
パーセント記号「%」やアンダースコア「_」の前に
バックスラッシュ「\」をつけてエスケープする。
railsでの実装例
str = "%_test"
puts "'%s'" % ActiveRecord::Base.connection.quote_string(str).gsub(/([%_])/){"\\" + $1}
#「'\%\_test'」と出力
カラム・テーブル名パターン
エスケープ・クォート方法
カラム名・テーブル名ともにバッククォート「`」で囲みます。
別名中にバッククォートが含まれる場合はバッククォート「`」をふたつ続けてかきます。
railsでの実装例
str = "test`"
#rails3.1以上
puts ActiveRecord::Base.connection.quote_column_name(str)
#「`test```」と出力
#rails3.0
puts ActiveRecord::Base.connection.quote_column_name(str.gsub("`", "``"))
#「`test```」と出力
定数パターン(ASC DESCなど)
エスケープ・クォート方法
クォートで囲まない。入力する場合はホワイトリスト方式で厳密にチェックする。
注意が疎かになりがちなのが
カラム・テーブル名パターン
です。
まずバッククォート「`」で囲むのを忘れているケースをよくみます。
また、囲んでいてもrails3.0ではquote_column_nameする前に
文字列中のバッククォート「`」を自前で「“」に変換しなければいけません。
