c# - C# 登录表单 - 从 Form1 登录后检索与表单 2 中的用户名对应的数据库值

Question

我有一个 C# 表单应用程序。Form1 有两个目的，一个用于注册，另一个用于登录。注册数据包括保存在数据库中的用户名、ID 号和密码，也只允许唯一用户名，即如果用户名已在使用中，则不能使用再次。

登录需要用户名和密码。但是登录后，如何在form2的文本框中显示对应唯一用户名的ID号是个问题。

以下是我当前的代码。

//for signing in
    private void button1_Click(object sender, EventArgs e)
    {
        if(textBox1.Text == "" || textBox2.Text == "")
        {
            MessageBox.Show("!!Please fill in both Username and Password!! ");
        }
        else
        {
            SqlConnection sqlcon = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=""C:\Users\Lenovo\Desktop\dev\C# .net\Aadi Paw Plethysmometer\Aadi Paw Plethysmometer\Database1.mdf"";Integrated Security=True");
            string query = "Select * from Signup where Username = '" + textBox1.Text.Trim() + "' and Password = '" + textBox2.Text.Trim() + "'";
            SqlDataAdapter sda = new SqlDataAdapter(query,sqlcon);
            DataTable dtbl = new DataTable();
            sda.Fill(dtbl);
            if (dtbl.Rows.Count > 0)
            {

                Form2 newForm = new Form2();
                newForm.Show();
                this.Hide();

            }
            else
            {
                MessageBox.Show("Invalid username or password");
                textBox1.Text = textBox2.Text = "";
            }

        }
    }

//For Sign Up
private void button2_Click(object sender, EventArgs e)
    {
        if (FirstName.Text == "" || SecondName.Text == "" || Username.Text == "" || Password.Text == "" || InstituteID.Text == "" || RInstituteID.Text == "")
        {
            MessageBox.Show("Kindly fill in all the specified fields.");
        }
        else if((InstituteID.Text != RInstituteID.Text) && (Password.Text != RPassword.Text))
        {
            MessageBox.Show("Password Mismatch and Institute ID Mismatch. Please enter again...");
        }
        else if ((InstituteID.Text != RInstituteID.Text) && (Password.Text == RPassword.Text))
        {
            MessageBox.Show("Institute ID Mismatch. Please enter again...");
        }
        else if ((InstituteID.Text == RInstituteID.Text) && (Password.Text != RPassword.Text))
        {
            MessageBox.Show("Password Mismatch. Please enter again...");
        }
        else
        {
            using (SqlConnection sqlCon = new SqlConnection(connectionstring))
            {
                sqlCon.Open();
                //after connection is open, using following "if" code to check uniqueness of Username
                string query2 = "Select * from Signup where Username = '" + Username.Text.Trim() + "'";
                SqlDataAdapter sda = new SqlDataAdapter(query2, sqlCon);
                DataTable dtbl2 = new DataTable();
                sda.Fill(dtbl2);
                if (dtbl2.Rows.Count > 0)
                {
                    MessageBox.Show("Username already in Use. Change Username and try Signing up again...");
                }

                else
                {
                    SqlCommand sqlcmd = new SqlCommand("Useradd", sqlCon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.Parameters.AddWithValue("@FirstName", FirstName.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@SecondName", SecondName.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@Username", Username.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@Password", Password.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@RPassword", RPassword.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@InstituteID", InstituteID.Text.Trim());
                    sqlcmd.Parameters.AddWithValue("@RInstituteID", RInstituteID.Text.Trim());
                    sqlcmd.ExecuteNonQuery();
                    MessageBox.Show("Sign Up is Successfull!");
                    clear();
                }

            }

        }
    }

标签： c#authenticationlogging

Answer 1

只需从数据表中获取此值：

int uniqueId= dtbl.Rows[0].Field<int>("UniqueId");

但是我在您的两种方法中看到了一些重要的安全/设计问题：

最重要的是 - 始终关闭数据库连接，因为您的连接池将充满连接（默认情况下它有 100 个连接）。

只需在 Use 运算符中使用您的连接：

using (var conn = new SQLConnection(connStr) {
     conn.Open();
     //open connection, get data
} //here it will be disposed, and close for connection will be executed

不要像你一样使用参数注入：

Select * from Signup where Username = '" + textBox1.Text.Trim() + "' and ...

用户可以在 userane 中插入 ' ，您将获得 SQL 注入的经典示例（如果输入类似 'update Signup set Password= 'you was hacked';），则可以从您的登录名/密码输入执行用户代码。只需使用 SQL 参数即可。

另一个问题：您不需要数据表来检查用户是否存在：只需使用代码：

  select count(*) from [dbo].[Signup] where Username = @UserName and Password = @Password

向您的 SqlCommand 添加 2 个参数，您的请求将是安全的。在您可以执行 ExecuteScalar 并获得密码出现次数之后。

string sql =
        "select count(*) from [dbo].[Signup] where Username = @UserName and Password = @Password";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.Parameters.Add("@UserName", SqlDbType.VarChar);
            cmd.Parameters["@UserName"].Value = username;
            cmd.Parameters.Add("@Password", SqlDbType.VarChar);
            cmd.Parameters["@Password"].Value = password;
            try
            {
                conn.Open();
                object userCount = cmd.ExecuteScalar();
                if (!userCount .Equals(DBNull.Value)) 
                { 
                        //user exist 
                } 
            }
            catch (Exception ex)
            {
                     Console.WriteLine(ex.Message);
            }
        }
    }