パラメータ化されたクエリ

シンプルプログラムでは基本的なクエリの実行方法を学びました。実際のアプリケーションでは、ユーザー入力や変数の値に基づいてクエリを実行することが多くなります。このページでは、安全にパラメータを扱う方法を学びます。

ldbcでは、SQLインジェクション攻撃を防ぐために、パラメータ化されたクエリを使用することを強く推奨しています。パラメータ化されたクエリを使うと、SQLコードとデータを分離し、より安全なデータベースアクセスが可能になります。

パラメータの基本

ldbcでは、SQL文にパラメータを埋め込むための2つの主要な方法があります：

1. 動的パラメータ - 通常のパラメータとして使用され、SQLインジェクション攻撃を防ぐためにPreparedStatementによって処理される
2. 識別子のエスケープ - テーブル名・カラム名など識別子をバッククォートで安全に埋め込む（ident 関数を使用）

動的パラメータの追加

まずは、パラメーターを持たないクエリを作成します。

sql"SELECT name, email FROM user".query[(String, String)].to[List]

次にクエリをメソッドに組み込んで、ユーザーが指定するidと一致するデータのみを選択するパラメーターを追加してみましょう。文字列の補間を行うのと同じように、id引数を$idとしてSQL文に挿入します。

val id = 1

sql"SELECT name, email FROM user WHERE id = $id".query[(String, String)].to[List]

Connectorを使用してクエリを実行すると問題なく動作します。

import ldbc.connector.*

// Connectorを作成
val connector = Connector.fromDataSource(datasource)

sql"SELECT name, email FROM user WHERE id = $id"
  .query[(String, String)]
  .to[List]
  .readOnly(connector)

ここでは何が起こっているのでしょうか？文字列リテラルをSQL文字列にドロップしているだけのように見えますが、実際にはPreparedStatementを構築しており、id値は最終的にsetIntの呼び出しによって設定されます。これにより、SQLインジェクション攻撃からアプリケーションを守ることができます。

さまざまな型のパラメータを使用できます：

val id: Int = 1
val name: String = "Alice"
val active: Boolean = true
val createdAt: LocalDateTime = LocalDateTime.now()

sql"INSERT INTO user (id, name, active, created_at) VALUES ($id, $name, $active, $createdAt)"

ldbcでは、各型に対して適切なエンコーダーが用意されており、Scala/Javaの値をSQLの値に安全に変換します。

複数のパラメータ

複数のパラメータも同様に使用できます。

val id = 1
val email = "alice@example.com"

// Connectorを使用して実行
  sql"SELECT name, email FROM user WHERE id = $id AND email > $email"
    .query[(String, String)]
    .to[List]
    .readOnly(connector)

クエリーの結合

大きなクエリを構築する場合は、複数のSQLフラグメントを結合することができます。

val baseQuery = sql"SELECT name, email FROM user"
val whereClause = sql"WHERE id > $id"
val orderClause = sql"ORDER BY name ASC"

val query = baseQuery ++ whereClause ++ orderClause

SQLヘルパー関数

ldbcは、複雑なSQL句を簡単に構築するためのヘルパー関数を多数提供しています。

IN句の扱い

SQLでよくある課題は、一連の値をIN句で使用したい場合です。ldbcではin関数を使って簡単に実装できます。

val ids = NonEmptyList.of(1, 2, 3)

// Connectorを使用して実行
  (sql"SELECT name, email FROM user WHERE " ++ in("id", ids))
    .query[(String, String)]
    .to[List]
    .readOnly(connector)

これは以下のSQLに相当します：

SELECT name, email FROM user WHERE (id IN (?, ?, ?))

idsはNonEmptyListである必要があることに注意してください。これはIN句が空であってはならないためです。

その他のヘルパー関数

ldbcでは他にも多くの便利な関数を提供しています：

VALUES句の生成

val users = NonEmptyList.of(
  (1, "Alice", "alice@example.com"),
  (2, "Bob", "bob@example.com")
)

(sql"INSERT INTO user (id, name, email) " ++ values(users))

WHERE句の条件

AND条件とOR条件を簡単に構築できます：

val activeFilter = sql"active = true"
val nameFilter = sql"name LIKE ${"A%"}"
val emailFilter = sql"email IS NOT NULL"

// WHERE (active = true) AND (name LIKE 'A%') AND (email IS NOT NULL)
val query1 = sql"SELECT * FROM user " ++ whereAnd(activeFilter, nameFilter, emailFilter)

// WHERE (active = true) OR (name LIKE 'A%')
val query2 = sql"SELECT * FROM user " ++ whereOr(activeFilter, nameFilter)

SET句の生成

UPDATE文のSET句を簡単に生成できます：

val name = "New Name"
val email = "new@example.com"

val updateValues = set(
  sql"name = $name",
  sql"email = $email",
  sql"updated_at = NOW()"
)

sql"UPDATE user " ++ updateValues ++ sql" WHERE id = 1"

ORDER BY句の生成

val query = sql"SELECT * FROM user " ++ orderBy(sql"name ASC", sql"created_at DESC")

オプションの条件

条件がオプションの場合（存在しない可能性がある場合）は、Optサフィックスが付いた関数を使用できます：

val nameOpt: Option[String] = Some("Alice")
val emailOpt: Option[String] = None

val nameFilter = nameOpt.map(name => sql"name = $name")
val emailFilter = emailOpt.map(email => sql"email = $email")

