検索
連載

第3回 .NETデータ・プロバイダによるレコードの挿入/削除/更新基礎解説 ADO.NET基礎講座 ―初めての.NETデータベース・プログラミング―(2/3 ページ)

前回のレコード取得に続き、.NETデータ・プロバイダで提供されるクラスを利用したレコードの挿入、削除、更新について分かりやすく解説。

Share
Tweet
LINE
Hatena

.NETデータ・プロバイダによるレコードの挿入

 さて、それではinsert文を使用してプログラムからレコードを追加してみよう。今回は先にサンプル・プログラムを示す。

// insert.cs

using System;
using System.Data.SqlClient;

public class Insert {
  public static void Main(string[] args) {

    if (args.Length != 2)
      return;

    string connStr = "Server=(local)\\NetSDK;"
                   + "Trusted_Connection=yes;"
                   + "database=pubs";

    string sqlStr = "INSERT INTO publishers(pub_id, pub_name) "
                  + " VALUES ('{0}', '{1}')";

    sqlStr = String.Format(sqlStr, args[0], args[1]);

    SqlConnection conn = new SqlConnection(connStr);
    SqlCommand cmd = new SqlCommand(sqlStr, conn);

    conn.Open();

    int num = cmd.ExecuteNonQuery();
    Console.WriteLine(num);

    conn.Close();
  }
}

// コンパイル方法:csc insert.cs

insert文を実行するサンプル・プログラム(insert.cs)
insert.csのダウンロード

 このサンプル・プログラムは、コマンドラインで指定された2つのパラメータをpub_idカラムとpub_nameカラムにセットしたレコードを作成する。そして作成したレコードの件数を表示する(この場合には通常は1件だが)。

 次の画面はこのプログラム実行し、続けて冒頭のselect文を実行するサンプル・プログラム(reader.cs)により、テーブルの全レコードを表示したものだ。


サンプル・プログラムの実行結果
まず上記のサンプル・プログラムによりレコードを1件追加し、続いて冒頭で示したサンプル・プログラム(reader.cs)により、追加されたレコードを確認している。

 2番目のパラメータで「@IT Insider.NET」をダブルクォートで囲っているのは、文字列中に空白文字が含まれているからで、こうしておかなければ「@IT」と「Insider.NET」が別のパラメータとなってしまうためだ。

 プログラムではまず、次のコードによりコマンドラインのパラメータで指定された値をinsert文に埋め込んで、データベースに送信するSQL文を作成している。

  string sqlStr = "INSERT INTO publishers(pub_id, pub_name) "
                + " VALUES ('{0}', '{1}')";

  sqlStr = String.Format(sqlStr, args[0], args[1]);

 StringクラスのstaticなメソッドであるFormatメソッドは、1番目の引数で指定された文字列中の“{0}”、“{1}”を、2番目、3番目の引数で指定された文字列(コマンドラインで指定された2つのパラメータ)に置き換える働きをする。

 これに続くSqlConnectionオブジェクトとSqlCommandオブジェクトの生成については、select文のとき(reader.cs)とまったく同じやり方だ。

 接続をオープンした後、select文のときにはExecuteReaderメソッドによりDataReaderオブジェクトを得て検索結果を取得したが、insert文ではデータベースから戻ってくるデータは皆無である。このような結果を返さないSQL文の実行は、ExecuteReaderメソッドの代わりにExecuteNonQueryメソッドを呼び出して行う。メソッド名が表しているように、このメソッドは問い合わせ(Query)なしで、単にSqlCommandオブジェクトにセットされた命令を実行するだけだ。

  conn.Open();

  int num = cmd.ExecuteNonQuery();
  Console.WriteLine(num);

  conn.Close();

 ExecuteNonQueryメソッドの戻り値は、データベース側で実際に処理されたレコードの件数を表している。ちなみに、ExecuteNonQueryメソッドではなくExecuteReaderメソッドを呼び出してもinsert文は同じように実行されるが、わざわざそうする意味はない。

SQL Injectionへの注意

 ところで、すでに気付いている方も多いと思われるが、上記のサンプル・プログラム(insert.cs)には大きなセキュリティ・ホールがある。いわゆる「SQL Injection」と呼ばれるものだ(Injectionは「注入」の意)。

 例えば次のようなパラメータを指定してサンプル・プログラムを実行した場合を考えてみていただきたい。

insert 9998 "dummy'); DELETE FROM publishers WHERE (pub_id = '9990"

 2番目のパラメータには何やら長い文字列を指定しているが、これにより実際にデータベースに送信される最終的なSQL文は次の1行になる。

