安全地将数据插入SQL表，避免SQL插入错误

本文关键字：SQL 插入 避免 错误 数据 安全 | 更新日期: 2023-09-27 18:28:49

MyI创建了一个表单，应该从数据中收集信息。这些数据可能包括随机字符，包括"等。我的问题是如何在不受sql注入攻击的情况下安全地插入数据，以及在哪里可以插入转义字符，这样每当插入"/等字符时，此表单就不会抛出错误。

这是我到目前为止的代码：

   public string InsertRecordSet()
    {        
        source = new FileInfo(@"c:'scripts'db_connection.txt");
        stream = source.OpenText();
        String text = stream.ReadLine();
        stream.Close();
        String uid = text.Substring(0, text.IndexOf(":"));
        String pw = text.Substring(text.IndexOf(":") + 1, (text.Length - uid.Length - 1));
        String connectionString = "dsn=" + "db" + "; uid=" + uid + "; pwd=" + pw + ";";
        String statement = "INSERT INTO table (SubmitDate, FirstName, LastName, Email, Phone, Major, Description, HearAbout) VALUES (@SubmitDate, @FirstName, @LastName, @Email, @Phone, @Major, @Description, @HearAbout)";                    
        conn = new OdbcConnection(connectionString);
        command = new OdbcCommand(statement, conn);
        command.Parameters.AddWithValue("@SubmitDate", DateTime.Now);
        command.Parameters.AddWithValue("@FirstName", txtFname.Text);
        command.Parameters.AddWithValue("@LastName", txtLname.Text);
        command.Parameters.AddWithValue("@Email", txtEmail.Text);
        command.Parameters.AddWithValue("@Phone", txtPhone.Text);
        command.Parameters.AddWithValue("@Major", txtMajor.Text);
        command.Parameters.AddWithValue("@Desciption", txtDescription.Text);
        command.Parameters.AddWithValue("@HearAbout", txtMaxwell.Text);
        conn.Open();
        try
        {               
            command.ExecuteNonQuery();
            return "true";
        }
        catch (OdbcException oe)
        {
            _dbError = true;
            Session.Contents.Add("USIFormException", oe);
            return (oe.ToString());
        }
        finally
        {
            conn.Close();
        }
    }

安全地将数据插入SQL表，避免SQL插入错误

对于SQL注入保护，不要使用内联SQL，而是使用参数化SQL。

参数化SQL阻止SQL注入，因为它只允许值（或参数）成为字符串的一部分，而不允许任何内容。例如，参数化SQL字符串中不能有DROP TABLE xyz，因为Command对象知道它不是合法的参数值。

所以不用这个代码：

String statement = "INSERT INTO MyTable(SubmitDate, FirstName, LastName, Email, Phone, Major, Description, HearAbout)";                    
statement += "VALUES (";
statement += "'" + DateTime.Now.ToShortDateString() + "',";
statement += "'" + txtFname.Text + "', ";  
conn = new OdbcConnection(connectionString);
command = new OdbcCommand(statement, conn);

你应该有这样的代码：

String statement = String statement = "INSERT INTO MyTable(SubmitDate, FirstName, LastName, Email, Phone, Major, Description, HearAbout) VALUES (@SubmitDate, @FirstName)";
command.Parameters.Add("@SubmitDate", DateTime.Now.ToShortDateString());
command.Parameters.Add("@FirstName", txtFname.Text);

注意：如果您的数据库逻辑正在执行动态SQL，那么参数化查询将不能完全保护您免受SQL注入的影响，因此也要避免这种情况。

您应该使用参数化的值。

string query =  "INSERT INTO MyTable(SubmitDate, FirstName, LastName, Email, Phone, Major, Description, HearAbout)";                    
query += " VALUES (@SubmitDate, @FirstName, @LastName, @EMail, @Phone, @Major, @Description, @HearAbout)";
conn = new OdbcConnection(connectionString);
command = new OdbcCommand(query, conn);
command.Parameters.AddWithValue("@SubmitDate", DateTime.Now.ToShortDateString());
command.Parameters.AddWithValue("@FirstName",  txtFname.Tex);
...
conn.Open();
command.ExecuteNonQuery();

http://msdn.microsoft.com/en-us/library/system.data.odbc.odbccommand.aspx

http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcparametercollection.aspx

相关文章：