Login Authentication with no sql injection

percival.09b

Member
Joined
Jul 19, 2022
Messages
15
Location
Philippines
Programming Experience
Beginner
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

Inside the login button function:
 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();
            }
        }
 
Last edited:
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
 

Attachments

  • Capture2.PNG
    Capture2.PNG
    61.4 KB · Views: 11
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?
 
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..
 
Back
Top Bottom