ActiveRecord SQLインジェクション クイズ (Rails 7.1.3.4) 2024.10.26 Kaigi on Rails 2024 Koji NAKAMURA (@kozy4324)

こんな経験ありませんか？

No content

order メソッドにユーザー入力を そのまま受け付けていいんだっけ？

ゴール もうコワクナイ、SQL インジェクション！ ● SQL インジェクションの押さえるべき基本を知る ● Rails における正しい防ぎ方を知る

アジェンダ ● 自己紹介 ● SQL インジェクション脆弱性とその一般的な対策 ● SQL インジェクションクイズ ● Brakeman のクイズ回答 ● おわりに

自己紹介

Koji NAKAMURA ● 𝕏: @kozy4324 ● GitHub:@kozy4324 ● Classi株式会社所属 ● Shinjuku.rb所属 ● Kashiwa.rb主催 自己紹介

https://corp.classi.jp/careers/ 「Classi」と「tetoru」は Ruby on Rails で開発されています

https://shinjukurb.connpass.com/event/324989/ 今回 Shinjuku.rb で書いたプロポーザルが採択されました！

https://kashiwarb.connpass.com/ Kashiwa.rb を立ち上げました。こちらもよろしくお願いします！

SQLインジェクション脆弱性と その一般的な対策

SQLインジェクション脆弱性とは データベースと連携したウェブアプリケーションの多くは、利用者からの入 力情報を基に SQL 文（データベースへの命令文）を組み立てています。こ こで、SQL 文の組み立て方法に問題がある場合、攻撃によってデータベー スの不正利用を招く可能性があります。このような問題を「SQL インジェク ションの脆弱性」と呼び、問題を悪用した攻撃を、「SQL インジェクション攻 撃」と呼びます。 安全なウェブサイトの作り方 改訂第７版 https://www.ipa.go.jp/security/vuln/websecurity/about.html

Rails で問題になる SQL 文の組み立て方法 上の危険なコードが検索用のアクションにあり、ユーザーは検索したいプロジェクト名を入力できると します。ここで、悪意のあるユーザーが ' OR 1) -- という文字列を入力すると、以下の SQLクエ リが生成されます。 2つのダッシュ「--」が末尾に置かれると、以後に追加されるクエリがすべてコメントと見なされてしま い、実行されなくなります。そのため、 projectsテーブルからすべてのレコードが取り出されます。 Rails セキュリティガイド - Railsガイド https://railsguides.jp/v7.1/security.html Project.where("name = '#{params[:name]}'") SELECT * FROM projects WHERE (name = '' OR 1) --')

発生しうる脅威 ● データベースに蓄積された非公開情報の閲覧 個人情報の漏えい 等 ● データベースに蓄積された情報の改ざん、消去 ウェブページの改ざん、パスワード変更、システム停止 等 ● 認証回避による不正ログイン ログインした利用者に許可されている全ての操作を不正に行われる ● ストアドプロシージャ等を利用した OS コマンドの実行 システムの乗っ取り、他への攻撃の踏み台としての悪用 等 安全なウェブサイトの作り方 改訂第７版 https://www.ipa.go.jp/security/vuln/websecurity/about.html

☠ どれもサービス継続が困難になるレベルの脅威

● SQL文の組み立ては全てプレースホルダで実装する ○ 静的プレースホルダ プレースホルダのまま SQL 文をコンパイルしておき、データベースエンジン側で値を割り当てる方 式 (Prepared Statement) ○ 動的プレースホルダ アプリケーション側のデータベース接続ライブラリ内で値をエスケープ処理してプレースホルダには め込む方式 ● SQL文の組み立てを文字列連結により行う場合は、エスケープ処理等を行うデータ ベースエンジンのAPIを用いて、SQL文のリテラルを正しく構成する 安全なウェブサイトの作り方 改訂第７版 https://www.ipa.go.jp/security/vuln/websecurity/about.html 一般的な対策方法

ここまでのまとめ ● SQL インジェクション脆弱性とは ○ データベースの不正利用を招く可能性がある問題 ○ サービス継続が困難になるレベルの脅威 ● 一般的な対策方法は 3つ ○ 静的プレースホルダ ○ 動的プレースホルダ ○ エスケープ処理等を行うデータベースエンジンのAPIを用いて、SQL文 のリテラルを正しく構成する

