初心者向けに動的SQLを説明　例や注意点も合わせて説明！

はじめに
動的SQLが有効な例
動的SQLを使用する際の注意点
1. 性能に関する考慮
2. セキュリティの問題
あとがき

はじめに

「動的SQL」とは、入力値に従ってプログラムにより柔軟にSQL文を生成することを指します。
条件が外から与えられる場合に便利です。

この記事では、動的SQLが有効となる例と、動的SQLを使う際の注意点について、説明していきます。

動的SQLが有効な例

例として、ユーザーマスタテーブルを検索する例を紹介します。

ユーザーマスタテーブルの定義は以下の通りとします。

create table user_master
(
user_id CHAR(4) NOT NULL,
user_name VARCHAR(10) NOT NULL,
create_date CHAR(8) NOT NULL,
delete_date CHAR(8),
PRIMARY KEY (user_id)
);

create table user_master

(

user_id CHAR(4) NOT NULL,

user_name VARCHAR(10) NOT NULL,

create_date CHAR(8) NOT NULL,

delete_date CHAR(8),

PRIMARY KEY (user_id)

);

ユーザーマスタテーブルに対して、以下のInsert文により、データが挿入するとします。

insert into user_master values('0001','hoge','20190601',null);
insert into user_master values('0002','fuga','20200101','20210731');
insert into user_master values('0003','piyo','20210401',null);

insert into user_master values('0001','hoge','20190601',null);

insert into user_master values('0002','fuga','20200101','20210731');

insert into user_master values('0003','piyo','20210401',null);

ここまでの前提で、ユーザーマスタテーブルは以下の状態となっています。

user_id	user_name	create_date	delete_date
0001	hoge	20190601	null
0002	fuga	20200101	20240731
0003	piyo	20250401	null

このユーザマスターテーブルの内容を、画面プログラムで表示するとします。

また、画面から

user_id
user_name
create_date
delete_date

を指定することで、表示結果を抽出することができるとします。
このような場合に、動的SQLが便利です。

例えば、何も指定されていない場合は以下のようなSQLをプログラムから発行します。

select * from user_master 
;

1 2	select * from user_master ;

user_idに「0002」が与えられた場合は、プログラムでwhere句とuser_idに関する条件文を文字列結合により生成することで、以下のようなSQLを発行します。

select * from user_master 
where user_id = '0002' 
;

select * from user_master

where user_id = '0002'

;

user_nameに「hoge」が与えられた場合は、同じ要領で、where句とuser_nameに関する条件文を文字列結合します。

select * from user_master 
where user_name = 'hoge' 
;

select * from user_master

where user_name = 'hoge'

;

複数の条件が指定される場合も同じ要領です。
例えば、create_dateに「20200101」以降と指定され、delete_dateが無しと指定された場合は、where句とcreate_dateに関する条件文、and句とdelete_dateに関する条件文を文字列結合します。

select * from user_master 
where create_date ＞= '20200101' 
and   delete_date is not null 
;

select * from user_master

where create_date ＞= '20200101'

and delete_date is not null

;

このようにコーディングすることで、画面からの入力の有無や内容に従って、柔軟に適切な条件でクエリを発行することができるようになります。

動的SQLを使用する際の注意点

ただし、動的SQLを使用する場合は、以下の２つのことに注意してください。

性能に関する考慮

動的SQLは生成されるSQL文が都度変わるので、事前にSQL文の実行時間を最適化することが困難になります。
そのため、あまり件数が多いテーブルや、複雑な結合条件が発生するテーブルに対しては、動的SQLは行わない方が無難です。
どうしても動的SQLで実装する必要がある場合は、良く指定されるカラムに対してインデックスを貼る、非正規化を行ってテーブル結合を減らす、等の性能対策を行い、応答時間が遅くなるケースを少しでも減らすことを考える必要があります。

セキュリティの問題

外からSQL文の条件を指定できる動的SQLでは、SQLインジェクションで攻撃される危険が発生します。
前述の例で、user_idに「hoge’; delete from user_master where ‘1’ = ‘1」が与えられた場合、以下のようなSQL文が生成されてしまい、意図しないdelete文（全件削除）が発行されてしまいます。

select * from user_master 
where user_name = 'hoge'; delete from user_master where '1' = '1' 
;

select * from user_master

where user_name = 'hoge'; delete from user_master where '1' = '1'

;

このような問題を防ぐためには、SQLの文法上意味がある文字の入力を禁止・エスケープする、候補をリストから選ばせる等、自由にSQL文を生成させない対策が必要になります。
（Javaの場合は、「プリペアードステートメント」と呼ばれる仕組みを使っても良いです）

SQLの文法上意味がある文字としては、以下のものがあります。
これらの文字の入力をできないようにするか、無害な文字に変換するかしてください。

セミコロン (;)
- SQL文の終端記号。この入力を許すと複数のSQL文の実行が行われる危険がある。
シングルクォート (‘)、ダブルクォート (“)
- SQL文の文字列リテラルを囲むために使用される。この入力を許すと、悪意のある入力が文法エラーにならなくなる危険がある。
- 一部のRDBMS（例：MySQL）では、シングルクォートだけではなく、ダブルクォートも使用される。
バッククォート (`)
- 一部のRDBMS（例：MySQL）では、カラム名等を囲むために使用される。この入力を許すと、SQLの予約語がカラム名等として解釈されるため、入力を許さない方が無難。
バックスラッシュ (\)
- 文法上意味がある文字がエスケープされる。この入力を許すと、意図しないエスケープによりSQL文の構造が変わる可能性があるため、入力を許さない方が無難。
パーセント記号 (%)、アンダースコア (_)
- LIKE句で使われるワイルドカード。この入力を許すと、意図しない前方一致検索・後方一致検索・部分一致検索が行われる可能性がある。

RDBMSの仕様や業務要件等の事情によっては、上記以外の文字のエスケープも検討してください。
（例：改行文字(\rと\n)、タブ文字(\t)、ヌル文字(\0)）

あとがき

今回は、実務で良く見る実装に関する記事でした。
このような実装を正しく行うためには、プログラミングに関する基礎的な知識が必要になります。

株式会社サイゼントでは、即戦力のJavaプログラマーを育てるための書籍「絶対にJavaプログラマーになりたい人へ」をKindleで販売しています。

同じく、Spring Frameworkについてきめ細かく解説した別冊も販売中です。

また、上記の書籍をテキストとして用いたプログラミングスクール「サイゼントアカデミー」も開校しています。
このスクールは、受託開発事業・SES事業である弊社が、新入社員向けの研修で培ったノウハウを詰め込んだものです。

ご興味がある方は、上記画像から個別ページにアクセスしてみてください！