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

• DForck42: I used the first example and it has fixed my issue