SQL INSERTのシングルクォートエスケープが必要な理由

公開ツールCSV → SQL ジェネレーター

目次
  1. SQLはなぜ文字列をシングルクォートで囲むのか
  2. 値の中にシングルクォートがあると何が起きるのか
  3. なぜクォートを2回繰り返して書くのか
  4. ツールが値ごとの判断を代わりに行う仕組み
  5. すべてのカラムをクォートで囲むわけではない
  6. まとめ
  7. よくある質問

CSVファイルをSQLのINSERT文に変換していると、突然構文エラーが表示されることがあります。原因を調べると、ほとんどの場合は値の中にシングルクォート(アポストロフィ)が1つ入っているだけです。たとえば商品名が「Women's Shoes」のようにアポストロフィを含んでいると、その値をそのままSQL文に入れた瞬間に、残りの文全体が壊れてしまいます。

この現象は、データがおかしいから起きる問題ではありません。SQLが文字列を表現する方法そのものから生じる必然的な結果です。仕組みがわかれば、なぜエスケープ処理が必要なのか、そしてなぜ値を1つずつ手作業で直すのではなく変換の過程で自動的に処理すべきなのかがはっきり見えてきます。

まとめ ① SQLは文字列の値をシングルクォート1組で囲み、「ここからここまでがテキスト」と示します。 ② 値の中にシングルクォートがそのまま入っていると、SQLパーサーはその地点で文字列が終わったと誤解します。 ③ そのため、値の中のシングルクォートは2回繰り返して書くことで、「これは文字列を終わらせる記号ではなく、実際の文字」だと区別されます。

SQLはなぜ文字列をシングルクォートで囲むのか

SQL文では、テキストの値は必ずシングルクォート1組で囲みます。たとえば名前のカラムに「モニター」を入れるなら、'monitor'のように前後をシングルクォートで囲まないと、データベースはこれが数値や命令ではなく文字そのもののテキストであることを認識できません。

このシングルクォートは単なる飾りではなく、文字列の始まりと終わりを示す区切り文字です。パーサーは開きのシングルクォートを見つけると「ここからテキストの区間だ」と認識し、次のシングルクォートに当たった瞬間に「ここでテキストが終わった」と判断します。このルール自体は、どのSQL方言を使っても共通して適用されます。

値の中にシングルクォートがあると何が起きるのか

問題は、値そのものにシングルクォートが含まれているときに発生します。商品名が「Women's Shoes」だと仮定して、この値をそのまま移すと、VALUES ('Women's Shoes') という文ができあがります。

パーサーの側では、この文を別の読み方で解釈します。最初のシングルクォートで文字列が始まり、2つ目のシングルクォートであるWomenのすぐ後ろのものに当たった瞬間に「文字列はここで終わった」と判断してしまうのです。すると残った s Shoes' は文字列の外にぽつんと置かれた文字の塊になり、パーサーが期待していた位置(カンマや閉じ括弧)に想定外の文字が来るため、構文エラーにつながります。

なぜクォートを2回繰り返して書くのか

この問題を解決する方法は、値の中のシングルクォートを2回連続で書くことです。つまり 'Women's Shoes' ではなく 'Women''s Shoes' と書きます。2つが続けて現れると、パーサーはこれを「文字列が終わったという合図」ではなく、「文字列の中に実際に入っているシングルクォート1文字」として解釈します。

この方法は特定のプログラムだけの裏技ではなく、SQL標準で定められたエスケープ規則です。そのため、値をSQL文に入れる前には必ず「この値の中にシングルクォートがいくつあるか、それぞれを2回ずつ繰り返したか」を確認しないと、構文が壊れてしまいます。CSVファイル1つに数百行があり、そのうちのいくつかにだけアポストロフィが混ざっている場合、この確認を値ごとに手作業で繰り返すのは目立たないうえに見落としやすくなります。

CSV → SQL ジェネレーター

CSVを貼り付けるだけで、値の中のシングルクォートまで自動でエスケープされたCREATE TABLE文とINSERT文をすぐに生成します。

ツールが値ごとの判断を代わりに行う仕組み

CSVをSQLに変換するこのツールは、各値をSQL文に入れる前に、値の中のシングルクォートを自動で2つずつに置き換えて書きます。CSVの1行にアポストロフィがいくつ入っていても、どの行のどのカラムにあっても関係なく、変換の過程で同じように適用されるため、入力したCSVを貼り付けた瞬間に、結果のINSERT文にはすでにエスケープが済んだ値が現れます。値ひとつひとつを開いてクォートがあるか目で探して直す必要がなくなる、というポイントです。

すべてのカラムをクォートで囲むわけではない

ただし、この処理はテキストと判定されたカラムにだけ適用されます。CSVの各カラムの値を見て、すべてが数値の形式なら整数や小数、true/falseやyes/noの形式ならブーリアンと判定しますが、このようにテキスト以外の値と判定されたカラムは、そもそもシングルクォートで囲まずに値そのままの形でSQL文に入ります。シングルクォートの問題自体が「文字列リテラル」でのみ発生する現象なので、数値やブーリアンのカラムにはエスケープすべき対象そのものが存在しないわけです。逆に、値に文字が混ざっていてテキストと判定されたカラムだけがクォートで囲まれ、その中のシングルクォートだけがエスケープの対象になります。

