SQL LIKEのセキュリティ、パフォーマンス、ベストプラクティスの最適化

記事

Toshihiko Minamoto · 19 hr 前 10m read

前回の記事、SQLでの変数やパターンを使ったLIKEの使用では、組み込みSQLから動的SQLまで、さまざまなシナリオでのLIKE述語の動作や、ワイルドカードや変数を使った場合のパフォーマンスへの影響について解説しました。前回の記事では、動作するLIKEクエリを書くことに慣れることをテーマにしました。しかし、SQLを動かすことができるようになるのは、あくまで出発点に過ぎません。信頼性が高く、スケーラブルで安全なアプリケーションを構築するには、LIKEを使ったクエリを含む、すべてのSQLに共通するベストプラクティスを理解する必要があります。

この記事では、次のステップに進みます。 SQLコードを強化し、ありがちな間違いを避け、SELECTステートメント文が正しく動作するだけでなく、効率的かつ安全に実行されるようにするためのいくつかの重要なポイントを見ていきます。途中で、LIKE述語を使ったSELECTステートメントを例として取り上げ、これらの基本原則がどのようにクエリやその結果に直接影響するかを説明します。

*これは、Geminiがこの記事のために生成したものです。いい感じです。

安全性とセキュリティ

まず第一に、高性能なアプリケーションも、安全でなければ意味がありません。ユーザーによる入力でLIKEを使用する場合、最も重大な脅威はSQLインジェクションです。これにより、結果の整合性やデータベース全体のセキュリティが脅かされる可能性があります。

念のために補足すると、SQLインジェクションは、現在A03:2021 – Injectionとしてランク付けされており、2021年OWASPトップ10リストで第3位に位置しています。 2021年OWASPアップデートでトップから第3位に下がりましたが、この脅威は依然として重要で影響力があり、最も一般的な問題ではないにせよ注意が必要です。もう一つの変更点は、対象範囲がSQLインジェクションだけでなく、SQL、NoSQL、OSコマンドなど一般的なインジェクション全般に拡大されたことです。テストされたアプリケーションの94%で検出され、平均発生率は3.37%となっており、よく見られる脆弱性領域のひとつと言えます。

では、どうやってこの脅威から身を守ることができるでしょうか？先日、コミュニティのメンバーのひとりが、このまさに今回の脆弱性に関するコードサンプルを投稿して質問していました。ユーザの入力をクエリ文字列の一部として直接渡していたため、攻撃者はクエリに悪意のあるSQLコードを注入して改ざんし、不正アクセスやデータ変更を行う可能性がありました。

この問題については、いくつかの記事で取り上げられています。最近では、私のフランス語での記事（英語に翻訳していないことをすっかり忘れていました👼）や、@Ash.Sherzhanovの英語での記事があります。手短に言えば、ユーザー入力をSQLステートメントに直接組み込むのはやめましょう。つまり、このようなことはしないでください。

 SET input = "Smith"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE '%"_input_"%'")
 SET rs = statement.%Execute()
 DO rs.%Display()

この場合、攻撃者は入力を使って悪意のあるコード部分を渡します。例えば、次のとおりです。

 SET input = "' or 1=1 --"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE '%"_input_"%'")
 SET rs = statement.%Execute()
 DO rs.%Display()

要するに、攻撃者は事前知識がなくても、銀行の顧客に関するすべての情報を取得できてしまいます。

代わりに、プレースホルダー「?」を使用します。

 SET input = "Smith"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE ?")
 SET rs = statement.%Execute("%"_input_"%")
 DO rs.%Display()

ベストプラクティス：

事前に準備されたパラメーターを使用する。クエリ構造と入力値を分けることで、攻撃者による悪意のあるSQLコードの注入を防ぎます。動的SQLでは、プレースホルダー「?」です。組み込みSQLでは、「:」に続く名前付きパラメーターを使用します。
ユーザー入力の検証。 SQLクエリに挿入する前にユーザー入力を検証することも、重要な予防策のひとつです。正規表現や適切な検証関数を使って、データの有効性を確認しましょう。
クラスの説明でパターンを使用する。これは前のポイントと似ていますが、新しいインスタンスを追加する際に入力を検証でできます。
特殊文字のフィルタリング。SQLを注入するために使用できる特殊文字を除外することも、攻撃を防ぐのに役立ちます。潜在的に危険な文字を除去またはエスケープするために、適切なフィルタリング関数を使用しましょう。
ストアドプロシージャを使用する。ストアドプロシージャは、SQLステートメントのセットをカプセル化したデータベースオブジェクトで、効率的な実行と再利用を可能にします。ストアドプロシージャを使うことで、アプリケーションのロジックを一元化し、SQLインジェクションのリスクを減らすことができます。
ソフトウェアコンポーネントを定期的に更新する。最新のセキュリティパッチの恩恵を受けるために、InterSystems IRISおよび関連ソフトウェアコンポーネントを定期的にアップデートするようにしましょう。

パフォーマンス

高性能なアプリケーションは、大量のデータや同時接続ユーザーを遅延なく処理できます。パフォーマンスの要はSQLクエリの効率にあります。遅いクエリはタイムアウトしたり、同時実行システムではリソースをロックして一貫性のない結果が生じる可能性があります。 LIKE述語を使ったクエリでは、実行計画の高速と低速の違いにより、ユーザー体験や返されるデータの信頼性が大きく変わってしまいます。

