c# - 在 MS Visual Studio 中使用 C# 在 ASP.NET 中注册页面

Question

我正在努力使我的注册页面在 C# 中的 asp.net 中工作。当我运行我的代码时，我不断收到错误。我创建了一个带有点击事件的按钮。

protected void btn_Register_Click(object sender, EventArgs e)
{
    try
    {
        SqlConnection Regconn = new SqlConnection(
            // when logging in, use the following
                //"Data Source=LAPTOP-MHFEI4S3;Initial Catalog=db_OnlineBookClub; User ID=sa; Password=p@55word;");
            // with windows authentication use the following
                "Data Source=LAPTOP-MHFEI4S3;Initial Catalog=db_OnlineBookClub;Integrated Security=SSPI;");

        //opening database connection                
        Regconn.Open();

        //creating CRUD statement
        SqlCommand cmdReg = Regconn.CreateCommand();
        cmdReg.CommandText = @"INSERT INTO tbl_userInfo (name, surname, age, email, pword, securityQuestion, securityAnswer) 
                            VALUES('" + txt_Name.Text + "', '" + txt_Surname.Text + "', '" + 
                                txt_Age.Text + "', '" + txt_Email.Text + "', '" + txt_Password.Text + "', '" + 
                                txt_Q.Text + "', '" + txt_A.Text + "')";

        cmdReg.ExecuteNonQuery();

        //closing database connection
        Regconn.Close();

        Response.Redirect("Home.aspx");
    }

    catch (Exception err)
    {
        lbl_RegError.Text = err.Message;
    }
}

显然，我的插入 CRUD 语句中有一个错误。

标签： c#asp.net

Answer 1

请使用参数来防止 SQL 注入。如果值为 null，则参数的值需要设置为DBNull.Value。使用using确保连接正确关闭的语句。每次操作后也要关闭连接。

尝试以下操作：

添加以下 using 语句：

using System.Data;
using System.Data.SqlClient;

注册：

private void Register(string name, string surname, string age, string email, string encryptedPwd, string securityQuestion, string encryptedSecurityAnswer, string connectionStr)
{
    int rowsAffected = 0;
    string sqlText = "INSERT INTO tbl_userInfo(name, surname, age, email, pword, securityQuestion, securityAnswer) VALUES(@name, @surname, @age, @email, @pword, @securityQuestion, @securityAnswer);";


    try
    {
        using (SqlConnection con = new SqlConnection(connectionStr))
        {
            //open connection
            con.Open();

            using (SqlCommand cmd = new SqlCommand(sqlText, con))
            {
                //name
                if (!String.IsNullOrEmpty(name))
                {
                    cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
                }
                else
                {
                    cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = DBNull.Value;
                }

                //surname
                if (!String.IsNullOrEmpty(surname))
                {
                    cmd.Parameters.Add("@surname", SqlDbType.NVarChar).Value = surname;
                }
                else
                {
                    cmd.Parameters.Add("@surname", SqlDbType.NVarChar).Value = DBNull.Value;
                }

                //age
                if (!String.IsNullOrEmpty(age))
                {
                    cmd.Parameters.Add("@age", SqlDbType.NVarChar).Value = age;
                }
                else
                {
                    cmd.Parameters.Add("@age", SqlDbType.NVarChar).Value = DBNull.Value;
                }

                //email
                if (!String.IsNullOrEmpty(email))
                {
                    cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
                }
                else
                {
                    cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = DBNull.Value;
                }

                //pword
                if (!String.IsNullOrEmpty(encryptedPwd))
                {
                    cmd.Parameters.Add("@pword", SqlDbType.NVarChar).Value = encryptedPwd;
                }
                else
                {
                    cmd.Parameters.Add("@pword", SqlDbType.NVarChar).Value = DBNull.Value;
                }

                //securityQuestion
                if (!String.IsNullOrEmpty(securityQuestion))
                {
                    cmd.Parameters.Add("@securityQuestion", SqlDbType.NVarChar).Value = securityQuestion;
                }
                else
                {
                    cmd.Parameters.Add("@securityQuestion", SqlDbType.NVarChar).Value = DBNull.Value;
                }

                //securityAnswer
                if (!String.IsNullOrEmpty(encryptedSecurityAnswer))
                {
                    cmd.Parameters.Add("@surname", SqlDbType.NVarChar).Value = encryptedSecurityAnswer;
                }
                else
                {
                    cmd.Parameters.Add("@securityAnswer", SqlDbType.NVarChar).Value = DBNull.Value;
                }

                //execute; returns the number of rows affected
                rowsAffected = cmd.ExecuteNonQuery();
            }
        }

        Response.Redirect("Home.aspx");
    }
    catch(SqlException ex)
    {
        lbl_RegError.Text = "Error: " + ex.Message;

        //uncomment the following for debugging
        //throw ex;
    }
    catch (Exception ex)
    {
        lbl_RegError.Text = "Error: " + ex.Message;

        //uncomment the following for debugging
        //throw ex;
    }
}

有关连接字符串的更多信息，请参阅连接字符串。