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

Applicability of This Document

Ready-to-Run Commands

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

When to Use This

• 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"

Possible Causes (Most Likely First)

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.

Diagnostic Steps

1. 1

   Enumerate the nvarchar/nchar columns on the target table

   Read-only

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

2. 2

   Search the source code for missing N prefixes

   Read-only

   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

   Read-only

   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

   Read-only

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

Solutions

Differences by Version and Environment

If This Doesn't Resolve It

• 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.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

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

What the Risk Labels Mean

• Read-onlyDoes not change data or settings.
• LowImpact is limited, but permissions and load should be checked.
• MediumMay affect performance, locking, or cost.
• HighMay cause an outage, data loss, or require recovery work.
• Expert Review RequiredRequires a separate review before applying to production.

GIIP's Scope of Support

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.

Related Services

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