funny characters in Sql String-CodePudding

I have a query string that works fine if tableStr is for example MSFT. However, if tableStr is BRK-B, the query fails.

    string query = "SELECT * FROM "   tableStr   " where DATE(Date) >= "   "'"   howFarBack.ToString("yyyy-MM-dd")   "'";

How do I get around this?

CodePudding user response：

Per MySQL Documentation, "The identifier quote character is the backtick (`)".

This means that if your table name has special characters in it, you need to surround the identifier with backticks. To avoid SQL injection problems, you should also escape all backticks ` by converting them to double backticks ``.

For all other parameters, use command parameters to avoid SQL injection.

using var cmd = new MySqlCommand();
cmd.CommandText = "SELECT * FROM `"   tableStr.Replace("`", "``")   "` where DATE(Date) >= @howFarBack";
cmd.Parameters.AddWithValue("@howFarBack", howFarBack);