How to edit 'bit' data type?
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?
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.
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.
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
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.
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.
精彩评论