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();
            }
        }

 

[Skydiver]

Microsoft SQL Server uses an @ prefix, not _ for parameters. I don't know about PostreSQL.

 

[jmcilhinney]

I had a quick search for examples using that ADO.NET provider and, based on this, it appears that the parameter prefix should be a colon:

sql = "SELECT employee_no FROM public.tb_userlogin where username = :username AND Decoypass = :password";

// ...

cmd.Parameters.AddWithValue(":username", userText.Text);
cmd.Parameters.AddWithValue(":password", passText.Text);

 

[Skydiver]

Interesting that in that link the NpgSqlParameter() calls didn't have the prefix. (As expected, though, the query text had the prefix.)

 

[jmcilhinney]

Interesting that in that link the NpgSqlParameter() calls didn't have the prefix. (As expected, though, the query text had the prefix.)

I've noticed that in some other providers too. I think the Oracle provider supports that. Not sure whether SqlClient does or not as I've never tried.

It's also worth noting that the second example on that page uses @ in the SQL code, so maybe that's supported too. I know that some other providers, e.g. Connector/Net for MySQL, added support for @ because that was what .NET developers were used to from using Microsoft databases and they wanted to follow the principle of least surprise. Using the same parameter prefix as much as possible is a good thing as it makes your SQL code more generic. That said, you probably can't copy it into a database management application then, as it might not support that prefix.

 

[percival.09b]

I had a quick search for examples using that ADO.NET provider and, based on this, it appears that the parameter prefix should be a colon:

sql = "SELECT employee_no FROM public.tb_userlogin where username = :username AND Decoypass = :password";

// ...

cmd.Parameters.AddWithValue(":username", userText.Text);
cmd.Parameters.AddWithValue(":password", passText.Text);

Thank you, I tried both prefix $ and : it says syntax error at near"$". While prefix @, error says operator does not exist: @ character varying. Anyway I'm using Postgresql database.

 

[jmcilhinney]

Thank you, I tried both prefix $ and : it says syntax error at near"$".

You used a colon and it said there was a syntax error near a $ symbol?

 

[percival.09b]

You used a colon and it said there was a syntax error near a $ symbol?

I tried both but they have the same outcome:

 

[jmcilhinney]

Nope. That says there's a syntax error near the colon, not a $ symbol.

I just saw another example that uses % as the parameter prefix, so maybe try that too. Most examples I can see use a colon though, so I'm not sure why that doesn't work. Anyway, I have no specific PostgreSQL knowledge and you can search the web as well as I can, so I'll leave to you from here.

 

[JohnH]

Npgsql Basic Usage | Npgsql Documentation

www.npgsql.org

 

[percival.09b]

Nope. That says there's a syntax error near the colon, not a $ symbol.

I just saw another example that uses % as the parameter prefix, so maybe try that too. Most examples I can see use a colon though, so I'm not sure why that doesn't work. Anyway, I have no specific PostgreSQL knowledge and you can search the web as well as I can, so I'll leave to you from here.

Thanks for your time in helping me.

 

[percival.09b]

Npgsql Basic Usage | Npgsql Documentation

www.npgsql.org

I used $1 and $2 place holder, quite resolved but when I am bout to log in here saying: 08P01 bind message supply 0 parameters, but prepared statement "" requires 2

 

[jmcilhinney]

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.

 

[Skydiver]

As a quick aside, I noticed that you are comparing the user entered password directly with the password in the database. That means all the users passwords are sitting naked in the database. That's a big no-no. You should only store salted and hashed passwords in the database.

 

[percival.09b]

As a quick aside, I noticed that you are comparing the user entered password directly with the password in the database. That means all the users passwords are sitting naked in the database. That's a big no-no. You should only store salted and hashed passwords in the database.

True, that's not a good practice I'm really aware of that, and I will going to fix that too.