- PR -

MySQLでSqlParameterを使ったSQLがうまく動かない。

1
投稿者投稿内容
はね
会議室デビュー日: 2003/03/20
投稿数: 11
投稿日時: 2006-04-09 15:31
こんにちは、はねです。

表題の件で困っています。
環境としては次のとおりです。
・Windows2000Professional
・IIS 5.0
・.NET framework v2.0.50727
・Visual C#
・MySQL 5.0
・MySQL Connector/Net 1.0

具体的には以下のコードを実行しているのですが、NICKNAMEが'hane'のレコードが存在するにもかかわらず、DataSetは0件で返ってきます。試しに条件をじか書きしたSQL(コード中でコメントアウトしているSQL)を実行すると正しく1件返ってきました。

----------------------------------------------------
String connString = "Database=xxx;Data Source=192.168.xxx.xxx;Port=3306;User Id=xxx;Password=xxx;Pooling=false";
String sql = "SELECT * FROM MEMBER_MST WHERE NICKNAME=@NICKNAME";
//String sql = "SELECT * FROM MEMBER_MST WHERE NICKNAME='hane'";

MySqlConnection conn = new MySqlConnection(connString);
MySqlDataAdapter adpt = new MySqlDataAdapter();
MySqlCommand cmd = new MySqlCommand(sql, conn);

MySqlParameter param = cmd.CreateParameter();
param.ParameterName = "@NICKNAME";
param.Value = "hane";
param.DbType = System.Data.DbType.String;
cmd.Parameters.Add(param);

adpt.SelectCommand = cmd;
MySqlCommandBuilder cb = new MySqlCommandBuilder(adpt);
conn.Open();
DataSet ds = new DataSet();
adpt.Fill(ds);
conn.Close();
----------------------------------------------------

ためしにINSERT文をSqlParameterを使って実行してみたところ、
MySqlCommandにはパラメータがセットされている(セットしたあとにMySqlCommandのParametersプロパティーの中身を確認しました)にもかかわらず、パラメータで値をセットしている項目が全てnullなレコードが作成されてしまいました。

何かものすごく初歩的なことで間違っているような気がするのですが、なにぶんC#もMySQLも初めて使う環境で、はまってしまっています。
原因がおわかりになる方がいらっしゃいましたご教授のほどよろしくお願いします。m(__)m

[ メッセージ編集済み 編集者: はね 編集日時 2006-04-09 15:33 ]
はね
会議室デビュー日: 2003/03/20
投稿数: 11
投稿日時: 2006-04-09 20:43
自己解決しました。
実は前にVB.NET&SQLServerの開発を少しだけやったことがあって、その時はパラメータ名の先頭に"@"を使っていたので、てっきり"@"だと思っていたのですが、MySQL Connectorに添付されていたテストクラスのソースを見たところ"?"を使っていたのでもしやと思い"?"に変えてみたところ正常動作しました。

