update命令中的语法错误 [英] syntax error in update command

问题描述

{
           try
           {

               con = new OleDbConnection(cs);
               con.Open();

              // string cb = "update Registration set User_Password='" + txtPassword.Text + "',ContactNo='" + txtContact_no.Text + "',Email='" + txtEmail_Address.Text + "',Name='" + txtName.Text + "',JoiningDate='" + System.DateTime.Now + "',where UserName='" + txtUsername.Text + "'";
               string cb = "update Registration set User_Password='" + txtPassword .Text + "',ConatctNo="+ txtContact_no.Text +",Email='"+ txtEmail_Address .Text +"',Name='"+txtName.Text +"',JoiningDate="+ System.DateTime.Now +",where UserName='"+txtUsername.Text +"'";

               cmd = new OleDbCommand(cb);
               cmd.Connection = con;
               cmd.ExecuteReader();
               con.Close();
               MessageBox.Show("Successfully updated", "User Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
              // Autocomplete();
               btnUpdate_record.Enabled = false;

               con.Open();

推荐答案

首先，不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。



其次，这可能会解决您的问题 - 这是日期字段格式中包含混淆SQL的字符！



第三，不要使用ExecuteReader进行更新，使用ExecuteNonQuery - 更新不返回任何行。



第四，从不以明文形式存储密码 - 这是一个主要的安全风险。有关如何在此处执行此操作的信息：密码存储：如何做到这一点。 [ ^ ]



第五，你负责处理你创建的对象 - 连接，命令，数据读取器 - 使用块是一种很好的方法。

Firstly, do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Secondly, that will probably fix your problem - which is the date field format has characters that confuse SQL!

Thirdly, don't use ExecuteReader to do an Update, use ExecuteNonQuery - and update does not return any rows.

Fourthly, never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]

Fifthly, you are responsible for disposing of objects your create - Connections, Commands, DataReaders - a using block is a good way to do that.

根据你删除的评论，你已经代码来更新记录使用参数化查询。您刚刚对其进行了评论，并将其替换为易受 SQL注入 [ ^ ]。



注释掉的代码块只有两个问题 - 你错了 ContactNo 列为 CotactNo ，并且您以错误的顺序添加参数。

Based on your deleted comment, you already have the code to update the record using a parameterized query. You've just commented it out and replaced it with code that's vulnerable to SQL Injection[^].

There are only two things wrong with the commented-out code block - you've misspelt the ContactNo column as CotactNo, and you're adding the parameters in the wrong order.

private void Update_record_Click(object sender, EventArgs e)
{
    const string query = "UPDATE Registration SET User_Password = ?, ContactNo = ?, Name = ?, Email = ?, JoiningDate = ? WHERE UserName = ?";

    try
    {
        using (var con = new OleDbConnection(cs))
        using(OleDbCommand cmd = new OleDbCommand(query, con))
        {
            // The parameter names don't matter here, only the order:
            cmd.Parameters.AddWithValue("Password", txtPassword .Text);
            cmd.Parameters.AddWithValue("ContactNo", txtContact_no.Text);
            cmd.Parameters.AddWithValue("Name", txtName.Text);
            cmd.Parameters.AddWithValue("Email", txtEmail_Address .Text);
            cmd.Parameters.AddWithValue("JoiningDate", DateTime.Now);
            cmd.Parameters.AddWithValue("UserName", txtUsername.Text);

            con.Open();
            cmd.ExecuteNonQuery();
        }

        MessageBox.Show("Successfully updated", "User Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (System.Data.Common.DbException ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

现在你修复了该代码块中的SQL Injection漏洞，你需要修复密码存储策略。以纯文本格式存储密码是一个极其坏主意。您应该只存储密码的盐渍哈希：

安全密码验证简单说明 [ ^ ]

Salted Password Hashing - 正确行事 [ ^ ]

Now that you've fixed the SQL Injection vulnerability in that code block, you need to fix your password storage strategy. Storing passwords in plain text is an extremely bad idea. You should only ever store a salted hash of the password:
Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

这篇关于update命令中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！