Passing NULL values in a query string

Question

I have a function called "InsertEmpolyee" that receives parameters to fill a query string. The thing is, I want to make some of these variables optional, in other words, I would like to be able to call the function without passing values to those parameters and still make the correct query string by inserting NULL into the database.

This is the function InsertEmployee

public int InsertEmployee(string FirstName, char Minit, string LastName, int SSN
 , int? Salary)
{
    string query = "INSERT INTO Employee (Fname, Minit, Lname, SSN, Salary) " +    "Values ('" + FirstName + "','" + Minit + "','" + LastName + "'," + Salary + ");";
    return model.ExecuteNonQuery(query);
}

And here is how I call it.

int res = Controlobj.InsertEmployee(txtbox_FirstName.Text, txtbox_Minit.Text[0],
                   txtbox_LastName.Text, Int32.Parse(txtbox_SSN.Text), null);

I have tried to do the following

if (!Salary.HasValue)
            Salary = DBNull.Value;

But it gives me the following error "Can't implicitly convert system.DBNull to int?"

How can I fix this? And is there is a better way to do this?

Do not use string concatenation for your queries, use parameterized queries instead. This will ensure your code is not vulnerable to sql injection attacks. It will also solve your problem because you can then pass in System.DBNull.Value as the parameter value when one of your incoming parameters is null. — Igor
– Igor, Commented Oct 24, 2017 at 21:14
@Igor No, it's a class I have written. I will read about parameterized queries. Actually I knew that string concatenation is vulnerable for SQL injections, but this is literally my first time dealing databases. — Sayed Alesawy
– Sayed Alesawy, Commented Oct 24, 2017 at 21:19
Why recreate SqlCommand? If you want to abstract it then use DbCommand or IDbCommand which are already abstracted types. You are stripping away core functionality that you need. See Bobby Tables — Igor
– Igor, Commented Oct 24, 2017 at 21:27

ikegami · Accepted Answer · 2017-10-26 17:16:29Z

7

Your code doesn't only fail on null, but on strings containing apostrophes. There could be other pitfalls as well. That's why we use parameters.

public int InsertEmployee(string Fname, char Minit, string Lname, int SSN, int? Salary)
{
    return model.ExecuteNonQuery(
        @"
            INSERT INTO Employee (
                       Fname, Minit, Lname, SSN, Salary
                   ) VALUES (
                       @Fname, @Minit, @Lname, @SSN, @Salary
                   )
        ",
        new SqlParameter("@Fname",  SqlDbType.VarChar) { Value = (object)Fname  ?? System.DBNull.Value },
        new SqlParameter("@Minit",  SqlDbType.VarChar) { Value =         Minit                         },
        new SqlParameter("@Lname",  SqlDbType.VarChar) { Value = (object)Lname  ?? System.DBNull.Value },
        new SqlParameter("@SSN",    SqlDbType.Int    ) { Value =         SSN                           },
        new SqlParameter("@Salary", SqlDbType.Int    ) { Value = (object)Salary ?? System.DBNull.Value });
}

edited Oct 26, 2017 at 17:16

answered Oct 24, 2017 at 21:18

ikegami

391k17 gold badges291 silver badges555 bronze badges

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

7 Comments

user743382 Over a year ago

This is not a constructor of SqlParameter that you should be using. It can cause very confusing behaviour because it infers the type from the value passed in, but the value passed in may be null, which doesn't have any type. (BTW, the link I included earlier was not a good one, please ignore that, now edited out.)

Sayed Alesawy Over a year ago

I have written the model class myself, and thus the function ExecuteNonQuery takes a string as a parameter. What would be the suitable parameter for your proposed method?

ikegami Over a year ago

Then you need to fix ExecuteNonQuery. There's a reason SqlCommand.ExecuteNonQuery takes parameters.

ikegami Over a year ago

@hvd, Addressed.

user743382 Over a year ago

Looks good to me. You could alternatively have kept it inline with new SqlParameter(...) { Value = ... } but a separate method is of course perfectly fine as well.

|

Collectives™ on Stack Overflow

Passing NULL values in a query string

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related