C言語DB入門|プリペアドステートメントを使う

C言語DB入門|プリペアドステートメントを使う

これまでの記事では、書籍の登録・一覧表示・変更・削除を、C言語から実行してきました。

その中で繰り返し登場したのが、プリペアドステートメントです。

この記事では、これまで使ってきた関数の役割を整理し、SQL文と入力値を分けて扱う仕組みを詳しく学びます。

  • プリペアドステートメントとは何か
  • SQL文へ文字列を直接連結する方法との違い
  • sqlite3_prepare_v2によるSQLの準備
  • sqlite3_bind_*による値の設定
  • sqlite3_stepによる実行
  • sqlite3_finalizeによる後片付け
  • 同じステートメントを再利用する方法

アポストロフィを含む書名を登録するプログラムも作成し、文字列がそのままデータとして扱われることを確認しましょう。

プリペアドステートメントとは

プリペアドステートメントは、SQL文を実行できる形に準備したものです。

SQLiteでは、SQL文をsqlite3_prepare_v2へ渡すと、SQLite内部で実行するための形式へ変換されます。その結果をsqlite3_stmt型のポインターで扱います。

SQL文に疑問符を置いておけば、その場所へ後から値を設定できます。

たとえば、書籍の登録では、次のように分けて考えます。

分けて扱うもの内容
SQL文の構造booksテーブルへtitleとauthorを登録する
登録する値入力されたタイトルと著者名

SQL文の構造を先に決め、値は別の関数で渡します。

この「値を設定すること」を、バインドすると呼びます。

dbとstmtの役割は異なる

これまでのプログラムでは、dbとstmtという変数を使用してきました。

変数型扱うもの
dbsqlite3 *データベースへの接続
stmtsqlite3_stmt *準備したSQL文

dbは、どのデータベースへアクセスするかを管理します。

stmtは、その接続を使って実行する、個々のSQL文を管理します。登録用のINSERTと一覧表示用のSELECTでは、それぞれ別のステートメントを準備します。

SQL文へ文字列を直接連結するとどうなるか

書籍を登録するSQL文を、タイトルや著者名の文字列をつなぎ合わせて作る方法も考えられます。

しかし、その方法では、入力値にSQLの構文として意味を持つ文字が含まれていないかを考慮しなければなりません。

たとえば、次のタイトルにはアポストロフィが含まれています。

C Programmer's Guide

SQLでは、アポストロフィは文字列の区切りにも使われます。そのため、何も処理せずにSQL文へ埋め込むと、書名の一部が文字列の終わりとして解釈され、SQLが正しく動かなくなる場合があります。

入力値によってSQLの意味が変わることもある

外部から受け取った文字列をSQLへ直接組み込むと、入力内容によってSQLの条件や処理を変えられてしまう場合があります。これをSQLインジェクションと呼びます。

プリペアドステートメントでは、値をバインドすることで、その値をSQLの命令ではなく、データとして扱えます。

比較する点文字列をそのまま連結する方法値をバインドする方法
SQLと入力値1つの文字列として組み立てる分けて渡す
アポストロフィなど適切な処理を自分で行う必要がある値に含まれる文字として渡せる
整数の設定SQL文字列へ組み込む整数用の関数で設定する
入力値によるSQL構造の変更不適切な組み立て方では起こり得るバインドした値はSQL構造にならない

ただし、プリペアドステートメントを使っても、別の部分で外部入力をSQL文へ直接連結すれば、その部分の問題は残ります。利用者が入力する値は、バインドして渡す方針にしましょう。

図1:SQL文の構造と入力データを分ける

この図から分かること

プリペアドステートメントでは、SQL文と入力値を別々に渡します。

書名にアポストロフィが含まれていても、その文字をSQLの区切りとして組み込む必要はありません。書名の一部として扱えます。

基本は4つの処理

プリペアドステートメントを使用する基本の流れは、次のとおりです。