まとめ

  • SQLは文字列の値をシングルクォート1組で囲み、テキストの始まりと終わりを示します。
  • 値の中にシングルクォートがそのまま入っていると、パーサーがその地点を文字列の終わりと誤解し、残りの文が構文エラーにつながります。
  • 解決策は、値の中のシングルクォートを2回連続で書くことで、これはSQL標準が定めたエスケープ方式です。
  • この問題はテキストとして扱われる値でのみ発生し、数値やブーリアンと判定されたカラムはそもそもクォートで囲まないため該当しません。
  • CSVの行が増えるほど、アポストロフィが混ざった値を手作業で探して直すより、変換の過程で自動的にエスケープするほうが、漏れなく安全です。
CSV → SQL ジェネレーター

CSVを貼り付けるだけで、値の中のシングルクォートまで自動でエスケープされたCREATE TABLE文とINSERT文をすぐに生成します。

よくある質問

シングルクォートの代わりにダブルクォートで文字列を囲めば、この問題はなくなりますか?

方言によって異なります。一部のデータベースは文字列にダブルクォートの使用を許容することもありますが、PostgreSQLやSQL Serverのような方言では、ダブルクォートは文字列ではなく、テーブルやカラムといった識別子を囲む用途で使われます。そのため、ダブルクォートに変えることは根本的な解決にはならず、かえって別の構文エラーを生む可能性があります。文字列リテラルはシングルクォートという前提のうえでエスケープするのが、方言をまたいで最も広く通用する方法です。

クォートを2回書く代わりに、バックスラッシュ(')でエスケープしてもよいですか?

一部のデータベースはバックスラッシュによるエスケープも認識しますが、これはSQL標準に含まれる方式ではなく、特定の方言の拡張機能に近いものです。設定や方言によってはバックスラッシュがそのまま文字として扱われ、かえって値がおかしな形で入ってしまうこともあります。シングルクォートを2回繰り返す方式が、複数の方言で共通して通じる標準的なエスケープ方法です。

エスケープをしないと、必ず目に見えるエラーになりますか?

必ずそうとは限りません。シングルクォートのせいで文字列が想定より早く終わると、その後に残った文字が文の構造と合わず、たいていは構文エラーにつながります。ただし、残った文字が偶然に次の文とうまく噛み合う形になれば、エラーなく実行されながら、本来の意図とは違う値が保存される場合も理論上はありえます。具体的にどんな結果になるかは残りの文の構造やデータベースによって変わるため一般化は難しく、そもそもエスケープをしてこの不確実性そのものをなくすほうが安全です。

数値やブーリアンのカラムの値もエスケープを気にする必要がありますか?

その必要はありません。値をシングルクォートで囲むのは文字列(テキスト)の値だけに当てはまる表記方法で、数値やブーリアンの値はそもそもクォートなしでそのままSQL文に入ります。値がクォートで囲まれないので、その中でクォートが文字列を切ってしまう問題も起きません。このツールも、カラムを数値やブーリアンと判定したときは値をクォートなしでそのまま書き、テキストと判定されたカラムだけをクォートで囲んでエスケープ処理を適用します。

この記事は韓国語の原文をもとに作成しています。 韓国語の原文を読む

関連記事

  1. CSV連絡先をiPhoneに一括登録するvCard変換手順Excelで整理した連絡先がiPhoneで開かないのは、連絡先アプリがCSVに対応しておらずvCard(.vcf)しか読み込めないためです。CSVをvCardに変換して1つのファイルにまとめる方法を解説します。
  2. アイゼンハワーマトリクス4象限|緊急と重要の分け方アイゼンハワーマトリクスの1〜4象限がそれぞれ何か、緊急と重要をどんな質問で分けるかを整理。緊急だが重要でない第3象限に時間が偏る理由も説明します。
  3. Excel CSVの文字化けはUTF-8 BOM 3バイトが原因ExcelでCSVを開くと文字化けする原因をバイト単位で解説。UTF-8 BOMの3バイトが何をしているのか、ファイルを加工せずに開く方法までまとめました。
  4. Excel CSV 文字化けの原因とUTF-8変換方法ExcelでCSV保存したファイルを他のプログラムで開くと文字が「?」や記号に化ける原因(文字コードの違い)を説明し、UTF-8で文字化けしないCSVに変換してダウンロードする方法を紹介します。
  5. Excelの複数シートをCSV保存すると1つしか残らない理由複数シートのExcelをCSV保存すると、開いているシート1つだけが残る理由と、すべてのシートをCSVとして残す方法を整理して解説します。
  6. ExcelのJSON変換で日付が数字になる原因と計算方法ExcelをJSONに変換すると日付が46037のような数字になる理由と、シリアル値を実際の日付に戻す計算式、事前に文字列へ変換しておく方法を解説します。

関連ツール