パラメータ名の先頭文字はてっきり"@"で統一されていると思っていたのですが、RDBMS依存だなんて...。
これじゃ、インターフェースプログラミングで極力依存コードを排除しても、それだけではだめということですよね。(-_-;
"@"の部分を定数にしておいて、各コード中ではそれを連結して使う方法や、共通メソッドをかませてリプレイスする方法すれば各コードからは排除できそうですが煩雑になるのと微々たる物とはいえ負荷が増えるのはやですねぇ。
何かよい方法があって僕が知らないだけ?皆さんはどのようにしてます?

あと、SQL文中の変数に何らかの記号をつけないといけないのはしかたがないと思うのですが、記号が決まっているのならパラメーターにセットする時の名前にも記号を付けないといけないのはちょっと冗長と思い、試しに取ってみたらちゃんと動きました。

String sql = "SELECT * FROM MEMBER_MST WHERE NICKNAME=?NICKNAME";
param.ParameterName = "NICKNAME";

この辺は.NET技術者の間ではよく知られていることでしょうか?
もしかして、これは「出来るけどやらないほうがよい」方法だったりするのでしょうか?



[ メッセージ編集済み 編集者: はね 編集日時 2006-04-09 20:43 ]
じゃんぬねっと
ぬし
会議室デビュー日: 2004/12/22
投稿数: 7811
お住まい・勤務地: 愛知県名古屋市
投稿日時: 2006-04-09 23:02
引用:

はねさんの書き込み (2006-04-09 20:43) より:

"@"の部分を定数にしておいて、各コード中ではそれを連結して使う方法や、共通メソッドをかませてリプレイスする方法すれば各コードからは排除できそうですが煩雑になるのと微々たる物とはいえ負荷が増えるのはやですねぇ。


SQL インジェクションの的になりそうですね。

引用:

何かよい方法があって僕が知らないだけ?
皆さんはどのようにしてます?


私も素直にパラメータを使うか、ストアドを使います。

引用:

あと、SQL文中の変数に何らかの記号をつけないといけないのはしかたがないと思うのですが、記号が決まっているのならパラメーターにセットする時の名前にも記号を付けないといけないのはちょっと冗長と思い、試しに取ってみたらちゃんと動きました。


これは、

引用:

param.ParameterName = "NICKNAME";


ここの話ですか?
私も結構取っちゃうことがあったのですが、最近は全部つけています。

_________________
C# と VB.NET の入門サイト
じゃんぬねっと日誌
Access
ぬし
会議室デビュー日: 2002/04/08
投稿数: 829
投稿日時: 2006-04-10 06:22
引用:

実は前にVB.NET&SQLServerの開発を少しだけやったことがあって、その時はパラメータ名の先頭に"@"を使っていたので、てっきり"@"だと思っていたのですが、MySQL Connectorに添付されていたテストクラスのソースを見たところ"?"を使っていたのでもしやと思い"?"に変えてみたところ正常動作しました。



SQL Server、Oracleは名前指定のパラメータ(Named Parameter)をサポートしていますが、Access、 MySQLは位置指定のパラメータ(Positional Parameter)しかサポートしていないようです。ちなみに、Oracleはどちらかを選択できます。位置指定の場合は、パラメータに「?」を使うのが一般的なようです。

なお、位置指定のパラメータを使用するときは値を埋め込むときパラメータの順番に値を代入する必要がありますので注意してください。

SQLインジェクションを考慮してアドホッククエリを利用するときもパラメータ変数を利用して値を代入した方が安全です。

_________________
ASP.NET+Ajaxサンプル集


[ メッセージ編集済み 編集者: Access 編集日時 2006-04-10 06:25 ]
はね
会議室デビュー日: 2003/03/20
投稿数: 11
投稿日時: 2006-04-10 10:50
じゃんぬねっとさん、Accessさん、返信ありがとうございます。

引用:

ここの話ですか?
私も結構取っちゃうことがあったのですが、最近は全部つけています。


はい、その話です。
サンプルなどを見ても両方に記号を付けているので、僕も付けるようにしておきます。


引用:

SQL Server、Oracleは名前指定のパラメータ(Named Parameter)をサポートしていますが、Access、 MySQLは位置指定のパラメータ(Positional Parameter)しかサポートしていないようです。ちなみに、Oracleはどちらかを選択できます。位置指定の場合は、パラメータに「?」を使うのが一般的なようです。
なお、位置指定のパラメータを使用するときは値を埋め込むときパラメータの順番に値を代入する必要がありますので注意してください。


なるほど、そういう仕様の値があるんですね。

で、確認のために次の2パターンを試してみたのですがどちらも正しく動作しました。
どうも MySQL 5.0 & MySQL Connector/Net 1.0 の環境では名前指定のパラメータとして動いているようです。

(1)SQL文中に2種類のパラメータを使い、SQL文中の出現順と値の設定順を逆にする。
SELECT * FROM MEMBER_MST WHERE NICKNAME=?NICKNAME AND PASSWD=?PASSWD

(2)SQL文中に1種類のパラメータを複数回使い、値の設定は1度だけ行う。
SELECT * FROM MEMBER_MST WHERE NICKNAME=?NICKNAME AND PASSWD=?NICKNAME

逆に、次のSQL文では、異なる値を2つ設定しても、両方のパラメータ値が後から設定された値として動作しました。
"?"自体を名前として解釈したようです。

SELECT * FROM MEMBER_MST WHERE NICKNAME=? AND PASSWD=?

以上、ご報告まで。
Jitta
ぬし
会議室デビュー日: 2002/07/05
投稿数: 6267
お住まい・勤務地: 兵庫県・海手
投稿日時: 2006-04-11 06:03
引用:

はねさんの書き込み(2006-04-09 20:43)より:

"@"の部分を定数にしておいて、各コード中ではそれを連結して使う方法や、共通メソッドをかませてリプレイスする方法すれば各コードからは排除できそうですが煩雑になるのと微々たる物とはいえ負荷が増えるのはやですねぇ。


 私はリプレースしています。

 仮想クラスで

SELECT * FROM TBL_USER WHERE NAME=@NAME

と定義しておき、派生クラスで

Regex.Replace("\@\w", "?")

〆 written by Jitta@わんくま同盟 on 2006/04/10
□ Microsoft MVP for Visual Developer ASP/ASP.NET October, 2005 - September, 2006
NAL-6295
ぬし
会議室デビュー日: 2003/01/26
投稿数: 966
お住まい・勤務地: 東京
投稿日時: 2006-04-11 08:56
NAL-6295です。

引用:

"@"の部分を定数にしておいて、各コード中ではそれを連結して使う方法や、共通メソッドをかませてリプレイスする方法すれば各コードからは排除できそうですが煩雑になるのと微々たる物とはいえ負荷が増えるのはやですねぇ。


私の場合は、
DBにアクセスするために必要なクラスのインスタンスを生成するセットをインタフェースで定義してあり、
その中にパラメータの名前の付け方等も定義できるようにしてあります。
別に用意している条件クラス等は全てそのインタフェースを参照する形になっていて、
内容にあわせて生成されるパラメータ名も変化するようになっています。
で、そのインタフェースを各DB毎に実装しておいて、それを取り替えるだけでよいようにしてあります。

はね
会議室デビュー日: 2003/03/20
投稿数: 11
投稿日時: 2006-04-11 12:00
NAL-6295さん、Jittaさん、ご意見ありがとうございます。

何らかの対策をされている方もいらっしゃるんですね。
このあたり今後共通化されればうれしいですねぇ。

今回の僕の場合は、最終的にSQL文とそのパラメータを解釈して実行する処理を共通クラスにしているのと、今のところMySQL以外を使う予定もないので、パラメーター名は直接"?"を使うようにしました。

もし今後他のRDBMSを使うことになっても共通クラスの内部実装でリプレイスをかけるようにしてやれば対応できそうなので。

みなさま貴重なご意見ありがとうございした。m(__)m

1

スキルアップ/キャリアアップ(JOB@IT)