🤔 Rails はどの対策方法をとっている？ 3つの対策方法

SQLインジェクションクイズ

SQL インジェクション脆弱性があるのはどれ？ 󰍹 User.find_by(name: params[:name]) 󰍽 User.where("name = #{params[:name]}") 󰍼 User.order("age #{params[:order]}") 󰍶 User.exists?(params[:id])

シンキングタイム 15秒 🕘

SQL インジェクション脆弱性があるのはどれ？ 󰍹 User.find_by(name: params[:name]) 󰍽 User.where("name = #{params[:name]}") 󰍼 User.order("age #{params[:order]}") 󰍶 User.exists?(params[:id])

󰍹 User.find_by(name: params[:name]) 脆弱性があると思う方 🙋

󰍽 User.where("name = #{params[:name]}") 脆弱性があると思う方 🙋

󰍼 User.order("age #{params[:order]}") 脆弱性があると思う方 🙋

󰍶 User.exists?(params[:id]) 脆弱性があると思う方 🙋

順番にみていきましょう ✅

󰍹 User.find_by(name: params[:name])

👍 SQL インジェクション脆弱性の問題はない

👍 User.find_by(name: params[:name]) ● e.g.) postgresql ドライバの場合 ○ デフォルトでは prepared_statements: true ○ 静的プレースホルダ (prepared statement) で SQL 文が組み立てられる ● e.g.) mysql2 ドライバの場合 ○ デフォルトでは prepared_statements: false ○ リテラルはエスケープ処理されて SQL 文が組み立てられる irb(main):001> User.find_by(name: "' OR 1 --") User Load (0.8ms) SELECT "users".* FROM "users" WHERE "users"."name" = $1 LIMIT $2 [["name", "' OR 1 --"], ["LIMIT", 1]] irb(main):001> User.find_by(name: "' OR 1 --") User Load (1.6ms) SELECT `users`.* FROM `users` WHERE `users`.`name` = '\' OR 1 --' LIMIT 1 postgresql mysql2

󰍽 User.where("name = #{params[:name]}")

☠ SQL インジェクション脆弱性の問題がある

☠ User.where("name = #{params[:name]}") ● SQL フラグメントはそのまま SQL 文へ組み込まれる ● 位置指定ハンドラ or 名前付きハンドラを使用する必要がある ○ User.where("name = ?", params[:name]) ○ User.where("age > :age", { age: params[:age] }) irb(main):001> User.where("name = '' OR true") User Load (3.3ms) SELECT "users".* FROM "users" WHERE (name = '' OR true) /* loading for pp */ LIMIT $1 [["LIMIT", 11]] postgresql

