CODEWITHSUNDEEP
vb.nett-sql
htm11h
htm11h

Reputation: 1779

apostrophe causing SQL UPDATE to fail

I am having an issue with the SQLCommand query update handling apostrophes. I have a gridview that accepts edited text which might have apostrophes and other such accent characters.

The UPDATE keeps throwing errors on the apostrophes in the text entered causing the SQL UPDATE to fail.

Here is the code:

Dim lbl1 As Label = GridView3.Rows(e.RowIndex).Cells(0).FindControl("Label1")
IDVal = lbl1.Text

' New translation
Dim TB1 As TextBox = GridView3.Rows(e.RowIndex).Cells(0).FindControl("TextBox1")
updateString = TB1.Text
updateString = HttpUtility.HtmlAttributeEncode(updateString)

' Brief Description
Dim TB2 As TextBox = GridView3.Rows(e.RowIndex).Cells(0).FindControl("TextBox2")
newBrief = TB2.Text

If newBrief = "" Then
    newBrief = DBNull.Value.ToString
Else
   newBrief = TB2.Text
End If

'    update the corresponding string value for Record
rootTableUpdate = "UPDATE " + userTable + " SET lang_String = '" + updateString + "', date_Changed ='" + myDate + "', prev_LangString = '" + Session("oldString") + "', brief_Descrip = '" + newBrief + "', needsTranslation = 'False', submittedBy= '" + userName + "' WHERE [Id] = " + IDVal + ";"

Dim command1 As New SqlCommand(rootTableUpdate, connection)
connection.Open()
command1.ExecuteNonQuery()
connection.Close()

The word that error'd below is actually: d'alimentation.

Incorrect syntax near 'alimentation'.
Unclosed quotation mark after the character string ' WHERE [Id] = 258;'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:

System.Data.SqlClient.SqlException: Incorrect syntax near 'alimentation'.
Unclosed quotation mark after the character string ' WHERE [Id] = 258;'.

Source Error: 

Line 164:            Dim command1 As New SqlCommand(rootTableUpdate, connection)
Line 165:            connection.Open()
Line 166:            command1.ExecuteNonQuery()

SQEE, VB.net

Upvotes: 0

Views: 3746

Answers (3)

Oded
Oded

Reputation: 499072

You should be using parametrized queries instead of concatenating a string.

This will both avoid the problem you are having and help protect against SQL Injection.

I can see you are passing in the table name, so you do have to use dynamic SQL - you need to escape any single ' by doubling it, otherwise it will act as a string terminator.

You still need to mitigate against SQL Injection - just doubling apostrophes will not help in this respect.

Alternatively, consider a design that doesn't require a dynamic table name or one that constructs the dynamic SQL in a stored procedure and using parameters, so you can at least sanitize passed in parameters to a certain extent.

Upvotes: 1

Curtis
Curtis

Reputation: 103368

Your code is prone to SQL Injection.

Use Parameterized queries.

Dim cmd As New SqlCommand("UPDATE Table SET ColA=@ColA, ColB=@ColB", Conn)
cmd.Parameters.Add("@ColA", SqlDbType.Int).Value = ColA
cmd.Parameters.Add("@ColB", SqlDbType.Int).Value = ColB
cmd.ExecuteNonQuery()

This will also prevent the issues you are having with apostrophe's.

Upvotes: 3

gbn
gbn

Reputation: 432311

If you parametrised the call (like @Curt's example) rather then concatenating strings:

  • you wouldn't have this problem
  • you'd be safer against SQL injection attacks

Upvotes: 2

Related Questions