PostgreSQL エスケープ文字完全ガイド：特殊文字を正しく扱いSQLインジェクションを防ぐ方法

データベース・SQL

「シングルクォートを含むデータが登録できない…」 「LIKE検索で%や_が正しく検索できない」 「SQLインジェクション攻撃が心配」

PostgreSQLでアプリケーション開発をしていると、こんな問題に直面したことはありませんか？

エスケープ文字の正しい理解と使用は、データベースアプリケーションの安全性と信頼性を確保する上で不可欠です。不適切な処理は、データの破損やセキュリティ脆弱性につながる可能性があります。

この記事では、PostgreSQLにおけるエスケープ文字の基本から、実践的な使用方法、セキュリティ対策まで、実例とベストプラクティスを交えながら徹底的に解説していきます。

スポンサーリンク

🔤 PostgreSQLのエスケープ文字基本

文字列リテラルの基本ルール

シングルクォートのエスケープ

基本的なエスケープ方法：

-- エラーになる例
INSERT INTO messages (content) VALUES ('It's a nice day');
-- ERROR: syntax error at or near "s"

-- 正しい方法1：シングルクォートを2つ重ねる（標準SQL）
INSERT INTO messages (content) VALUES ('It''s a nice day');

-- 正しい方法2：ドルクォート記法（PostgreSQL拡張）
INSERT INTO messages (content) VALUES ($$It's a nice day$$);

-- 正しい方法3：エスケープ文字列（E''）を使用
INSERT INTO messages (content) VALUES (E'It\'s a nice day');

エスケープ文字一覧

標準エスケープシーケンス

E”記法で使用可能なエスケープシーケンス：

-- 主要なエスケープシーケンス
SELECT E'改行: \n' as newline;
SELECT E'タブ: \t文字' as tab;
SELECT E'バックスラッシュ: \\' as backslash;
SELECT E'シングルクォート: \'' as single_quote;
SELECT E'ダブルクォート: \"' as double_quote;
SELECT E'キャリッジリターン: \r' as carriage_return;
SELECT E'バックスペース: \b' as backspace;
SELECT E'フォームフィード: \f' as form_feed;

-- 8進数表記（\ooo）
SELECT E'\101';  -- 'A'
SELECT E'\040';  -- スペース

-- 16進数表記（\xhh）
SELECT E'\x41';  -- 'A'
SELECT E'\x20';  -- スペース

-- Unicode表記（\uxxxx, \Uxxxxxxxx）
SELECT E'\u3042';  -- 'あ'
SELECT E'\U0001F600';  -- 😀（絵文字）

標準準拠文字列

standard_conforming_stringsの影響

-- 設定を確認
SHOW standard_conforming_strings;
-- on (PostgreSQL 9.1以降のデフォルト)

-- standard_conforming_strings = on の場合
SELECT 'バックスラッシュ: \';  -- \ がそのまま文字として扱われる
SELECT E'バックスラッシュ: \\';  -- エスケープが必要

-- standard_conforming_strings = off の場合（古い動作）
SET standard_conforming_strings = off;
SELECT 'バックスラッシュ: \\';  -- エスケープが必要
-- WARNING: nonstandard use of escape in a string literal

🔍 LIKE句でのエスケープ

ワイルドカード文字の扱い

%と_のエスケープ

LIKE句の特殊文字：

-- テストデータ
CREATE TEMP TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT
);

INSERT INTO products (name) VALUES 
    ('50% OFF Sale'),
    ('25_percent_discount'),
    ('100% Cotton'),
    ('user_name'),
    ('normal product');

-- 問題：%や_が特殊文字として解釈される
SELECT * FROM products WHERE name LIKE '%50%%';  -- 意図しない結果

-- 解決法1：ESCAPE句を使用
SELECT * FROM products WHERE name LIKE '%50\%%' ESCAPE '\';
-- 結果：'50% OFF Sale'のみ

-- 解決法2：エスケープ関数を作成
CREATE OR REPLACE FUNCTION escape_like(p_string TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN REPLACE(REPLACE(REPLACE(p_string, '\', '\\'), '%', '\%'), '_', '\_');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 使用例
SELECT * FROM products 
WHERE name LIKE '%' || escape_like('50%') || '%' ESCAPE '\';

大文字小文字を無視した検索

ILIKEとエスケープ

-- ILIKE（大文字小文字を区別しない）でもエスケープが必要
SELECT * FROM products 
WHERE name ILIKE '%50\%%' ESCAPE '\';

-- より安全な検索関数
CREATE OR REPLACE FUNCTION safe_search(
    p_table_name TEXT,
    p_column_name TEXT,
    p_search_term TEXT
)
RETURNS TABLE(result_row TEXT) AS $$
DECLARE
    v_escaped TEXT;
    v_query TEXT;
BEGIN
    -- 検索文字列をエスケープ
    v_escaped := REPLACE(REPLACE(REPLACE(p_search_term, '\', '\\'), '%', '\%'), '_', '\_');
    
    -- 動的SQLを構築
    v_query := format(
        'SELECT %I::TEXT FROM %I WHERE %I ILIKE %L ESCAPE %L',
        p_column_name, p_table_name, p_column_name, 
        '%' || v_escaped || '%', '\'
    );
    
    RETURN QUERY EXECUTE v_query;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT * FROM safe_search('products', 'name', '50%');

🎭 正規表現でのエスケープ

正規表現の特殊文字

メタ文字のエスケープ

正規表現で特別な意味を持つ文字：

-- 正規表現のメタ文字
-- . ^ $ * + ? { } [ ] \ | ( )

-- テストデータ
CREATE TEMP TABLE urls (
    id SERIAL PRIMARY KEY,
    url TEXT
);

INSERT INTO urls (url) VALUES 
    ('https://example.com'),
    ('https://sub.example.com'),
    ('http://example.org'),
    ('https://example.com/path?query=1'),
    ('ftp://files.example.com');

-- 問題：ドットがメタ文字として解釈される
SELECT * FROM urls WHERE url ~ 'example.com';
-- 'example.com' だけでなく 'exampleXcom' もマッチしてしまう

-- 解決：エスケープする
SELECT * FROM urls WHERE url ~ 'example\.com';

-- より複雑な例：URLパターンのマッチング
SELECT * FROM urls 
WHERE url ~ '^https?://[^/]+\.example\.com(/.*)?$';

regexp_replace での安全な置換

-- 特殊文字を含む文字列の置換
CREATE OR REPLACE FUNCTION safe_regexp_replace(
    p_text TEXT,
    p_pattern TEXT,
    p_replacement TEXT
)
RETURNS TEXT AS $$
DECLARE
    v_escaped_pattern TEXT;
BEGIN
    -- パターン内の特殊文字をエスケープ
    v_escaped_pattern := regexp_replace(
        p_pattern, 
        '([.\\+*?[^$(){}=!<>|:-])', 
        E'\\\\\\1', 
        'g'
    );
    
    RETURN regexp_replace(p_text, v_escaped_pattern, p_replacement, 'g');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- テスト
SELECT safe_regexp_replace(
    'Price is $10.99 (tax included)',
    '$10.99',
    '$12.99'
);
-- 結果：'Price is $12.99 (tax included)'

🛡️ SQLインジェクション対策

危険なコードの例

絶対にやってはいけないこと

-- ❌ 危険：文字列連結でSQL構築
CREATE OR REPLACE FUNCTION dangerous_search(p_input TEXT)
RETURNS TABLE(user_id INT, username TEXT) AS $$
BEGIN
    RETURN QUERY EXECUTE 
        'SELECT user_id, username FROM users WHERE username = ''' || p_input || '''';
END;
$$ LANGUAGE plpgsql;

-- SQLインジェクション攻撃の例
SELECT * FROM dangerous_search('admin'' OR ''1''=''1');
-- すべてのユーザーが返される！

-- ✅ 安全：パラメータ化クエリ
CREATE OR REPLACE FUNCTION safe_search(p_input TEXT)
RETURNS TABLE(user_id INT, username TEXT) AS $$
BEGIN
    RETURN QUERY 
    SELECT u.user_id, u.username 
    FROM users u 
    WHERE u.username = p_input;
END;
$$ LANGUAGE plpgsql;

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

最も安全な方法

-- プリペアドステートメントの使用
PREPARE safe_insert (TEXT, TEXT) AS
    INSERT INTO users (username, email) VALUES ($1, $2);

-- 実行（特殊文字も安全に処理される）
EXECUTE safe_insert('O''Brien', 'o''brien@example.com');

-- format関数を使った安全な動的SQL
CREATE OR REPLACE FUNCTION dynamic_query_safe(
    p_table_name TEXT,
    p_column_name TEXT,
    p_value TEXT
)
RETURNS TABLE(result JSONB) AS $$
DECLARE
    v_query TEXT;
BEGIN
    -- %I: 識別子（テーブル名、カラム名）を安全にエスケープ
    -- %L: リテラル値を安全にエスケープ
    v_query := format(
        'SELECT to_jsonb(t.*) FROM %I t WHERE %I = %L',
        p_table_name,
        p_column_name,
        p_value
    );
    
    RETURN QUERY EXECUTE v_query;
END;
$$ LANGUAGE plpgsql;

-- 使用例（SQLインジェクション攻撃を自動的に防ぐ）
SELECT * FROM dynamic_query_safe('users', 'username', 'O''Brien');

quote関数ファミリー

PostgreSQL組み込みのエスケープ関数

-- quote_literal: 文字列リテラルを安全にエスケープ
SELECT quote_literal('It''s a test');
-- 結果：'It''s a test'

SELECT quote_literal(NULL);
-- 結果：NULL

-- quote_nullable: NULLも適切に処理
SELECT quote_nullable('test');
-- 結果：'test'

SELECT quote_nullable(NULL);
-- 結果：NULL (文字列として)

-- quote_ident: 識別子（テーブル名、カラム名）をエスケープ
SELECT quote_ident('user');  -- 予約語
-- 結果："user"

SELECT quote_ident('my-table');  -- 特殊文字
-- 結果："my-table"

-- 実践例：安全な動的テーブル作成
CREATE OR REPLACE FUNCTION create_table_safe(
    p_table_name TEXT,
    p_column_defs TEXT[]
)
RETURNS VOID AS $$
DECLARE
    v_sql TEXT;
    v_col_def TEXT;
    v_col_defs TEXT := '';
BEGIN
    -- カラム定義を安全に構築
    FOREACH v_col_def IN ARRAY p_column_defs
    LOOP
        IF v_col_defs != '' THEN
            v_col_defs := v_col_defs || ', ';
        END IF;
        v_col_defs := v_col_defs || v_col_def;
    END LOOP;
    
    -- テーブル作成SQL
    v_sql := format('CREATE TABLE %I (%s)', p_table_name, v_col_defs);
    
    EXECUTE v_sql;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT create_table_safe(
    'user_data', 
    ARRAY['id SERIAL PRIMARY KEY', 'name TEXT NOT NULL']
);

💻 プログラミング言語別の対処法

Python（psycopg2）

import psycopg2
from psycopg2 import sql

# 接続
conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()

# ❌ 危険：文字列連結
user_input = "'; DROP TABLE users; --"
dangerous_query = f"SELECT * FROM users WHERE name = '{user_input}'"
# SQLインジェクション！

# ✅ 安全：パラメータ化クエリ
safe_query = "SELECT * FROM users WHERE name = %s"
cur.execute(safe_query, (user_input,))

# ✅ 動的なテーブル名/カラム名も安全に
from psycopg2 import sql
query = sql.SQL("SELECT {column} FROM {table} WHERE name = %s").format(
    column=sql.Identifier('email'),
    table=sql.Identifier('users')
)
cur.execute(query, ('John',))

Node.js（node-postgres）

const { Client } = require('pg');
const client = new Client();

// ❌ 危険：テンプレートリテラル
const userInput = "'; DROP TABLE users; --";
const dangerous = `SELECT * FROM users WHERE name = '${userInput}'`;

// ✅ 安全：パラメータ化クエリ
const safe = {
    text: 'SELECT * FROM users WHERE name = $1',
    values: [userInput]
};
client.query(safe);

// ✅ 複数パラメータ
const multiParams = {
    text: 'INSERT INTO users (name, email) VALUES ($1, $2)',
    values: ["O'Brien", "o'brien@example.com"]
};
client.query(multiParams);

Java（JDBC）

import java.sql.*;

// ❌ 危険：文字列連結
String userInput = "'; DROP TABLE users; --";
String dangerous = "SELECT * FROM users WHERE name = '" + userInput + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(dangerous);  // SQLインジェクション！

// ✅ 安全：PreparedStatement
String safe = "SELECT * FROM users WHERE name = ?";
PreparedStatement pstmt = connection.prepareStatement(safe);
pstmt.setString(1, userInput);
ResultSet rs = pstmt.executeQuery();

🔧 特殊なケースの処理

JSON/JSONBデータのエスケープ

-- JSONデータ内のエスケープ
CREATE TEMP TABLE json_data (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- 特殊文字を含むJSONの挿入
INSERT INTO json_data (data) VALUES 
    ('{"message": "It''s a \"quoted\" text"}'::JSONB),
    ('{"path": "C:\\Users\\Admin"}'::JSONB),
    ('{"regex": "^[a-z]+$"}'::JSONB);

-- JSONパス演算子でのエスケープ
SELECT data->>'message' FROM json_data;  -- It's a "quoted" text

-- JSON文字列の構築
CREATE OR REPLACE FUNCTION build_json_safe(
    p_key TEXT,
    p_value TEXT
)
RETURNS JSONB AS $$
BEGIN
    RETURN jsonb_build_object(p_key, p_value);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 使用例（自動的にエスケープ）
SELECT build_json_safe('user''s note', E'Line 1\nLine 2');
-- 結果：{"user's note": "Line 1\nLine 2"}

配列データのエスケープ

-- 配列要素のエスケープ
CREATE TEMP TABLE array_data (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);

-- 特殊文字を含む配列
INSERT INTO array_data (tags) VALUES 
    (ARRAY['tag1', 'tag''2', 'tag"3']),
    ('{tag1,"tag''2","tag\"3"}'::TEXT[]);

-- 配列要素の安全な追加
UPDATE array_data 
SET tags = array_append(tags, 'new''tag')
WHERE id = 1;

-- 配列を文字列に変換（自動エスケープ）
SELECT array_to_string(tags, ', ') FROM array_data;

COPYコマンドでのエスケープ

-- CSVファイルへのエクスポート
COPY (
    SELECT 
        id,
        REPLACE(name, E'\n', ' ') as name,  -- 改行を除去
        description
    FROM products
) TO '/tmp/products.csv' 
WITH (FORMAT CSV, HEADER TRUE, QUOTE '"', ESCAPE '"');

-- CSVファイルからのインポート
COPY products (id, name, description) 
FROM '/tmp/products.csv' 
WITH (FORMAT CSV, HEADER TRUE, QUOTE '"', ESCAPE '"');

-- タブ区切りファイル
COPY products TO '/tmp/products.tsv' 
WITH (FORMAT TEXT, DELIMITER E'\t', NULL '\\N');

⚠️ よくあるエラーと対処法

エラー1：unterminated quoted string

-- エラーが発生するケース
INSERT INTO comments (text) VALUES ('User's comment');
-- ERROR: unterminated quoted string at or near "'User'"

-- 解決方法
INSERT INTO comments (text) VALUES ('User''s comment');
-- または
INSERT INTO comments (text) VALUES ($$User's comment$$);
-- または
INSERT INTO comments (text) VALUES (E'User\'s comment');

エラー2：invalid escape sequence

-- エラーが発生するケース
SELECT 'C:\Users\Admin';
-- WARNING: nonstandard use of escape in a string literal

-- 解決方法
SELECT E'C:\\Users\\Admin';  -- E''記法でエスケープ
SELECT 'C:\Users\Admin';     -- standard_conforming_strings = on なら可
SELECT $$C:\Users\Admin$$;   -- ドルクォート記法

エラー3：LIKE句での予期しない結果

-- 問題：アンダースコアが任意の1文字にマッチ
SELECT * FROM files WHERE filename LIKE 'log_2024%';
-- log_2024.txt だけでなく logX2024.txt もマッチする

-- 解決：エスケープする
SELECT * FROM files WHERE filename LIKE 'log\_2024%' ESCAPE '\';

📊 パフォーマンスへの影響

エスケープ処理のオーバーヘッド

-- パフォーマンステスト
CREATE TEMP TABLE perf_test (
    id SERIAL PRIMARY KEY,
    data TEXT
);

-- 100万件のデータ投入
INSERT INTO perf_test (data)
SELECT 'Test ''data'' with quotes: ' || i
FROM generate_series(1, 1000000) i;

-- インデックス作成
CREATE INDEX idx_perf_test_data ON perf_test(data);

-- パフォーマンス比較
EXPLAIN ANALYZE
SELECT * FROM perf_test 
WHERE data LIKE '%Test ''data''%';  -- エスケープあり

EXPLAIN ANALYZE  
SELECT * FROM perf_test 
WHERE data LIKE '%Test data%';  -- エスケープなし

-- 結果：エスケープ自体のオーバーヘッドは極小

最適化のヒント

-- 1. 全文検索インデックスの活用（エスケープ不要）
CREATE INDEX idx_fulltext ON products 
USING gin(to_tsvector('english', name));

SELECT * FROM products 
WHERE to_tsvector('english', name) @@ to_tsquery('english', '50%');

-- 2. トリグラムインデックス（特殊文字も扱える）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_trgm ON products USING gin(name gin_trgm_ops);

SELECT * FROM products 
WHERE name % '50% OFF';  -- 類似検索

💡 ベストプラクティス

セキュリティガイドライン

-- 1. 常にパラメータ化クエリを使用
-- 2. 動的SQLではformat()関数を使用
-- 3. ユーザー入力は必ず検証

CREATE OR REPLACE FUNCTION validate_and_insert(
    p_username TEXT,
    p_email TEXT
)
RETURNS VOID AS $$
BEGIN
    -- 入力検証
    IF p_username !~ '^[a-zA-Z0-9_]+$' THEN
        RAISE EXCEPTION 'Invalid username format';
    END IF;
    
    IF p_email !~ '^[^@]+@[^@]+\.[^@]+$' THEN
        RAISE EXCEPTION 'Invalid email format';
    END IF;
    
    -- 安全な挿入
    INSERT INTO users (username, email) 
    VALUES (p_username, p_email);
END;
$$ LANGUAGE plpgsql;

コーディング規約

-- 推奨：明確で一貫性のある記法

-- 1. 短い文字列：シングルクォート2重
'It''s a simple text'

-- 2. 複雑な文字列：ドルクォート
$$Multiple lines
with 'quotes' and "double quotes"$$

-- 3. 特殊文字が多い：E''記法
E'Path: C:\\Users\\Admin\nNext line'

-- 4. 動的SQL：format関数
format('SELECT * FROM %I WHERE %I = %L', 
       table_name, column_name, value)

エラーハンドリング

CREATE OR REPLACE FUNCTION safe_operation(p_input TEXT)
RETURNS TEXT AS $$
DECLARE
    v_result TEXT;
BEGIN
    BEGIN
        -- メイン処理
        v_result := process_data(p_input);
    EXCEPTION
        WHEN OTHERS THEN
            -- エラー情報をログに記録
            RAISE WARNING 'Error processing input: %, Error: %', 
                         p_input, SQLERRM;
            -- エスケープされた値を返す
            RETURN quote_literal(p_input);
    END;
    
    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

📚 まとめ：エスケープ文字で安全なPostgreSQLアプリケーションを

PostgreSQLのエスケープ文字の重要ポイント：

✅ シングルクォートは重ねるかドルクォート記法を使用 ✅ LIKE句では%と_をエスケープ ✅ SQLインジェクション対策は必須 ✅ パラメータ化クエリとformat関数を活用 ✅ 各プログラミング言語の安全な実装方法を理解

エスケープ文字を正しく理解し使用することで、安全で堅牢なアプリケーションを構築できます。

今すぐ実践すべき3つのアクション：

  1. 既存コードでSQLインジェクションの脆弱性をチェック
  2. format関数を使った動的SQLに書き換える
  3. プリペアドステートメントの使用を標準化する

これらの知識を活用して、セキュアなPostgreSQLアプリケーションを開発しましょう！

Youtube

神話・歴史・伝承の解説チャンネル

スポンサーリンク

コメント

タイトルとURLをコピーしました