MySQL truncate command with parameter not working

Question

Why do I get an exception when trying to truncate a MySQL table (using MySQL Connector/Net)? I am trying to give the table name with a parameter.

This is the code I'm executing:

var connectionString = "Server="+_server+";Uid="+_user+";Pwd="+_password+";Database="+_database+";";

try
{
    using (var conn = new MySqlConnection(connectionString))
    {
        conn.Open();
        const string sql = "TRUNCATE TABLE @tablename"; // also tried with TRUNCATE @tablename
        var cmd = new MySqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@tablename", "test");
        cmd.ExecuteNonQuery();
        conn.Close();
    }

}
catch (MySqlException ex)
{
    Console.WriteLine(ex.ToString());
}

And this is the execption:

MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQ L syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''test'' at line 1

When I try a select query, for example, then I don't have any problems. This runs fine and returns correct data:

conn.Open();
const string sql = "SELECT body FROM test WHERE id=@pid";
var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@pid", 1);
cmd.ExecuteScalar();
conn.Close();

what's the result giving the table name without Paramters? `var cmd = "TRUNCATE TABLE test"` — Gianni B., Jul 01 '13 at 10:10

Tamim Al Manaseer · Accepted Answer · 2016-01-25T13:32:42.003

2

Parameters are used for query values, not object names like tables.

So this will not work for sure.

You need to set the table name in the command string by using string concatenation. You can avoid sql injection attacks by manually checking for weird characters in the table name (spaces, dashes, semicolons, etc..)

edited Jan 25 '16 at 13:32

answered Jul 01 '13 at 10:26

Tamim Al Manaseer

3,554
3
24
33

Thanks, [this SO question](http://stackoverflow.com/questions/3128582/table-name-and-table-field-on-sqlparameter-c) seems to confirm it. – Siim K Jul 01 '13 at 11:05

score 0 · Answer 2 · answered Jul 01 '13 at 10:21

I've been playing around with this for a while now, and i can't seem to get it to work either. I can't find any documentation online, so i'm starting to think you may not be able to truncate with a parameter like you've tried.

However, is there really a need to prevent SQL injection on this command? Does the user enter the name of the table they want to truncate, and if so, they're just going to truncate a table which...is essentially what the command does anyway?

No real need, I guess. Just was so focused on doing things "the right way" that did not realize it :) — Siim K, Jul 01 '13 at 11:06

MySQL truncate command with parameter not working

2 Answers2