Getting 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 near

I am getting this error message:

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 ”D:\AirMaintStorage\’ where id=1′ at line 1

I run this in WorkBench and it works correctly but from my code in my app I get the error above. Here is my code:

string SQL = "Update Company_Table set Company_Table_Default_Storage='";
SQL += ra.EscFunction(DefaultStorage);
SQL += "' where id=";
SQL += cp.id.ToString();
MySql.Data.MySqlClient.MySqlConnection  conn = new MySql.Data.MySqlClient.MySqlConnection (ra.conn_String1);
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(SQL, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

The query is:

Update Company_Table set Company_Table_Default_Storage='D:\\AirMaintStorage\\' where id=1

ra is a utilities class that holds the connection information and escfunction escapes apostrophes in a string. cmp.cp.id is the id field of my company table.

>Solution :

I expect there’s a bug in EscFunction(). You should remove that function from your code base completely; it’s entirely the wrong way to approach the issue. It is not correct to sanitize your database inputs!

Rather, the only correct approach is to QUARANTINE your database inputs using parameterized queries, as demonstrated below:

string SQL = "
UPDATE Company_Table 
SET Company_Table_Default_Storage= @DefaultStorage 
WHERE ID= @ID";

using (var conn = new MySqlConnection(ra.conn_String1))
using (var cmd = new MySqlCommand(SQL, conn))
{
    cmd.Parameters.AddWithValue("@DefaultStorage", DefaultStorage);
    cmd.Parameters.AddWithValue("@ID", cp.id);
    conn.Open();
    cmd.ExecuteNonQuery();
} // No need to even call conn.Close(); The using block takes care of it.

Also note the use of using blocks to manage the connection lifetime.

Finally, I want to address this comment:

I have been trying to move to a more multi-platform environment so I have started moving my app to MySQL.

I’m getting outside the realms of the both the question scope and the fact-based verifiable-answer here, and more into an expression of my personal opinion on a specific technology. Nevertheless, I didn’t want to leave that comment alone.

I do get wanting to support a broader set of database technologies, but you should know the MySql product spent many years (from ~2005 to ~2018) almost completely stagnant. It has fallen significantly behind the other options and is the least standards compliant of the major products in the space. If I need to move an app to a new open source DB today I’d pick Postgresql, and it wouldn’t be close.