Insert comma into MS Access database using Visual Studio

Question

I'm creating an application using Visual Studio 2019, with a connection to an MS Accsess database to add, get, modify and delete values inside the database.

I'm willing to insert a text that could contain a comma, for example : Gousse d'ail. But I know there will be a problem because the string has to be surrounded by commas. So I added a backslash before every extra comma inside the text I'm willing to insert.

The thing is a get an error message saying there is a syntax error, I believe it's because of the backslash.

Here is the message I get :

System.Data.OleDb.OleDbException (0x80040E14) : Syntax error (missing operator) in query expression " 'Gousse d\'ail', unite = 'kg', allergene = False, fournisseurID = 1 WHERE ingrédientID = 40; "

Everything works really well until there is comma.

Here is the method I use to insert into the database:

public void UpdateIngédient(int ingredientID, InfoIngredient ing)
{
    string query = "UPDATE Ingrédients ";
    query += "SET nom = '" + ing.Nom + "', unite = '" + ing.Unité + "', allergene = " + ing.Allergene + ", fournisseurID = " + ing.Fournisseur;
    query += " WHERE ingredientID = " + ingredientID + ";";

    OleDbCommand com = new OleDbCommand(query, oleConnection);

    com.ExecuteNonQuery();
}

Answer 1

Your query is begging for SQL injection, as well as bugs exactly like the one you've encountered.

If you're doing any work with a SQL table (or OLE in your case) I strongly recommend spending some time to look into SQL injection to understand the risks.

It's very easy to defend against SQL injection and a rewrite of your code is shown below to protect against it.

void UpdateIngédient(int ingredientID, InfoIngredient ing)
{
  string query = "UPDATE Ingrédients SET nom = @nom, unite = @unite, allergene = @allergene, fournisseurID = @fournisseur WHERE ingredientID = @ingredientID;";
  OleDbCommand cmd = new OleDbCommand(query, oleConnection);
  cmd.Parameters.Add(new OleDbParameter("@nom", ing.Nom));
  cmd.Parameters.Add(new OleDbParameter("@unite", ing.Unité));
  cmd.Parameters.Add(new OleDbParameter("@allergene", ing.Allergene));
  cmd.Parameters.Add(new OleDbParameter("@fournisseur", ing.Fournisseur));
  cmd.Parameters.Add(new OleDbParameter("@ingredientID", ingredientID));
  OleDbCommand com = new OleDbCommand(query, oleConnection);
  com.ExecuteNonQuery();
}

This should safeguard against "unexpected" characters in your strings such as the ' character