在 sql 参数化查询中转义单引号 ' [英] Escaping single quote ' in sql parameterized query

问题描述

我正在尝试在 vb.net 中使用 sql 查询插入数据，如下所示.名称 = corp int'l poc = 1

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

当我尝试插入时，出现错误(字符串 ' 后的未闭合引号").当我尝试仅用 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.

因此我添加了一个替换函数，用 2 个单引号替换 1 个单引号以转义符号.没有错误，但是当我查看我的数据库时，添加了 2 个单引号而不是 1 个.

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

推荐答案

作为参数(即 SqlParameter 对象)传递的值不需要转义.这是因为客户端 API 使用 RPC 调用来执行查询，查询本身和参数分别传递.通过 RPC 调用，实际参数值通过 TDS 协议以本机(二进制)格式发送到 SQL Server，而不是嵌入到语句中.这减少了 SQL 注入问题并提供了其他好处，例如强类型和改进的性能.

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.

这篇关于在 sql 参数化查询中转义单引号 '的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！