How to sovle Query Issue throwing MySql.Data.MySqlClient.MySqlException

Question

I am doing a project with C# and I have this error:

MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' last_name= , email= , phone= , address= WHERE id= 6' at line 1

I know this is a query error, but I tried many things and I don't see the issue.

My query is this:

cm = new MySqlCommand("UPDATE customers SET first_name= " + txtNombre.Text + "," + " last_name= " + txtApellidos.Text + "," + " email= " + txtEmail.Text + "," + " phone= " + txtTelefono.Text + "," + " address= " + txtDireccion.Text + " WHERE id= " + dgvClient.SelectedRows[0].Cells[0].Value.ToString() , con);

Answer 1

There should be single quotes around the text that you want to inject into the query, so it will look like this:

var query = "UPDATE customers SET first_name= '" + txtNombre.Text + "'";

This is the easiest solution but is advised against, mostly because of a possiblity for 'sql injection'. The easiest way to show this is by using the name O'Brian, because of the quote the database will think that the name is only O and then see it followed by Brian that it doesn't know what to do with and gives an error. Some people can use this to add other things to your query to cause harm to your database (like dropping tables or the whole database)

It is advised to use parameters, this solves this whole sql injection issue. Your code will look as follows:

cm = new MySqlCommand("UPDATE customers SET first_name=@first_name, last_name=@apellidos WHERE id=@id", con) ;
cm.Parameters.AddWithValue("@first_name", txtNombre.Text);
cm.Parameters.AddWithValue("@apellidos", txtApellidos.Text);
cm.Parameters.AddWithValue("@id", dgvClient.SelectedRows[0].Cells[0].Value.ToString());

It is best to always use parameters for your query, you can also look into using a framework like Entity Framework that does this automatically for you.