画面側から渡されたパラメータの内容を弄ってSQLのロジックに取り込みたい。

Question

いつもお世話になっております。
PL/pgSQLについて、質問がございます。

画面側→SQLにパラメータが渡され、そのパラメータを使用してwhere句の条件に取り込みたいのですが
画面側から受け取ったパラメータが改行とtabが入っておりそのままでは使用が難しく
適切なSQL文の書き方がはっきりと思い浮かばないです。
＜パラメータの内容＞
$1 = '1
(改行)(tab)2
(改行)(tab)3'

現状のPL/pgSQLの内容としては以下の通りです。

CREATE OR REPLACE FUNCTION postgres.a_func(p1 varchar)
RETURNS TABLE (
    COLM_1 VARCHAR
)
DECLARE
    p text;
    u1 text[];
    s text;
AS'
BEGIN
    --パラメータを変数に代入
    p := p1
    --変数をstring_to_array()で文字列を配列に一度変換
    u1 := string_to_array(p, chr(10)) --改行・tabをなくして配列に変換['1','2','3']できれば
    --配列に変換した後、文字列に変換
    s := array_to_string(u1,',') --'1,2,3'になっている想定
    
    RETURN QUERY
    SELECT
        b
    FROM
        テーブル
    WHERE
        cast(id,varchar) IN (u1) --IN句を使用して複数パラメータが渡った時に対応できるようにする
END;
'
LANGUAGE PLPGSQL;

テーブル構造はシンプルで以下の通りになっております。
|id(int)|b(varchar)|
|1 |ああ |
|2 |いい |
|3 |うう |

独力で調べてみて、array_to_string,string_to_arrayというのが使えそうだなと思い取り入れようとしたのですが

u1 := string_to_array(p, chr(10)) --改行・tabをなくして配列に変換['1','2','3']できれば

ここの改行・tabスペースを変換する箇所でどのように記述すればコメントのような変換ができるのかがわからないです。
\r\n\tみたいな改行コードを使用するのではなくCHR(10),CHR(9)と書く的なことが記していたので
string_to_array(p,CHR(10),CHR(9))と書くのでしょうか？

string_to_arrayを使用する際、改行・tabスペースが入った文字列を変換するような内容が調べても出てこなかったので
ご教授頂きたいです。

使用DBはpostgres12です。

Answer 1

u1 := string_to_array(p, chr(10)) --改行・tabをなくして配列に変換['1','2','3']できれば

区切りは文字列も使えますので、LF+Tabとして指定すれば良いだけです。

u1 := string_to_array(p, chr(10) || chr(9)) 

それから、配列を条件にする場合に使用するのは、in演算子ではありません。

WHERE cast(id,varchar) IN (u1) --IN句を使用して複数パラメータが渡った時に対応できるようにする

以下の様にします。

WHERE array[id]::text[] <@ u1

9.19. 配列関数と演算子

in演算子を使用する場合は、配列を展開する必要があります。

WHERE id in (select unnest(u1))

Answer 2

画面側→SQLにパラメータが渡され、そのパラメータを使用してwhere句の条件に取り込みたいのですが

どんな環境でどのような言語を使っているのか明記されていませんが、画面から入力されたものを、そのままdbに渡してしまったら、SQLインジェクション等の問題が発生するのではありませんか。

画面側から受け取ったパラメータが改行とtabが入っておりそのままでは使用が難しく適切なSQL文の書き方が...

不適切な値が画面側から渡されることも考慮して、「画面側から受け取ったパラメータ」の検証をおこない、「使用が難しく」ならないような形に整えてから、a_funcに渡せばいいのではないでしょうか。

Comments

[sazi]

インジェクション対策は、SQLに埋め込むのではなく、変数として扱う事ですから、質問のコードはインジェクション対策されていると思います。