How to insert a null-value to the database in C# without using parameters?

Question

Using SQL parameters, I could insert a NULL value to the database using this sort of C# code:

string query = "INSERT INTO table_name (column_name) VALUES (@value)";
using (SqlConnection connection = new SqlConnection(/* connection info */))
{
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.Add("@value", DBNull.Value);
        command.ExecuteNonQuery();
    }
}

Not using parameters is usually considered to be bad practice, because of SQL-Injection and some other reasons. But, I am curious to know if there is a way to do so without using them. Something like this:

var nullValue = DBNull.value;
string query = "INSERT INTO table_name (column_name) VALUES (" + nullValue + ")";

or even:

string query = "INSERT INTO table_name (column_name) VALUES (\\NULL\\)";

or whatever. Thanks.

What problem you are facing?

Raghuveer
– Raghuveer

2016-05-09 05:27:59 +00:00
Commented May 9, 2016 at 5:27 — Raghuveer
– Raghuveer, Commented May 9, 2016 at 5:27
I do not face a problem. I am just curious. :)

Michael Haddad
– Michael Haddad

2016-05-09 05:33:23 +00:00
Commented May 9, 2016 at 5:33 — Michael Haddad
– Michael Haddad, Commented May 9, 2016 at 5:33

Thomas Andreè Wang · Accepted Answer · 2017-07-31 13:41:11Z

Uhmm yes. You could do that, if you are certain that the column should always be NULL, then you could simply write the SQL statement in a full string, like this.

string query = "INSERT INTO table_name (column_name) VALUES (NULL)";

If you take input from a user, then NEVER do this, this is exactly how you get injected.

var nullValue = DBNull.value;
string query = "INSERT INTO table_name (column_name) VALUES (" + nullValue + ")";

The injection would happen if the nullValue contained something like the string NULL); DROP TABLE table_name. Where the NULL); just completes your own SQL allowing for more SQL code to wreak havoc on your data.

The first rule of web development. NEVER EVER trust user data. Use parameters.

using(SqlConnection connection = new SqlConnection("connection_String"))
{
    string query = "INSERT INTO table_name (column_name) VALUES (@val1)";

    using(SqlCommand inputQuery = new SqlCommand(query))
    {
        inputQuery.Connection = openCon;
        inputQuery.Parameters.AddWithValue("@val1", DBNull.Value);
        try
        {
            connection.Open();
            int recordsAffected = inputQuery.ExecuteNonQuery();
        }
        catch(SqlException)
        {
            // error here
        }
        finally
        {
            connection.Close();
        }
    }
}

Banwari Yadav · Accepted Answer · 2016-05-18 14:10:26Z

3

Please try this..

command.Parameters.AddWithValue(
"@ProductName",
// this requires a cast as ?: must return the same type
String.IsNullOrWhiteSpace(productName)
    ? (object)DBNull.Value
    : (object)productName
);

edited May 18, 2016 at 14:10

answered May 9, 2016 at 5:38

Banwari Yadav

5061 gold badge3 silver badges18 bronze badges

1 Comment

Michael Haddad Over a year ago

Your code uses parameters... I want to know how to do this without using them. Thank. :)

Collectives™ on Stack Overflow

How to insert a null-value to the database in C# without using parameters?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related