使用C#（Visual Studio）通过Navicat更新MySQL用户失败求助

核心问题分析 #

你当前代码的最大问题是直接拼接用户输入生成SQL语句，这会导致三个致命问题：

1. 存在严重SQL注入风险
2. 输入包含单引号、特殊字符时直接破坏SQL语法，导致执行失败
3. 日期、数值类型未做格式转换，MySQL无法正确识别

另外，现有代码不管SQL是否真的执行成功，都会弹出成功提示，完全无法排查实际执行情况。

具体修复步骤 #

1. 改用参数化查询（必须执行） #

彻底替换SQL拼接方式，用参数化查询避免语法错误和注入风险：

Activity = "";
Con.InitializeConnection(Server, "maternal");

// 预定义参数化SQL模板
string sql = @"UPDATE tb_maternal_masterlist 
SET lname = @lname, fname = @fname, mname = @mname,
    age = @age, birth = @birth, occupation = @occupation,
    sex = @sex, marital = @marital, zip = @zip,
    address = @address, street = @street, barangay = @barangay,
    city = @city, hlname = @hlname, hfname = @hfname,
    hmname = @hmname, hsuffix = @hsuffix, hage = @hage,
    hoccupation = @hoccupation, hcontact = @hcontact,
    religion = @religion, marriage = @marriage, hreligion = @hreligion,
    nocba = @nocba, nocbl = @nocbl, noa = @noa, nosb = @nosb,
    familyplanning = @familyplanning, method = @method, willing = @willing,
    menshistory = @menshistory, gravida = @gravida, para = @para,
    hemorage = @hemorage, toxemia = @toxemia, placenta = @placenta,
    sepsis = @sepsis, famhistory = @famhistory, nausea = @nausea,
    vomiting = @vomiting, headache = @headache, dizziness = @dizziness,
    edema = @edema, pruritus = @pruritus, constipation = @constipation,
    cramps = @cramps, bleeding = @bleeding, leucorrhea = @leucorrhea,
    indigestion = @indigestion, blurring = @blurring, lastmens = @lastmens,
    expectmens = @expectmens, aptype = @aptype, riskcode = @riskcode,
    lastdelivery = @lastdelivery, placedelivery = @placedelivery,
    tt1 = @tt1, tt2 = @tt2, tt3 = @tt3, tt4 = @tt4, tt5 = @tt5,
    special = @special
WHERE pincode = @pincode";

