C# SQL parameterized SQL Query

Question

I just keep on getting Syntax error when I used parameterized sql query.

public List<string> Cat(string product,string table)
{
    List<string> Products = new List<string>();
    Global global = new Global();
    string sql = "SELECT @prod FROM @tbl";
    MySqlConnection connection = new MySqlConnection(global.ConnectionString);
    MySqlCommand command = new MySqlCommand(sql, connection);
    command.Parameters.AddWithValue("@prod", product);
    command.Parameters.AddWithValue("@tbl", table);
    connection.Open();
    MySqlDataReader reader = command.ExecuteReader();
    if (reader.HasRows)
    {
        while (reader.Read())
            Products.Add(reader.GetString("@prod"));
    }
    connection.Close();
    return Products;
}

public List<string> CallProducts(string category)
{
    string table;
    string product;
    List<string> stacks = new List<string>();
    if (category == "Accessories")
    {
        product = "Accessories_Name";
        table = "tbl_accessories";
        stacks.AddRange(Cat(product, table).ToArray());                
    }
    else if (category == "Batteries")
    {
        table = "tbl_batteries";
    }
    else if (category == "Cotton")
    {
        table = "tbl_cotton";
    }
    else if (category == "Juices")
    {
        table = "tbl_juices";
    }
    else if (category == "Kits")
    {
        table = "tbl_kits";
    }
    else if (category == "Mods")
    {
        table = "tbl_mods";
    }
    else
    {
        table = "tbl_vapeset";
    }
    return stacks;
}

I just keep on getting SQL Syntax Error. It works if the table and the name is manually inputted rather than using parameters.

Hoping you can help.

Need for a project.

Thanks!

**Pro Tip** we cant see your screen, so copying the errors in is mandatory — TheGeneral, Sep 30 '18 at 08:20
Hi Pro Tip,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 ''tbl_accessories'' at line 1. — Richard Pabalan, Sep 30 '18 at 09:02
Possible duplicate of [MySQL table name as parameter](https://stackoverflow.com/questions/33254721/mysql-table-name-as-parameter) — Bradley Grainger, Oct 01 '18 at 00:28

score 0 · Answer 1 · answered Sep 30 '18 at 08:29

0

Correct use would be:

string sql = $"SELECT {product} FROM {table}";

Because table and column are not parameters.

Moreover, I would recommend using Command.Parameters.Add(...).Value(...), over Parameters.AddWithValue, since in first approach you can explicitly decide what datatype you want to pass and prevent SQL from guessing it.

answered Sep 30 '18 at 08:29

Michał Turczyn

32,028
14
47
69

Hi, I tried using this but also gives the same error. 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 '} FROM {table}' at line 1 – Richard Pabalan Sep 30 '18 at 09:05
Hey thanks for the tip :) it was such a big help :) we got it now – Richard Pabalan Sep 30 '18 at 09:09
If answer helped you, you should accept it (green check mark on the left) and optionally upvote it. – Michał Turczyn Sep 30 '18 at 09:12

C# SQL parameterized SQL Query

1 Answers1