public static void UpdateMode(User user, string mode, SecureString spassword = null) { using (var myConnection = new MySqlConnection { ConnectionString = MyConnectionString }) { myConnection.Open(); using (var myCommand = myConnection.CreateCommand()) { using (var myTrans = myConnection.BeginTransaction()) { try { myCommand.Connection = myConnection; myCommand.Transaction = myTrans; if (mode.Equals("SetPassword")) { var password = SecurePasswordHasher.Hash(SecurePasswordBox.ConvertToUnsecureString(spassword)); myCommand.CommandText = $"UPDATE `User` SET `password`=@password WHERE `username`=@username;"; myCommand.Parameters.AddWithValue("@username", user.Username); myCommand.Parameters.AddWithValue("@password", password); } if (mode.Equals("GetCode")) { myCommand.CommandText = $"UPDATE `User` SET `resetTimes`=@resetTimes WHERE `username`=@username;"; myCommand.Parameters.AddWithValue("@username", user.Username); myCommand.Parameters.AddWithValue("@resetTimes", user.ResetTimes + 1); } myCommand.ExecuteNonQuery(); myTrans.Commit(); } catch (MySqlException e) { try { myTrans.Rollback(); } catch (SqlException ex) { if (myTrans.Connection != null) { Console.WriteLine( $@"An exception of type {ex.GetType()} was encountered while attempting to roll back the transaction."); } } } } } } }
/// <summary> /// Create new user in the database /// </summary> /// <param name="username">the username</param> /// <param name="password">the password</param> /// <returns> /// 1 - Duplicate username /// </returns> public static int CreateNewUser(string username, SecureString spassword, string email) { using (var myConnection = new MySqlConnection { ConnectionString = MyConnectionString }) { myConnection.Open(); using (var myCommand = myConnection.CreateCommand()) { using (var myTrans = myConnection.BeginTransaction()) { try { myCommand.Connection = myConnection; myCommand.Transaction = myTrans; var password = SecurePasswordHasher.Hash(SecurePasswordBox.ConvertToUnsecureString(spassword)); myCommand.CommandText = $"INSERT INTO User (username,password,email) VALUES (@username,@password,@email);"; myCommand.Parameters.AddWithValue("@username", username); myCommand.Parameters.AddWithValue("@password", password); myCommand.Parameters.AddWithValue("@email", email); myCommand.ExecuteNonQuery(); myTrans.Commit(); } catch (MySqlException e) { var message = e.Message; if (message.Contains("Duplicate")) { return(1); } try { myTrans.Rollback(); } catch (SqlException ex) { if (myTrans.Connection != null) { Console.WriteLine( $@"An exception of type {ex.GetType()} was encountered while attempting to roll back the transaction."); } } } } } } return(0); }
public static UserValid VerifyUser(string username, SecureString spassword) { var hashPassword = ""; var user = new UserValid(); using (var myConnection = new MySqlConnection { ConnectionString = MyConnectionString }) { myConnection.Open(); using (var myCommand = myConnection.CreateCommand()) { using (var myTrans = myConnection.BeginTransaction()) { myCommand.Connection = myConnection; myCommand.Transaction = myTrans; try { myCommand.CommandText = "SELECT id, password FROM User WHERE username = @username;"; myCommand.Parameters.AddWithValue("@username", username); using (var reader = myCommand.ExecuteReader()) { while (reader.Read()) { hashPassword = reader.GetString("password"); user.Id = reader.GetInt32("id"); } if (string.IsNullOrWhiteSpace(hashPassword)) { user.Valid = false; return(user); } } } catch (Exception e) { try { myTrans.Rollback(); } catch (SqlException ex) { if (myTrans.Connection != null) { Console.WriteLine( $@"An exception of type {ex.GetType()} was encountered while attempting to roll back the transaction."); } } Console.WriteLine( $@"An exception of type {e.GetType()} was encountered while reading the data."); } } } } user.Valid = SecurePasswordHasher.Verify(SecurePasswordBox.ConvertToUnsecureString(spassword), hashPassword); return(user); }