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

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