SQLパラメータの利用 - C#

SQLパラメータを利用するコードについて紹介します。

概要

C#などのプログラムから、SQL文を実行する場合、実行するSQLを文字列として準備し実行します。
下記の例のように、Formatメソッドを利用してSQL文を生成することがあります。

string sqlstr = string.Format("select * from Contents where id ={0:d}", editid);
try {
  SqlCommand com = new SqlCommand(sqlstr, con);
  SqlDataReader sdr = com.ExecuteReader();
  if (sdr.Read() == true) {
    //データの読み出し処理
  }
  sdr.Close();
  com.Dispose();
}
catch (Exception exc) {
  Debug.WriteLine(exc.Message);
}
finally {
  con.Close();
}

上記の例のような単純な場合は問題はあまり発生しませんが、nchar, varcharなどの文字列を利用したアップデート文を処理する場合には、SQLインジェクションを防ぐために、サニタイジングをする必要があります。

mytitle=mytitle.Replace("'", "''");
mytitle=mytitle.Replace(";", " ");
sqlstr = string.Format("update Contents set title='{0:s}'", mytitle);

try {
  SqlCommand com = new SqlCommand(sqlstr, con);
  com.ExecuteNonQuery
  sdr.Close();
  com.Dispose();
}
catch (Exception exc) {
  Debug.WriteLine(exc.Message);
}
finally {
  con.Close();
}

すべての値に対して、サニタイジングの処理の記述をするのは大変です。
SQLパラメータを利用すると、ライブラリ側でサニタイジングをし、不正な値の場合はSQLの実行をしない動作のため、SQLインジェクションなどの攻撃を容易に防ぐことができます。この記事では、SQLパラメータを利用したコードを紹介します。

プログラム

UI

下図のUIを作成します。

コード

下記のコードを記述します。[button1]のクリックイベントを記述します。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;

namespace SimpleSQLParameter
{
  public partial class FormMain : Form
  {
    public FormMain()
    {
      InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      String ConnectionStr = "Data Source=(データベースサーバーのホスト名);Initial Catalog=iPentecSandbox;Connect Timeout=60;Persist Security Info=True;User ID=(データベースに接続するのユーザーID);Password=(パスワード);";
      SqlConnection con = new SqlConnection(ConnectionStr);

      string sqlstr = "insert into Products (name, price, category) values (@name, @price, @category)";

      con.Open();
      try {
        SqlCommand com = new SqlCommand(sqlstr, con);

        SqlParameter param = com.CreateParameter();
        param.ParameterName = "@name";
        param.SqlDbType = SqlDbType.NChar;
        param.Direction = ParameterDirection.Input;
        param.Value = "Penguin";
        com.Parameters.Add(param);

        param = com.CreateParameter();
        param.ParameterName = "@price";
        param.SqlDbType = SqlDbType.Int;
        param.Direction = ParameterDirection.Input;
        param.Value = 2400;
        com.Parameters.Add(param);

        param = com.CreateParameter();
        param.ParameterName = "@category";
        param.SqlDbType = SqlDbType.NChar;
        param.Direction = ParameterDirection.Input;
        param.Value = "bird";
        com.Parameters.Add(param);

        com.ExecuteNonQuery();

      }
      catch (Exception exc) {
        System.Diagnostics.Debug.WriteLine(exc.Message);
      }
      finally {
        con.Close();
      }

      MessageBox.Show("SQLの実行が完了しました。");
      
    }
  }
}

解説

SQL文の実行に関するコードの詳細はこちらの記事を参照してください。

SQL文にはパラメーターを含むSQLを記述します。パラメーターは"@"で始まる文字列です。今回のSQL文では、"@name", "@price", "@category" の3つをSQLパラメーターとしています。

  string sqlstr = "insert into Products (name, price, category) values (@name, @price, @category)";

下記コードにて、SQLパラメータの作成とSqlCommandへの追加をします。
SqlParameterはSqlCommandクラスのインスタンスオブジェクトのCreateParameter()メソッドで作成します。作成したSqlParameterのインスタンスのParameterNameにパラメータ名を、SqlDbTypeにデータベースの型名を、Directionにパラメータの方向（入力パラメータか、出力パラメータか）を、valueにパラメータの値を代入します。
SqlCommandクラスのインスタンスオブジェクトのparameterプロパティのAddメソッドでパラメータをSqlCommandに追加します。

        SqlParameter param = com.CreateParameter();
        param.ParameterName = "@name";
        param.SqlDbType = SqlDbType.NChar;
        param.Direction = ParameterDirection.Input;
        param.Value = "Penguin";
        com.Parameters.Add(param);

今回実行するSQL文は下記 パラメータが設定された後のSQLは と同等のSQLが実行されることになります。

実行結果

プロジェクトを実行します。下図のウィンドウが表示されます。


[button1]をクリックします。SQLが実行されます。実行が完了すると下図のメッセージボックスが表示されます。


データベースのレコードを確認します。コードで記述したレコードが追加されていることが確認できます。

補足:結果のあるSQLクエリの場合のコード例

string sqlstr = "select * from Contents where id =@id";
con.Open();
try {
  SqlCommand com = new SqlCommand(sqlstr, con);

  SqlParameter param = com.CreateParameter();
  param.ParameterName = "@id";
  param.SqlDbType = SqlDbType.Int;
  param.Direction = ParameterDirection.Input;
  param.Value = editid;
  com.Parameters.Add(param);

  SqlDataReader sdr = com.ExecuteReader();
  if (sdr.Read() == true) {
    FillData(ref di, sdr, tagcon);
  }
  sdr.Close();
  com.Dispose();
}
catch (Exception exc) {
  Debug.WriteLine(exc.Message);
}
finally {
  con.Close();
}

補足:パラメーターが多数の場合

パラメータが多い場合はパラメータ設定部分を関数にしておくのもよいかもしれません。

string sqlstr = "update Figure "
  + "set FigureID=@figureid, Category=@category, OwnerPage=@ownerpage, "
  + "ModifiedDate=@modifieddate, ImageType=@imagetype where id=@id";

con.Open();
SqlCommand com = new SqlCommand(sqlstr, con);
try {
  AddSqlParameter(com, "@figureid", SqlDbType.NChar, FigureID);
  AddSqlParameter(com, "@category", SqlDbType.NChar, FigureCategory);
  AddSqlParameter(com, "@ownerpage", SqlDbType.Int, OwnerPage);
  AddSqlParameter(com, "@modifieddate", SqlDbType.DateTime, DateTime.Now);
  AddSqlParameter(com, "@imagetype", SqlDbType.NChar, ImageType);
  AddSqlParameter(com, "@id", SqlDbType.Int, ID);

  int rows = com.ExecuteNonQuery();
}
catch (SqlException exc) {
}
finally {
  com.Dispose();
  con.Close();
}

public static void AddSqlParameter(
  SqlCommand com, string ParameterName, SqlDbType type, Object value)
{
  SqlParameter param = com.CreateParameter();
  param.ParameterName = ParameterName;
  param.SqlDbType = type;
  param.Direction = ParameterDirection.Input;
  param.Value = value;
  com.Parameters.Add(param);
}