WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

接続文字列のエラーメッセージ

by WebSurfer 24. July 2012 23:20

データベースに接続しようとして "初期化文字列の形式が使用に適合しません。index x で始まっています。"(実際には x には数字が入ります)というエラーメッセージが出ることがあります。

接続文字列のエラーメッセージ

「使用」って何?、「index」って何?・・・って感じで意味不明ですが、原文(英文)は次のようになっていて、これなら意味が分ります。

"Format of the initialization string does not conform to specification starting at index x"

つまり、接続文字列の x 文字目(0 から数えて)以降が、仕様(使用ではなくて)に適合しないということです。要するに接続文字列が間違っているということです。

例えば、接続文字列で Initial Catalog=Northwind の設定を間違えて以下のようにしたとします。

Data Source=.\SQLEXPRESS;Initial Catalog='Northwind ...

そうすると、上の画像のように "初期化文字列の形式が使用に適合しません。index 25 で始まっています。" というエラーがでます。'Northwind は間違いで、その ' は最初の文字 Data の D を 0 から数えて 25 番目です。

.NET 4 になって、「使用」の間違いぐらいは「仕様」に直したかと思って、調べてみましたが、間違ったままでした。

ただし、接続文字列の間違いが問題ではなくて、レジストリキーの修正が必要という話もありますので注意してください。

データベースの接続が出来ません

Tags:

ADO.NET

パラメータ化クエリ

by WebSurfer 2. February 2012 22:47

パラメータ化クエリについて少々(かなり?)誤解してました。どのように誤解していたかは恥ずかしいので秘密です。(笑) 調べたことを備忘録として書いておきます。

パラメータ化クエリの説明図

パラメータ化の説明で、パラメータの入力はリテラルとして扱われるから SQL インジェクション攻撃を防ぐことができると言われています。

そこがキーポイントですが、そのリテラルというのが何だか分らないと話が始まりませんので、まずクエリの要素の名前の説明をします。以下の SELECT クエリを例に取ります。

SELECT id, Product FROM TableA WHERE Category='server'

クエリを構成する要素には、キーワード、演算子、識別子、リテラルなどがあり、上記の SELECT クエリではそれぞれ以下のようになります。

キーワード SELECT FROM WHERE
演算子 =
識別子 id Product Category TableA
リテラル 'server'

クエリをパラメータ化するというのはリテラルの部分をプレースホルダを使って記述することです。上記の SELECT クエリの例では以下のようになります。

SELECT id, Product FROM TableA WHERE Category=@Category

プレースホルダの構文はデータソースに依存します。SQL Server の場合は @parametername 形式の名前付きパラメータが使用されます。(頭に @ を付けるのは単なる命名規則です)

パラメータ名は必ずしも識別子と同じ名前にする必要はありませんが、同じにしておいた方が混乱がなさそうです。(例えば、識別子の名前が Category ならパラメータ名は @Category とする)

ADO.NET のコマンドオブジェクトは、パラメータを使用して SQL ステートメントまたはストアドプロシージャに値を渡すことを可能にしています。SQL Server の場合で、上記 SELECT クエリを例に取ると以下のようになります。

string connectionString = 
  WebConfigurationManager.
  ConnectionStrings["MyDB"].ConnectionString;

string query = 
  "SELECT id, Product FROM TableA WHERE Category=@Category";

using (SqlConnection connection =
           new SqlConnection(connectionString))
{
    SqlCommand command =
        new SqlCommand(query, connection);
    SqlParameter param = 
        new SqlParameter("@Category", SqlDbType.VarChar, 50);
    command.Parameters.Add(param);

    command.Parameters["@Category"].Value = TextBox1.Text;

    connection.Open();

    SqlDataReader reader = command.ExecuteReader();

    // 中略
}

SQL Server の場合、Parameters コレクションに追加したパラメータの名前は、クエリのパラメータ名前と一致している必要があります。(Access 等に使われる OleDb プロバイダの場合は、疑問符 (?) で指定される位置パラメータマーカーが使用されますがその話は割愛します。詳しくは、パラメータおよびパラメータのデータ型の構成 (ADO.NET) の「パラメーターのプレースホルダーの使用」セクションを参照してください)

