システム設計Tweet「SQLアンチパターン」について、自分なりのチェックポイント言語化シリーズ第4弾です。下記の記事の続きとなります。含めたシステム設計全体のお作法に関する話になります。文でパスワードの突き合わせをする電文上でパスワードが晒されることになります。クエリログやバックアップファイルにも平文のパスワードが表示/格納されてしまい、個人情報流出時の被害が大きくなります。サイトがありますが、あの手のサイトは裏側で平文でパスワードを格納しているのだと思います。二つの方式が推奨されていました。ハッシュ化(平文パスワード+ソルト)パスワードにソルトを加え、ハッシュ化して格納すべきです。ハッシュ化するだけでは、何らかの理由でDBに接続されてしまった際に、辞書攻撃をされる可能性があるので、ユーザーごとにソルトを与えた上でハッシュ化することが推奨されていました。);SQLクエリでもハッシュ後パスワードを利用するまた、SQLクエリでも平文パスワードを使わず、アプリケーションサイドでハッシュ化した上で、クエリを投げることも推奨されていました。ユーザIDで検索し、返ってきたソルトを用いてアプリケーション側で入力された平文パスワードをハッシュ化し、クエリを投げる。テーブルの全てのレコードが削除されてしまいます。インジェクションをひとつの方法で完璧に防ぐのは難しく、下記の方法を理解し適切に使い分けるべきと書かれています。フロントエンド、バックエンドで適切にバリデーション処理を行い、不正な入力値をフィルタリングするようにします。文が発行されることはありません。プリペアドステートメントを使った場合、インデックスを含む列へのクエリの最適化がおかしくなる場合があるそうです。その場合、(性能要件で)どうしてもプリペアドステートメントを使えない場合、下記のように引用符で囲む方法も紹介されていました。文を動的に組み立ててリスクを減らす方法も紹介されていました。チームメンバーにコードレビューしてもらうことを徹底する。欠番を割り当てるといった形で埋めにいってしまうのはアンチパターンです。誤ったデータを参照してしまう、ということになりかねません。著者曰く、この「ムズムズ」は疑似キーを行番号と捉えてしまうことが背景になっているとのことです。ページングなどで行番号を利用したい場合は、エンジンごとに別の文法が提供されているので、そちらを利用しましょう。「欠番を埋めない」ことがグッドパターンです。シー・ノー・エビル(臭いものに蓋):DB接続している部分で例外処理しないプログラムはないかアンチパターン例アプリケーションプログラムでDB接続した際に、戻り値の確認や例外処理をしない部分があるのはアンチパターンです。幾つか確認ポイントが紹介されていたので、グッドパターンとして掲載します。グッドパターン例データベース接続時や、SQLクエリ実行の際に、戻り値の確認や例外処理を正しく行う。見られる環境を整備する。実行したSQLをログなどに出力する方式にする。関連のリソースが管理されていないことがある、という章です。集中してしがちで、結果としてそのような状態が生まれてしまうとのことでした。関連の情報がドキュメント化されないこともよくあります。著者のおすすめな進め方の一部をグッドパターン例として掲載します。図などでドキュメント化する。文も必ずソースレビューの対象とする。セキュリティ観点のレビューを行うようにする。関連のリソースもバージョン管理の対象とする。図などもここに含めること。アプリケーションコードとデータベースコードは同じリポジトリにすること。操作を定義するものです。このパターンはテーブルに対するシンプルな接続方法を提供してくれる良いパターンですが、モデルが常にアクティブレコード化する(全てのモデルクラスがアクティブレコードの基底クラスを継承する)場合、下記のような問題が発生するとのことです。本章では、下記の設計をすることで、この問題を回避できると紹介されています。砂の城:発生しうる問題やそれに対する対応はまとまっているか?アンチパターン例システム障害や性能問題が発生したときの想定をしておらず、問題が発生してからドギマギしてしまうのがアンチパターンです。しても目に見える機能の開発が優先され、このような「発生するかもしれないこと」への準備がおざなりになってしまうことがよくありますが、正しく「想定」しておきましょう、というのがこの章の内容です。私も改めて気を付けようと思います。グッドパターン例トラブルは起きるものと捉えて、その想定をしておく。事前に性能テストを行った上で、どの程度までのリクエスト量をさばけるかのベンチマークを取得しておく本番同等の検証環境を1セット用意するバックアップを適切に取るシステムの要件に合わせて高可用性な設計を行う運用ポリシーを作成し、障害発生時のリカバリ手順などを準備しておくまとめ第3弾から少し時間が経ってしまいましたが、第4弾についてまとめました。これまでの3つとは毛色が少し異なり、アプリケーションコーディングの際に意識したいポイントが中心でした。これまでの章についてはこちらをご覧ください。第1弾「DB論理設計」編はこちらです。www.ketancho.net第2弾「DB物理設計」編はこちらです。クエリ」編はこちらです。www.ketancho.net(別途まとめ記事を書くかもしれませんが、)今回の記事で、「SQLアンチパターン」の整理は完了です。非常に勉強になる本でした。この記事をまとめたことで、各章2回以上は読んだことになり(実は、3〜4回読んでいる章が大半ですw)、考え方が定着してきた気がします。設計をする際に、無意識的にグッドパターンを選択できるようになれればと思います。データを格納しないシステムはないといっても過言ではありません。いう意味で、全てのエンジニアにとって必読な本だと思います。#awsblackbelt例によってオンラインセミナーを聴講したので、そのメモです。...2018-04-25「SQLアンチパターン」を避けるためのチェックリスト(サマリー版)これまで「SQLアンチパターン」本のまとめを書いてきました。...2018-03-312018年1月〜3月ふりかえりとKPT2018年1月〜3月について振り返りです。はてなブログPro2児の父なITエンジニアです。好きなサッカーチームは川崎フロンターレです。その他、子育て、社会問題、シビックテック関連の記事についても書いていきたいと思います:)※各ポストは個人の意見であり、所属する組織の見解とは関係ありません。(1)月別アーカイブその他プライバシーポリシースターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。