How to escape forward slash?

Question

I have the following sql command through code and because the parameter contains a forward slash when I evaluate the sql row after the update the column is just empty.

sqlCommand.CommandText = String.Format("update {0} set {1}='{2}'where id = @Id",
                                        tableName, ColumnName, forwardSlashText);
sqlCommand.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = rowId;
numRowsAffected = sqlCommand.ExecuteNonQuery();

adding a log.debug to this command i get the following output...

update my_table_name set mime_type='application/pdf' where id = @Id

So i would assume that the command is correct, but then looking at the row the mime_type column is empty.

Answer 1

The slash isn't the problem. While on the subject, though, why not submit forwardSlashText as a parameter, just as you did for @Id?

Answer 2

If you copy and paste that SQL into SQL Server Management Studio and execute it, how many rows are affected? You need to rule out a WHERE clause issue, because that sounds like the problem. You should also log.debug the value of ID to check it's doing the correct one.

Chances are it's -1 or something because the problem is elsewhere.

Answer 3

First off, by using String.Format() to synthesize your query, watch out that you are not setting yourself up for a SQL injection attack. (Make sure that tableName and ColumnName come from a trusted source.

Second, this is how I would do it. Notice the brackets around the table and column names (which will escape any funky characters you might have in the table or column names). But more importantly, notice that forwardSlashText is now a parameter:

sqlCommand.CommandText = String.Format("update [{0}] set [{1}] = @val where id = @Id",
                                    tableName, ColumnName);
sqlCommand.Parameters.AddWithValue("@Id", rowId);
sqlCommand.Parameters.AddWithValue("@val", forwardSlashText);
numRowsAffected = sqlCommand.ExecuteNonQuery();