0

This is my code so far:

private void loginbtn_Click(object sender, EventArgs e)
    {
        CRUD.cmd = new NpgsqlCommand("SELECT COUNT(*) FROM users WHERE username = '" + txtuser.Text + "' AND password = '" + txtpass.Text + "'", CRUD.con);
        NpgsqlDataAdapter da = new NpgsqlDataAdapter();
        DataTable dt = new DataTable();
        da.SelectCommand = CRUD.cmd;
        da.Fill(dt);

        if (dt.Rows[0][0].ToString() == "1")
        {
            Welcome welcome = new Welcome();
            welcome.Show();

            this.Hide();
        }
        else
        {
            MessageBox.Show("Username or password incorrect, try again.",
                "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            txtpass.Clear();
            txtuser.Clear();
            txtuser.Focus();

        }
        if (RememberMe.Checked)
        {
            Properties.Settings.Default.UserName = txtuser.Text;
            Properties.Settings.Default.Password = txtpass.Text;
            Properties.Settings.Default.Save();
        }
    }

I know I should use SQL Parameters, but it's a safe local environment. My question is, how can I retrieve info of which user is currently logged in? I need it so when the user does INSERT method to SQL table, I want to know which user did the entry or which user deleted it. Also to add that users info together with the rest of INSERT method to 2 additional columns (USER ID AND PASS).

Edit:

What I have here is a small windows form application with a datagridview (DGV) and a few text fields to insert values to table with a button, then display that info to DGV, with the ability to delete rows also. I made Form2 so the user has to log in before he gains access to Form1 (DGV Form). All I want to do is to somehow save that users info and whenever he makes a change to DGV, add his credentials to 2 extra columns in the table next to the entry that he has made.

mjwills
  • 23,389
  • 6
  • 40
  • 63
q1werty
  • 51
  • 6
  • well, instead of just _counting_ the users, matching your username and unhashed password (which you should ***never*** do, even in a safe local environment. and especially if you're still learning - learn it right!), you could just `SELECT *` and read the data? – Franz Gleichmann Dec 15 '20 at 07:25
  • You can retrieve user name or user domain name from window environment like this doc "https://learn.microsoft.com/en-us/dotnet/api/system.environment.username?view=net-5.0" let's hope that your pc environment and AD properly setup – phonemyatt Dec 15 '20 at 07:25
  • @FranzGleichmann Thank you, I'm not very familiar with hashed passwords, yet. Not entirely sure of how I should implement the SELECT * method to find currently logged in user, could you give me a small example? Environment.UserName gets me the current user on windows, I need to grab a user that is currently logged in in my app – q1werty Dec 15 '20 at 07:32
  • Save the user name and password in a reference, what's the issue here? Also make sure to clarify your questions, otherwise people misinterpreted them, and you get closed a duplicate. I have removed the duplicate i have added. Can you edit the question and be more specific about what you actually need. I found this a little ambiguous – TheGeneral Dec 15 '20 at 07:33
  • I edited the question to make it more clear what I'm trying to do. Sorry if I misunderstood something, I'm still a newbie with windows forms. – q1werty Dec 15 '20 at 07:49
  • 3
    `I know I should use SQL Parameters, but it's a safe local environment.` No excuse. Do it properly, every time. https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection https://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database – mjwills Dec 15 '20 at 07:51
  • `My question is, how can I retrieve info of which user is currently logged in?` They must have logged in when they loaded the app, yes? So store the username at that point in a `static` variable. – mjwills Dec 15 '20 at 07:52
  • Thank you guys, It's a test app so I'm not worried by SQL injections. Just trying to learn some new things, I'll try to sort it out somehow.. – q1werty Dec 15 '20 at 07:59
  • `so I'm not worried by SQL injections.` I want to be crystal clear here - this is not how to develop software. – mjwills Dec 15 '20 at 08:02
  • @mjwills Yeah and I totally agree with you on that one, I just learned about parameters and sql injections 10 days into this test project, so I decided to at least make it work before I go and change every mistake I made during the build. – q1werty Dec 15 '20 at 08:06

1 Answers1

0

Knowing who is logged in

Who is logged in to the windows machine

In case you're referring to who is logged in to eg. the Windows machine, you could use something like Environment.UserName.

Custom solution / database

If it's your own solution / database to "log in" to, that's a feature you'd need to add. It's usually done on its own server with well-known libraries instead of being 'hand-written', but for a crude/insecure example for your test project you could use Properties.Settings, eg:

/* "Logging in" */
if (password == databaseUser.Password) {
    Properties.Settings.Default.myLoggedInUserId = databaseUser.Id;
    Properties.Settings.Default.myLoggedInUserName = databaseUser.Name;
}

/* "Logging out" */
Properties.Settings.Default.myLoggedInUserId = null;
Properties.Settings.Default.myLoggedInUserName = null;

Which user created/edited a row

This is often implemented by adding additional fields to the table, eg. CreatedDate and CreatedBy:

var sqlText = "INSERT INTO MyTableName(Name, CreationDate, CreatedBy) VALUES (@Name, @CreatedDate, @CreatedBy);";
var command = new SqlCommand(sqlText, connection);

command.Parameters.Add("@Name", name);
command.Parameters.Add("@CreatedDate", DateTime.UtcNow);
command.Parameters.Add("@CreatedBy", myLoggedInUserName);

For who edited a row, consider "modified date" and "modified by" columns.

Who Deleted a row

This could be done either by:

  • manually INSERTing into another table after the action is successful (which it sounds like you have)

    DELETE FROM Users WHERE UserId = @UserId;
INSERT INTO MyLoggingTable(LogMessage, LoggedInUserId)
  VALUES(@Message, @LoggedInUserId);

  • when combined with "modified date"/"modified by" columns as mentioned above, using a "soft delete" which deletes by setting a property rather than an SQL DELETE, eg users.Active:

    --List all active users
SELECT * FROM users WHERE users.Active = 1;

--Delete a user
UPDATE users SET Active = 0 WHERE UserId = @UserId;

--Who deleted a user (assuming you can't edit a deleted user)
SELECT users.ModifiedBy FROM users WHERE users.UserId = @Id;

  • similar to the above section, but adding "deleted date" and "deleted by" columns (may be needed if its still possible to modify something that is 'inactive')

Shameen
  • 2,656
  • 1
  • 14
  • 21