INSERT INTO publishers(pub_id, pub_name) VALUES ('9998', 'dummy'); DELETE FROM publishers WHERE (pub_id = '9990')

 ポイントは、複数のSQL文をセミコロン(;)で連結して記述した場合、1回の送信で各SQL文を連続して実行できるということだ。これにより、正規のSQL文(insertからセミコロンまで)に、別の任意のSQL文を紛れ込ませて実行させるということができてしまうのである。

 セミコロン以降のdelete文は、詳しくは後述するが、レコードを削除するためのSQL文である。ここでは控えめ(?)にレコードを1件削除しているだけだが、全レコードを削除したり、テーブル自体を削除したりするようなことも容易にできてしまう。

 この問題への対策は、ユーザーにより入力された文字列をそのままSQL文に利用しないことである。当然のことではあるのだが、入力された文字を厳密にチェックするのはその1つの方法だ。例えばこの場合には、2番目のパラメータの値は出版社名であるので、入力できる文字の範囲を限定することができだろう。

 別の方法としては、入力された値全体を単なる文字列として扱うために「パラメータを含んだSQL文」を使用する方法がある。これを利用したinsert文の実行について次に述べる。

パラメータを利用したSQL文

 SqlCommandオブジェクトで指定するSQL文には「パラメータ」と呼ばれる記述を含めることができる。これを使用したSQL文は例えば次のようになる。

insert into publishers (pub_id, pub_name) values (@PubId, @PubName)

 ここで、先頭にアットマーク(@)を付けた「@PubId」と「@PubName」がパラメータだ。その名前は自由に設定でき、「名前付きパラメータ」とも呼ばれる*1

*1 OLE DB用の.NETデータ・プロバイダの場合には、名前付きパラメータではなく、パラメータ部分に「プレースホルダ」と呼ばれる「?」を記述する。


 名前付きパラメータを利用する場合には、まず上記のようなSQL文をあらかじめSqlCommandオブジェクトにセットしておき、これとは別に、実際の値を含んだSqlParameterクラスのインスタンスを作成し、それをSqlCommandオブジェクトのParametersプロパティに追加する。

 SqlParameterクラスは、今回初めて登場する.NETデータ・プロバイダの代表的なクラスの1つで、SQL文中のパラメータに対応した値を指定するためのクラスだ。そのコンストラクタにはいくつかのオーバーロードされたバージョンが用意されているが、最も簡単なバージョンを使用すれば、SqlParameterオブジェクトを作成し、それをSqlCommandオブジェクトにセットするコードは次のようになる。

  SqlCommand cmd = new SqlCommand(……);

  SqlParameter p1 = new SqlParameter("@PubId", args[0]);
  cmd.Parameters.Add(p1);

  SqlParameter p2 = new SqlParameter("@PubName", args[1]);
  cmd.Parameters.Add(p2);

 当然ながらSqlParameterオブジェクトは、SQL文中に指定した名前付きパラメータの数だけ必要となる。

 SqlCommandオブジェクトのParametersプロパティは、SqlParameterCollectionクラスのオブジェクトである。そのAddメソッドにも、SqlParameterクラスのコンストラクタと同様にオーバーライドされたメソッドがいくつか用意されている。そのうちの1つを利用すれば、次のようにSqlParameterクラスのインスタンス作成の記述を省略することもできる。

  cmd.Parameters.Add("@PubId",   args[0]);
  cmd.Parameters.Add("@PubName", args[1]);

 SqlParameterクラスを利用して先ほどのサンプル・プログラム(insert.cs)を書き換えたものを次に示しておく。

// paramins.cs

using System;
using System.Data.SqlClient;

public class ParameterInsert {
  public static void Main(string[] args) {

    if (args.Length != 2)
      return;

    string connStr = "Server=(local)\\NetSDK;"
                   + "Trusted_Connection=yes;"
                   + "database=pubs";

    string sqlStr = "INSERT INTO publishers(pub_id, pub_name) "
                  + " VALUES (@PubId, @PubName)";

    SqlConnection conn = new SqlConnection(connStr);
    SqlCommand cmd = new SqlCommand(sqlStr, conn);

    SqlParameter p1 = new SqlParameter("@PubId", args[0]);
    cmd.Parameters.Add(p1);

    SqlParameter p2 = new SqlParameter("@PubName", args[1]);
    cmd.Parameters.Add(p2);

    conn.Open();

    int num = cmd.ExecuteNonQuery();
    Console.WriteLine(num);

    conn.Close();
  }
}

// コンパイル方法:csc paramins.cs

名前付きパラメータを用いたinsert文を実行するサンプル・プログラム(paramins.cs)
paramins.csのダウンロード

 パラメータを使用したこのバージョンでは、先ほどのSQL Injection問題は発生しない。

Copyright© Digital Advantage Corp. All Rights Reserved.

ページトップに戻る