7. ODBCを使ったデータベース連携

ここまでの章では、フォームから受け取った値をCGIプログラムの中で処理し、HTMLとして返してきました。

実際のWebアプリケーションでは、投稿、ユーザー情報、設定値などをリクエストをまたいで保存する必要があります。この章では、C言語からODBCを使ってMariaDBに接続し、SQLを実行する基本を扱います。

この章で学ぶこと

CGIとデータベースを連携する理由
ODBCの構成要素
C言語からODBCで接続する流れ
INSERT と SELECT の実行
ODBCのエラー情報を確認する方法
CGIでDBを扱うときの注意点

データベースを使う理由

CGIプログラムだけでも、受け取った値をその場で処理することはできます。しかし、次のような機能を作るには、データを保存し、検索し、更新できる仕組みが必要です。

投稿された日記やメッセージを保存する
登録ユーザーを管理する
ログイン中のユーザーに紐づくデータを取り出す
一覧を並べ替えたり、条件で絞り込んだりする

この役割を担うのがデータベースです。本書では、MariaDBを例にして説明します。

ODBCとは何か

ODBC（Open Database Connectivity）は、アプリケーションからデータベースへ接続するための共通APIです。

C言語には、標準ライブラリだけでMariaDBやPostgreSQLに接続する機能はありません。各DB製品の専用ライブラリを使う方法もありますが、本書では共通的な手順で扱えるODBCを使います。

ODBCは、主に次の要素で構成されます。

要素	役割
アプリケーション	C言語で書いたCGIやコマンドラインプログラム
ドライバマネージャ	ODBC API呼び出しを適切なドライバへ中継する層
ODBCドライバ	MariaDBなど、実際のDB製品と通信する部品
データベース	データを保存する本体

Linuxでは、ドライバマネージャとして unixODBC を使うことが一般的です。

[Cプログラム]
    |
    | ODBC API
    v
[unixODBC]
    |
    | MariaDB ODBC Driver
    v
[MariaDB]

ODBCを使っても、SQL文そのものの違いが完全になくなるわけではありません。DB製品ごとのSQL方言や型の違いは残ります。ただし、接続、SQL実行、結果取得の基本手順は共通化できます。

事前準備

この章のサンプルでは、次のようなデータベースとテーブルを使います。

CREATE DATABASE sample;

CREATE TABLE sample.users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

接続ユーザーの作成例です。

CREATE USER 'user'@'localhost' IDENTIFIED BY 'pass';
GRANT ALL PRIVILEGES ON sample.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

ODBCドライバや odbc.ini / odbcinst.ini の設定は環境によって異なります。詳細はAppendixを参照してください。この章では、接続文字列を直接指定する例を使います。

DRIVER={MariaDB};SERVER=localhost;DATABASE=sample;UID=user;PWD=pass;

ODBC接続の基本手順

ODBCでは、いくつかのハンドルを順番に確保して使います。

ハンドル	用途
`SQLHENV`	ODBC全体の環境
`SQLHDBC`	データベース接続
`SQLHSTMT`	SQL文の実行

基本の流れは次のとおりです。

環境ハンドルを確保する
ODBCバージョンを設定する
接続ハンドルを確保する
データベースに接続する
ステートメントハンドルを確保する
SQLを実行する
結果を確認する
ハンドルを解放する

ODBC関数は、戻り値として成功・失敗を返します。多くの場合、次の2つを成功として扱います。

ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO

この判定は何度も使うので、補助関数にしておくと読みやすくなります。

int odbc_succeeded(SQLRETURN ret) {
    return ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO;
}

エラー情報を表示する

ODBCで失敗したときは、SQLGetDiagRec() を使うと詳しい情報を取得できます。

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

void print_odbc_error(SQLSMALLINT handle_type, SQLHANDLE handle) {
    SQLCHAR state[6];
    SQLINTEGER native_error;
    SQLCHAR message[256];
    SQLSMALLINT message_len;

    SQLRETURN ret = SQLGetDiagRec(
        handle_type,
        handle,
        1,
        state,
        &native_error,
        message,
        sizeof(message),
        &message_len
    );

    if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
        fprintf(stderr, "ODBC error [%s] %s\n", state, message);
    }
}

この関数は、接続失敗やSQL実行失敗の原因を調べるときに使います。ブラウザに内部エラーをそのまま表示するのは避け、開発中は標準エラーやApacheのエラーログに出すようにします。

接続して切断する

