12

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);                
}
Rafael Adel
  • 7,673
  • 25
  • 77
  • 118
  • 5
    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. – rene Dec 22 '12 at 13:34

3 Answers3

19

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);                    

...
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • 5
    Does this allow sql injection in any way ? – Rafael Adel Dec 22 '12 at 13:44
  • 7
    @RafaelAdel Yes it does. You should check if `tblname` is a table in your database (`SELECT * FROM INFORMATION_SCHEMA.TABLES`). – ctusch Feb 06 '13 at 09:25
  • 2
    @RafaelAdel, I check the table name for [ and ]. If it contains either then stop. Then enclose the table name in [] . – Christopher Thomas Nicodemus May 06 '14 at 00:47
  • @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 – Haseeb Jadoon Aug 15 '16 at 06:49
5

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);
...
user1888014
  • 157
  • 3
0

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

Tony
  • 16,527
  • 15
  • 80
  • 134
Michał B
  • 144
  • 1
  • 4