user1823986 user1823986 - 23 days ago 20
SQL Question

Escaping single quote ' in sql parameterized query

I am trying to insert data using sql query in vb.net as follows. name = corp int'l poc = 1

When i tried to insert, i get an error ("Unclosed Quotation Mark after the character String '"). This happens when i tried to insert name with only 1 single quote.

Therefore i added a replace function to replace 1 single quote with 2 single quote to escape the symbol. There was no error but when i look into my database, 2 single quotes are added instead of 1.

Can anyone advise me how i can escape the single quote with my parameterized query? Thanks!

Public Function InsertData(ds As DataSet) As Boolean
Dim cmd As New SqlCommand
Dim cmd1 As New SqlCommand
Dim status As Boolean
Dim name As String
Dim poc As String

Dim id_p As New SqlParameter("id", SqlDbType.VarChar)
Dim name_p As New SqlParameter("name", SqlDbType.VarChar)

cmd.Parameters.Add(id_p)
cmd.Parameters.Add(name_p)

For i = 0 To ds.Tables(0).Rows.Count - 1

If checkExists(ds.Tables(0).Rows(i)(1).ToString(), ds.Tables(0).Rows(i)(2).ToString(), ds.Tables(0).Rows(i)(3).ToString()) = True Then


name = ds.Tables(0).Rows(i)(1).ToString()
poc = ds.Tables(0).Rows(i)(2).ToString()

If name.Contains("'") Then
name = name.Replace("'", "''")
End If
If poc.Contains("'") Then
poc = poc.Replace("'", "'")
End If

name_p.SqlValue = name
id_p.SqlValue = poc


cmd.CommandText = "INSERT INTO Code (Name,ID)" _
& " VALUES (@name,@id)"

status = ExecuteNonQuerybySQLCommand(cmd)
End If
Next

Return status

End Function


Dim strcon As String = "Data Source=x.x.x.x,1433;Network Library=DBMSSOCN;Initial Catalog=code_DB;User ID=xxx;Password=xxx;"

Public Function ExecuteNonQuerybySQLCommand(ByVal cmd As SqlCommand) As Boolean
Dim sqlcon As New SqlConnection
Dim i As Integer = 0

sqlcon.ConnectionString = strcon
cmd.Connection = sqlcon

Try
sqlcon.Open()
i = cmd.ExecuteNonQuery()
sqlcon.Close()

If i > 0 Then
Return True
Else
Return False
End If

Catch ex As Exception
Console.Write(ex)
Return False
End Try
End Function

Answer

Values passed as parameters (i.e. SqlParameter object) do not need to be escaped. This is because the client API uses an RPC call to execute the query, with the query itself and parameters passed separately. With an RPC call, the actual parameter values are sent to SQL Server in native (binary) format over the TDS protocol rather than embedded within the statement. This mitigates SQL injection concerns and provides other benefits, such as strong-typing and improved performance.

Comments