/// <summary> /// Establishes a database connection to the Dovetail server. /// Developer is responsible to close the connection before function exit. /// </summary> /// <returns>SqlConnection to 'dovetail-db'</returns> public static Boolean TestConnection() { try { SqlConnection connection = DovetailDbConnection.GetConnection(); return(true); } catch { return(false); } }
/// <summary> /// API procedure for removing the specified user into the Dovetail software. /// </summary> /// <param name="username"></param> /// <param name="password"></param> /// <returns>True, if remove successful; false otherwise</returns> public static bool RemoveUser(DovetailUser user) { // Prepare connection to the database SqlConnection connection = DovetailDbConnection.GetConnection(); bool canRemove = false; // Attempt to connect to database and verify user credentials try { // Prepare sign-in query StringBuilder sb = new StringBuilder(); sb.Append("DELETE COUNT(1) "); sb.Append("FROM Users "); sb.Append("WHERE Username = @Username AND Password = @Password AND Access = 1;"); string sql = sb.ToString(); SqlCommand command = new SqlCommand(sql, connection) { CommandType = CommandType.Text }; if (string.IsNullOrEmpty(user.Username) || string.IsNullOrEmpty(user.Password)) { connection.Close(); return(false); } command.Parameters.AddWithValue("@Username", user.Username); command.Parameters.AddWithValue("@Password", user.Password); // Execute query; only one valid user should be found/returned int queryResult = Convert.ToInt32(command.ExecuteScalar()); if (queryResult != 1) { connection.Close(); return(false); } // Sign-in is successful canRemove = true; } catch (SqlException sqle) { connection.Close(); MessageBox.Show(sqle.Message); } connection.Close(); return(canRemove); }
/// <summary> /// API procedure for updating the specified user's privileges to the new user type. /// </summary> /// <param name="username"></param> /// <param name="newUserType"></param> /// <returns>True, if user type updated successfully; false otherwise</returns> public static bool UserUpdateUserType(string username, string newUserType) { SqlConnection connection = DovetailDbConnection.GetConnection(); bool UserTypeUpdatedSuccessfully = false; try { // Prepare register new user query StringBuilder sb = new StringBuilder(); sb.Append("UPDATE Users "); sb.Append("SET UserType = @NewUserType "); sb.Append("WHERE Username = @Username;"); string sql = sb.ToString(); SqlCommand command = new SqlCommand(sql, connection) { CommandType = CommandType.Text }; if (string.IsNullOrEmpty(username) || string.IsNullOrEmpty(newUserType)) { connection.Close(); return(false); } command.Parameters.AddWithValue("@Username", username); command.Parameters.AddWithValue("@NewUserType", newUserType); // Execute query; only one valid user should be updated/returned int result = command.ExecuteNonQuery(); if (result != 1) { connection.Close(); return(false); } // User's email has been successfully updated UserTypeUpdatedSuccessfully = true; } catch (SqlException sqle) { connection.Close(); MessageBox.Show(sqle.Message); } connection.Close(); return(UserTypeUpdatedSuccessfully); }
/// <summary> /// Check if the specified user already exists in the database. /// </summary> /// <param name="username"></param> /// <returns>True, if user already exists; false otherwise</returns> private static bool UserAlreadyExists(string username) { SqlConnection connection = DovetailDbConnection.GetConnection(); bool userAlreadyExists = false; try { // Prepare register new user query StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM Users "); sb.Append("WHERE Username = @Username;"); string sql = sb.ToString(); SqlCommand command = new SqlCommand(sql, connection) { CommandType = CommandType.Text }; command.Parameters.AddWithValue("@Username", username); // Execute query; only one valid user should be found/returned int queryResult = Convert.ToInt32(command.ExecuteScalar()); if (queryResult > 0) { userAlreadyExists = true; } } catch (SqlException sqle) { connection.Close(); MessageBox.Show(sqle.Message); } connection.Close(); return(userAlreadyExists); }
/// <summary> /// API procedure for signing the specified user into the Dovetail software. /// </summary> /// <param name="username"></param> /// <param name="password"></param> /// <returns>True, if sign-in successful; false otherwise</returns> public static bool UserSignIn(DovetailUser user) { // Prepare connection to the database SqlConnection connection = DovetailDbConnection.GetConnection(); bool canSignIn = false; //unhashing hashed password //string savedPasswordHash = user.Password; //byte[] hashBytes = Convert.FromBase64String(savedPasswordHash); //byte[] salt = new byte[16]; //Array.Copy(hashBytes,0,salt,0,16); //var pbkdf2 = new Rfc2898DeriveBytes(user.Password,salt,10000); //byte[] hash = pbkdft.GetBytes(20); //bool same = true; //for(int i = 0; i < 20; i++) { // if(hashBytes[i+16]!=hash[i]) // same = false; // } // Attempt to connect to database and verify user credentials try { // Prepare sign-in query StringBuilder sb = new StringBuilder(); sb.Append("SELECT COUNT(1) "); sb.Append("FROM Users "); sb.Append("WHERE Username = @Username AND Password = @Password AND Access = 1;"); string sql = sb.ToString(); SqlCommand command = new SqlCommand(sql, connection) { CommandType = CommandType.Text }; if (string.IsNullOrEmpty(user.Username) || string.IsNullOrEmpty(user.Password)) { connection.Close(); return(false); } command.Parameters.AddWithValue("@Username", user.Username); command.Parameters.AddWithValue("@Password", user.Password); // Execute query; only one valid user should be found/returned int queryResult = Convert.ToInt32(command.ExecuteScalar()); if (queryResult != 1) { connection.Close(); return(false); } // Sign-in is successful canSignIn = true; } catch (SqlException sqle) { connection.Close(); MessageBox.Show(sqle.Message); } connection.Close(); return(canSignIn); }
/// <summary> /// API procedure for creating/registering a new user for the Dovetail software. /// </summary> /// <param name="user"></param> /// <returns>True, if success; false otherwise</returns> public static bool RegisterNewUser(DovetailUser user) { // Prepare connection to the database SqlConnection connection = DovetailDbConnection.GetConnection(); bool canRegisterUser = false; //byte[] salt; //new RNGCryptoServiceProvider().GetBytes(salt = new byte[16]); //var pbkdf2 = new Rfc2898DeriveBytes(user.Password, salt, 10000); //byte[] hash = pbkdf2.GetBytes(20); //byte[] hashBytes = new byte[36]; //Array.Copy(salt,0,hashBytes,0,16); //Array.Copy(hash,0,hashBytes,16,20); //string savedPasswordHash = Convert.ToBase64String(hashBytes); //todo store with savedPasswordHash, but everything is g2g // Attempt to connect to database and verify user credentials try { // Check if user already exists if (UserAlreadyExists(user.Username)) { connection.Close(); return(false); } // Prepare register new user query StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO Users "); sb.Append("VALUES (@Username, @Password, @FirstName, @LastName, @Email, @UserType, @Access);"); string sql = sb.ToString(); SqlCommand command = new SqlCommand(sql, connection) { CommandType = CommandType.Text }; if (string.IsNullOrEmpty(user.Username) || string.IsNullOrEmpty(user.Password) || string.IsNullOrEmpty(user.FirstName) || string.IsNullOrEmpty(user.LastName) || string.IsNullOrEmpty(user.Email)) { connection.Close(); return(false); } if (!IsValidEmail(user.Email)) { connection.Close(); return(false); } command.Parameters.AddWithValue("@Username", user.Username); command.Parameters.AddWithValue("@Password", user.Password); command.Parameters.AddWithValue("@FirstName", user.FirstName); command.Parameters.AddWithValue("@LastName", user.LastName); command.Parameters.AddWithValue("@Email", user.Email); command.Parameters.AddWithValue("@UserType", user.UserType); command.Parameters.AddWithValue("@Access", user.HasAccess); // Execute query; only one valid user should be created/returned int result = command.ExecuteNonQuery(); if (result != 1) { connection.Close(); return(false); } // New user successfully registered canRegisterUser = true; } catch (SqlException sqle) { connection.Close(); MessageBox.Show(sqle.Message); } connection.Close(); return(canRegisterUser); }