How to edit 'bit' data type?

Question

I am coding in Visual Basic. I am using a checkbox control. Now depending on its checked property I need to set/unset a bit column in a SQL Server database. Here's the code:

Try
    conSQL.Open()

    Dim cmd As New SqlCommand("update Student set send_mail = " + _
        sendemailCheckBox.Checked.ToString + " where student_id = '" _
        + sidnolabel.Text + "'", conSQL)

    cmd.ExecuteNonQuery()
Finally
    conSQL.Close()
End Try

The send_mail attribute is of bit datatype. This code is not working. How do I go about it?

Answer 1

my son is enrolled in your school. His name is:

Robert'); DROP TABLE STUDENT; --

We call him little Bobby Tables.

---

_{Okay, I have been told that this was going to be deleted as not an answer and that I needed to fall on the sword or clean up my own mess. Although I'll keep}
^{my rep if it gets deleted I think it is important to point out the vulnerability in the OP's code (may his account rest in peace). So lemme elaborate...}

Let's actually fix the above name from the XKCD comic to something that would actually work against the concatenated query (assuming OP's boolean issue has been fixed):

123; DROP TABLE STUDENT; --

If we add this in the sidnolabel text box (or whatever it is; if it is in the viewstate you could change it with a simple tool), the resulting concatenated query is

update Student   
set send_mail = 1  
where student_id = 123; DROP TABLE STUDENT; --'

which will run a bogus query then drop the Student table.

This is the definition of "not good."

What OP should be doing is using a parameterized query. This prevents injection attacks like this. Updating OP's code...

Try
    conSQL.Open()
    Dim cmd As New SqlCommand(
        "update Student " + _ 
        "set send_mail = @sendMail " + _
        "where student_id = @studentId", conSQL)
    cmd.Parameters.Add( _
        "@sendMail", SqlDbType.Bit).Value = _
            sendemailCheckBox.Checked
    cmd.Parameters.Add( _
        "@studentId", SqlDbType.NChar, 25).Value = _
             sidnolabel.Text ' better to keep track of this in the Session!
    cmd.ExecuteNonQuery()
Finally
    conSQL.Close()
End Try

Which should fix OP's issue and prevent sql injection attacks. Ain't that something?

*Please note, I don't VB, and this is not tested. Any VB.NET devs who want to edit and fix errors I'll be happy to accept your help.

Answer 2

Try:

Convert.ToInt16(sendemailCheckBox.Checked)

In general, I use SqlParameters for this, and you can specify a SqlDbType.TinyInt.

Here is an example of using sqlparameter which is better because it avoids SQL injection.

EDIT: I just noticed you were using bit, not tinyint. not sure if this will work.

Answer 3

To answer your question, just assign a Boolean value (i.e. sendemailCheckBox.Checked) to the bit column in the database.

To help you out with your SQL injection issues - do not directly write user input into a SQL string. You must use parameters to ensure that users cannot mess with your database. Your code should be written like this:

Using conSQL As New SqlConnection("SomeConnectionString")
   conSQL.Open()
   Using cm as SqlCommand = conSQL.CreateCommand()
      cm.CommandType = CommandType.Text
      cm.CommandText = "UDPATE Student SET send_mail = @send_mail WHERE student_id = @student_id"
      cm.Parameters.AddWithValue("@send_mail", sendemailCheckBox.Checked)
      cm.Parameters.AddWithValue("@student_id", sidnolabel.Text)

      cm.ExecuteNonQuery()
   End Using
End Using

Answer 4

First, in the name of everything sacred, at least PARAMETERIZE your SQL code. Otherwise, you're asking for a SQL injection attack.

Second, the "bit" datatype uses 1 for True and 0 for False. That's what SQL wants to see when you're assigning values.

Answer 5

Well bit data type is equivalent to boolean in C# or Visual Basic, so you can simply assign true or false values to these types and can then update the record in your database.