“以'...开头的标识符'太长了“;当插入值包含字符 '(单引号)时引发 SQL 异常

本文关键字：单引号 字符 异常 SQL 包含 开头 标识符 插入 | 更新日期: 2023-09-27 18:35:22

我有一个抛出 sql 异常的文件上传函数 

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ','. 
The identifier that starts with   
'PGFzcDpHcmlkVmlldyBJRD0iR3JpZFZpZXcyIiANCiAgICAgICAgcnVuYXQ9InNlcnZlciI
gV2lkdGg9IjgyMHB4IiBBdXRvR2VuZXJhdGVTZWxlY3RCdXR0b249IlRy' is too long. 
Maximum length is 128"  whenever the file name contains a single quote character (')

文件上传功能如下：

protected void btn_file_upload_Click(object sender, EventArgs e)
{
    try
    {
        if (FileUpload1.HasFile)
        {
            byte[] byte_file = FileUpload1.FileBytes;
            string str_file = Convert.ToBase64String(byte_file);
            SqlCommand cmd = new SqlCommand("insert into spt_files values('" + FileUpload1.FileName + "','" + str_file + "','" + dd_students.Text + "')", conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            lbl_file_upload.Text = "File uploaded!";
        }
        else
            lbl_file_upload.Text = "Choose a file";
    }
    catch (Exception ex)
    {
        Response.Write(ex.ToString());
    }
}

如何解决这个问题？

“以'...开头的标识符'太长了“;当插入值包含字符 '(单引号)时引发 SQL 异常

您可以使用 

 protected void btn_file_upload_Click(object sender, EventArgs e)
{
    try
    {
        if (FileUpload1.HasFile)
        {
            byte[] byte_file = FileUpload1.FileBytes;
            string str_file = Convert.ToBase64String(byte_file);
            SqlCommand cmd = new SqlCommand("insert into spt_files values('" + FileUpload1.FileName.Replace("'", "''") + "','" + str_file + "','" + dd_students.Text + "')", conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            lbl_file_upload.Text = "File uploaded!";
        }
        else
            lbl_file_upload.Text = "Choose a file";
    }
    catch (Exception ex)
    {
        Response.Write(ex.ToString());
    }
}

并增加表中列的大小。

相关文章：