Security check using an SQL call

Question

In our latest Veracode scan for an application, I have come across the issue of Improper Resource Shutdown or Release. It is pointing at a function. Here's what the code looks like:

Imports System.Data.SqlClient
Public Class DAL
    Public Shared ConnString As String = ConfigurationManager.ConnectionStrings("connection").ConnectionString


Public Shared Function CheckSecurity(ByVal strUserID As String, ByVal strOperation As String, ByVal strAppID As String) As Boolean
    Dim sbSQL As New StringBuilder
    Dim MyConnection As SqlConnection = New SqlConnection()
    Dim sqlCmd As SqlCommand = New SqlCommand

    MyConnection.ConnectionString = ConnString

    sbSQL.Clear()

    sbSQL.AppendLine("EXEC dbo.CheckSecurity @UserID, @AppID, @Operation")

    sqlCmd.CommandText = sbSQL.ToString
    sqlCmd.Connection = MyConnection

    With sqlCmd.Parameters
        .Clear()
        .Add("@UserID", SqlDbType.VarChar, 15).Value = strUserID
        .Add("@AppID", SqlDbType.VarChar, 50).Value = strAppID
        .Add("@Operation", SqlDbType.VarChar, 50).Value = strOperation
    End With


    Try
        If getDataTableFromSqlCmd(sqlCmd).Rows.Count > 0 Then
            CheckSecurity = True
        Else
            CheckSecurity = False
        End If

    Catch ex As Exception
        Throw New ApplicationException("SECURITY ACCESS ERROR")
    Finally
        If MyConnection.State = ConnectionState.Open Then
            MyConnection.Close()
        End If

        MyConnection.Dispose()
        sqlCmd.Dispose()

    End Try
End Function

Code for getDataTableFromSqlCMD:

Public Shared Function getDataTableFromSqlCmd(ByVal sqlCmd As SqlCommand) As DataTable
    Dim dt As New DataTable
    Dim MyAdapter As New SqlDataAdapter(sqlCmd)

    Try
        sqlCmd.CommandTimeout = m_iSQLTimeOut
        MyAdapter.Fill(dt)
        getDataTableFromSqlCmd = dt

    Catch ex As Exception
        Throw New ApplicationException("GET DATA TABLE ERROR")
    Finally
        sqlCmd.Dispose()
        MyAdapter.Dispose()
        dt.Dispose()
    End Try
End Function

As far as I can tell the resources in this code are being properly deallocated. Am I missing something?

Answer 1

It needs more Using statements. For example:

Public Shared Function CheckSecurity(strUserID$, strOperation$, strAppID$) As Boolean
    Try
        Using da As New SqlDataAdapter("dbo.CheckSecurity", ConnString) 
            Dim sc = da.SelectCommand, p = sc.Parameters, dt = New DataTable
            sc.CommandType = CommandType.StoredProcedure
            sc.CommandTimeout = m_iSQLTimeOut

            p.Add("@UserID", SqlDbType.VarChar, 15).Value = strUserID
            p.Add("@AppID", SqlDbType.VarChar, 50).Value = strAppID
            p.Add("@Operation", SqlDbType.VarChar, 50).Value = strOperation

            Return da.Fill(dt) > 0     ' .Fill returns the number of rows successfully added
        End Using           ' da is disposed here even if Exception occurs
    Catch ex As Exception
        Throw New ApplicationException("SECURITY ACCESS ERROR")
    End Try
    Return False
End Function

or

Public Shared Function CheckSecurity(strUserID$, strOperation$, strAppID$) As Boolean
    Try
        Using con = New SqlConnection(ConnString), 
              cmd = New SqlCommand("dbo.CheckSecurity", con)

            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandTimeout = m_iSQLTimeOut

            cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 15).Value = strUserID
            cmd.Parameters.Add("@AppID", SqlDbType.VarChar, 50).Value = strAppID
            cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 50).Value = strOperation

            con.Open()
            Using reader = cmd.ExecuteReader
                Return reader.HasRows
            End Using
        End Using       ' con and cmd are closed and disposed here even if Exception occurs
    Catch ex As Exception
        Throw New ApplicationException("SECURITY ACCESS ERROR")
    End Try
    Return False
End Function

Some other examples https://stackoverflow.com/questions/24023575/how-to-pass-parameters-to-sqldataadapter, https://stackoverflow.com/questions/14566980/c-sharp-data-adapter-parameters