順番処理使用する関数
1SQL文を準備するsqlite3_prepare_v2
2値を設定するsqlite3_bind_*
3SQLを実行するsqlite3_step
4ステートメントを解放するsqlite3_finalize

sqlite3_bind_*は、複数のバインド用関数をまとめた表記です。その名前の関数が1つあるわけではありません。

実際には、文字列ならsqlite3_bind_text、64ビット整数ならsqlite3_bind_int64などを使用します。

また、固定の一覧表示SQLのように、後から設定する値がない場合は、バインドの処理は不要です。

sqlite3_prepare_v2でSQLを準備する

sqlite3_prepare_v2には、データベース接続とSQL文字列を渡します。

これまで使用してきた引数の意味を確認しましょう。

引数使用例意味
第1引数db使用するデータベース接続
第2引数sql準備するSQL文字列
第3引数-1SQL文字列を末尾のナル文字まで読み取る
第4引数&stmt作成されたステートメントを受け取る
第5引数NULL未処理のSQL文字列の位置を受け取らない

stmtはポインター変数です。関数の中からstmtへ結果を設定してもらうため、そのアドレスである&stmtを渡します。

準備に成功すると、SQLITE_OKが返ります。

ただし、この段階ではINSERTやDELETEなどの処理はまだ実行されていません。実行するのは、後でsqlite3_stepを呼び出したときです。

補足

&stmt

sqlite3_prepare_v2() が作った「SQLを実行するための準備済みオブジェクト」を指すポインタを、stmt に保存してもらうために、stmt 自身のアドレスを渡しています。

sql
→ SQLの文字列

stmt
→ 準備されたSQLを操作するためのポインタ

&stmt
→ stmtそのものを書き換えてもらうためのアドレス

1回の準備で扱うSQL文は1つ

sqlite3_prepare_v2が準備するのは、渡した文字列の先頭にある1つのSQL文です。

複数のSQL文をセミコロンで並べても、1回の呼び出しですべてが準備されるわけではありません。本教材では、1つのステートメントに1つのSQL文を渡します。

sqlite3_bind_*で値を設定する

SQL文の疑問符は、後から値を設定するための場所です。こうした場所をパラメーター、またはプレースホルダーと呼びます。

本教材で使用している単独の疑問符は、左から順番に1、2、3と番号が付けられます。

値の種類に合った関数を使う

関数設定する値
sqlite3_bind_textUTF-8の文字列
sqlite3_bind_int32ビット整数
sqlite3_bind_int6464ビット整数
sqlite3_bind_double浮動小数点数
sqlite3_bind_nullSQLのNULL

書籍管理アプリでは、タイトルと著者名にsqlite3_bind_text、IDにsqlite3_bind_int64を使用しています。

バインドの位置は1から始まります。一方、SELECTの結果を読むsqlite3_column_*の列番号は0から始まるので、区別しておきましょう。

sqlite3_bind_textの引数

タイトルを設定するときの引数は、次のように考えます。

引数使用例意味
第1引数stmt値を設定するステートメント
第2引数1設定する位置
第3引数title渡す文字列
第4引数-1文字列を末尾のナル文字まで扱う
第5引数SQLITE_TRANSIENTSQLite側で文字列をコピーする

第4引数に長さを指定する場合、その単位は文字数ではなくバイト数です。

SQLITE_TRANSIENTを指定すると、SQLiteはバインド関数から戻る前に文字列をコピーします。呼び出し元の配列を後から変更しても、設定済みの値はそのコピーを使います。

対してSQLITE_STATICは、コピーを要求しない指定です。文字列を保持する責任が呼び出し元に残るため、本教材では扱いやすいSQLITE_TRANSIENTを基本にします。

sqlite3_stepで実行する

準備と値の設定が終わったら、sqlite3_stepを呼び出します。

戻り値の扱いは、SQLが結果の行を返すかどうかによって変わります。

