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:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
Microsoft SQL Server uses an @ prefix, not _ for parameters. I don't know about PostreSQL.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,554
Location
Sydney, Australia
Programming Experience
10+
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:
C#:
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

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
Interesting that in that link the NpgSqlParameter() calls didn't have the prefix. (As expected, though, the query text had the prefix.)
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,554
Location
Sydney, Australia
Programming Experience
10+
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

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

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,554
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
Jul 19, 2022
Messages
15
Location
Philippines
Programming Experience
Beginner
You used a colon and it said there was a syntax error near a $ symbol?
I tried both but they have the same outcome:

1.PNG
 
Last edited:

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,554
Location
Sydney, Australia
Programming Experience
10+
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

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
1,436
Location
Norway
Programming Experience
10+

percival.09b

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

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,554
Location
Sydney, Australia
Programming Experience
10+
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

Staff member
Joined
Apr 6, 2019
Messages
5,469
Location
Chesapeake, VA
Programming Experience
10+
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

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

percival.09b

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

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,554
Location
Sydney, Australia
Programming Experience
10+
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?
 
Top Bottom