"Identifier that starts with '.......' is too long" ;当插入值包含字符 '(单引号)时引发 SQL 异常？

Question

我有一个抛出 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());
    }
}

如何解决这个问题？

Answer 1

您可以使用

 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());
    }
}

并增加表中列的大小。