SQLの例主な戻り値対応
本教材のINSERT・UPDATE・DELETESQLITE_DONE実行が完了したことを確認する
SELECTで行を取得できた場合SQLITE_ROW列の値を取り出す
SELECTを最後まで読み終えた場合SQLITE_DONE繰り返しを終了する
実行に失敗した場合エラーを表す値原因を表示して後片付けする

SELECTでは、SQLITE_ROWが返るたびに現在の行を読み取り、次のsqlite3_stepへ進みます。

SQLITE_ROWはエラーではありません。「読み取れる行がある」という状態です。

sqlite3_finalizeで後片付けする

ステートメントを使い終わったら、sqlite3_finalizeで解放します。

これは、登録済みの書籍を削除する処理でも、データベース接続を閉じる処理でもありません。

関数終了する対象
sqlite3_finalize準備したステートメント
sqlite3_closeデータベース接続

SQLの実行中にエラーが起きた場合も、作成したステートメントは解放します。解放後のstmtを、sqlite3_stepやsqlite3_bind_*へ渡してはいけません。SQLite公式ドキュメント:sqlite3_finalize

図2:準備・値の設定・実行・後片付け

この図から分かること

値を設定しただけでは、SQLはまだ実行されません。sqlite3_stepによって処理が進みます。

また、使用したステートメントは、処理の成功・失敗にかかわらず、最後に解放する必要があります。

アポストロフィを含む書名を登録してみよう

ここからは、プリペアドステートメントの動作を確認するプログラムを作成します。

今回はキーボード入力を省き、プログラム内に用意した次の値を使用します。

項目値
タイトルC Programmer's Guide
著者名佐藤花子

登録後は、著者名を条件にSELECTを実行し、保存した内容を表示します。

今回はメモリ上のデータベースを使用する

接続先に:memory:を指定し、メモリ上に一時的なデータベースを作ります。

接続を閉じると、このデータベースの内容はなくなります。そのため、何度実行しても同じ状態から確認できます。これまで使ってきたbooks.dbには変更を加えません。

通常の書籍管理アプリでは、引き続きbooks.dbへ接続してデータを保存します。

動作確認用のプログラムを作成する

作業場所はC:\c-db\chap04です。

VS Codeでprepared_statement_demo.cを作成し、UTF-8で保存してください。

書名をバインドして登録し、著者名を条件に取得するプログラム

ファイル名: prepared_statement_demo.c

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int insert_book(sqlite3 *db,
                       const char *title,
                       const char *author)
{
    const char *sql =
        "INSERT INTO books (title, author) VALUES (?, ?);";

    sqlite3_stmt *stmt = NULL;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "登録SQLの準備に失敗しました:%s\n",
                sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        return 0;
    }

    rc = sqlite3_bind_text(stmt, 1, title, -1, SQLITE_TRANSIENT);

    if (rc == SQLITE_OK) {
        rc = sqlite3_bind_text(stmt, 2, author, -1, SQLITE_TRANSIENT);
    }

    if (rc != SQLITE_OK) {
        fprintf(stderr, "登録する値の設定に失敗しました:%s\n",
                sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        return 0;
    }

    rc = sqlite3_step(stmt);

    if (rc != SQLITE_DONE) {
        fprintf(stderr, "登録に失敗しました:%s\n",
                sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        return 0;
    }

    rc = sqlite3_finalize(stmt);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "登録ステートメントの解放に失敗しました:%s\n",
                sqlite3_errmsg(db));
        return 0;
    }

    printf("書籍を登録しました。\n");
    return 1;
}

