Incorrect syntax near '<'. The label &#

Question

I am getting a string from a database that is in xml format and trying to update the xml with the following query:

ExecuteNonQuery("Update Logs SET Message = " + encryptedMessage + " WHERE ID = " + message.Id);

but it gives me the error message:

Incorrect syntax near '<'. The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure. An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

I have a feeling it might have something to do with the quotes, but I am not sure. I have tried different options like single quotes, mixture,etc.

For example, if I do:

ExecuteNonQuery("Update Logs SET Message = " + encryptedMessage.Replace('"','\'')+ " WHERE ID = " + message.Id);

Would this permanently update the double quotes in the message to single quotes. I don't want to do this.

Answer 1

Use parametrized query and command object instead, your encryptedMessage might contain characters which break the syntax of your UPDATE statement.

Answer 2

Yes, it looks like you are missing the quotes around the message:

ExecuteNonQuery("Update Logs SET Message = '" + encryptedMessage + "' WHERE ID = " + message.Id);

The XML itself probably has single quotes in it as well, so you may need to escape those (e.g. change one single quote to two single quotes inside the message)

Answer 3

As @Tomek mentioned you should use parameterized queries. It is more secure and removes the need for doing the conversions suggested in @Dan Sueava's answer.

    SqlCommand command = 
     new SqlCommand("Update Logs SET Message = @EncryptedText WHERE ID = @MessageId");
    command.Parameters.AddWithValue("@EncryptedText", encryptedMessage);
    command.Parameters.AddWithValue("@MessageId", message.Id);

    command.ExecuteNonQuery();