SQL语法错误求助：System.Data.SqlClient.SqlException提示'11'附近语法错误

异常信息 #

System.Data.SqlClient.SqlException: 'Incorrect syntax near '11'.'

生成的Insert语句 #

Insert into Creditors 
Values ('asdf', 'asdf', 'asdf', 'asdf', 'sadf', 'asdf', 'Australia', '231234', 13/10/2023 11:26:53 AM, 'Admin', 1)

对应VB.NET代码 #

Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click
    'Save addition/Changes
    'Insert/Edit A Record
    'Dim i As Integer

    Dim I1 As String = TextBox1.Text
    Dim I2 As String = TextBox2.Text
    Dim I3 As String = TextBox3.Text
    Dim I4 As String = TextBox4.Text
    Dim I5 As String = TextBox5.Text
    Dim I6 As String = TextBox6.Text
    Dim I7 As String = TextBox7.Text
    Dim I8 As String = TextBox9.Text
    Dim I9 As Date = Now()
    Dim I10 As String = strUserName
    Dim I11 As String = CheckBox1.Enabled

    If CheckBox1.Checked = True Then
        I11 = 1
    Else
        I11 = 0
    End If

    Dim pid As Integer = TextBox8.Text

    'Stop
    If con.State = ConnectionState.Open Then con.Close()

    If IsAdding = True Then

        'Stop
        con.Open()
        'Insert line into Creditors
        sqlAction = "Insert into Creditors Values ('" & I1 &
                    "','" & I2 & "', '" & I3 &
                    "', '" & I4 & "', '" & I5 & "', '" & I6 &
                    "','" & I7 & "','" & I8 & "', " & I9 & ", '" &
                    I10 & "', " & I11 & ")"
        sqlCmd = New SqlCommand(sqlAction, con)

        sqlCmd.ExecuteNonQuery()

        'MessageBox.Show("Sucessful")

        Call DefaultButtons()
        ' Stop
        LoadData(0)

    Else
        ' Stop

        'Edit Existing Record
        sqlAction = "Update Creditors Set CreditorName = '" & I1 &
                    "', Addr1 = '" & I2 & "', Addr2 = '" & I3 &
                    "', City = '" & I4 & "', State = '" & I5 &
                    "', PostCode = '" & I6 & "', Country ='" & I7 &
                    "', ContactNbr = '" & I8 &
                    "',DateAdded = " & I9 & ", AddedBy = '" & I10 & "', IsActive = " & I11 &
                    " where CreditorID = " & pid & ""
        con.Open()
        sqlCmd = New SqlCommand(sqlAction, con)

        sqlCmd.ExecuteNonQuery()
        LoadData(0)

        'MessageBox.Show("Sucessful")
    End If
    con.Close()
    Call TextBoxDisabled()
    IsAdding = False
    IsEditing = False
End Sub

表结构 #

CreditorId  int False   
CreditorName    nvarchar(50)    False   
Addr1   nvarchar(50)    True    
Addr2   nvarchar(50)    True    
City    nvarchar(50)    True    
State   nvarchar(50)    True    
PostCode    nvarchar(50)    True    
Country nvarchar(50)    True    
ContactNbr  nvarchar(50)    True    
DateAdded   date    False   
AddedBy nvarchar(50)    False   
IsActive    bit False   

错误原因分析 #

1. 日期值未加单引号：SQL Server要求日期类型的数值必须用单引号包裹，生成的语句中13/10/2023 11:26:53 AM没有引号，SQL会将13/10/2023解析为除法运算，后续的11就成为了语法错误的触发点。
2. 字符串拼接SQL的固有问题：直接将变量和用户输入拼接进SQL语句，不仅容易引发语法错误，还存在严重的SQL注入安全风险。

解决方案 #

1. 临时修复日期格式问题 #

在拼接日期变量时添加单引号，同时使用SQL Server兼容的标准日期格式：

' Insert语句修改日期部分
sqlAction = "Insert into Creditors Values ('" & I1 &
            "','" & I2 & "', '" & I3 &
            "', '" & I4 & "', '" & I5 & "', '" & I6 &
            "','" & I7 & "','" & I8 & "', '" & Format(I9, "yyyy-MM-dd HH:mm:ss") & "', '" &
            I10 & "', " & I11 & ")"