🔑 安全かどうかを見極めるポイント ● SQL 文のリテラルと1対1に対応する値は原則的に安全 ○ 静的プレースホルダ (prepared statement) で適用されるか、もしくは SQL 文 のリテラルとしてエスケープできることが自明 irb(main):001> User.find_by(name: "' OR 1 --") User Load (0.8ms) SELECT "users".* FROM "users" WHERE "users"."name" = $1 LIMIT $2 [["name", "' OR 1 --"], ["LIMIT", 1]] postgresql irb(main):001> User.find_by(name: "' OR 1 --") User Load (1.6ms) SELECT `users`.* FROM `users` WHERE `users`.`name` = '\' OR 1 --' LIMIT 1 mysql2 irb(main):013> User.where("name = ?", "' OR 1 --") User Load (2.4ms) SELECT "users".* FROM "users" WHERE (name = ''' OR 1 --') /* loading for pp */ LIMIT $1 [["LIMIT", 11]] postgresql

● SQL フラグメントにおいて、位置指定ハンドラ or 名前付きハンドラを利 用していないものは原則的に安全ではない ○ エスケープすべき部分文字列がどこからどこまでかが自明ではない ○ ActiveRecord はエスケープ処理をしない（できない） 🔑 安全かどうかを見極めるポイント irb(main):001> User.where("name = 'joe' AND age > 20") User Load (1.2ms) SELECT `users`.* FROM `users` WHERE (name = 'joe' AND age > 20) /* loading for pp */ LIMIT 11 mysql irb(main):001> User.where("name = '' OR true") User Load (3.3ms) SELECT "users".* FROM "users" WHERE (name = '' OR true) /* loading for pp */ LIMIT $1 [["LIMIT", 11]] postgresql

󰍼 User.order("age #{params[:order]}")

👍 SQL インジェクション脆弱性の問題はない

👍 User.order("age #{params[:order]}") ● リテラルと1対1に対応するケースではない ○ SQL フラグメントのケースのように見えるが... ● 許可された文字列パターンを満たさない場合は例外が発生する ● 例外により SQL インジェクション攻撃は成立しない irb(main):001> User.order("age desc; SELECT * FROM users;--") (irb):1:in `': Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "age desc; SELECT * FROM users;--".This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql(). (ActiveRecord::UnknownAttributeReference) postgresql

👍 User.order("age #{params[:order]}") ● 例えば複雑な式を使ったソートがしたい場合はどうする？ ● Arel.sql() でラップすれば良い irb(main):001> User.order("updated_at - created_at desc") (irb):1:in `': Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "updated_at - created_at desc".This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql(). (ActiveRecord::UnknownAttributeReference) postgresql irb(main):002> User.order(Arel.sql("updated_at - created_at desc")) User Load (4.0ms) SELECT "users".* FROM "users" /* loading for pp */ ORDER BY updated_at - created_at desc LIMIT $1 [["LIMIT", 11]] postgresql

󰍶 User.exists?(params[:id])

☠ SQL インジェクション脆弱性の問題がある

● exists? は引数を受け取るバリエーションが複数ある ○ Integer - Finds the record with this primary key. ○ String - Finds the record with a primary key corresponding to this string (such as '5'). ○ Array - Finds the record that matches these where-style conditions. ○ Hash - Finds the record that matches these where-style conditions. ○ false - Returns always false. ○ No args - Returns false if the relation is empty, true otherwise. ● Array の場合は SQL フラグメントを受け付ける ○ e.g.) Person.exists?(['name LIKE ?', "%#{query}%"]) ● 一方 params[:id] はクエリストリング次第で Array の値もとる ○ ?id[]=1%3D1 → Parameters: {"id"=>["1=1"]} ☠ User.exists?(params[:id])

☠ User.exists?(params[:id]) class UsersController < ApplicationController def index @exists = User.exists?(params[:id]) end end app/controllers/users_controller.rb Started GET "/users/index?id[]=1%3D1" for ::1 at 2024-10-15 09:03:07 +0900 Processing by UsersController#index as HTML Parameters: {"id"=>["1=1"]} User Exists? (1.5ms) SELECT 1 AS one FROM "users" WHERE (1=1) LIMIT $1 [["LIMIT", 1]] : Completed 200 OK in 14ms (Views: 11.0ms | ActiveRecord: 1.4ms | Allocations: 1892) log/development.log

● SQL フラグメントを受け取るメソッドを一定把握する必要がある ○ e.g.) calculate, find_by, from, group, having, joins, select, where, etc… ○ https://rails-sqli.org/ にカタログされているので一読しておくと良い ● クエリメソッドへ外部入力（特に params ）を雑に渡すな！！！ ○ エスケープが確実に適用される形式を選択する ○ もしくは渡される値が制限されるような入力値チェックを行う 🔑 exists? のケースから学べること

SQL インジェクションクイズ結果 👍 User.find_by(name: params[:name]) ☠ User.where("name = #{params[:name]}") 👍 User.order("age #{params[:order]}") ☠ User.exists?(params[:id])

ActiveRecord のエスケープ処理 ● 内部では quote メソッドが呼ばれる ○ ActiveRecord::ConnectionAdapters::PostgreSQL::Quoting#quote ■ pg gem の #escape ● libpg の PQescapeStringInternal 関数 ○ ActiveRecord::ConnectionAdapters::Mysql2Adapter#quote ■ mysql2 gem の #escape ● libmysql の escape_string_for_mysql 関数 ● データベースエンジンの API を使っていることが確認できる

sanitize_sql_array メソッド ● 位置指定ハンドラの I/F がないメソッドの場合にどうするか？ ○ e.g.) User.joins("INNER JOIN books b ON b.type = '#{type}'") ● sanitize_sql_array メソッドを使う ○ e.g.) User.joins(User.sanitize_sql_array(["INNER JOIN books b ON b.type = ?", type])) ● sanitize_sql_like など状況別に利用できる API が用意されている ○ API リファレンスを確認しておくと良い

1. 静的プレースホルダ 2. 動的プレースホルダ 3. エスケープ処理等を行うデータベースエンジンの API を用いて、 SQL 文を正しく構成する ● Rails 内部 (Rails → DBドライバ) において ○ 静的プレースホルダを使う設定においては「 1.」 ○ 静的プレースホルダを使わない設定や位置指定ハンドラなどにおいては「 3.」 ● Rails が提供する I/F (Rails ユーザー → Rails) において ○ 静的プレースホルダを使う設定にすることで「 1.」 ○ 位置指定ハンドラや snitize_sql_array といった I/F が提供されている「2.」 Rails はどの対策方法をとっていた？

💯 Rails では3つの対策方法を使い分けていた

● SQL フラグメントを受け取るメソッドを一定把握する ● クエリメソッドへ外部入力（特に params ）を雑に渡さない ● クエリメソッドへ外部入力を渡す時は以下のいずれかを行う ○ エスケープが確実に適用される形式を選択する ○ 位置指定ハンドラ or 名前付きハンドラを利用する ○ sanitize_sql_array などの sanitize メソッドを組み合わせて利用する ○ 渡される値が制限されるような入力値チェックを行う Rails での SQL インジェクションの防ぎ方

Brakeman のクイズ回答

https://brakemanscanner.org/

Brakeman とは？ ● Ruby on Rails Static Analysis Security Tool ● Rails 7.2 からはデフォルトで含まれる ○ rails new すると Gemfile に含まれる ● コードをスキャンして脆弱性疑いをレポートしてくれる ● 多くの種類の脆弱性をレポートしてくれる ○ e.g.) XSS, コマンドインジェクション, オープンリダイレクト, etc…

== Warning Types == No warnings found Brakeman のクイズ回答 1⃣ 👍 User.find_by(name: params[:name]) == Warnings == Confidence: High Category: SQL Injection Check: SQL Message: Possible SQL injection Code: User.where("name = #{params[:name]}") File: app/controllers/users_controller.rb Line: 4 2⃣ ☠ User.where("name = #{params[:name]}") == Warning Types == No warnings found 3⃣ 👍 User.order("age #{params[:order]}") == Warnings == Confidence: High Category: SQL Injection Check: SQL Message: Possible SQL injection Code: User.exists?(params[:id]) File: app/controllers/users_controller.rb Line: 6 4⃣ ☠ User.exists?(params[:id])

💯 全問正解

Brakeman はどのようにスキャンしている？ ● ソースコード → AST（抽象構文木） → パターンチェック ○ Rails バージョンごとのチェック対象メソッドが網羅されている

🎉 SQL フラグメントを受け取るメソッドを 人間が頑張って全て覚える必要がない！

注意点: 偽陽性は多い ● 「非常に疑わしい (extremely suspicious) 」というスタンス ● Brakeman のレポートがオオカミ少年にならないように ○ 特定の warning を無視する設定を追加して運用することも検討する ○ 詳細はドキュメントを参考ください ■ Ignoring False Positives - https://brakemanscanner.org/docs/ignoring_false_positives/

おわりに

もうコワクナイ、SQL インジェクション！ ● 一般的な対策方法 ○ 静的プレースホルダ ○ 動的プレースホルダ ○ エスケープ処理等を行うデータベースエンジンの API を用いて SQL 文を正しく構成する ● Rails における SQL インジェクションの正しい防ぎ方 ○ SQL フラグメントを受け取るメソッドを一定把握する ○ クエリメソッドへ外部入力（特に params ）を雑に渡さない ○ クエリメソッドへ外部入力を渡す時は以下のいずれかを行う ■ エスケープが確実に適用される形式を選択する ■ 位置指定ハンドラ or 名前付きハンドラを利用する ■ sanitize_sql_array など適切な sanitize_sql_* メソッドを利用する ■ 渡される値が制限されるような入力値チェックを行う ● Brakeman も活用しよう！ ○ 人間が頑張って覚えなければいけないことが減る

Appendix: 参考リンク ● 安全なウェブサイトの作り方 | 情報セキュリティ | IPA 独立行政法人 情報処理推進機構 https://www.ipa.go.jp/security/vuln/websecurity/about.html ● Rails セキュリティガイド - Railsガイド https://railsguides.jp/v7.1/security.html ● Rails SQL Injection Examples https://rails-sqli.org/ ● Brakeman https://brakemanscanner.org/

EOF