// nameFilterはSome(...)、emailFilterはNoneなので、WHERE句には「name = ?」のみが含まれます
val query = sql"SELECT * FROM user " ++ whereAndOpt(nameFilter, emailFilter)

識別子のエスケープ

カラム名やテーブル名など、SQL文の構造的な部分をパラメータ化したい場合があります。このような場合、ident 関数を使用します。

動的パラメータ（通常の$value）はPreparedStatementによって処理され、クエリ文字列では?に置き換えられますが、ident は識別子をバッククォートで囲んでSQL文に直接埋め込みます。NUL文字の除去も行うため、識別子を安全に扱えます。

val column = "name"
val table = "user"

// 動的パラメータとして扱うと「SELECT ? FROM user」となってしまう
// sql"SELECT $column FROM user".query[String].to[List]

// ident を使うと「SELECT `name` FROM `user`」となる
sql"SELECT ${ident(column)} FROM ${ident(table)}".query[String].to[List]

ident の一般的な使用例：

// 動的なカラム選択
val sortColumn = "created_at"

sql"SELECT * FROM user ORDER BY ${ident(sortColumn)} DESC"

// 動的なテーブル選択
val schema = "public"
val table = "user"

sql"SELECT * FROM ${ident(schema)}.${ident(table)}"

注意: ident はバッククォートでエスケープを行いますが、信頼できる値（定数・設定値など）に対して使用することを推奨します。ユーザー入力をそのまま識別子として使用する設計は避けてください。値が識別子として妥当かを事前に検証したい場合は、後述のisSimpleIdentifierが利用できます。

パラメータのエスケープとsql_mode

動的パラメータはPreparedStatementが処理するため、通常はエスケープを意識する必要はありません。ただし、ldbcコネクターのクライアントサイドPreparedStatement（useServerPrepStmts = false、既定）はクエリをクライアント側で組み立てるため、エスケープ方法がサーバーのsql_modeに依存します。

ldbcはセッションのsql_modeを追跡し、以下のように文字列リテラルをエスケープします。

NO_BACKSLASH_ESCAPESが有効なセッションではバックスラッシュが通常の文字として扱われるため、\'ではクォートを無効化できません。そのためクォートの二重化に切り替えています。

このsql_modeは接続時に取得したあと、サーバーから受信するOK/EOFパケットごとに更新されます。接続後にSET SESSION sql_mode = ...を実行した場合でも、その時点以降のクエリ生成に正しく反映されます。

利用者側で必要な設定はありません。

識別子と値のクォート（JDBC 4.3互換API）

JDBC 4.3で標準化されたクォート用のメソッドがStatementおよびPreparedStatementで利用できます。sql補間子の外でSQL文を組み立てる場合や、JDBC標準APIとの互換性が必要な場合に使用します。

for
  stmt <- conn.createStatement()
  a    <- stmt.enquoteLiteral("G'Day")              // 'G''Day'
  b    <- stmt.enquoteIdentifier("my table", false) // `my table`
  c    <- stmt.enquoteIdentifier("user", true)      // `user`
  d    <- stmt.enquoteNCharLiteral("Hello")         // N'Hello'
  e    <- stmt.isSimpleIdentifier("user_name")      // true
  f    <- stmt.isSimpleIdentifier("select")         // false（予約語）
yield ()

isSimpleIdentifierはMySQLの規則に従い、[0-9a-zA-Z$_]とU+0080以上の拡張文字のみで構成され、数字のみではなく、64文字以内で、予約語でないものを単純な識別子と判定します。ANSI_QUOTES sql_modeが有効な場合、識別子のクォート文字はバッククォートではなく"になります。

注意: sql補間子の中で識別子を埋め込む場合は、引き続きidentを使用してください。これらのメソッドはStatementのインスタンスを必要とするため、主にSQL文を文字列として組み立てる場面で使います。

条件付きSQL断片

条件に応じてSQL断片を付加したい場合は、when 関数を使用します。

val limit: Option[Int] = Some(10)

sql"SELECT name, email FROM user" ++ when(limit.isDefined)(sql" LIMIT ${limit.get}")

when(condition)(fragment) は condition が true のときだけ fragment を追加します。false のときは空の断片になります。

複数の条件を組み合わせることもできます：

val nameFilter: Option[String] = Some("Alice")
val activeOnly: Boolean = true

val query =
  sql"SELECT * FROM user" ++
  when(nameFilter.isDefined)(sql" WHERE name = ${nameFilter.get}") ++
  when(activeOnly)(sql" AND active = true")

ページネーション

一覧取得でよく使われる LIMIT / OFFSET を簡潔に書くには、paginate 関数を使用します。

// limit と offset を両方指定
sql"SELECT name, email FROM user " ++ paginate(limit = 20, offset = 40)
// → SELECT name, email FROM user LIMIT ? OFFSET ?

// limit のみ指定
sql"SELECT name, email FROM user " ++ paginate(limit = 20)
// → SELECT name, email FROM user LIMIT ?

ページ番号からオフセットを計算する場合の例：

val pageSize = 20
val page     = 3  // 1始まり

sql"SELECT name, email FROM user ORDER BY id " ++ paginate(limit = pageSize, offset = (page - 1) * pageSize)

注意: limit または offset に負の値を渡すと IllegalArgumentException がスローされます。

次のステップ

これでパラメータ化されたクエリの使い方を理解できました。パラメータを扱えるようになると、より複雑で実用的なデータベースクエリを構築できるようになります。

次はデータ選択に進み、データをさまざまな形式で取得する方法を学びましょう。