static int find_books_by_author(sqlite3 *db, const char *author)
{
    const char *sql =
        "SELECT id, title, author FROM books "
        "WHERE author = ? ORDER BY id;";

    sqlite3_stmt *stmt = NULL;
    size_t count = 0;

    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

    if (rc == SQLITE_OK) {
        rc = sqlite3_bind_text(stmt, 1, author, -1, SQLITE_TRANSIENT);
    }

    if (rc != SQLITE_OK) {
        fprintf(stderr, "検索の準備に失敗しました:%s\n",
                sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        return 0;
    }

    printf("\n著者名:%s\n", author);
    printf("ID | タイトル | 著者名\n");

    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        sqlite3_int64 id = sqlite3_column_int64(stmt, 0);

        const unsigned char *title = sqlite3_column_text(stmt, 1);

        if (title == NULL) {
            fprintf(stderr, "タイトルを取得できませんでした。\n");
            sqlite3_finalize(stmt);
            return 0;
        }

        const unsigned char *book_author =
            sqlite3_column_text(stmt, 2);

        if (book_author == NULL) {
            fprintf(stderr, "著者名を取得できませんでした。\n");
            sqlite3_finalize(stmt);
            return 0;
        }

        printf("%lld | %s | %s\n",
               (long long)id,
               (const char *)title,
               (const char *)book_author);

        count++;
    }

    if (rc != SQLITE_DONE) {
        fprintf(stderr, "検索に失敗しました:%s\n",
                sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        return 0;
    }

    rc = sqlite3_finalize(stmt);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "検索ステートメントの解放に失敗しました:%s\n",
                sqlite3_errmsg(db));
        return 0;
    }

    printf("\n%zu件の書籍が見つかりました。\n", count);
    return 1;
}

int main(void)
{
    sqlite3 *db = NULL;
    int success = 0;

    const char *title = "C Programmer's Guide";
    const char *author = "佐藤花子";

    const char *create_sql =
        "CREATE TABLE books ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "title TEXT NOT NULL,"
        "author TEXT NOT NULL"
        ");";

    int rc = sqlite3_open(":memory:", &db);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "データベースを開けませんでした。\n");

        if (db != NULL) {
            fprintf(stderr, "詳細:%s\n", sqlite3_errmsg(db));
            sqlite3_close(db);
        }

        return EXIT_FAILURE;
    }

    rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "テーブルの作成に失敗しました:%s\n",
                sqlite3_errmsg(db));
    } else if (insert_book(db, title, author)) {
        success = find_books_by_author(db, author);
    }

    rc = sqlite3_close(db);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "データベース接続を閉じられませんでした:%s\n",
                sqlite3_errmsg(db));
        return EXIT_FAILURE;
    }

    return success ? EXIT_SUCCESS : EXIT_FAILURE;
}

コンパイルして実行する

MSYS2 UCRT64で作業場所へ移動します。

cd /c/c-db/chap04

短いコンパイルコマンドはこちらです。

gcc prepared_statement_demo.c -o prepared_statement_demo.exe -lsqlite3

C17と警告オプションを指定する場合は、次のコマンドを使用します。

gcc -std=c17 -Wall -Wextra prepared_statement_demo.c -o prepared_statement_demo.exe -lsqlite3

どちらか一方でコンパイルし、成功したら実行してください。

./prepared_statement_demo.exe

実行結果の例

書籍を登録しました。

著者名:佐藤花子
ID | タイトル | 著者名
1 | C Programmer's Guide | 佐藤花子

1件の書籍が見つかりました。

アポストロフィを含む書名が、そのまま登録・表示されています。

このプログラムでは、タイトル中のアポストロフィを削除したり、別の文字へ置き換えたりしていません。文字列全体をsqlite3_bind_textで渡しています。

登録と検索の両方でバインドしている

今回のプログラムでは、2つのSQLにプリペアドステートメントを使用しています。

関数SQLの目的バインドする値
insert_book書籍を登録するタイトル、著者名
find_books_by_author著者名で検索する著者名

プリペアドステートメントは、INSERTだけで使うものではありません。

WHEREに指定する検索条件や、UPDATE・DELETEで対象を選ぶIDにも使用できます。

今回は仕組みに集中するため固定の文字列を使いましたが、キーボードから読み取った配列も、同じようにバインドできます。

固定のテーブル作成SQLはsqlite3_execで実行する

mainでは、テーブル作成にsqlite3_execを使用しています。

