Updating past VS 2017 project

Mark McCumber 431 Reputation points
2023-05-03T16:50:13.3+00:00

I developed a data entry tool back in 2008 and I want to upgrade it to VS 2017/2022. I made this tool to help me populate MS Access and SQL Server database tables by dynamically creating data entry forms based on the structure of the table. When I wrote this application there were no standards on how an inline SQL statement was supposed to be written.

The code snippet written below contains warnings when analyzing. I have provided the Warning code next to the offending lines. Can someone please tell me how to fix these warnings?


Using objConn As New OleDbConnection(cnnOleDB)
            Try
                If objConn.State = ConnectionState.Open Then
                    objConn.Close()
                Else
                    objConn.Open()
                    strSQL = "SELECT * FROM " & strTab 'CA2100 Warning- Need Parameterized query
                    da = New OleDbDataAdapter(strSQL, objConn)
                    'Build Commands for add, delete, and update
                    sb = New OleDbCommandBuilder(da)
                    'Update the selected table
                    da.Update(dt_Records)
                    Msg = "The " & strTab & " table from the " & cboOleDBTbls.Text &
                          " database has been successfully updated."
                    MessageBox.Show(Msg)
                End If
            Catch ex As Exception
                'Log error 
                Dim el As New Log.ErrorLogger
                el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                'Finally
                'objConn.Close()
            End Try
        End Using 'CA2202 Warning - 
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,665 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Jiachen Li-MSFT 29,106 Reputation points Microsoft Vendor
    2023-05-04T02:02:33.5933333+00:00

    Hi, @Mark McCumber ,

    Based on CA2100: Review SQL queries for security vulnerabilities.

    This rule assumes that any string, whose value can't be determined at compile time, may contain user input. A SQL command string that is built from user input is vulnerable to SQL injection attacks. In a SQL injection attack, a malicious user supplies input that alters the design of a query in an attempt to damage or gain unauthorized access to the underlying database. Typical techniques include injection of a single quotation mark or apostrophe, which is the SQL literal string delimiter; two dashes, which signifies a SQL comment; and a semicolon, which indicates that a new command follows. If user input must be part of the query, use one of the following, listed in order of effectiveness, to reduce the risk of attack.

    Use a stored procedure.

    Use a parameterized command string.

    • Validate the user input for both type and content before you build the command string.'

    You can refer to the following code to use a parameterized command string, you can also refer to the example in the document I provided above.

    Dim strSQL As String = "SELECT * FROM @tableName"
    Dim cmd As New SqlCommand(strSQL, connection)
    cmd.Parameters.AddWithValue("@tableName", tableName)
    Dim reader As SqlDataReader = cmd.ExecuteReader()
    

    Best Regards.

    Jiachen Li


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Mark McCumber 431 Reputation points
    2023-05-05T16:16:59.4033333+00:00

    Found the solution yesterday. My problem occurred when I imported the form into my project. For some reason the OpenFileDialog tool failed to import along with the form.

    0 comments No comments