MySql.Data 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

Question

The following code:

string query = "INSERT INTO `@tableName` (@tableKeys) VALUES(@tableValues)";

MySqlCommand sql = new MySqlCommand(query);
sql.Connection = connection;
sql.Parameters.Add(new MySqlParameter("@tableName", values[0]));
sql.Parameters.Add(new MySqlParameter("@tableKeys", values[1]));
sql.Parameters.Add(new MySqlParameter("@tableValues", values[2]));
sql.ExecuteNonQuery();

Returns the error:

An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

Additional information: 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 ''porto,date') VALUES('232,17-11-2014 10:48:11')' at line 1

And I call on the code using this:

string[] values = {"logs","porto,date",num+","+date};
bool dbopen = dbManager.OpenConnection();
if(dbopen){
   dbManager.Insert(values);
}

Can anyone please tell me what I'm doing wrong?

possible duplicate of [Sql - tablename as variable](http://stackoverflow.com/questions/2838490/sql-tablename-as-variable) — Renatas M., Nov 17 '14 at 10:01
`INSERT INTO 'log' (porto, date) VALUES (@num, @date)` and add 2 parameters for @num and @date. — Renatas M., Nov 17 '14 at 10:03

Soner Gönül · Accepted Answer · 2014-11-17T10:05:19.980

You can't parameterize your table names and column names.

You can only parameterize your values. You need to add your table name and column name as a part of your sql query.

You should either perform very stringent validation on the table name before putting it into the SQL, or have a whitelisted set of valid table names, in order to avoid SQL injection attacks in the normal way.

Read The Curse and Blessings of Dynamic SQL

score 0 · Answer 2 · answered Nov 17 '14 at 11:03

0

i fixed the problem myself using this piece of code:

string query = "INSERT INTO `"+values[0]+"` VALUES("+values[2]+")";
        MySqlCommand sql = new MySqlCommand(query);
        sql.Connection = connection;
        sql.ExecuteNonQuery();

answered Nov 17 '14 at 11:03

Giovanni Le Grand

784
7
19

MySql.Data 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

2 Answers2