1. 私たちは皆、インデックスがクエリのパフォーマンスを大幅に向上させることをよく知っています。しかし、正しいインデックスを設定するだけでなく、それを効果的に活用することも重要です。LIKEクエリの場合、インデックスの存在や適切な設計、さらにその活用方法によって、データベースが高速な検索を行うか、それとも時間のかかるフルテーブルスキャンになるかが決まります。

上の例を見ると、インデックスを使いにくいことがわかります。LIKEパターンがワイルドカードで始まる場合、標準的なBツリーインデックスでは検索範囲を絞ることができません。データベースは一致する行を見つけるために、テーブル内のすべての行をスキャンせざるを得ません。大きなテーブルの場合、これによりクエリが遅くなり、処理が止まったように見えたり、タイムアウトして結果を取得できなくなることもあります。そのため、このようなクエリは書かないでください。

SELECT * FROM Banking.Person WHERE name LIKE '%Smith%'

これだと、名前（名前と苗字を含む）のどこに「Smith」が見つかっても、テーブルやインデックスのフルスキャンが必要になります。

代わりに、探している文字列の最初の文字がわかっている場合は、先頭にワイルドカード（%や_）を付けずに指定してください。

SELECT * FROM Banking.Person WHERE name LIKE 'Smith%'

さらにおすすめは、LIKEの代わりに%STARTSWITH述語を使うことです。

 SET input = "Smith"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name %STARTSWITH ?")
 SET rs = statement.%Execute(input)
 DO rs.%Display()

この場合、データベースは高速なインデックススキャンを実行できるため、クエリ時間を大幅に短縮できます。これにより、結果を素早く取得できます。

ちょっとした豆知識ですが、デフォルトでは%STARTSWITHとLIKEの文字列比較は大文字小文字を区別しません。つまり、次のクエリは同じ結果を返します。

 Set input = "smith%"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE ?")
 SET rs = statement.%Execute(input)
 DO rs.%Display()

2. クエリのパフォーマンスに影響を与えるもうひとつの方法は、文字列プロパティに適切な照合順序を使用することです。IRISにはいくつかの照合順序が用意されており、最も一般的なのは以下のとおりです。

SQLUPPER: ほとんどの文字列データ型のデフォルトの照合順序で、比較する前に列の値とパターンの両方を内部的に大文字に変換して、大文字小文字を区別せずに比較を行います。便利ですが、この内部操作により、直接的なバイナリ比較と比べると若干のパフォーマンス上のオーバーヘッドが発生する可能性があります。
SQLSTRING：この照合順序は、大文字小文字を区別する正確なバイナリ比較を行います。大文字小文字を区別する必要がある等価比較やLIKE比較は、内部変換ステップを避けるため、より高速になることが多いです。

大文字小文字の区別や特定の言語に基づく並べ替えが必要な場合は、列に照合順序を明示的に定義するか、LIKE句でSQLSTRINGを使用してください。これにより、データベースが予期しないまたは非効率な大文字小文字を区別しない比較を実行するのを防ぐことができます。

何らかの理由でプロパティ自体に照合順序を適用できない、あるいは適用したくない場合は、常にSQL照合順序関数である%SQLSTRINGを使用できます。これは、大文字小文字を区別する文字列として並べ替えられる形式に式を変換します。%SQLSTRINGは文字列の末尾の空白（スペース、タブなど）を削除した後に、文字列の先頭に1つの空白を追加します。

そのため、すべての「Smith」を見つけたい場合で、「smith」や「sMith」などは含めたくない場合は、SQLステートメントで直接%SQLSTRINGを使用できます。

 SET input = "Smith%"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE %SQLSTRING name LIKE %SQLSTRING ?")
 SET rs = statement.%Execute(input)
 DO rs.%Display()

この例は、%SQLSTRING形式を使用して、この比較を大文字小文字を区別する比較にしています。「Smith」で始まるすべての名前を返します。この場合、ステートメントの両側に%SQLSTRING照合順序を適用する必要があることに注意してください。ただし、列自体がすでにクラスプロパティでSQLSTRING照合順序に設定されている場合は、列はすでに望ましい照合順序になっているため、比較対象となるリテラルや変数に%SQLSTRINGを適用する必要があるだけです。列がSQLUPPER（デフォルト）の場合、両側に%SQLSTRINGを適用することで、列のデフォルト設定を正しく上書きできます。

ベストプラクティス：

ワイルドカードに注意する。可能であれば、検索文字列の先頭のシンボルとしてワイルドカードを使用することを避けてください。
照合順序を最大限に活用する。よく使うクエリを念頭に置き、照合順序をクラス定義に直接設定するか、クエリ内で設定するようにしましょう。

まとめると、LIKE述語はパターンマッチングにおいて強力かつ汎用性の高いツールです。しかし、その有効性と信頼性は、より広いパフォーマンスやセキュリティの原則と密接に結びついています。先頭にワイルドカードがあるLIKEクエリは、結果を迅速に返すことができない場合があり、脆弱なLIKEクエリは意図しないまったく別の結果を返すように改ざんされてしまう可能性があります。前回の記事で紹介した構文や変数の扱い方と、インデックス作成とパラメーター化クエリの徹底した使用といったベストプラクティスを組み合わせることで、LIKEステートメントを単に機能させるだけでなく、高速かつ安全、そして信頼性の高いものにできます。これにより、アプリケーションへ常に正しい結果を提供できるようになります。

@Iryna Mykhailovaさんが書いた元の記事へ