// 创建命令并添加参数
MySqlCommand cmd = new MySqlCommand(sql, Con.GetConnection()); // 假设Con能返回有效连接对象
cmd.Parameters.AddWithValue("@lname", textBox2.Text);
cmd.Parameters.AddWithValue("@fname", textBox3.Text);
cmd.Parameters.AddWithValue("@mname", textBox4.Text);
cmd.Parameters.AddWithValue("@age", textBox5.Text);
// 日期类型直接传DateTime对象，避免格式问题
cmd.Parameters.AddWithValue("@birth", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@occupation", textBox6.Text);
cmd.Parameters.AddWithValue("@sex", textBox7.Text);
cmd.Parameters.AddWithValue("@marital", comboBox2.Text);
cmd.Parameters.AddWithValue("@zip", textBox8.Text);
// 地址直接在参数里拼接，无需提前生成字符串
cmd.Parameters.AddWithValue("@address", $"{textBox9.Text} {textBox10.Text} {textBox11.Text}");
cmd.Parameters.AddWithValue("@street", textBox9.Text);
cmd.Parameters.AddWithValue("@barangay", textBox10.Text);
cmd.Parameters.AddWithValue("@city", textBox11.Text);
cmd.Parameters.AddWithValue("@hlname", textBox12.Text);
cmd.Parameters.AddWithValue("@hfname", textBox13.Text);
cmd.Parameters.AddWithValue("@hmname", textBox14.Text);
cmd.Parameters.AddWithValue("@hsuffix", textBox15.Text);
cmd.Parameters.AddWithValue("@hage", textBox16.Text);
cmd.Parameters.AddWithValue("@hoccupation", textBox17.Text);
cmd.Parameters.AddWithValue("@hcontact", textBox18.Text);
cmd.Parameters.AddWithValue("@religion", textBox1.Text);
cmd.Parameters.AddWithValue("@marriage", textBox49.Text);
cmd.Parameters.AddWithValue("@hreligion", textBox48.Text);
cmd.Parameters.AddWithValue("@nocba", textBox19.Text);
cmd.Parameters.AddWithValue("@nocbl", textBox20.Text);
cmd.Parameters.AddWithValue("@noa", textBox21.Text);
cmd.Parameters.AddWithValue("@nosb", textBox22.Text);
cmd.Parameters.AddWithValue("@familyplanning", cbx1.Text);
cmd.Parameters.AddWithValue("@method", textBox23.Text);
cmd.Parameters.AddWithValue("@willing", cbx2.Text);
cmd.Parameters.AddWithValue("@menshistory", textBox46.Text);
cmd.Parameters.AddWithValue("@gravida", textBox40.Text);
cmd.Parameters.AddWithValue("@para", textBox41.Text);
cmd.Parameters.AddWithValue("@hemorage", cbx3.Text);
cmd.Parameters.AddWithValue("@toxemia", cbx4.Text);
cmd.Parameters.AddWithValue("@placenta", cbx5.Text);
cmd.Parameters.AddWithValue("@sepsis", cbx6.Text);
cmd.Parameters.AddWithValue("@famhistory", textBox42.Text);
cmd.Parameters.AddWithValue("@nausea", cbx7.Text);
cmd.Parameters.AddWithValue("@vomiting", cbx8.Text);
cmd.Parameters.AddWithValue("@headache", cbx9.Text);
cmd.Parameters.AddWithValue("@dizziness", cbx10.Text);
cmd.Parameters.AddWithValue("@edema", cbx11.Text);
cmd.Parameters.AddWithValue("@pruritus", cbx12.Text);
cmd.Parameters.AddWithValue("@constipation", cbx13.Text);
cmd.Parameters.AddWithValue("@cramps", cbx14.Text);
cmd.Parameters.AddWithValue("@bleeding", cbx15.Text);
cmd.Parameters.AddWithValue("@leucorrhea", cbx16.Text);
cmd.Parameters.AddWithValue("@indigestion", cbx17.Text);
cmd.Parameters.AddWithValue("@blurring", cbx18.Text);
cmd.Parameters.AddWithValue("@lastmens", textBox24.Text);
cmd.Parameters.AddWithValue("@expectmens", textBox25.Text);
cmd.Parameters.AddWithValue("@aptype", cbx19.Text);
cmd.Parameters.AddWithValue("@riskcode", textBox26.Text);
cmd.Parameters.AddWithValue("@lastdelivery", textBox27.Text);
cmd.Parameters.AddWithValue("@placedelivery", textBox28.Text);
cmd.Parameters.AddWithValue("@tt1", textBox29.Text);
cmd.Parameters.AddWithValue("@tt2", textBox30.Text);
cmd.Parameters.AddWithValue("@tt3", textBox31.Text);
cmd.Parameters.AddWithValue("@tt4", textBox32.Text);
cmd.Parameters.AddWithValue("@tt5", textBox33.Text);
cmd.Parameters.AddWithValue("@special", textBox47.Text);
cmd.Parameters.AddWithValue("@pincode", FUCKINGPINCODE);

try
{
    int rowsAffected = cmd.ExecuteNonQuery();
    if (rowsAffected > 0)
    {
        Activity = "Updating Patient Info and History";
        MessageBox.Show("PATIENT INFO AND HISTORY SUCCESSFULLY UPDATED!");
        SaveActivity();
        this.Close();
    }
    else
    {
        MessageBox.Show("未找到匹配的用户数据，更新失败！");
    }
}
catch (Exception ex)
{
    MessageBox.Show($"更新出错：{ex.Message}");
    // 可在此处添加日志记录，方便后续排查
}
finally
{
    // 确保数据库连接关闭
    if (cmd.Connection.State == ConnectionState.Open)
    {
        cmd.Connection.Close();
    }
}

2. 检查按钮事件绑定 #

• 右键图片按钮→属性→事件→确认Click事件已正确绑定到上述代码所在的方法
• 若未绑定，直接在事件列表中选择对应的方法名即可

3. 验证更新条件值 #

• 确认FUCKINGPINCODE变量的值是当前用户的有效pincode，无空值或拼写错误
• 可在执行SQL前添加MessageBox.Show(FUCKINGPINCODE);验证值的正确性

4. 检查数据库连接与权限 #

• 确认Con.InitializeConnection方法已成功建立数据库连接
• 确保数据库账号拥有tb_maternal_masterlist表的UPDATE权限

额外建议 #

• 永远不要直接拼接用户输入到SQL语句中，参数化是行业标准做法
• 所有数据库操作必须添加异常捕获，避免程序崩溃且便于排查问题
• 日期类型优先使用dateTimePicker1.Value而非Text属性，避免格式兼容问题

内容的提问来源于stack exchange，提问作者Justine Louise Villa