MENU

Azure Data FactoryのCopy Activityで出る「Unclosed quotation mark」エラーを完全解説｜動的SQLの安全な書き方とconcat/replace実装

Azure

Azure Data Factory（ADF）のCopy Activityで、動的SQLを使った途端に「Unclosed quotation mark」エラーで失敗した――そんな事象は珍しくありません。本記事では原因の仕組みを“式エンジン×SQL”の二重解釈から紐解き、実戦的な回避方法（concat/replace/format、検証観点、再発防止策、代替アーキテクチャ）までを一気通貫で解説します。

目次

エラーの概要と再現条件

次のように、Copy Activity の Source（Azure SQL / SQL Server） に動的クエリを記述したケースを想定します。

SELECT TOP 10 * FROM dbo.[Master] WITH (NOLOCK)
WHERE PlanName='@{pipeline().parameters.PlanName}'

実行時、以下のようなエラーで失敗します。

Incorrect syntax near 'WHERE PlanName=' ...
Unclosed quotation mark after the character string ...

このエラーは、文字列リテラルのクォート（'）が閉じていないことを示しています。根本原因は次章のとおりです。

なぜ「Unclosed quotation mark」が起きるのか（仕組み）

ADF の動的コンテンツは、「@{ ... }」の部分だけが実行時に評価され、それ以外は 静的文字列として SQL に渡されます。上記クエリでは、'@{ ... }' として 式の前後を単一引用符（'）で囲んでいます。

もしパラメータ PlanName が testplan なら、最終的な SQL は次のように正しく展開されます。

... WHERE PlanName = 'testplan'

しかし、値の中にシングルクォートが含まれると（例：O'Brien）、SQL の文字列リテラル規則では O''Brien のように 2 連続（''）でエスケープしなければなりません。ところが、素直に '@{pipeline().parameters.PlanName}' と書くと、最終 SQL は以下のようになり、途中で閉じずに途切れてしまいます。

... WHERE PlanName = 'O'Brien'
                               ^ ここで文字列が途切れる → Unclosed quotation mark

さらに、次の要因が重なると不整合が起きやすくなります。

  • 値に タブ・改行（\t / \r / \n）が紛れ込んでいる。
  • コピー＆ペースト由来の スマートクォート（全角/曲がった引用符）が混在する。
  • 動的コンテンツを一部だけ式にし、残りを文字列にした結果、クォートの総数が奇数になっている。

最も簡単で堅牢な対処：concatで SQL を組み立てる

ADF の式だけで 安全な SQL 文字列を作るには、concat と ''（シングルクォート二連）を使います。

