I have an input form that contains a textarea in which people can input the description of an item. They then click Insert or Update and the information is inserted or updated to a SQL Server database. Everything works fine unless someone includes a quote in the description. For example:
The item is Bob's computer.
The apostrophe in Bob creates a problem. I receive the following:
Incorrect syntax near 's'. Unclosed quotation mark before the character string '.
I understand the problem. How do I correct it?
Thanks!
PS I am using C#.Use parameters.
See here|||OF COURSE!! I knew I had done this at some time...thanks for JOGGING my brain!! :)|||I have the same problem, but I don't see how that tutorial would work for an imput text box. If the user types in something like "Mike's car" (without the quotes), it ruins the sql string. How can I code around, or get the server to accept single quotes or apostrophes?
Thanks,
Sean|||The previous link will in fact resolve the problem. Honest.
A poorer alternative is replacing all ' with two ' characters ('' - this is NOT a regular quote, but two single quotes). Doing this still allows SQL Injection attacks to occur.|||Sorry, but I don't see how to apply it to an update statement. Here's a piece of my code:
Sub btnSubmit_Click(sender As Object, e As EventArgs)
Dim strPurpose as string =txtPurpose.text
Dim MySQL as string = "Insert into tbl_ExpsReports (expsPurpose) values ('" & strPurpose & "')"
Dim myConn As New OLEDBConnection(configurationSettings.AppSettings("MSDBconn"))
Dim Cmd as New OleDbCommand(MySQL, MyConn)MyConn.Open()
cmd.ExecuteNonQuery
MyConn.close()
End Sub
How do I allow the user to key in a single quote or apostrope into the txtPurpose text box? The tutorial seems to be geared towards a return rather than input statement.
Thanks,
Sean|||What you are doing is not that unusual:
Sub btnSubmit_Click(sender As Object, e As EventArgs)
Dim strPurpose as string =txtPurpose.text
Dim MySQL as string = "Insert into tbl_ExpsReports (expsPurpose) values (?)"
Dim myConn As New OLEDBConnection(configurationSettings.AppSettings("MSDBconn"))
Dim Cmd as New OleDbCommand(MySQL, MyConn)
Cmd.Parameters.Add("expsPurpose",strPurpose)
MyConn.Open()
cmd.ExecuteNonQuery
MyConn.close()
End Sub
</code>|||So, what you are saying is, if I use a perameterized insert statement, then the user can key in an apostrophe or single quote? Cool! ;^]|||Yes. And prevents SQL Injection.|||SQL injection... hmmmm... sounds bad.
