Answered Please help me understand the following Code Analysis Error

[Slabs1960]

I do not understand the following Warning when running a Code Analysis in Visual Studio 2019:

Warning CA2100 The query string passed to 'OleDbDataAdapter.New(String, OleDbConnection)' in 'FrmConfigurarion.SubSaveEquipmentToDBF()'
could contain the following variables 'Path.GetFileNameWithoutExtension(sFilePath)'.
If any of these variables could come from user input, consider using a stored procedure or a parameterized SQL query instead of building the query with string concatenations.
NamdebConfigurationTool D:\Documents\Visual Studio 2019\Projects\NamdebConfigurationTool\NamdebConfigurationTool\FrmConfigurarion.vb

Relevant section of code:

        '~~> Equipment Generate Parameters - OLEDB Connection
        Dim sFilePath As String = gThisApplicationRunFolder & "\UserData\Template\DBF Files\equip.dbf"

        Dim sFolder As String = Path.GetDirectoryName(sFilePath)
        Dim sDBFFileNameNoExtension As String = Path.GetFileNameWithoutExtension(sFilePath)

        Try
            Using dt As New DataTable

                DGVScadaEquipmentGeneration.DataSource = Nothing

                Using cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & sFolder & "';Extended Properties=dBase IV")

                    Using da As New OleDbDataAdapter("SELECT * FROM " & sDBFFileNameNoExtension, cnn)
                        cnn.Open()
                        da.Fill(dt)
                    End Using

Highlighted line 14 is the source of the warning.

I get the same warning with a diiferent section of code.:

Warning CA2100 The query string passed to 'OleDbCommand.New(String, OleDbConnection)' in 'FrmConfigurarion.SubSaveEquipmentToDBF()' could contain the following variables 'Path.GetFileNameWithoutExtension(sFilePath)', 'row("NAME")', 'row("CLUSTER")', 'row("TYPE")', 'row("COMMENT")', 'row("IODEVICE")', 'row("PAGE")', 'row("TAGPREFIX")', 'row("PARAM")'. If any of these variables could come from user input, consider using a stored procedure or a parameterized SQL query instead of building the query with string concatenations. NamdebConfigurationTool D:\Documents\Visual Studio 2019\Projects\NamdebConfigurationTool\NamdebConfigurationTool\FrmConfigurarion.vb 609 Active

                   For Each row As DataRow In dt.Rows

                        'Add New Rows to DBF
                        If row.RowState = DataRowState.Added Then

                            Try
                                Dim addCmd As New OleDbCommand("INSERT INTO " & sDBFFileNameNoExtension & " (NAME, CLUSTER, TYPE, COMMENT, IODEVICE, PAGE, TAGPREFIX, PARAM) VALUES ('" &
                                                                            row.Item("NAME").ToString & "', '" & row.Item("CLUSTER").ToString & "', '" & row.Item("TYPE").ToString & "', '" &
                                                                               row.Item("COMMENT").ToString & "', '" & row.Item("IODEVICE").ToString & "', '" & row.Item("PAGE").ToString & "', '" &
                                                                                    row.Item("TAGPREFIX").ToString & "', '" & row.Item("PARAM").ToString & "')", cnn)
                                addCmd.ExecuteNonQuery()

                            Catch ex As Exception
                                MessageBox.Show(ex.Message & vbLf & "Copy Equipment to DBF", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
                            Finally
                                'nothing
                            End Try


                            'Update Progress Bar
                            r = r + 1
                            statusConfigFrmProgressBar.Value = r
                            statusConfigFrm.Text = "Generating equipment list... [" & CStr(statusConfigFrmProgressBar.Value) & " of " & CStr(dt.Rows.Count) & "] - " & row.Item("TAGPREFIX").ToString
                            Update()

                        End If

                    Next

I do not know what the issue is. Explanation will be appreciated.

 

[jmcilhinney]

Read what it says:

If any of these variables could come from user input, consider using a stored procedure or a parameterized SQL query instead of building the query with string concatenations.

Do any of the variables come from user input? If not, there's nothing to worry about. If so, it tells you what to do about it. The point is, it's trying to avoid, among other things, SQL injection. If you don't know what that is, you should look it up.

 

[Slabs1960]

Thanks. None do. I will supress the warning.

 

[jmcilhinney]

Looking more closely at your second code snippet, you should be doing that quite differently anyway. To loop through a DataTable and call ExecuteNonQuery for each DataRow makes no sense when you can just pass the DataTable to a call to the Update method of a data adapter and save the lot in one go. In that case, you would have to use parameters but, even if you want top stick with doing it the way you are, the proper way would be to create one command and add the parameters, then set the Value of each parameter in the loop.