@concat(
  'SELECT TOP 10 * FROM dbo.[Master] WITH (NOLOCK) WHERE PlanName = ''',
  string(pipeline().parameters.PlanName),
  ''''
)

ポイント：

  • ''' は SQL に 1 個の ' を出力するためのエスケープ（式文字列内で二重に必要）。
  • string() で明示的に文字列化し、null や数値を巻き込んだ時の意図しない挙動を避ける。

ただしこれだけでは 値の中の単一引用符を逃がせません。次の「より安全な対策」を合わせることで、実運用に耐える堅牢性になります。

より安全な対策：値側のクォートを二重化（replace）

PlanName の値に含まれる ' を '' に置換してから結合します。

@concat(
  'SELECT TOP 10 * FROM dbo.[Master] WITH (NOLOCK) WHERE PlanName = ''',
  replace(string(pipeline().parameters.PlanName), '''', ''''''),
  ''''
)

これにより、O'Brien → O''Brien に正規化され、SQL の文字列リテラルとして常に整合します。

Unicode カラム（NVARCHAR）に厳密対応したい場合は、N プレフィックスを付けます。

@concat(
  'SELECT TOP 10 * FROM dbo.[Master] WITH (NOLOCK) WHERE PlanName = N''',
  replace(string(pipeline().parameters.PlanName), '''', ''''''),
  ''''
)

format による可読性重視の書き方

テンプレート文字列を使うと見通しが良くなります。

@format(
  'SELECT TOP 10 * FROM dbo.[Master] WITH (NOLOCK) WHERE PlanName = N''{0}''',
  replace(string(pipeline().parameters.PlanName), '''', '''''')
)

{0} 部分に、エスケープ済みの値だけを差し込む発想です。

デバッグの肝：sqlReaderQuery を必ず目視確認

ADF の Copy Activity 実行結果では、「Input」側に sqlReaderQuery（送信された最終 SQL） が表示されます。必ずこれをコピーして SSMS 等で単独実行し、SQL として成立するか確認します。

確認ポイント良い例悪い例（エラー誘発）
クォートの対... WHERE PlanName = 'test'... WHERE PlanName = 'test
内部クォートのエスケープ'O''Brien''O'Brien'
余計な空白・改行... WHERE PlanName = 'a'... WHERE PlanName = 'a '

原因と対処の早見表

症状主因対処
Unclosed quotation mark値内の ' 未エスケープreplace(value, '''', '''''') で二重化
Incorrect syntax near …式と文字列の混在でクォート奇数concat で一貫して組み立て
Unicode欠落N プレフィックス無しN''...'' を使用
環境差で再現スマートクォート混入全て ASCII の ' に置換

堅牢化のための追加チェック（実務テンプレート）

実運用では、以下の正規化を値に適用してからクエリへ挿入すると堅牢性が上がります。

@{ 
  // 疑似コード（式を段階化する考え方）
  // 1) 文字列化
  // 2) トリム
  // 3) 改行・タブ除去
  // 4) クォート二重化
}
@concat(
  '... WHERE PlanName = N''',
  replace(
    replace(
      replace(
        trim(string(pipeline().parameters.PlanName)),
        '\n', ''
      ),
      '\r', ''
    ),
    '''', ''''''
  ),
  ''''
)

「そこまでやるのか？」と思うかもしれませんが、ペイロード由来の不可視文字がエラー原因になることは現場で非常に多いです。

Copy Activity 設定例（最小構成）

Source の Use query（あるいは sqlReaderQuery）に次の式を設定します。

@format(
  'SELECT TOP 10 * FROM dbo.[Master] WITH (NOLOCK) WHERE PlanName = N''{0}''',
  replace(trim(string(pipeline().parameters.PlanName)), '''', '''''')
)

パイプライン パラメータ側は例えば次のように定義します。

{
  "name": "PlanName",
  "type": "String",
  "defaultValue": ""
}

SSMS 単体検証のすすめ（切り分け）

ADF 側の問題か、SQL 側の文法かを切り分けるために、展開後の完全な SQL を SSMS に貼り付けて実行します。

  • SQL が単独で成功 → ADF の式構築や値の前処理を疑う。
  • SQL が単独でも失敗 → クエリ自体の文法・オブジェクト・権限を点検。

より良い設計への置き換え案（ベストプラクティス）

動的 SQL を直接投げる設計は、保守性・安全性・テスト容易性の観点で弱いです。次の代替を検討してください。

Stored Procedure Activity で抽出を固定化

  • 入力パラメータを NVARCHAR で受け、内部で安全に使用（sp_executesql でパラメータ化など）。
  • 結果を一時テーブルやステージングに出力 → Copy Activity は固定テーブルから吸い上げ。

テーブル値関数（TVF）を活用

  • SELECT * FROM dbo.fn_MasterByPlanName(@p) のように、引数付き TVFでロジックをサーバ側に集約。

ビュー＋固定クエリへの集約

  • フィルタをビュー側へ寄せ、ADF では なるべく定型クエリにする。

これらは SQL インジェクション耐性の向上にも寄与します。

セキュリティ観点（SQL インジェクションの最小化）

  • 外部入力を元に動的 SQL を組み立てる場合は、ホワイトリスト検証（許可文字のみ許す）を検討。
  • 文字列連結ではなく、可能ならパラメータ化（Stored Procedure + sp_executesql）。
  • どうしても連結するなら、replace(value, '''', '''''') による 単一引用符の二重化は最低限。

NOLOCK の注意点

WITH (NOLOCK) は読み取りブロッキングを回避できますが、ダーティリード・ゴーストリードのリスクがあります。ETL の特性上許容できるか、SLA に照らして判断しましょう。必要に応じて READ COMMITTED SNAPSHOT 等の併用を検討します。

現場で使えるスニペット集

部分一致（LIKE）

@format(
  'SELECT ... WHERE PlanName LIKE N''%{0}%''',
  replace(trim(string(pipeline().parameters.PlanName)), '''', '''''')
)

IN 句（CSV を安全に展開）

CSV で渡された一覧を 安全化してから連結します（実際は Stored Procedure で TVP を推奨）。

@concat(
  'SELECT ... WHERE PlanName IN (',
  join(
    array(
      // 例: 値の配列を map でエスケープしてから join
    ),
    ','
  ),
  ')'
)

注：IN 句の生連結は危険度が高いので、基本は TVP か一時テーブルに落とす設計を推奨します。

日付リテラル

@format(
  'WHERE CreateDate >= CAST(N''{0}'' AS datetime2(0))',
  replace(string(pipeline().parameters.FromDate), '''', '''''')
)

チェックリスト（実行前の最終確認）

  • Input.sqlReaderQuery をコピーして SSMS で実行 → 文法OK？
  • 値の trim、改行、タブ除去を実施した？
  • 単一引用符の二重化（replace）を入れた？
  • Unicode 列には N プレフィックスを付けた？
  • 値に スマートクォート（’ や ‘）が混入していない？
  • 可能なら Stored Procedure / TVF へ移行できないか検討した？

よくある誤りと対照例

誤った式問題点正しい式
WHERE PlanName='@{pipeline().parameters.PlanName}'値内の ' を未エスケープreplace(..., '''', '''''') を介してから結合
@concat('... PlanName = ''', pipeline().parameters.PlanName, '''')PlanName が null/数値だと意図外string() と trim() を挟む
'@{...}' + '@{...}'（混在）クォート数の管理が難しいconcat や format で一括生成

エラーメッセージの読み方

Incorrect syntax near 'WHERE PlanName=' は、直前までの文字列を SQL が解析できた位置を示します。つまり、この直後に来るはずの ' が 閉じられずに終端へ到達した、ということです。したがって、疑うべきは値の中身（未エスケープな '・不可視文字・スマートクォート）です。

パフォーマンスの補足

  • フィルタ列（PlanName）に インデックスがあるか確認。
  • TOP 10 は開発時の検証には有効だが、本番は 必要な列のみを明示し、転送量を抑制。
  • 可能であれば 列投影（mapping） と プッシュダウンを活用し、Source 側で絞り込む。

トラブル対応の実例（ケーススタディ）

ケース1：シンプルな名前でも失敗

原因はスマートクォート。Word からコピペした結果、’ が混入。ASCII の ' に置換して解決。

ケース2：データにアポストロフィ

O'Brien を含むレコードで失敗。replace(..., '''', '''''') を追加して解消。sqlReaderQueryの目視で一撃で特定。

ケース3：国際化（多言語）

韓国語・中国語のプラン名でマッチしない。N'' を付けて NVARCHAR として解釈させて解決。

まとめ

ADF の Copy Activity で発生する「Unclosed quotation mark」は、式エンジンと SQL の文字列規則のズレから生じます。対策の要諦は次の 3 点です。

  1. クエリは concat/format で一貫して組み立てる
  2. 値側の ' を replace(..., '''', '''''') で二重化（Unicode は N''）
  3. sqlReaderQuery を必ず目視し、SSMS で単独検証

動的 SQL による柔軟性は魅力ですが、保守性・安全性のためには Stored Procedure / TVF といったサーバ側の抽象化も強力です。プロジェクトの要件に合わせ、最小の実装で最大の堅牢性を目指してください。

付録：そのまま使える完全版ひな形

最小限の正規化（trim＋改行除去＋replace）と Unicode 対応を施した、実用度の高いテンプレです。

@format(
  'SELECT TOP 10 * FROM dbo.[Master] WITH (NOLOCK) WHERE PlanName = N''{0}''',
  replace(
    replace(
      replace(
        trim(string(pipeline().parameters.PlanName)),
        '\n',''
      ),
      '\r',''
    ),
    '''',''''''
  )
)

LIKE 検索版：

@format(
  'SELECT * FROM dbo.[Master] WITH (NOLOCK) WHERE PlanName LIKE N''%{0}%''',
  replace(
    replace(
      replace(
        trim(string(pipeline().parameters.PlanName)),
        '\n',''
      ),
      '\r',''
    ),
    '''',''''''
  )
)
Azure
SQL Server エラー対処 動的SQL Azure Data Factory Copy Activity

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次