' Update语句同理修改日期部分
sqlAction = "Update Creditors Set CreditorName = '" & I1 &
            "', Addr1 = '" & I2 & "', Addr2 = '" & I3 &
            "', City = '" & I4 & "', State = '" & I5 &
            "', PostCode = '" & I6 & "', Country ='" & I7 &
            "', ContactNbr = '" & I8 &
            "',DateAdded = '" & Format(I9, "yyyy-MM-dd HH:mm:ss") & "', AddedBy = '" & I10 & "', IsActive = " & I11 &
            " where CreditorID = " & pid & ""

2. 推荐方案：使用参数化查询（彻底解决问题） #

参数化查询会自动处理数据类型格式，同时完全杜绝SQL注入风险：

' Insert场景的参数化实现
If IsAdding = True Then
    con.Open()
    sqlAction = "Insert into Creditors (CreditorName, Addr1, Addr2, City, State, PostCode, Country, ContactNbr, DateAdded, AddedBy, IsActive) " &
                "VALUES (@CreditorName, @Addr1, @Addr2, @City, @State, @PostCode, @Country, @ContactNbr, @DateAdded, @AddedBy, @IsActive)"
    sqlCmd = New SqlCommand(sqlAction, con)
    
    ' 添加参数
    sqlCmd.Parameters.AddWithValue("@CreditorName", I1)
    sqlCmd.Parameters.AddWithValue("@Addr1", I2)
    sqlCmd.Parameters.AddWithValue("@Addr2", I3)
    sqlCmd.Parameters.AddWithValue("@City", I4)
    sqlCmd.Parameters.AddWithValue("@State", I5)
    sqlCmd.Parameters.AddWithValue("@PostCode", I6)
    sqlCmd.Parameters.AddWithValue("@Country", I7)
    sqlCmd.Parameters.AddWithValue("@ContactNbr", I8)
    sqlCmd.Parameters.AddWithValue("@DateAdded", I9)
    sqlCmd.Parameters.AddWithValue("@AddedBy", I10)
    sqlCmd.Parameters.AddWithValue("@IsActive", I11)
    
    sqlCmd.ExecuteNonQuery()
    ' 后续逻辑...
Else
    ' Update场景的参数化实现
    con.Open()
    sqlAction = "Update Creditors Set CreditorName = @CreditorName, Addr1 = @Addr1, Addr2 = @Addr2, " &
                "City = @City, State = @State, PostCode = @PostCode, Country = @Country, ContactNbr = @ContactNbr, " &
                "DateAdded = @DateAdded, AddedBy = @AddedBy, IsActive = @IsActive " &
                "where CreditorID = @CreditorID"
    sqlCmd = New SqlCommand(sqlAction, con)
    
    ' 添加参数
    sqlCmd.Parameters.AddWithValue("@CreditorName", I1)
    sqlCmd.Parameters.AddWithValue("@Addr1", I2)
    sqlCmd.Parameters.AddWithValue("@Addr2", I3)
    sqlCmd.Parameters.AddWithValue("@City", I4)
    sqlCmd.Parameters.AddWithValue("@State", I5)
    sqlCmd.Parameters.AddWithValue("@PostCode", I6)
    sqlCmd.Parameters.AddWithValue("@Country", I7)
    sqlCmd.Parameters.AddWithValue("@ContactNbr", I8)
    sqlCmd.Parameters.AddWithValue("@DateAdded", I9)
    sqlCmd.Parameters.AddWithValue("@AddedBy", I10)
    sqlCmd.Parameters.AddWithValue("@IsActive", I11)
    sqlCmd.Parameters.AddWithValue("@CreditorID", pid)
    
    sqlCmd.ExecuteNonQuery()
    ' 后续逻辑...
End If

额外优化点：代码中I11的初始化存在冗余，建议直接定义为布尔类型，参数化查询会自动处理到bit类型的转换：

Dim I11 As Boolean = CheckBox1.Checked

内容的提问来源于stack exchange，提问作者MGC