• Hello and welcome to our new forums. We upgraded our forum sites to a more robust and modern system which we hope you will enjoy. Be sure to check out your profile by clicking the button on the top right and configure your preferences, signature, time zone, avatar, etc. as you wish. If you need help with using this new forum'ware try the help link on the bottom right.

    Click here to review your account now.

Answered Please help me understand the following Code Analysis Error

Slabs1960

Member
Joined
Mar 19, 2017
Messages
9
Programming Experience
10+
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:
VB.NET:
        '~~> 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
VB.NET:
                   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.
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,047
Location
Sydney, Australia
Programming Experience
10+
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.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,047
Location
Sydney, Australia
Programming Experience
10+
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.
 
Top Bottom