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?

andreapier · Accepted Answer · 2013-04-16 07:53:31Z

22

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", (object)someVar ?? DBNull.Value);
//...

This also protects you from SQL injection

edited Apr 16, 2013 at 7:53

andreapier

2,9582 gold badges41 silver badges51 bronze badges

answered Dec 17, 2008 at 13:30

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

buzzzzjay Over a year ago

I get the following error when using "someVar ?? DBNull.Value" like above: "Operator '??' cannot be applied to operands for type string and System.DBNull" Any thoughts on how this would work with a string?

Marc Gravell Over a year ago

@buzzzzjay add a (object) in front of either of them.

Stefan Schultze · Accepted Answer · 2008-12-17 13:32:26Z

5

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();

answered Dec 17, 2008 at 13:32

Stefan Schultze

9,4166 gold badges39 silver badges42 bronze badges

1 Comment

beardog Over a year ago

Also a great answer but Marc beat you to it. Thanks for the suggestion.

rjrapson · Accepted Answer · 2008-12-17 13:51:42Z

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 :-)

Collectives™ on Stack Overflow

Problem inserting string or NULL into SQL Server database

3 Answers 3

2 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Related