SQLの?、$1、:nameを変えたい。引数の順番は？

使うツール

• このツールを開く: SQLプレースホルダー変換

まずは二つの値で、出現順を確認する

SELECT * FROM orders WHERE owner = :user AND state = :state;

{"user":"demo","state":"open"}

入力方式をnamed、変換先をpostgresqlにすると、:userは$1、:stateは$2になります。引数欄には上のJSONオブジェクトを入れます。SQLの出現順で番号が決まるので、JSONのキーを並べ替えただけで番号が変わるとは考えません。結果ではsqlだけでなくargumentsを開き、$1にdemo、$2にopenが対応することを確認します。

ここでのpostgresqlは番号付きプレースホルダーの表記を選ぶ設定です。SQL全体を別DBの方言へ翻訳する機能ではありません。日付関数、型、識別子の引用方法まで変わったと思って貼り付けると、別の問題を作ります。変換したい対象を『値を渡す穴と対応表』に限定すると、確認すべき範囲を小さくできます。

同じ名前が二回出ると、引数も二つ必要？

SELECT * FROM tasks WHERE owner = :user OR reviewer = :user;

引数を{"user":"demo"}として番号付きへ変換すると、二つとも$1です。同じ名前が同じ値を指すという関係を保つためです。一方、変換先をquestionにすると二つとも?になり、引数の出現順はdemo、demoとなります。見た目の記号が同じでも、?では一つ目と二つ目を別の位置として扱うことに注目します。

結果のparametersはSQL中の出現ごとの対応、argumentsは変換先へ渡す値の対応です。出現回数と、異なる値の個数を混同しないように読み分けます。実装へ移す前に、二つの条件で同じ値を使う意図だったかも確認してください。名前の使い回し自体が誤りなら、変換だけで修正されることはありません。

文字列やコメントの中まで置き換えない

SELECT ':user' AS note FROM tasks WHERE owner = :user; -- :user

この例で変換するのはWHEREの:userだけです。引用符の中はデータ、--以降はコメントとして残ります。検索と一括置換だけで:userを$1へ変えると、説明用の文字まで変えてしまいます。出力の差分を見るときは、置き換わった箇所だけでなく、残るべき箇所が残っているかも確認します。

ただし、このツールをあらゆるSQL構文を理解するパーサーとして使わないでください。DB固有の引用、演算子、複雑なキャストなどは別の確認が必要です。特にPostgreSQLの?には演算子としての意味があるため、questionからの変換をPostgreSQL方言で実行する操作は拒否します。エラーを消すためだけに方言を変えるのではなく、本当にプレースホルダーかを先に確かめます。

不足だけでなく、余った引数も確認する

SQLには:userしかないのに、引数にstateも残っていたら、以前のSQLの設定を持ち越している可能性があります。このツールは名前付き引数の不足と余分なキーをエラーにします。位置付きならJSON配列を使用し、?の個数や番号に必要な個数と照合します。何となく空文字を追加して通すのではなく、SQL側と値側のどちらが古いかを調べます。

最後はドライバーへ渡す形で確かめる

出力を確認できても、接続ライブラリがその記号を受け付けるかは別問題です。同じDBでもライブラリによって渡し方が違う場合があります。利用先の公式資料でSQLと引数を別々に渡す方法を確認し、隔離したテストデータで期待する行が選ばれるかを調べます。値をSQL文字列へ連結する方法へ戻す必要はありません。実データや秘密を例に使わず、demoのような架空値で対応を確かめます。

参考資料

• SQLite — SQL expressions and parameters (新しいタブで開きます)

使うツール

• SQLプレースホルダー変換