user1823986 user1823986 - 1 year ago 99
SQL Question

Escaping single quote ' in sql parameterized query

I am trying to insert data using sql query in 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)


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

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

i = cmd.ExecuteNonQuery()

If i > 0 Then
Return True
Return False
End If

Catch ex As Exception
Return False
End Try
End Function

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download