MySqlDependency with Parameterized Query

[Zero-G.]

Hey

Using the .Net Core Version of your mySQL Product
I maybe found a bug in the MySqlDependency class
I want to add a CommandDependency by using a list, what works really fine:

Code: Select all

  locListOfTables.ForEach(Sub(locItem)
                                    Dim locCommand As New MySqlCommand With {.Connection = myConnection,
                                                                             .CommandText = String.Format("Select * from {0}", locItem}

                                    Dim locTableCommand = locCommand

                                    locDependency.AddCommandDependency(locTableCommand)
                                End Sub)

The problem with this code is, that Visual Studio says, that the Select command is not safe: https://docs.microsoft.com/en-us/visual ... ew=vs-2019

This is, why I tried to add parameterized Query to the CommandDependency:

Code: Select all

locListOfTables.ForEach(Sub(locItem)
                                    Dim locCommand As New MySqlCommand With {.Connection = myConnection,
                                                                             .CommandText = "Select * from :table"}

                                    locCommand.Parameters.Add("table", locItem.ToString)

                                    Dim locTableCommand = locCommand

                                    locDependency.AddCommandDependency(locTableCommand)
                                End Sub)

But then, then OnChange event get's never hit. In the first solution, everything works fine.
Could you please check this
THX a lot

[Shalex]

Refer to the discussion at https://stackoverflow.com/questions/332 ... -parameter:

You can not parameterize your table names, column names or any other databse objects. You can only parameterize your values.

You need to pass it as a string concatenation on your sql query but before you do that, I suggest use strong validation or white list (only fixed set of possible correct values).

[Shalex]

I want to add a CommandDependency by using a list, what works really fine:

Code: Select all

  locListOfTables.ForEach(Sub(locItem)
                                    Dim locCommand As New MySqlCommand With {.Connection = myConnection,
                                                                             .CommandText = String.Format("Select * from {0}", locItem}

                                    Dim locTableCommand = locCommand

                                    locDependency.AddCommandDependency(locTableCommand)
                                End Sub)

The problem with this code is, that Visual Studio says, that the Select command is not safe: https://docs.microsoft.com/en-us/visual ... ew=vs-2019

It's a warning that SQL query may contain user input. Review your code to check if this applies to your case.