SQLインジェクションや他の攻撃からの防御: PREPARE, EXECUTE, parameterized queries

じゃあ、まず何を守るのか、もっと言うと「誰から」守るのかを知ろう。このレベルの最初でもSQLインジェクションについてちょっと触れたけど、これはデータベースへの攻撃の中でも超有名でヤバいやつなんだ。攻撃の仕組みはこう：悪意のあるやつが、君のクエリに悪いSQLコードを送り込んで、「だます」ことで本来アクセスできないデータにアクセスしようとする。じゃあ、もうちょい詳しく見ていこう。

SQLインジェクションの例

例えば、君がユーザー名とパスワードを入力するだけのシンプルなWebアプリを作ったとしよう。バックエンドは、データベースにユーザーがいるかどうかをSQLクエリでチェックする：

SELECT * FROM users WHERE username = 'admin' AND password = 'password123';

このクエリは普通に見えるよね…ユーザーが正しいデータを入力してる間は。でも、もし誰かがこう入力したらどうなる？

• ユーザー名: admin' --
• パスワード: （ここは空欄のまま）

その結果、クエリはこんな感じになる：

SELECT * FROM users WHERE username = 'admin' -- AND password = 'password123';

SQLで--はコメントの始まりを意味するから、それ以降は全部無視される。つまり、パスワードのチェックがスルーされて、悪いやつが管理者としてログインできちゃう！

SQLインジェクションの潜在的な結果

SQLインジェクションはマジでヤバい結果をもたらす：

1. 許可されてないデータへのアクセス。例えば、悪いやつがユーザーのパスワードみたいな機密データをゲットできる。
2. データの削除や改ざん。誰かがテーブルを全部消したり、めちゃくちゃにしたりできる。
3. 好き勝手なSQLコードの実行。例えばDROP DATABASEコマンドを実行されたら…うん、悪夢だよね。

でも、ビビる必要はない！PostgreSQLには、こういう攻撃から守るためのツールがちゃんと用意されてる。

どうやって守る？SQLインジェクション防止の方法

1. 準備済みクエリ（PREPAREとEXECUTE）の利用

準備済みクエリ（prepared statements）は、SQLコードのためのレシピみたいなもの。一度「準備」しておいて、あとからデータだけ渡す。これで悪いコードを混ぜ込むのが不可能になる。

正しいやり方の例を見てみよう：

PREPAREでクエリを準備する。

PREPARE user_login (text, text) AS
SELECT * 
FROM users 
WHERE username = $1 AND password = $2;

このクエリは$1と$2という2つのパラメータを待ってて、実際の値はあとで渡す。

EXECUTEでクエリを実行する。

EXECUTE user_login('admin', 'password123');

このとき、PostgreSQLはユーザーのデータを自動的にエスケープしてくれるから、悪いSQLコードの混入を防げる。

このやり方のメリット：

• SQLインジェクションができない。パラメータはただのデータとして扱われて、SQLコードの一部にはならないから。
• クエリの実行が安全で速くなる。実行プランがキャッシュされるからね。

2. パラメータ化クエリ（Parameterized Queries）

この方法は、PythonやJavaみたいなプログラミング言語で書かれたアプリで特によく使われる。PREPAREやEXECUTEを手動で使う代わりに、ライブラリやORMがパラメータを自動で処理してくれる。

Pythonとpsycopg2ライブラリの例：

import psycopg2

connection = psycopg2.connect(
    dbname="your_db",
    user="your_user",
    password="your_password",
    host="localhost",
    port="5432"
)

cursor = connection.cursor()

# パラメータ化クエリの利用
username = "admin"
password = "password123"
query = "SELECT * FROM users WHERE username = %s AND password = %s"
cursor.execute(query, (username, password))

# 君のデータは完全に安全！
result = cursor.fetchall()
print(result)

SQLクエリの%sに注目してね。ここにパラメータが入る。psycopg2ライブラリが安全にデータを渡してくれる。

3. 入力データの検証

ユーザーが入力したデータを渡すときは、それがちゃんと期待通りかどうかチェックしよう。例えば：

• テキストデータなら、正規表現で禁止文字が入ってないか確認する。
• 数字データなら、本当に数字かどうかチェックする。

Pythonの例：

import re

username = input("ユーザー名を入力してね: ")

# 英字、数字、アンダースコアだけ許可
if re.match(r"^\w+$", username):
    print("ユーザー名はOKだよ")
else:
    print("危険なユーザー名だよ！")

4. 最小限の権限を使う

クエリを実行するロールには、本当に必要な最小限の権限だけを与えよう。例えば、DROP TABLEやALTER TABLEの権限は不要なら絶対に与えない。

5. 怪しい動作のログを取る

PostgreSQLのパラメータを使って、ユーザーのアクティビティを監視できる：

• log_statement = 'all' — 全てのクエリをログに残す。
• log_connections = on — 全てのデータベース接続をログに残す。

これらの設定で、怪しい動きを見つけやすくなるよ。

実装例

例1: SQLで準備済みクエリを使う

-- 準備済みクエリを作成
PREPARE check_credentials (text, text) AS
SELECT * FROM users WHERE username = $1 AND password = $2;

-- 安全なパラメータでクエリを実行
EXECUTE check_credentials('admin', 'password123');

例2: Pythonでパラメータ化クエリを使う

query = "UPDATE users SET last_login = NOW() WHERE username = %s"
username = "admin"
cursor.execute(query, (username,))

セキュリティのおすすめ

必ず入力データをチェックしよう。 ユーザーから来たデータは絶対に信用しちゃダメ。

準備済みクエリかパラメータ化クエリだけ使おう。 これがSQLインジェクションへの最強の壁だよ。

ロールには本当に必要な最小限の権限だけを与えよう。 もし悪いやつに侵入されても被害を最小限にできる。

ログを設定して、定期的にチェックしよう。 データベースの動きをちゃんと把握しておこう。

SQLインジェクションは確かに怖いけど、準備済みクエリやパラメータ化クエリ、そして良いプラクティスを守れば、君のデータベースはしっかり守れる。夜も安心して眠れるよ！PostgreSQLには全部のツールが揃ってるから、どんどん使ってみて！