MySql command parameters with quotes

Question

i want to insert rows to a MySql Database through my c# application (using Connector/Net 8.0.11). I'm using command parameters to pass the values because - in my understanding - i don't have to care about any kind of special characters. Please find a simplified code example below.

MySqlCommand Cmd = new MySqlCommand("INSERT INTO testtab(TestCol1,TestCol2) VALUES(@test1,@test2)", Con);

Cmd.Parameters.AddWithValue("@test1", "12'34");
Cmd.Parameters.AddWithValue("@test2", "56");

Cmd.ExecuteNonQuery();

But the single quote in "12'34" leads to an exception:

"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 '34','56')' at line 1"

I know that i could simply not use parameters at all, and just double the quote inside my value string... but i have built similar apps using Connector/ODBC and they work fine. Can you please help me to find the trick with Connector/Net?

Answer 1

I really appreciate your help ladies and gentlemen - and special thanks to Progman, your comment solved my problem.

I'll now answer my own question to sum it up. There are 3 possible solutions:

The whole issue is related to the SQL mode NO_BACKSLASH_ESCAPES. One solution could be turning off that particular SQL mode.

fubo mentioned in his comment:

The escaping result of MySql.Data.MySqlClient.MySqlHelper.EscapeString("12'34") is 12\'34

It is weird but the SQL mode NO_BACKSLASH_ESCAPES struggles with the escape strings of parameter values (imo this is a bug in Connector/Net since Connector/ODBC works fine). Another solution could be using Connector/ODBC.

The final trick for me was Progmans comment:

According to dev.mysql.com/doc/connector-net/en/… you have to call Prepare() before adding the values.

This is more like a workaround, but if you use SQL mode NO_BACKSLASH_ESCAPES and command parameters together, you have to call Prepare() before calling ExecuteNonQuery(). Please note that - unlike the example in the developer guide - i couldn't call it before adding the parameters (you'll get an exception), and i added IgnorePrepare=false to the connection string. I assume the reason why this works is because it changes the data transfer between client and server:

Another advantage of prepared statements is that, with server-side prepared statements enabled, it uses a binary protocol that makes data transfer between client and server more efficient.

Here is the code snippet that works for me:

//add IgnorePrepare=false to the connection string
MySqlConnection Con = new MySqlConnection("Server = localhost; Port = 3306; Database = param_test; Uid = ***; Pwd = ***; SslMode=none; IgnorePrepare=false");
Con.Open();
MySqlCommand Cmd = new MySqlCommand("INSERT INTO testtab(TestCol1,TestCol2) VALUES(@test1,@test2)", Con);

Cmd.Parameters.AddWithValue("@test1", "12'34");
Cmd.Parameters.AddWithValue("@test2", "56");

Cmd.Prepare();  //calling it after Adding the parameters works fine
Cmd.ExecuteNonQuery();