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);
}