SQL Syntax error when using parameters

Question

This is the code from one class:

 Private Sub LoadTable()
    dt.Dispose()
    command.CommandText = "SELECT * FROM @TableName"
    command.Parameters.Add(New MySqlParameter("TableName", TableName))
    dt = SQL.ExecuteCommand(command)
    command.Dispose()
End Sub

This is the code from another class, which the above code calls:

Public Function ExecuteCommand(ByVal Command As MySqlCommand)
    Dim dt As New DataTable
    Dim da As New MySqlDataAdapter
    Dim con As New MySqlConnection

    con.ConnectionString = GetConnectionString
    Command.Connection = con

    con.Open()
    da.SelectCommand = Command
    da.Fill(dt)
    con.Close()

    con.Dispose()
    da.Dispose()
    Return dt
End Function

The variable TableName is equal to the string Client.

The syntax is valid, as I have tried it both in the MySQL Workbench and it works if I try it unparametrized.

The error I'm getting is that the syntax is not valid: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Client'' at line 1".

Any clue how to fix this, while still using a parametrized MySqlCommand?

In prepared statements table name cannot be passed as parameters — M Khalid Junaid
– M Khalid Junaid, Commented Mar 4, 2014 at 19:52

Steve · Accepted Answer · 2014-03-04 20:02:08Z

Parameters cannot be used to represent a tablename or a fieldname. They can only be used to represent values in WHERE conditions or to insert/update values in INSERT or UPDATE queries.

In this situation you have no alternative than reverting to string concatenation.
Just be sure that you don't allow your user to type the tablename, but only select it from a predefined list of well known names

Private Sub LoadTable(table as String)
    dt.Dispose()
    dt = SQL.ExecuteCommand("SELECT * FROM " & table)
    command.Dispose()
End Sub

I have also changed your ExecuteCommand to receive a string instead of a command. In my opinion this concentrates all of your disposable objects in the ExecuteCommand method where they could be easily initialized and disposed through the Using Statement

Public Function ExecuteCommand(cmdText as String)
    Dim dt As New DataTable
    Using con = New MySqlConnection(GetConnectionString())
    Using command = new MySqlCommand(cmdText, con)
       con.Open()
       Using da = new MySqlDataAdapter(command)
          da.Fill(dt)
       End Using
    End Using
    End Using
    Return dt
End Function

Collectives™ on Stack Overflow

SQL Syntax error when using parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related