How to pass a table as parameter to MySqlCommand?

Question

I am creating a method to select the id from any table by passing a search field.

private int SelectId(string tabela, string campo, string valor)
{
    int id = 0;

    using (command = new MySqlCommand())
    {
        command.Connection = conn;

        command.Parameters.Add("@tabela", MySqlDbType.).Value = tabela;
        command.Parameters.Add("@campo", MySqlDbType.Text).Value = campo;
        command.Parameters.Add("@valor", MySqlDbType.VarChar).Value = valor;

        command.CommandText = "SELECT `id` FROM @tabela WHERE @campo=@valor;";

        try
        {
            id = (int)command.ExecuteScalar();
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Number + " : " + ex.Message + command.CommandText);
        }
        catch (Exception)
        {
            throw;
        }
    }

    return id;
}

But I get an MySqlException about syntax error. When i look at the Exception message, it shows me the query with the quoted table! How do I pass the table as parameter without quotes?

Answer 1

Most databases won't let you specify table or column names via parameters. Parameters are meant to be for values. If you really, really need this to be dynamic, you should validate the input (it should be a known table name, with known column names within that table) and then include that in the SQL.

Answer 2

I agree with Jon. Here is a sample of your code with the table name inserted directly into the script, instead of as a parameter. Notice that you'll still want to validate the table and column name to prevent SQL injection. I have not included that here, but I have put in comment stubs for you.

private int SelectId(string tabela, string campo, string valor)
    {
        int id = 0;

        using (command = new MySqlCommand())
        {
            command.Connection = conn;

            command.Parameters.Add("@campo", MySqlDbType.Text).Value = campo;
            command.Parameters.Add("@valor", MySqlDbType.VarChar).Value = valor;

            // TODO:  Validate table name for parameter 'tabela' to prevent SQL injection
            // TODO:  Validate column name for parameter 'campo' to prevent SQL injection

            command.CommandText = "SELECT `id` FROM " + tabela + " WHERE @campo=@valor;";

            try
            {
                id = (int)command.ExecuteScalar();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Number + " : " + ex.Message + command.CommandText);
            }
            catch (Exception)
            {
                throw;
            }
        }

        return id;
    }