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