避免使用参数进行SQL注入? [英] Avoiding SQL Injections with Parameters?

问题描述

我最近调整了代码，以避免获得SQL注入，并获得了添加参数的帮助，但是现在代码对我来说是半外来的，并且想知道MySqlCommand.Parameters需要什么对象引用

I have recently adjusted my code to avoid getting SQL injections and got helped with adding parameters but now the code is semi-foreign to me and was wondering what object reference is required for MySqlCommand.Parameters

MySqlParameter parameter = new MySqlParameter();
        parameter.ParameterName = "@userid";
        parameter.MySqlDbType = MySqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = this.userid_txt.Text;
        parameter.Value = this.email_txt.Text;
        parameter.Value = this.passone_txt.Text;
        parameter.Value = this.passtwo_txt.Text;
        parameter.Value = this.lastname_txt.Text;
        parameter.Value = this.firstname_txt.Text;

        string Query = "insert into userdatabase.users (userid, email, passone, passtwo, lastname, firstname) values(@userid,@email,@passone,@passtwo,@lastname_txt,@firstname)";
        MySqlCommand.Parameters.AddWithValue("@userid", this.userid_txt.Text);
        MySqlCommand.Parameters.AddWithValue("@email", this.email_txt.Text);
        MySqlCommand.Parameters.AddWithValue("@passone", this.passone_txt.Text);
        MySqlCommand.Parameters.AddWithValue("@passtwo", this.passtwo_txt.Text);
        MySqlCommand.Parameters.AddWithValue("@lastname", this.lastname_txt.Text);
        MySqlCommand.Parameters.AddWithValue("@firstname", this.firstname_txt.Text);

        MySqlConnection conDataBase = new MySqlConnection();
        MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);
        MySqlCommand cmd = new MySqlCommand(Query, conDataBase);

        cmd.ExecuteNonQuery()

我的错误状态

'MySql.Data.MySqlClient.MySqlDbType'不包含对'NVarChar'的定义

'MySql.Data.MySqlClient.MySqlDbType' does not contain a defintion for 'NVarChar'

和主要

非静态字段，方法或属性'MySql.Data.MySqlClient.MySqlCommand.Parameters.get'需要对象引用

An object refrence is required for non-static field, method or property 'MySql.Data.MySqlClient.MySqlCommand.Parameters.get'

我对使用MySql还是比较陌生，因此可以提供任何帮助

I am relatively new to using MySql so any help is appreciated

一旦单击连接到数据库的注册器，新代码将导致致命错误

New code which causes a fatal error once I click register which connects to my database

MySqlCommand cmdDataBase = new MySqlCommand();
        MySqlParameter parameter = new MySqlParameter();

        string constring = "datasource=127.0.0.1;port=3306;username=root;password=welcome";
        string Query = "insert into userdatabase.users (userid, email, passone, passtwo, lastname, firstname) values(@userid,@email,@passone,@passtwo,@lastname_txt,@firstname)";
        cmdDataBase.Parameters.Add("@userid", MySqlDbType.VarChar).Value = userid_txt.Text;
        cmdDataBase.Parameters.Add("@email", MySqlDbType.VarChar).Value = email_txt.Text;
        cmdDataBase.Parameters.Add("@passone", MySqlDbType.VarChar).Value = passone_txt.Text;
        cmdDataBase.Parameters.Add("@passtwo", MySqlDbType.VarChar).Value = passtwo_txt.Text;
        cmdDataBase.Parameters.Add("@lastname", MySqlDbType.VarChar).Value = lastname_txt.Text;
        cmdDataBase.Parameters.Add("@firstname", MySqlDbType.VarChar).Value = firstname_txt.Text;


        MySqlConnection conDataBase = new MySqlConnection(constring);
        MySqlCommand cmd = new MySqlCommand(Query, conDataBase);

        try {
            conDataBase.Open();
            cmd.ExecuteNonQuery();
            MessageBox.Show("Welcome to iDSTEM!");
        }
            catch (Exception ex)
        {
                MessageBox.Show(ex.Message);
            }

推荐答案

您需要命令本身-稍后将其构建:

You need the command itself - you're building it later:

MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);

只需将其向上移动，然后使用:

Just move that higher, and then use:

cmdDataBase.Parameters.AddWithValue(...);

目前还不清楚为什么要创建parameter变量，因为您从未使用过它.

It's not clear why you're creating the parameter variable at all, as you're never using it.

我实际上不会使用AddWithValue，而是使用:

I would actually not use AddWithValue, but instead use:

cmdDataBase.Parameters.Add("@userid", MySqlDbType.VarChar).Value = userid_txt.Text;
// etc

MySQL没有NVarChar类型，因此MySqlDbType也没有，因此是您的错误-我强烈怀疑您只想要VarChar.您无需指定方向-默认情况下隐式为输入".

MySQL doesn't have a type of NVarChar, so MySqlDbType doesn't either, hence your error - I strongly suspect you just want VarChar. You don't need to specify the direction - that's implicitly "input" by default.

此外，您应该对连接和命令使用using语句:

Additionally, you should be using using statements for your connection and command:

using (var connection = new MySqlConnection(...))
{
    using (var command = new MySqlCommand(sql, connection))
    {
        ...
    }
}

这篇关于避免使用参数进行SQL注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！