「動的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)
);
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
を指定することで、表示結果を抽出することができるとします。
このような場合に、動的SQLが便利です。
例えば、何も指定されていない場合は以下のようなSQLになります。
select * from user_master
;
user_idに「0002」が与えられた場合は、以下のようなSQLをプログラムで生成します。
select * from user_master
where user_id = "0002"
;
user_idに「0002」が与えられた場合は、以下のようなSQLをプログラムで生成します。
select * from user_master
where user_name = "hoge"
;
create_dateに「20200101」以降と指定され、delete_dateが無しと指定された場合は、
以下のようなSQLをプログラムで生成されます。
select * from user_master
where create_date >= "20200101"
and delete_date is not null
;
----
しかし、動的SQLには、以下の２つの問題があります。
１．性能問題
動的SQLは生成されるSQ文Lが都度変わるので、事前に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"
;
このような問題を防ぐためには、候補をリストから選ばせる、スペースやセミコロン等のSQLの文法上意味がある入力を禁止・エスケープする等、自由にSQL文を生成させない対策が必要になります。
Javaの場合は、「プリペアードステートメント」と呼ばれる仕組みを使うと良いでしょう。
