Problem inserting string or NULL into SQL Server database

Question

I'm having trouble coming up with the proper syntax for allowing either a string or a NULL to be passed to the database. Here's my code:

string insertString = String.Format(
    @"INSERT INTO upload_history (field1, field2, field3) 
    VALUES ('{0}', '{1}', '{2}')",
    varField1, varField2, varField3);

I used single quotes around the variable placeholders so that the database would properly accept a string value. However, if NULL is passed, it ends up going into the database as the string "NULL".

Is there a way I can leave the single quotes out of the InsertCommand string and conditionally add single quotes to my variables?

Answer 1

Don't concatenate the string (string.Format) - use parameters (@p1 etc) - then you can pass DBNull.Value to mean null to SQL Server

SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"INSERT INTO upload_history (field1, field2, field3) 
   VALUES (@p1, @p2, @p3)";
cmd.Parameters.AddWithValue("@p1", someVar ?? DBNull.Value);
//...

This also protects you from SQL injection

Answer 2

Concentating the string with String.Format might be a big security risk (SQL Injection), and also problematic if you want to insert the ' character.

Solution:

cmd.CommandText = "INSERT INTO upload_history (field1, field2, field3) " +
    "VALUES (@p1, @p2, @p3)";
cmd.Parameters.AddWithValue("@p1", varField1);
cmd.Parameters.AddWithValue("@p2", varField2);
cmd.Parameters.AddWithValue("@p3", varField3);
cmd.ExecuteNonQuery();

Answer 3

I agree with Marc answer. Moreover, I found very useful ideas from this article: http://stackoverflow.com/questions/300427/using-an-arbitrary-number-of-parameters-in-t-sql

Answer 4

In the spirit of answering the question as it was asked, and being fully aware that refactoring the code to paramaterizing the queries is the correct solution, you could write a function that returns either a single-quoted string or a non-quoted NULL string value, then remove the single-quotes from the query string.

string insertString = String.Format(    @"INSERT INTO upload_history (field1, field2, field3)     VALUES ({0}, {1}, {2})",    ToStringorNull(varField1), ToStringorNull(varField2), ToStringorNull(varField3));

If you are using VS 2008 you could even implement it as an extension method.

string insertString = String.Format(    @"INSERT INTO upload_history (field1, field2, field3)     VALUES ({0}, {1}, {2})",    varField1.ToStringorNull, varField2.ToStringorNull, varField3.ToStringorNull);

I'll leave creating the ToStringorNull function to you - it isn't hard :-)