Column 'username' does not belong to table Table

Question

I am trying to do lock user account for Invalid login attempts in Asp.Net C# by using Visual Studio 2019. Database is using MySql Workbench 8.0 CE. But facing the error

C# code shown as below:

using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace Canteen_UAT
{
    public partial class LoginDetail : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click1(object sender, EventArgs e)
        {
            MySqlConnection scon = new MySqlConnection("server = XXX.XXX.XX.XXX; user id = root; password = XXXXX; persistsecurityinfo = True; database = posdbms_uat");
            String myquery = "select count(*) from posdbms_uat.logindetail where username='" + TextBox1.Text + "'";
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = myquery;
            cmd.Connection = scon;
            MySqlDataAdapter da = new MySqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            String uname;
            String pass;
            String status;
            //String lockstatus;
            int attemptcount = 0;

            if (ds.Tables[0].Rows.Count > 0)
            {
                uname = ds.Tables[0].Rows[0]["username"].ToString();
                pass = ds.Tables[0].Rows[0]["password"].ToString();
                status = ds.Tables[0].Rows[0]["status"].ToString();
                scon.Close();
                if (status == "Open")
                {
                    if (uname == TextBox1.Text && pass == TextBox2.Text)
                    {
                        Session["username"] = uname;
                        Response.Redirect("Order.aspx");
                    }
                    else
                    {
                        Label2.Text = "Invalid Username or Password - Relogin with Correct Username & Password. No of Attempts Remaining : " + (2 - attemptcount);
                        attemptcount = attemptcount + 1;
                    }
                }
                else if (status == "Locked")
                {
                    Label2.Text = "Your Account Locked Already : Contact Administrator";
                }
                else
                {
                    Label2.Text = "Invalid Username or Password - Relogin wit Correct Username and Password.";
                }
                if (attemptcount == 3)
                {
                    Label2.Text = "Your Account Has Been Locked Due to Three Invalid Attempts - Contact Administrator.";
                    setlockstatus(TextBox1.Text);
                    attemptcount = 0;
                }
            }
        }

        private void setlockstatus(String username1)
        {
            String mycon = "server = xxx; user id = root; password = xxx; persistsecurityinfo = True; database = posdbms_uat";                        
            String updatedata = "Update posdbms_uat.logindetail set status='Locked' where username='" + username1 + "' ";
            MySqlConnection con = new MySqlConnection(mycon);
            con.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = updatedata;
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
        }
    }
}

Not sure what might be causing this.

What I have tried:

I created a table as posdbms_uat, datatable match the column name in the database table and with appropriate datatype. Not sure how this error pops up.

SQL injections warning in 3...2...1 [Please take a look here](https://bobby-tables.com/) — nilsK, Jul 22 '21 at 08:10
From your comments in the accepted answer, looks like you are now using parametrized queries. Thats is nice. The last step to secure your data would be protecting the passwords (remember a lot of users sadly reuse the passwords) to avoid data leaks. The way to do it would storing the data hashed. Here is a link to [mysql hashing functions](https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html) and I would suggest using SHA as your hashing algorythm — Cleptus, Jul 23 '21 at 06:39

score 0 · Accepted Answer · answered Jul 22 '21 at 08:11

0

The query:

String myquery = "select count(*) from posdbms_uat.logindetail where username='" + TextBox1.Text + "'";

...only returns the number of rows matching the WHERE condition - not the actual data in the rows. It should be fixed by specifying the columns you want to get:

String myquery = "select username, password, status from posdbms_uat.logindetail where username='" + TextBox1.Text + "'";

Also, you should consider using parametrization to avoid SQL injection (see this SO question). Another thing is, please do not store the password in plain text.

answered Jul 22 '21 at 08:11

kaffekopp

2,551
6
13

Thanks Kaffekopp. Error was fixed after change as select username, password, status from DB. Then, encounter another error for line da.Fill(ds); "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select username, password, status from posdbms_uat.logindetail where Username = ' at line 1". – Mae Jul 22 '21 at 08:35
Your second error would probably get fixed if you change your code and follow the advice Kaffekopp gave. "_you should consider using parametrization to avoid SQL injection (see [this SO question](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements)_)" – Cleptus Jul 22 '21 at 08:51
Thanks Cleptus, I will change it. – Mae Jul 22 '21 at 08:58
Thanks Cleptus and kaffekopp. After go through SQL injection, my bugs was fixed. Later I will re-design the login page for the users access. Have a nice day. – Mae Jul 22 '21 at 09:13

Column 'username' does not belong to table Table

1 Answers1