まずは、データベースに接続してすぐ切断する最小プログラムです。

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>

int odbc_succeeded(SQLRETURN ret) {
    return ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO;
}

int main(void) {
    SQLHENV env = SQL_NULL_HENV;
    SQLHDBC dbc = SQL_NULL_HDBC;
    SQLRETURN ret;
    int connected = 0;
    SQLCHAR conn_str[] =
        "DRIVER={MariaDB};SERVER=localhost;DATABASE=sample;UID=user;PWD=pass;";

    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    if (!odbc_succeeded(ret)) {
        fprintf(stderr, "Failed to allocate environment handle.\n");
        goto cleanup;
    }

    ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    if (!odbc_succeeded(ret)) {
        fprintf(stderr, "Failed to set ODBC version.\n");
        goto cleanup;
    }

    ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    if (!odbc_succeeded(ret)) {
        fprintf(stderr, "Failed to allocate connection handle.\n");
        goto cleanup;
    }

    ret = SQLDriverConnect(
        dbc,
        NULL,
        conn_str,
        SQL_NTS,
        NULL,
        0,
        NULL,
        SQL_DRIVER_NOPROMPT
    );
    if (!odbc_succeeded(ret)) {
        fprintf(stderr, "Failed to connect database.\n");
        goto cleanup;
    }

    connected = 1;
    printf("Connected.\n");

cleanup:
    if (dbc != SQL_NULL_HDBC) {
        if (connected) {
            SQLDisconnect(dbc);
        }
        SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    }
    if (env != SQL_NULL_HENV) {
        SQLFreeHandle(SQL_HANDLE_ENV, env);
    }

    return odbc_succeeded(ret) ? EXIT_SUCCESS : EXIT_FAILURE;
}

コンパイル例です。

gcc db_connect.c -o db_connect -lodbc

実行します。

./db_connect

接続に失敗した場合は、接続文字列、ODBCドライバ名、MariaDBのユーザー、権限、起動状態を確認します。

INSERTを実行する

次に、INSERT でデータを追加します。

ユーザー入力をSQL文字列に直接連結するのは危険です。SQLインジェクションの原因になります。ODBCでは、? プレースホルダを使い、SQLBindParameter() で値を渡します。

SQLCHAR sql[] = "INSERT INTO users (name, age) VALUES (?, ?)";
SQLCHAR name[] = "Taro";
SQLINTEGER age = 25;
SQLLEN name_len = SQL_NTS;
SQLLEN age_len = 0;

ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
if (!odbc_succeeded(ret)) {
    fprintf(stderr, "Failed to allocate statement handle.\n");
    goto cleanup;
}

ret = SQLPrepare(stmt, sql, SQL_NTS);
if (!odbc_succeeded(ret)) {
    fprintf(stderr, "Failed to prepare INSERT.\n");
    goto cleanup;
}

ret = SQLBindParameter(
    stmt,
    1,
    SQL_PARAM_INPUT,
    SQL_C_CHAR,
    SQL_VARCHAR,
    sizeof(name),
    0,
    name,
    sizeof(name),
    &name_len
);
if (!odbc_succeeded(ret)) {
    fprintf(stderr, "Failed to bind name.\n");
    goto cleanup;
}

ret = SQLBindParameter(
    stmt,
    2,
    SQL_PARAM_INPUT,
    SQL_C_SLONG,
    SQL_INTEGER,
    0,
    0,
    &age,
    0,
    &age_len
);
if (!odbc_succeeded(ret)) {
    fprintf(stderr, "Failed to bind age.\n");
    goto cleanup;
}

ret = SQLExecute(stmt);
if (!odbc_succeeded(ret)) {
    fprintf(stderr, "Failed to execute INSERT.\n");
    goto cleanup;
}

printf("Inserted.\n");

このコード片は、前節の接続後に SQLHSTMT stmt = SQL_NULL_HSTMT; を用意して使います。後始末では、次のようにステートメントハンドルも解放します。

if (stmt != SQL_NULL_HSTMT) {
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}

SELECTで結果を取得する

SELECT では、SQLExecDirect() でSQLを実行し、SQLFetch() で1行ずつ結果を取り出します。列の値は SQLGetData() で取得します。

SQLCHAR sql[] = "SELECT id, name, age FROM users ORDER BY id";
SQLINTEGER id;
SQLCHAR name[101];
SQLINTEGER age;
SQLLEN id_ind;
SQLLEN name_ind;
SQLLEN age_ind;

ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
if (!odbc_succeeded(ret)) {
    fprintf(stderr, "Failed to allocate statement handle.\n");
    goto cleanup;
}

