C#でpostgresSQL呼び出し、変数を利用したSELECT文を作りたい

Question

###前提・実現したいこと
C#でpostgresSQL呼び出し、変数を利用したSELECT文を書いたのですが、エラーとなり困っています。

###発生している問題・エラーメッセージ

型 'Npgsql.NpgsqlException' のハンドルされていない例外が Npgsql.dll で発生しました

追加情報:ERROR: 42703: 列"name"は存在しません

###該当のソースコード

string name = "TEST";
string constr = @"Server=localhost;Port=5432;User Id=postgres;Password=test;Database=postgres";

//ポスグレ接続
NpgsqlConnection conn = new NpgsqlConnection(constr);

//DBオープン
conn.Open();

string sql = @"select username from test_table where data =  name";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);

//SQL実行
using (NpgsqlDataReader dr = cmd.ExecuteReader())

Answer 1

パラメーターを使いましょう。

/* 修正前 */
string sql = @"select username from test_table where data =  name";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);

↓

/* 修正後 */
string sql = @"select username from test_table where data = :name";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
cmd.Parameters.Add(new NpgsqlParameter("name", NpgsqlDbType.Varchar)) //パラメーターの追加
cmd.Parameters["name"].Value = name; //パラメーターに値をセット

パラメーターを使わずにクエリ文字列をいじる方法もありますが、SQLインジェクションと呼ばれる問題を生むため、お勧めしません。

/* お勧めできません */
string sql = @"select username from test_table where data = '" + name + "'";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);

変数nameにおかしな値、例えばhoge'; delete from test_table where 'a'='aなどという文字列が入っていたら……。

Comments

[alg]

あら、かぶりましたね……。

[hotty4782]

ご回答有難うございます。無事に解決しました。

Answer 2

相変わらずPostgreSQLは使っていないため動作確認はとっていませんが・・・

単純に文字列操作で行うのなら、

// VS2015以前の場合だと
string sql = String.Format("select username from test_table where data =  {0}", name);

// VS2015以降の場合だと
string sql = $"select username from test_table where data =  {name}";

となるけれど、Npgsql自体にパラメータを扱う機能があるようなので、
http://vdlz.xyz/Csharp/Database/Postgre/Npgsql/Doc/Npgsql_Doc_006.html
こちらを参考にして

string sql = "select username from test_table where data =  :nameParam";
using(NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);)
{
    // varcharなのかほかの文字列型なのかわからないのでDBの定義に合わせてNpgsqlDbType.XXXXは変更してください
    cmd.Parameters.Add(new NpgsqlParameter("nameParam", NpgsqlDbType.XXXX));
    cmd.Parameters[0].Value = name;

    using (NpgsqlDataReader dr = cmd.ExecuteReader())
    {
        // やりたいこと
    }
}

という感じになるかと思います
どちらが良いのかって話になると、おそらくはNpgsqlのパラメータだと思います

Comments

[Panzer_vor]

> len_soukoさん
横から失礼致します。

①SQLインジェクション対策が期待できる
②2度目以降実行ではキャッシュを利用した高速アクセスが期待できる

上記2点から、
バインド変数(パラメタ)を利用する方が確実に得策ですね。

[hotty4782]

ご回答有難うございます。無事に解決しました。
複数の案を頂き助かりました。