Select for login and update at the same time

2020-02-15 c# mysql sql c#-4.0 sql-update

Is it possible to use SQL command for login and update at the same time? I mean when the Login is done, I want to change logat in 1. Do I have to create a new if with OpenConnection()?

public bool IsLogin(string user, string pass) {
            string query = $"SELECT * from utiliz WHERE username='{user}' AND password='{GetSha1(pass)}'";
            string query_update = $"UPDATE utiliz SET logat='{1}' WHERE username='{user}'";
            try
            {
                if (OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(query, conn);
                    MySqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        reader.Close();
                        conn.Close();
                        return true;
                    }
                    else
                    {
                        reader.Close();
                        conn.Close();
                        return false;
                    }
                }
                else {
                    conn.Close();
                    return false;
                }
            }
            catch (Exception ex) {
                conn.Close();
                return false;
            }
            }

EDITED

Guys, I have edited my code, following parameters procedure. Is it good what I did?

if (String.IsNullOrEmpty(textBox_pass_login.Text) && String.IsNullOrEmpty(textBox_usr_login.Text) || String.IsNullOrEmpty(textBox_usr_login.Text) || String.IsNullOrEmpty(textBox_pass_login.Text))
            {
                System.Windows.Forms.MessageBox.Show("Both fields (username,password) are required");
            }
            else
            {
                string user = textBox_usr_login.Text;
                string password = textBox_pass_login.Text;
                string encryptedpass = GetSha1(password);
                try
                {
                    using (var connection = conn)
                    {
                        string query = "SELECT * from utiliz WHERE [email protected] AND [email protected]";
                        using (var command = new MySqlCommand(query, conn))
                        {
                            command.Parameters.AddWithValue("@user", user);
                            command.Parameters.AddWithValue("@password", encryptedpass);
                            MySqlDataAdapter sda = new MySqlDataAdapter(command);
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            conn.Open();
                            int i = command.ExecuteNonQuery();
                            conn.Close();
                            if (dt.Rows.Count > 0)
                            {
                                this.Hide();
                                var form2 = new Form1();
                                form2.Closed += (s, args) => this.Close();
                                form2.Show();
                            }
                            else
                            {
                                System.Windows.Forms.MessageBox.Show("Wrong credentials");
                                textBox_usr_login.Clear();
                                textBox_pass_login.Clear();
                            }
                        }
                    }
                }
                catch
                {
                    System.Windows.Forms.MessageBox.Show("Wrong credentials");
                    textBox_usr_login.Clear();
                    textBox_pass_login.Clear();
                }    
            }

Answers

There are some important points here. You must use Parameterized queries to improve the performence of your query on the database layer and avoid some problems such as sql injection. You also could use transactions to keep data integrity.

Check the sample bellow with comments (I didn't test this code, may not work properly on your environment):

public bool IsLogin(string user, string pass) 
{
    // prepare the queries with parameters with '@' and parameter name
    const string query = "SELECT count(username) from utiliz WHERE username = @username AND password = @password";
    const string query_update = "UPDATE utiliz SET logat = @logat WHERE username = @username";

    // prepare the encrypted password
    string encryptedPass = GetSha1(pass);

    // use a result variable to use as the function result

    bool result = false;
    try
    {
        if (OpenConnection())
        {
            // start a transaction from the connection object
            using (MySqlTransaction tran = conn.BeginTransaction())
            {
                try    
                {
                    int userFound = 0;
                    // prepare the MySqlCommand to use the query, connection and transaction.
                    using (MySqlCommand userCommand = new MySqlCommand(query, conn, tran))
                    {
                        userCommand.Parameters.AddWithValue("@username", user);
                        userCommand.Parameters.AddWithValue("@password", encryptedPass);

                        userFound = (int) userCommand.ExecuteScalar();
                    }  

                    if (userFound > 0)
                    {
                        // prepare the MySqlCommand to use the query, connection and transaction to update data
                        using (MySqlCommand logatCommand = new MySqlCommand(query_update, conn, tran))
                        {
                            logatCommand.Parameters.AddWithValue("@logat", DateTime.Now);
                            logatCommand.Parameters.AddWithValue("@username", user);                        

                            logatCommand.ExecuteNonQuery();
                        }
                    }  

                    // commit the transaction
                    tran.Commit();

                    result = true;
                }
                catch (Exception ex)
                {
                    // perform some log with ex object. 


                    tran.Rollback();
                }
                finally
                {
                    conn.Close();
                }
            }            
        }
    }
    catch (Exception e)
    {
        // perform some log...

        return false;
    }

    return result;
}

As recommended (and demonstrated) by Felipe Oriani, you should use parameterized queries.

Let me pinpoint, however, that you can do this with a single update query. The trick is to filter the update query on both user name and password:

UPDATE utiliz SET logat = @logat WHERE username = @username AND password = @password

You want to run the query with method ExecuteNonQuery, which returns the number of rows affected.

If credentials are valid, the where cause selects the relevant record and the update happens, returning 1 as the count of records affected. Else, no record is updated, and the method returns 0.

Related