how to check if user is logged in database C#

Question

    void checkOnline()
    {
        string sql = "select * from png_users where username = '" + txtboxUsername.Text + "' and password = '"  + txtboxPassword.Text + "' and userstatus = '1'";
        cm = new MySqlCommand(sql, cn);
        dr = cm.ExecuteReader();
        dr.Read();
        if (dr.HasRows)
        {
            MessageBox.Show("This account is currently online, you forgot to logout. Please approach administrator for help. Thank you", "THD FAM", MessageBoxButtons.OK, MessageBoxIcon.Error);
            dr.Close();
            this.Close();
            return;
        }
    }

I'm pretty new to database and I am trying to figure out how to use sessions to check and see if a user is logged into a database so that they would have authorization to access specific pages.

Thanks For Help

Why do you want to do this? Learning purpose? Because it shouldn't be a problem to log in again, if a user forgot to log out. What is not working with your posted code? Another hint: add [using statements](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement) to your disposable classes, that way you dont have to care about disposing it yourself. — nilsK, Jun 06 '19 at 13:56
ASP.Net Webforms? MVC? What are you doing for a session cookie? Please [edit] your question for more details. — O. Jones, Jun 06 '19 at 13:56
This code is wrong, it is utterly insecure. SQL Injection, wrong password policy — Cleptus, Jun 06 '19 at 13:58
It's a challenge for me, I got no idea how to do if user login the other people can't access that account because someone already logged in — Anden Castillo, Jun 06 '19 at 14:02

Dmitry Bychenko · Answer 1 · 2019-06-06T14:34:41.657

According to comments

If the userstatus is 0 you can use the account and if the userstatus = 1 you can't access the account because someone already used it

we should check for 3 cases:

user / password not found (let's return -1 as userstatus for this)
user owns the account (userstatus is 0)
account belongs to some other user (userstatus is 1)

Let's extract method:

  // -1 Account does't exist 
  //  0 Account exists and belongs to the user
  //  1 Account exists and belongs to different user
  public int UserLogStatus(string login, string password) {
    //DONE: do not reuse connection, but create a new one
    using (var con = new MySqlConnection(ConnectionStringHere)) {
      con.Open();

      //DONE: keep sql readable
      //DONE: make sql parametrized 
      string sql = 
        @"select userstatus
            from png_users 
           where username = @prm_username and
                 password = @prm_password";  

      //DONE: wrap IDisposable into using 
      using (MySqlCommand query = new MySqlCommand(sql, con)) {
        //TODO: better create params explicitly, Parameters.Add(name, type).Value = ...
        query.Parameters.AddWithValue("@prm_username", login);
        query.Parameters.AddWithValue("@prm_password", pasword);

        using (var reader = query.ExecuteReader()) {
          if (reader.Read()) 
            return Convert.ToInt32(reader[0]);
          else
            return -1;
        }
      }
    }
  }

And then you can use it:

  int status = IsUserLogged(txtboxUsername.Text, txtboxPassword.Text);

  if (status == 0) {
    MessageBox.Show("Either username or password is incorrect.", 
                    "THD FAM", 
                     MessageBoxButtons.OK, 
                     MessageBoxIcon.Error);

    return;
  } 
  else if (status == 1) {
    MessageBox.Show("This account is currently online, you forgot to logout. Please approach administrator for help. Thank you", 
                    "THD FAM", 
                     MessageBoxButtons.OK, 
                     MessageBoxIcon.Error);

    return;
  }

Warning! Do not store passwords as plain text. If someone steal the table all the users will be compromised. Store password hashes instead. When logging on, user must provide a string (password), such that

  HashFunction(password) == StoredHash

where HashFunction is one way function: easy to compute (i.e. it's easy to find HashFunction(password) value), difficult to reverse (i.e. it's almost impossible to find a sting such that HashFunction(password) == given value)

Mr. Bychenko ftw! I really hope your are teaching people offline too ;-) — nilsK, Jun 06 '19 at 14:07
If the userstatus is = 0 you can use the account and if the userstatus = 1 you can't access the account because someone already used it — Anden Castillo, Jun 06 '19 at 14:14
@Anden Castillo: Do you want to return `userstatus`? Say, `0` - user owns the acount, `1` - account exists but it belongs to a different user, `-1` - account doesn't exist. — Dmitry Bychenko, Jun 06 '19 at 14:26
when i use my account in database the userstatus in my row will be 1 and if the application is exit userstatus in database in my row will be 0 — Anden Castillo, Jun 06 '19 at 14:45
@Anden Castillo: technically if you want to change the status you can try using `update png_users set userstatus = @prm_userstatus where username = @prm_username and password = @prm_password` query; however, I doubt if two different users can share same `username` *and* `password`. I think you can declare `username` with `unique constraint` and just do not allow users have same names. — Dmitry Bychenko, Jun 06 '19 at 14:50
can you give me codes so i can get some idea :) thank you for help btw i appreciate it — Anden Castillo, Jun 06 '19 at 14:57
@Anden Castillo: to add `unique` constraint (`alter table`) if you don't want to allow users share same name or name + password combination: https://stackoverflow.com/questions/15255304/how-add-unique-key-to-existing-table-with-non-uniques-rows — Dmitry Bychenko, Jun 06 '19 at 15:02

score -2 · Answer 2 · answered Mar 22 '20 at 09:00

-2

You have to close the connection with con.close();

answered Mar 22 '20 at 09:00

Jure Kostanjevic

1

how to check if user is logged in database C#

2 Answers2