プレースホルダの方式には静的と動的があって、SQL Server の場合は静的プレースホルダを使用します。静的/動的の違いの説明については安全なSQLの呼び出し方 - IPA 独立行政法人 情報処理推進機構を参照してください。

動作は次のとおりです(上の図も参照ください)。まず、プレースホルダのままのクエリをデータベースエンジン側にあらかじめ送信して、実行前にクエリのコンパイルなどの準備をしておきます。クエリの実行の段階で、Parameters コレクションに追加したパラメータの値をデータベースに送信し、データベースがバインド処理します。

ユーザー入力から直接クエリを組み立ててコマンドテキストとして渡すのとは異なり、パラメータの入力は実行可能なコードとしてではなく、リテラル値として扱われます。これにより、攻撃者がサーバーのセキュリティを侵害するコマンドを "注入" しても、注入した値はリテラルの外にはみ出すことはない(上の SELECT クエリの例で言うと、WHERE 句の条件が true または false になるだけ)ので、SQL インジェクション攻撃を防ぐことができます。

なお、パラメータ化するとエスケープ処理されるという話を時々聞きますが、少なくとも SQL Server の場合はそれは誤解です(エスケープ処置はされません。静的プレースホルダ方式なので処置する必要がありません)。

パラメータ化のもう一つのメリットにパフォーマンスの向上があります。それについては 第 4 回 アドホック クエリのパラメータ化 が参考になると思います。このページには、SqlParameter クラスを利用した場合、SQL Server の内部的にどのようにパラメータ化クエリが実行されるかも書いてあって(sp_executesql に変換されて実行される)、興味深いと思いました。

-------- 2016/6/5 追記 --------

上に述べたように、SQL インジェクションの防止とパフォーマンスの向上がパラメータ化クエリを使う主な目的ですが、それ以外にも照合順序の違いによって文字化けに悩むことがなくなるという副次的な効用もあります。

詳しくは別の記事「パラメータ化の副次的な効用」に書きましたので、興味がありましたら見てください。

Tags:

ADO.NET

ACE OleDb で Excel のブック作成

by WebSurfer 26. January 2012 23:52

注意:
ダウンロードページ「Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント」の「詳細」にある条件に "ファイル作成の手段としての使用" はできないと書いてあります。ということは、この記事に書いてあるような Excel のブックを作るようなことはライセンス的に許可されてないのかもしれませんので注意してください。

ADO.NET と ACE OleDb プロバイダを利用して Excel のブックを新規作成する話です。

備忘録として残しておきたかったのは、作成するブックが xls 形式の時は接続文字列の Extended Properties に "Excel 8.0" を、xlsx 形式の時は "Excel 12.0 Xml" を指定するということです。

それを書いただけではブログの記事としては寂しいので、以下を追加しました。(笑)

ACE OleDb プロバイダで作成した Excel ファイル

JET でも ACE でも、接続文字列の Data Source に存在しないファイル名を指定して、CREATE TABLE クエリを実行すると、そのファイル名でブックを新規作成できます。

ACE の場合は以下のような感じです。この例では、c:\workbooks というフォルダに test.xlsx という名前のファイルが作成されます。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;

namespace OleDbExcel
{
  class Program
  {
    static void Main(string[] args)
    {
      DataTable table = CreateDataTable();
      string createQuery = CreateQuery(table);
      string insertQuery = InsertQuery(table);
      string connString = 
        "Provider=Microsoft.ACE.OLEDB.12.0;" + 
        "Data Source=C:\\workbooks\\test.xlsx;" + 
        "Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
            
      using (OleDbConnection connection = 
        new OleDbConnection(connString))
      {
        // Excel ブックを作成
        connection.Open();
        using (OleDbCommand command = 
          new OleDbCommand(createQuery, connection))
        {                    
          command.ExecuteNonQuery();
        }

        // Excel ブックにデータを INSERT
        using (OleDbCommand command = 
          new OleDbCommand(insertQuery, connection))
        {                    
          foreach (DataColumn column in table.Columns)
          {
            command.Parameters.Add(
              "@" + column.ColumnName, 
              OleDbType.VarWChar);                        
          }

          foreach (DataRow row in table.Rows)
          {
            foreach (DataColumn column in table.Columns)
            {
              command.
                Parameters["@" + column.ColumnName].
                Value = 
                  row[column.ColumnName];
            }
            command.ExecuteNonQuery();
          }
        }
      }
    }

