Must declare the table variable @table

Question

I'm a beginner in C# and SQL, i have this SQL insert statement that i want to perform. It asks for the table name among the other variables that i want to insert.

But when i run this console app i get this error :

Must declare the table variable @table

This is a part of the code :

StreamReader my_reader =  getFile(args);
string CS = formCS();
try
{
    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlCommand com = new SqlCommand("insert into @table (time, date, pin) values (@time, @date, @pin)", con);                    
        con.Open();
        Console.WriteLine("Enter table name:");
        Console.Write(">> ");
        string tblname = Console.ReadLine();
        com.Parameters.AddWithValue("@table", tblname);

        string line = "";
        int count = 0;
        while ((line = my_reader.ReadLine()) != null)
        {
            Dictionary<string, string> result = extractData(line);                        
            com.Parameters.AddWithValue("@time", result["regTime"]);
            com.Parameters.AddWithValue("@date", result["regDate"]);
            com.Parameters.AddWithValue("@pin", result["regPin"]);
            count += com.ExecuteNonQuery();
            com.Parameters.Clear();                        

        }
        Console.WriteLine("Recoreds added : {0}", count.ToString());
        Console.WriteLine("Press Enter to exit.");
    }
    Console.ReadLine();
}
catch (SqlException ex)
{
    Console.WriteLine(ex.Message);
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);                
}

Gastronome

It is not allowed to have the table name of your insert statememt as a variable. Look into sp_exec if you need something like that. – Exclusion

Answer 1

You can't do this. You can't pass the table name as a parameter the way you did:

SqlCommand com = new SqlCommand("insert into @table ...");
...
com.Parameters.AddWithValue("@table", tblname);

You can do this instead:

Console.WriteLine("Enter table name:");
Console.Write(">> ");
string tblname = Console.ReadLine();

string sql = String.Format("insert into {0} (time, date, pin) values ... ", tblname);

SqlCommand com = new SqlCommand(sql, con);                    

...

Encapsulate

Does this allow sql injection in any way ? – Gastronome

@RafaelAdel Yes it does. You should check if tblname is a table in your database (SELECT * FROM INFORMATION_SCHEMA.TABLES). – Mikaela

@RafaelAdel, I check the table name for [ and ]. If it contains either then stop. Then enclose the table name in [] . – Osmic

@Mahmoud Normally the queries are being cached to avoid parsing cost. That is one of the reasons to use paramterized queries as well. Your answer may cause the query to get parsed again and again since it might be different query text everytime – Drudgery

Answer 2

The table name cannot be an input parameter in a sql query. However, you can always "prepare the sql string BEFORE passing it to the SqlCommand as follows:

var sqlString = string.Format("insert into {0} (time, date, pin) values (@time, @date, @pin)", tblname) 

and then

SqlCommand com = new SqlCommand(sqlString);
...

Indisposition

Answer 3

Be aware that this could allow SQL Injection attacks like this...

string tblname = "; DROP TABLE users;";
var sqlString = string.Format("insert into {0} (time, date, pin) values (@time, @date, @pin)", tblname)

https://en.wikipedia.org/wiki/SQL_injection

Suttle