ret = SQLExecDirect(stmt, sql, SQL_NTS);
if (!odbc_succeeded(ret)) {
    fprintf(stderr, "Failed to execute SELECT.\n");
    goto cleanup;
}

while ((ret = SQLFetch(stmt)) != SQL_NO_DATA) {
    if (!odbc_succeeded(ret)) {
        fprintf(stderr, "Failed to fetch row.\n");
        goto cleanup;
    }

    SQLGetData(stmt, 1, SQL_C_SLONG, &id, 0, &id_ind);
    SQLGetData(stmt, 2, SQL_C_CHAR, name, sizeof(name), &name_ind);
    SQLGetData(stmt, 3, SQL_C_SLONG, &age, 0, &age_ind);

    printf("%d\t%s\t%d\n", id, name, age);
}

SQLFetch() が SQL_NO_DATA を返したら、取得できる行がなくなったという意味です。これはエラーではありません。

NULLを含む列を扱う場合は、SQLGetData() の最後の引数に入るインジケータ値を確認します。値が SQL_NULL_DATA なら、その列はNULLです。

CGIでSELECT結果をHTMLにする

CGIとしてDBの内容を表示する場合は、ヘッダを出してからHTMLを組み立てます。

printf("Content-Type: text/html; charset=UTF-8\r\n\r\n");
printf("<!DOCTYPE html>\n");
printf("<html lang=\"ja\"><body>\n");
printf("<table>\n");
printf("<tr><th>ID</th><th>Name</th><th>Age</th></tr>\n");

while ((ret = SQLFetch(stmt)) != SQL_NO_DATA) {
    SQLGetData(stmt, 1, SQL_C_SLONG, &id, 0, &id_ind);
    SQLGetData(stmt, 2, SQL_C_CHAR, name, sizeof(name), &name_ind);
    SQLGetData(stmt, 3, SQL_C_SLONG, &age, 0, &age_ind);

    printf("<tr>");
    printf("<td>%d</td>", id);
    printf("<td>");
    html_escape_print(stdout, (const char *)name);
    printf("</td>");
    printf("<td>%d</td>", age);
    printf("</tr>\n");
}

printf("</table>\n");
printf("</body></html>\n");

ここでも、DBから取り出した文字列をHTMLに出力するときはエスケープします。DBに入っている値だから安全、とは考えません。

リソース管理

ODBCでは、失敗した場合でも確保済みのハンドルを解放する必要があります。C言語では、goto cleanup を使って後始末を一箇所にまとめると、漏れを減らせます。

cleanup:
    if (stmt != SQL_NULL_HSTMT) {
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
    }
    if (dbc != SQL_NULL_HDBC) {
        if (connected) {
            SQLDisconnect(dbc);
        }
        SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    }
    if (env != SQL_NULL_HENV) {
        SQLFreeHandle(SQL_HANDLE_ENV, env);
    }

SQLDisconnect() は、接続に成功したあとで呼びます。前述の接続サンプルでは connected フラグを使い、接続できた場合だけ切断するようにしています。

CGIでDBを扱うときの注意点

CGIからDBに接続する場合、リクエストごとにプロセスが起動し、そのたびにDB接続が発生します。学習用途では仕組みが見えやすい一方、高負荷な用途には向きません。

また、次の点に注意します。

DB接続情報をソースコードに直接書くと管理しづらい
ユーザー入力をSQL文字列に連結しない
SQL実行エラーをブラウザにそのまま出さない
HTML出力時はDBの値もエスケープする
DBユーザーには必要な権限だけを与える
本番環境ではHTTPSやネットワーク制限も考える

本書では理解しやすさを優先して接続文字列をコード内に置いています。実際の運用では、設定ファイルや環境変数から読み込む構成を検討してください。

小まとめ

C言語からDBを扱うには、ODBCのような外部APIを使います。
ODBCでは、環境、接続、ステートメントの各ハンドルを確保して使います。
SQL実行後は、戻り値を必ず確認します。
ユーザー入力をSQLに渡すときは、プレースホルダとバインドを使います。
結果をHTMLに出すときは、DBの値でもエスケープします。
失敗時にもリソースを解放できるよう、後始末の流れを決めておきます。

次章では、Cookieとセッションを使って、ログイン状態を管理する方法に進みます。