パラメータ化の副次的な効用（文字化け回避） #SQLServer

SQL Server, ADO.NET, SqlClient の話ですが、先の記事「パラメータ化クエリ」に書いたようにパラメータ化するというセキュリティ対策として普通にやるべきことをやっていれば、照合順序の違いによる文字化けに悩むことはなさそうという話を書きます。

元は MSDN Forum の「nvarcharに日本語を入力すると?????????になる」という表題のスレッドでの話です。

上の MSDN Forum のスレッドの問題は、簡単に書くと、SQL Azure の照合順序のデフォルトは SQL_Latin1_General_CP1_CI_AS となっていて、それに例えば以下のように 'あいうえお' というように N プレフィックスをつけないリテラルを INSERT すると文字化けするという話です。

INSERT INTO [Table] ([Name]) VALUES ('あいうえお')

（何故文字化けするかは MSDN Blog の記事「Unicode型列(NCHAR/NVARCHAR) に格納されるデータが “?” になる」に説明されていますので、そちらを見てください。手抜きでスミマセン）

クエリをパラメータ化して ADO.NET + SqlClient 経由で INSERT, UPDATE をかければ、照合順序が Japanese_CI_AS（デフォルト）でも SQL_Latin1_General_CP1_CI_AS（LocalDB や SQL Azure のデフォルト）でも文字化けはしません。

パラメータ化あり / なしでどう違うかの例を以下に書きます。

まず、サンプルとして SQL Server 2008 Express に照合順序が SQL_Latin1_General_CP1_CI_AS のデータベースを作りました。以下の画像の通りです。

それに以下のコードで INSERT してみます。上のクエリがパラメータ化なし、下のクエリがパラメータ化ありです。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 
namespace ConsoleApplication
{
  class Program
  {
    static void Main(string[] args)
    {
      string connString = @"接続文字列";
 
      using(SqlConnection conn = new SqlConnection(connString))
      {
        conn.Open();
        string query = 
          "INSERT INTO [Table] ([Name]) VALUES ('あいうえお')";
        using(SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.ExecuteNonQuery();
        }
 
        query ="INSERT INTO [Table] ([Name]) VALUES (@Name)";
        using(SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(
            new SqlParameter("@Name", SqlDbType.NVarChar, 50));
          cmd.Parameters["@Name"].Value = "かきくけこ";
          cmd.ExecuteNonQuery();
        }
      } 
    }
  }
}

結果は以下の通りです。赤枠で囲ったものがパラメータ化なし、青枠で囲ったものがパラメータ化ありです。

何故パラメータ化クエリを使うと文字化けの問題がなくなるのかと言うと、TechNet の記事「第 4 回アドホッククエリのパラメータ化」に書いてありましたが (その記事は今はリンク切れ)、"SqlParameter クラスを利用すると、内部的には sp_executesql に変換されて実行されるようになり"、その際以下のように N プレフィックスが付与されるからだそうです。

exec sp_executesql 
    N'INSERT INTO [Table] ([Name]) VALUES (@Name)',
    N'@Name nvarchar(5)',
    @Name=N'かきくけこ'

というわけで、クエリをパラメータ化して ADO.NET + SqlClient を使えば（普通のやり方をしていれば）、照合順序の違いによって文字化けに悩むことはなさそうです。