Missing N prefix on NVARCHAR literals silently mangles Japanese/Korean text and emoji without any error

この文書の適用条件

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

-- 対象: 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 .

こういう状況で使います

• Inserting data containing Japanese, Korean, or emoji raises no error, but the stored value shows "?" or different characters
• Application tests (which use ASCII-only test data) show no problem; the issue surfaces only after going to production or loading real data
• It only happens on write paths that go through dynamic SQL (string concatenation or sp_executesql)
• The same symptom recurs in a different place or a different project, even in a codebase that was supposedly already "fixed"

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

1. 01

   The string literal is implicitly converted to the connection's default code page

   A single-quoted string literal without the N prefix is first interpreted as a non-Unicode string, then converted to the connection's default code page before being assigned to the NVARCHAR type. Characters that cannot be represented in that code page are lost during the conversion.

2. 02

   Dynamic SQL is assembled by string concatenation without thinking about NVARCHAR

   When building SQL statements via string concatenation or sp_executesql, it is easy to forget the N prefix on the literal being embedded — especially when the value is embedded directly as a string rather than passed as a parameter.

3. 03

   No error or warning is raised, so tests rarely catch it

   Because neither a compile-time nor a runtime error occurs, ASCII-only test data looks perfectly fine. The symptom appears only once real non-ASCII data is written.

4. 04

   Once a character is lost, it cannot be recovered without an independent source

   A character lost to implicit conversion no longer exists anywhere inside the NVARCHAR column. Unless there is some independent source — logs in another system, a copy of the original data — the correct value can never be restored afterward.

確認手順

1. 1

   Enumerate the nvarchar/nchar columns on the target table

   参照のみ

   Use the diagnostic SQL above to list columns that could hold non-ASCII characters.

2. 2

   Search the source code for missing N prefixes

   参照のみ

   Use grep to find suspicious string literals and places that build dynamic SQL, then check each one individually.

3. 3

   Check existing data for signs of corruption

   参照のみ

   For columns that should hold non-ASCII text, individually check whether values have been replaced by unintended characters.

4. 4

   Check whether an independent source exists for the corrupted data

   参照のみ

   Check whether some other source of truth — an external system's logs, a record from a different path — still holds the correct values.

対応方法

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

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

• Did you search across the whole codebase for the same pattern, not just one spot?

  Fixing a single location does not rule out the same pattern existing in other stored procedures or batch jobs.

• For columns suspected of corruption, does an independent source still exist?

  If not, design future operations on the assumption that recovery is impossible.

• Write paths that use parameterized queries are not affected by this issue in the first place

  Confirm that paths using ORM or driver parameter binding are out of scope, so you prioritize correctly.

この文書の根拠と限界

よくある質問

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

• Why does Japanese or Korean text get mangled in SQL Server
• What happens with a SQL string that has no N prefix

リスク表示の意味

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

GIIPの対応範囲

GIIP has built a review-stage check that mechanically detects missing N prefixes for any newly added SQL code. Because the same class of incident has recurred, in different forms, across multiple projects, our policy is to never rely solely on manual review and to always pair it with automated detection.

関連サービス

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