    // Excel ブックを作る基になるデータを SQL Server の
    // サンプルデータベース Northwind の Customers テー
    // ブルより取得し DataTable を作る。
    protected static DataTable CreateDataTable()
    {
      DataTable dataTable = new DataTable();
      SqlDataAdapter adapter = new SqlDataAdapter();
      string connString = Properties.Settings.Default.MyDB;
      string query = 
        "SELECT CustomerID, CompanyName, ContactName, ContactTitle" + 
        " FROM Customers";
      SqlConnection connection = new SqlConnection(connString);
      SqlCommand command = new SqlCommand(query, connection);
      adapter.SelectCommand = command;
      adapter.Fill(dataTable);
      return dataTable;
    }

    // DataTable の DataColumn を基に CREATE TABLE クエリ
    // を作成する。この例では以下のようになる。
    // CREATE TABLE [MySheet](CustomerID char(255),
    // CompanyName char(255),ContactName char(255),
    // ContactTitle char(255))
    protected static String CreateQuery(DataTable table)
    {
      StringBuilder sb1 = new StringBuilder();
      sb1.Append("CREATE TABLE [MySheet](");
      foreach (DataColumn column in table.Columns)
      {
        sb1.Append(column.ColumnName + " char(255),");
      }
      sb1.Remove(sb1.Length - 1, 1);
      sb1.Append(")");
      return sb1.ToString();
    }

    // DataTable の DataColumn を基に INSERT クエリを作成
    // する。この例では以下のようになる。
    // INSERT INTO [MySheet] (CustomerID,CompanyName,
    // ContactName,ContactTitle) values (?,?,?,?)
    protected static String InsertQuery(DataTable table)
    {
      StringBuilder sb1 = new StringBuilder();
      StringBuilder sb2 = new StringBuilder();
      sb1.Append("INSERT INTO [MySheet] (");
      sb2.Append(" values (");
      foreach (DataColumn column in table.Columns)
      {
        sb1.Append(column.ColumnName + ",");
        sb2.Append("?,");
      }
      sb1.Remove(sb1.Length - 1, 1);
      sb2.Remove(sb2.Length - 1, 1);
      sb1.Append(")");
      sb2.Append(")");
      return sb1.ToString() + sb2.ToString();
    }
  }
}

作成したファイルを Excel アプリケーションで開いたのが上の画像です。クエリで指定したテーブル名のシート(例では、MySheet)が生成されています。

上のコードで、接続文字列の Extended Properties="Excel 12.0 Xml;HDR=YES" の Excel 12.0 Xml の部分はソースデータベースの種類の指定です。HDR=YES は、シートの 1 行目がヘッダであることを指定します。

ソースデータベースの種類は、作成するブックの形式によって、以下のように変わります。

ブック形式 指定する DB 種類
xls Excel 8.0
xlsx Excel 12.0 Xml
xlsb Excel 12.0
xlsm Excel 12.0 Macro

xls は Excel 2003 以前の形式のブック、その他は Excel 2007 以降の Microsoft Office Open XML 形式のブックです。

8.0 または 12.0 という数字は ISAM (Indexed Sequential Access Method) ドライバのバージョンです。

注意すべきは、xlsx 形式のブックを作る時は "Excel 12.0 Xml" のように Xml を付けなければならないところです。JET の場合のように単に "Excel 12.0" とすると、中身は xlsb 形式(バイナリ形式)になってしまいます。

Excel アプリケーションでそのファイル開こうとすると、"Excel でファイルを開くことができません。ファイル形式または・・・" という警告メッセージが出て開けません(拡張子を xlsb に変更すれば開けますが)。

ブックを作った後、SELECT, INSERT, UPDATE(Excel の場合、DELETE はできません)操作を行う場合は、Microsoft サポートの記事 Visual Basic .NET と ADO.NET を使用して Excel ブックのレコードの取得と変更を行う方法 が参考になると思います。

Tags: , ,

ADO.NET

About this blog

2010年5月にこのブログを立ち上げました。その後 ブログ2 を追加し、ここは ASP.NET 関係のトピックス、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  October 2021  >>
MoTuWeThFrSaSu
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567

View posts in large calendar