Login Authentication with no sql injection

[percival.09b]

I made a login with authentication in DotNet windows forms app and I'm trying to do my best to guard the database from SQL injection attacks, but it seems like there was a wrong logic in my code. Any help would be appreciated

 private void BtnLogin_Click(object sender, EventArgs e) //user login authentication
        {
            bool userValidated = validateUserInput(userText.Text);
            bool passValidated = validateUserInput(passText.Text);

            if (userValidated && passValidated)
            {
                getConnection();
            }
           
            try
            {
                NpgsqlConnection conn = new NpgsqlConnection("Host=localhost;Database=UserLogin;Username=postgres;Password=adminAd1234");

                NpgsqlDataAdapter conDataAdapter = new NpgsqlDataAdapter();
                //NpgsqlDataAdapter conDataAdapter = new NpgsqlDataAdapter("select * from public.tb_userlogin where username='" + userText.Text + "'and password='" + passText.Text + "'", conn);
                DataTable conTable = new DataTable();
                conDataAdapter.Fill(conTable);

                /* -UNSAFE command-
                sql = @"SELECT employee_no FROM public.tb_userlogin where
                username ='" + Convert.ToString(userText.Text) + "' AND password ='" + Convert.ToString(passText.Text) + "'";
                */

                string username = userText.Text;
                string password = passText.Text;

                conn.Open();
                conDataAdapter.SelectCommand = cmd;
                //cmd = new NpgsqlCommand(sql, conn);
                cmd = new NpgsqlCommand("SELECT * FROM public.tb_userlogin where username = $username AND password = $password", conn);

                EmpNo = code.Converter_string(sql).ToString();
               

                cmd.Parameters.AddWithValue("$username", userText.Text);
                cmd.Parameters.AddWithValue("$password", passText.Text);
                NpgsqlDataReader dr = cmd.ExecuteReader();

                if (userText.Text == String.Empty || passText.Text == String.Empty)
                {
                    MessageBox.Show("Field cannot be empty!");
                }

                if (EmpNo != "0")//log in successfully
                {
                    this.Hide();
                    new ClientCrudFrm().Show();

                }
                else
                {
                    MessageBox.Show("Please check your username or password", "Login Failed", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    return;
                }
                if (conTable.Rows.Count == 1)
                {
                    MessageBox.Show("login successfully");
                }
                else
                {
                    MessageBox.Show("Error");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message,
                    "Something went wrong", MessageBoxButtons.OK, MessageBoxIcon.Error);
                conn.Close();
            }
        }

 

[percival.09b]

It's my understanding that when you use $1, etc, you are using positional parameters. There is no parameter with that name, so don't use that name when adding the parameter. Look at the example here in the documentation you linked to. Read the examples carefully and note the differences between positional and named parameters.

I updated the code above, I put all codes from the login button function

 

[jmcilhinney]

Did you read the error message? Set the SelectCommand property of your data adapter before calling Fill.

 

[percival.09b]

Did you read the error message? Set the SelectCommand property of your data adapter before calling Fill.

I Initialized NpgsqlDataAdapter to the variable conDataAdapter but still can't connect to pass the command to my table

 

[jmcilhinney]

Go back and look at post #1. Look at the line where you're creating the data adapter and then look at the line below it that you commented out. See the difference? How are you going to execute a query with no query?

 

[cjard]

I'm trying to do my best to guard the database from SQL injection attacks

You'll need to find some way of securing the connection string inside the config then.

Also, while you're on your mission of solving common database security failures, consider also not storing passwords in plain text..