zukucode
主にWEB関連の情報を技術メモとして発信しています。

VB.NET ODP.NETでSQLにパラメータを設定する

ODP.NETを使用してSQLを実行する際にパラメータを設定する方法を紹介します。

ファンクションの引数を条件にSQLを実行している処理があるとします。

以下のようにSQL文に変数を埋め込むような実装方法では、SQLインジェクションなどの脆弱性があるため、避ける必要があります。

Imports Oracle.DataAccess.Client 'ファイルの先頭に追加

Public Function SelectData(id As String) As DataSet

    Dim ds As New DataSet

    Using conn As OracleConnection = New OracleConnection(接続文字列)

        'コネクションOpen
        conn.Open()

        Dim sql As StringBuilder

        'TABLE_Aのデータを取得
        sql = New StringBuilder
        sql.AppendLine(" SELECT ")
        sql.AppendLine("     T1.COL1 ")
        sql.AppendLine("     ,T1.COL2 ")
        sql.AppendLine(" FROM ")
        sql.AppendLine("     TABLE_A T1 ")
        sql.AppendLine(" WHERE ")
        sql.AppendLine("     T1.COL1 = " & id & " ") 'これはマズい

        Using cmd As New OracleCommand(sql.ToString(), conn)
            Using da As OracleDataAdapter = New OracleDataAdapter(cmd)
                da.Fill(ds, "TABLE_A")
            End Using
        End Using

    End Using

    Return ds

End Function

変数はSQLに埋め込むのではなく、以下のようにバインド変数として指定する必要があります。

'TABLE_Aのデータを取得
sql = New StringBuilder
sql.AppendLine(" SELECT ")
sql.AppendLine("     T1.COL1 ")
sql.AppendLine("     ,T1.COL2 ")
sql.AppendLine(" FROM ")
sql.AppendLine("     TABLE_A T1 ")
sql.AppendLine(" WHERE ")
sql.AppendLine("     T1.COL1 = " & id & " ")
sql.AppendLine("     T1.COL1 = :COL1 ") 'バインド変数として設定

Using cmd As New OracleCommand(sql.ToString(), conn)
    cmd.BindByName = True
    cmd.Parameters.Add(":COL1", OracleDbType.Decimal).Value = id
    
    Using da As OracleDataAdapter = New OracleDataAdapter(cmd)
        da.Fill(ds, "TABLE_A")
    End Using
End Using

SQL文に:パラメータ名の形式で指定すると、バインド変数として扱われます。

cmd.BindByName = Trueを設定すると、バインド変数とパラメータを名称で紐づけることができます。

例えば以下のように複数のバインド変数があるSQLに対してパラメータを設定するときを考えます。

'TABLE_Aのデータを取得
sql = New StringBuilder
sql.AppendLine(" SELECT ")
sql.AppendLine("     T1.COL1 ")
sql.AppendLine("     ,T1.COL2 ")
sql.AppendLine(" FROM ")
sql.AppendLine("     TABLE_A T1 ")
sql.AppendLine(" WHERE ")
sql.AppendLine("     T1.COL1 = :COL1 ")
sql.AppendLine("     AND T1.COL2 = :COL2 ")
sql.AppendLine("     AND T1.COL3 = :COL1 ")

cmd.BindByName = Trueを設定していないとバインド変数が記載されている順番にパラメータを指定する必要があります。

Using cmd As New OracleCommand(sql.ToString(), conn)
    'バインド変数の順番とパラメータの順番を合わせる必要がある(パラメータ名に意味はない)
    cmd.Parameters.Add(":COL1", OracleDbType.Decimal).Value = id
    cmd.Parameters.Add(":COL2", OracleDbType.Decimal).Value = id2
    cmd.Parameters.Add(":COL3", OracleDbType.Decimal).Value = id
    
    Using da As OracleDataAdapter = New OracleDataAdapter(cmd)
        da.Fill(ds, "TABLE_A")
    End Using
End Using

cmd.BindByName = Trueを設定すると、バインド変数名とパラメータ名で紐づけることができます。

SQLに同じ名称のバインド変数名がある場合は、そのパラメータ名で1つだけパラメータを作成すれば、同じ名称のバインド変数はすべて紐づけることができます。

Using cmd As New OracleCommand(sql.ToString(), conn)
    cmd.Parameters.Add(":COL1", OracleDbType.Decimal).Value = id
    cmd.Parameters.Add(":COL2", OracleDbType.Decimal).Value = id2
    
    Using da As OracleDataAdapter = New OracleDataAdapter(cmd)
        da.Fill(ds, "TABLE_A")
    End Using
End Using

関連記事

  • VB.NET YYYYMMDD形式の文字列を日付型に型変換する

    VB.NETでYYYYMMDD形式の文字列を日付型に型変換する方法を紹介します。最初にYYYY/MM/DDのように、年月日の区切りに/を追加します。/区切りの文字列にするとCDateで日付型に型変換で...


  • VB.NET YYYYMMDD書式の文字列が日付かどうか判定する

    VB.NETで、YYYYMMDD書式の文字列が日付として妥当かどうかを判定します。以下のように、Date.TryParseExactを使用して、日付型の文字列に変換可能かどうかで判定しています。Dat...


  • VB.NET 文字列の全角と半角の変換を行う

    VB.NETで文字列を全角→半角や半角→全角に変換するにはStrConv関数を使用します。StrConvの第1引数に対象文字列を指定し、第2引数に変換方法を指定します。半角に変換するにはVbStrCo...


  • VB.NET TypeOfで変数の型判定・比較を行う

    VB.NETで変数の型をチェックするにはTypeOfを使用します。TypeOf チェックしたい変数名 Is 判定したい型の形式で指定します。


  • VB.NET StringBuilderの末尾の1文字を削除する

    StringBuilderで末尾の1文字だけ削除したいときは以下のようにします。Remove(位置, 文字数)で削除します。位置の指定について、1文字目は0から始まるため、文字列の長さ(Length)...


  • VB.NET 指定した文字列を削除する

    VB.NETで指定した文字列を削除する方法を紹介します。Replace関数は指定した文字列を別の文字列に置換する関数です。置換する文字列に空文字を指定すれば、指定した文字列が削除される形になります。J...


  • VB.NET 指定した桁数だけ同じ文字を文字列で定義する

    VB.NETで指定した桁数だけ同じ文字を文字列で定義する方法を紹介します。例えばオール9の10桁の文字列9999999999を定義するときに、9999999999を指定するのは、桁数を間違える危険があ...


  • VB.NET ダブルクォーテーションを文字列として扱う方法

    VB.NETで文字列を扱うときは以下のようにダブルクォーテーションで囲います。abcdeのように、文字列の中にダブルクォーテーションを含めたい場合は少し工夫が必要です。以下の2つの方法をよく見かけます...


  • VB.NET 文字列を数値に変換する方法

    VB.NETでString型の文字列を数値に変換する方法を紹介します。以下の例では、Integer型の数値に変換しています。文字列型の変数を数値型に変換する場合には以下に注意する必要があります。例えば...


  • VB.NET 文字列を連結する便利な方法まとめ

    VB.NETで文字列を連結します。VB.NETでは文字列の連結は文字列同士を&で結合できます。&の代わりに+でも可能ですが、数値型の場合は足し算として扱われてしまうので基本的には&を使用することをおす...