NVARCHARリテラルのNプレフィックス漏れで日本語・絵文字がエラーなく文字化けする

この文書の適用条件

そのまま実行できるコマンド

-- 対象: SQL Server 2012 以降 / Azure SQL Database・Managed Instance
-- 権限: メタデータの参照はpublicロールで可。個々のデータ確認には対象テーブルのSELECT権限が必要
-- 変更作業: なし（参照のみ）
-- Production実行: 可能
-- 非ASCII文字を保持しうる nvarchar / nchar 列を一覧化する（調査対象の絞り込み用）
SELECT
    t.name AS table_name,
    c.name AS column_name,
    ty.name AS type_name,
    c.max_length
FROM sys.columns AS c
INNER JOIN sys.tables AS t
        ON t.object_id = c.object_id
INNER JOIN sys.types AS ty
        ON ty.user_type_id = c.user_type_id
WHERE ty.name IN ('nvarchar', 'nchar')
ORDER BY t.name, c.column_id;

# 対象: アプリケーション・ストアドプロシージャのソースコードリポジトリ
# 権限: リポジトリの読み取り権限
# 変更作業: なし（参照のみ）
# Production実行: 可能
#
# .sql ファイル内で、Nプレフィックスの無いシングルクォート文字列リテラルのうち
# 非ASCII文字を含むものを検出する（正規表現なので誤検知はあり、ヒット箇所は目視確認が前提）
grep -RnoP "(?<![Nn])'[^']*[^\x00-\x7F][^']*'" --include=*.sql .

# 文字列結合や sp_executesql で動的SQLを組み立てている箇所も洗い出す
grep -RniE "(sp_executesql|EXEC\(|exec\()" --include=*.sql --include=*.cs --include=*.ts .

こういう状況で使います

• 日本語・韓国語・絵文字を含むデータを登録すると、エラーは出ないのに保存後の値が「?」や別の文字に置き換わっている
• アプリケーションのテスト（ASCIIのみのテストデータ）では問題が起きず、本番投入後や実データ投入後に初めて発覚する
• 動的SQL（文字列結合やsp_executesql）を経由する書き込み経路でだけ発生する
• 一度「直した」はずのコードベースで、別の箇所・別のプロジェクトで同じ症状が再発する

考えられる原因（可能性の高い順）

1. 01

   文字列リテラルが接続の既定コードページへ暗黙変換される

   Nプレフィックスの無いシングルクォート文字列リテラルは、まず非Unicodeの文字列として解釈され、接続の既定コードページに変換されたうえでNVARCHAR型に代入される。そのコードページで表現できない文字は変換時に失われる。

2. 02

   動的SQLの組み立てでNVARCHARを意識せずに文字列結合している

   文字列結合やsp_executesqlでSQL文を組み立てる際、埋め込む値の文字列リテラルにNプレフィックスを付け忘れやすい。特に値がパラメータではなく文字列として直接埋め込まれている場合に起きやすい。

3. 03

   エラーも警告も出ないため、テストで気づきにくい

   コンパイルエラーにも実行時エラーにもならないため、ASCIIのみのテストデータでは正常に見える。実際の非ASCIIデータが入って初めて症状が現れる。

4. 04

   一度失われた文字は、独立した参照元がなければ復旧できない

   暗黙変換で失われた文字はNVARCHAR列の中には残っていない。別システムのログや元データの控えなど、独立した参照元が別に無い限り、後から正しい値を復元することはできない。

確認手順

1. 1

   対象テーブルのnvarchar/nchar列を洗い出す

   参照のみ

   上記の診断SQLで、非ASCII文字を保持しうる列を一覧化する。

2. 2

   ソースコード内のNプレフィックス漏れを検索する

   参照のみ

   grepで疑わしい文字列リテラルと動的SQL構築箇所を洗い出し、1件ずつ確認する。

3. 3

   既存データの破損兆候を確認する

   参照のみ

   本来非ASCIIのはずの列で、意図しない文字への置き換わりが無いか個別に確認する。

4. 4

   破損データの独立した参照元があるか確認する

   参照のみ

   外部システムのログや別経路の記録など、正しい値を持つ別の情報源が残っているかを確認する。

対応方法

バージョン・環境による違い

これで解決しない場合に確認すること

• 自分たちのコードベースで同じパターンが他にないか横断的に検索したか

  1箇所直しただけでは、同種の書き方が他のストアドプロシージャやバッチ処理に残っている可能性がある。

• 破損が疑われる列について、独立した参照元が別に残っているか

  無い場合、復旧できないことを前提に今後の運用を設計する。

• パラメータ化されたクエリ経由の書き込み経路は、そもそもこの問題の影響を受けない

  ORMやドライバのパラメータバインドを使っている経路は対象外であることを確認し、優先度を正しく判断する。

この文書の根拠と限界

よくある質問

この文書がカバーする質問

• SQL Serverで日本語や絵文字が文字化けする原因
• NプレフィックスなしのSQL文字列で何が起きるか

リスク表示の意味

• 参照のみデータと設定を変更しません。
• 低影響は限定的ですが、権限と負荷の確認が必要です。
• 中性能・ロック・コストに影響する可能性があります。
• 高障害・データ損失・復旧作業が発生する可能性があります。
• 専門家レビュー必須本番適用前に別途レビューが必須です。

GIIPの対応範囲

GIIPでは新規に追加するSQLコードに対して、Nプレフィックス漏れを機械的に検出するチェックをレビュー工程に組み込んでいます。同じパターンの事故が複数のプロジェクトで形を変えて再発した経験から、レビューでの目視確認だけに頼らず、機械的な検出を必ず併用する方針としています。

関連サービス

• 文字化け・データ破損の調査を依頼する
• 本番投入前のレビュー体制を相談する