このSQLには利用者の入力を組み込んでいません。固定のSQLを実行するためにsqlite3_execを使用すること自体が問題なのではなく、外部入力をどうSQLへ渡すかが重要です。

同じSQLを再利用するときはresetする

同じ形のSQLを繰り返し実行する場合は、ステートメントを解放せずに再利用することもできます。

たとえば、複数の書籍を同じINSERTで登録する場合は、次の流れになります。

  1. INSERTを一度準備する。
  2. 1件目の値を設定する。
  3. sqlite3_stepで実行する。
  4. sqlite3_resetで実行状態を戻す。
  5. 2件目の値を設定する。
  6. 再びsqlite3_stepで実行する。
  7. すべて終わったらsqlite3_finalizeで解放する。

毎回同じSQL文を準備し直す処理を減らせるため、繰り返し実行する場面で役立ちます。

ただし、今回の動作確認プログラムは1回ずつ実行する構成なので、sqlite3_resetは使用していません。

resetしても、設定済みの値は消えない

sqlite3_resetは、ステートメントの実行状態を戻す関数です。バインド済みの値はそのまま残ります。

すべてのパラメーターをNULLへ戻したい場合は、sqlite3_clear_bindingsを使用します。

関数実行状態バインド済みの値
sqlite3_reset実行前の状態へ戻す残る
sqlite3_clear_bindings戻さないすべてNULLにする
sqlite3_finalizeステートメントを解放する再利用できなくなる

すべての値を毎回設定し直すなら、clear_bindingsは必須ではありません。ただし、一部だけ設定すると、残りに前回の値が使われる点に注意します。

また、sqlite3_resetも戻り値を確認する必要があります。直前の実行に関するエラーが返る場合があるためです。

図3:再利用するときは実行状態と設定値を区別する

この図から分かること

再利用するときは、ステートメントを解放する代わりに、実行状態を戻します。

ただし、実行状態を戻すことと、設定した値を消すことは別です。前回の値を残すか、新しい値で置き換えるかを意識する必要があります。

パラメーターにできるのは値

疑問符は、SQLのあらゆる部分を置き換えられるわけではありません。

基本的には、文字列や整数などの値が入る場所に使用します。

指定したいものバインドで渡せるか
タイトル渡せる
著者名渡せる
検索するID渡せる
テーブル名テーブル名としては渡せない
列名列名としては渡せない
ASCやDESCなどのSQLキーワードキーワードとしては渡せない

たとえば、列名の文字列をバインドしても、その文字列が列への参照に変わるわけではありません。

並べ替える列などを切り替えたい場合は、プログラム側で用意したSQLの候補から選ぶ方法を考えます。本教材では、テーブル名と列名を固定して扱います。

よくある間違いを整理する

ここまでのCRUDプログラムを読むときは、次の点を確認してみましょう。

間違いやすい点正しい扱い
値を設定する位置を0から数えるバインドの位置は1から数える
bindしただけでSQLが実行されたと思うsqlite3_stepで実行する
SELECTのSQLITE_ROWをエラー扱いする現在の行を読み取る
resetで値も消えると思う値は残るため、必要に応じて設定し直す
finalizeしたstmtを再利用する再度prepareして新しいステートメントを作る
バインドする文字列をSQL用に加工する元の文字列をデータとして渡す
prepareだけ成功すれば十分だと思うbind・step・finalizeの結果も確認する

未設定のパラメーターはNULLとして扱われます。今回のbooksテーブルではtitleとauthorがNOT NULLなので、設定し忘れると登録時に制約違反となります。

また、プリペアドステートメントは、空のタイトルを自動で拒否したり、WHEREの書き忘れを防いだりする機能ではありません。入力内容の確認と、正しいSQLの設計は引き続き必要です。

次の記事では、こうした入力ミスとSQLiteの実行エラーを整理し、利用者へ分かりやすく伝える処理を作成していきましょう。

Follow me!