DForck42 DForck42 - 1 month ago 12
Vb.net Question

VB.NET Improper Resource Shutdown or Release

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

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 How to pass parameters to SqlDataAdapter, C# Data Adapter Parameters