/// <summary> /// Método para atualizar o token refresh guardado na BD /// </summary> /// <param name="refreshToken">Refesh token</param> /// <param name="email">Email do utilizador</param> /// <returns>Retorna True se for atualizado, /// False caso contrário</returns> public bool updateEncryptedRefreshToken(string refreshToken, string email){ try{ using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = "UPDATE dbo.[RefreshTokens] SET TokenHash = @Th, TokenSalt = @Ts " + "WHERE Email = @Em"; cmd.Parameters.Add("@Em", SqlDbType.NVarChar).Value = email; var token = PasswordOperations.Encrypt(refreshToken); cmd.Parameters.Add("@Th", SqlDbType.NVarChar).Value = token.Item2; cmd.Parameters.Add("@Ts", SqlDbType.NVarChar).Value = token.Item1; if(cmd.ExecuteNonQuery() == 0){ return false; } return true; } } catch(Exception e) { throw new Exception(e.Message); } }
/// <summary> /// Método para guardar o token refresh encriptado na BD /// </summary> /// <param name="refreshToken">Refesh token</param> /// <param name="email">Email do utilizador</param> /// <returns>Retorna True se for guardado, /// False caso contrário</returns> public bool saveEncryptedRefreshToken(string refreshToken, string email){ UserDAO userDAO = new UserDAO(_connection); User user = userDAO.FindUserByEmail(email); if(user == null){ throw new Exception("O utilizador não existe"); } try{ using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = "INSERT INTO dbo.[RefreshTokens] (Email, TokenHash, TokenSalt) " + "VALUES (@Em , @Th, @Ts)"; cmd.Parameters.Add("@Em", SqlDbType.NVarChar).Value = email; var token = PasswordOperations.Encrypt(refreshToken); cmd.Parameters.Add("@Th", SqlDbType.NVarChar).Value = token.Item2; cmd.Parameters.Add("@Ts", SqlDbType.NVarChar).Value = token.Item1; if(cmd.ExecuteNonQuery() == 0){ return false; } return true; } } catch(Exception e) { throw new Exception(e.Message); } }
/// <summary> /// Método para criar um Employer /// na Base de dados /// </summary> /// <param name="model">Modelo do employer com os dados</param> /// <returns>Employer caso seja adicionado com sucesso, /// senão retorna NULL</returns> public Employer Create(Employer model) { try { using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; if (model.Description != null) { cmd.CommandText = "INSERT INTO dbo.[User] (UserName, Password, PasswordSalt, Email, Description, FirstName, LastName, RoleId, Address)" + "VALUES (@Un, @Pass, @Slt, @mail, @Desc, @Fn, @Ln, @Rid, @Addr); SELECT @@Identity"; cmd.Parameters.Add("@Desc", SqlDbType.NVarChar).Value = model.Description; } else { cmd.CommandText = "INSERT INTO dbo.[User] (UserName, Password, PasswordSalt, Email, FirstName, LastName, RoleId, Address)" + "VALUES (@Un, @Pass, @Slt, @mail, @Fn, @Ln, @Rid, @Addr); SELECT @@Identity"; } cmd.Parameters.Add("@Un", SqlDbType.NVarChar).Value = model.UserName; var password = PasswordOperations.Encrypt(model.Password); cmd.Parameters.Add("@Pass", SqlDbType.NVarChar).Value = password.Item2; cmd.Parameters.Add("@Slt", SqlDbType.NVarChar).Value = password.Item1; cmd.Parameters.Add("@mail", SqlDbType.NVarChar).Value = model.Email; cmd.Parameters.Add("@Fn", SqlDbType.NVarChar).Value = model.FirstName; cmd.Parameters.Add("@Ln", SqlDbType.NVarChar).Value = model.LastName; cmd.Parameters.Add("@Rid", SqlDbType.Int).Value = Roles.EMPLOYER; cmd.Parameters.Add("@Addr", SqlDbType.NVarChar).Value = model.Address; model.Id = int.Parse(cmd.ExecuteScalar().ToString()); model.Role = Roles.EMPLOYER; model.Password = password.Item2; model.PasswordSalt = password.Item1; } using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = "INSERT INTO dbo.[Employer] (Id)" + "VALUES (@Id); SELECT @@Identity"; cmd.Parameters.Add("@Id", SqlDbType.Int).Value = model.Id; cmd.ExecuteScalar(); } return(model); } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// Método para recuperar a password /// </summary> /// <param name="newPass">Nova Password</param> /// <param name="email">Email do user que vai alterar a password</param> /// <returns>Retorna bool</returns> public bool RecoverPassword(RecoverPasswordModel newPass, string email) { using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = "UPDATE dbo.[User] SET Password = @pass, PasswordSalt = @salt " + "WHERE Email = @email"; cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email; var password = PasswordOperations.Encrypt(newPass.Password); cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = password.Item2; cmd.Parameters.Add("@salt", SqlDbType.NVarChar).Value = password.Item1; newPass.Password = password.Item2; cmd.ExecuteNonQuery(); } return(true); }
/// <summary> /// Método que atualiza a password /// de um determindado utilizador /// </summary> /// <param name="newPass">Nova palavra-passe</param> /// <param name="id">Id do utilizador que pretende alterar /// a sua palavra-passe</param> /// <returns> /// True caso a password seja atualizada com sucesso /// False caso contrário /// </returns> public bool UpdatePassword(PasswordUpdateModel newPass, int?id) { User user = FindById((int)id); if (user == null) { throw new Exception("O utilizador não existe!"); } if (PasswordOperations.VerifyHash(newPass.OldPassword, user.Password, user.PasswordSalt)) { using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = "UPDATE dbo.[User] " + "SET Password = @pass, PasswordSalt = @salt " + "WHERE Id = @id"; cmd.Parameters.Add("@id", SqlDbType.Int).Value = id; var password = PasswordOperations.Encrypt(newPass.Password); cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = password.Item2; cmd.Parameters.Add("@salt", SqlDbType.NVarChar).Value = password.Item1; if (cmd.ExecuteNonQuery() == 0) { return(false); } } return(true); } else { throw new Exception("A password antiga é inválida!"); } }
/// <summary> /// Método para criar/registar um Mate na base de dados /// </summary> /// <param name="model">Modelo do mate com os dados</param> /// <returns>Mate caso seja adicionado com sucesso, /// senão retorna NULL</returns> public Mate Create(Mate model) { try { using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; if (model.Description != null) { cmd.CommandText = "INSERT INTO dbo.[User] (UserName, Password, PasswordSalt, Email, Description, FirstName, LastName, RoleId, Address)" + "VALUES (@Un, @Pass, @Slt, @mail, @Desc, @Fn, @Ln, @Rid, @Addr); SELECT @@Identity"; cmd.Parameters.Add("@Desc", SqlDbType.NVarChar).Value = model.Description; } else { cmd.CommandText = "INSERT INTO dbo.[User] (UserName, Password, PasswordSalt, Email, FirstName, LastName, RoleId, Address)" + "VALUES (@Un, @Pass, @Slt, @mail, @Fn, @Ln, @Rid, @Addr); SELECT @@Identity"; } cmd.Parameters.Add("@Un", SqlDbType.NVarChar).Value = model.UserName; var password = PasswordOperations.Encrypt(model.Password); cmd.Parameters.Add("@Pass", SqlDbType.NVarChar).Value = password.Item2; cmd.Parameters.Add("@Slt", SqlDbType.NVarChar).Value = password.Item1; cmd.Parameters.Add("@mail", SqlDbType.NVarChar).Value = model.Email; cmd.Parameters.Add("@Fn", SqlDbType.NVarChar).Value = model.FirstName; cmd.Parameters.Add("@Ln", SqlDbType.NVarChar).Value = model.LastName; cmd.Parameters.Add("@Rid", SqlDbType.Int).Value = Roles.M8; cmd.Parameters.Add("@Addr", SqlDbType.NVarChar).Value = model.Address; model.Id = int.Parse(cmd.ExecuteScalar().ToString()); model.Role = Roles.M8; model.Password = password.Item2; model.PasswordSalt = password.Item1; model.Rank = Ranks.MATE; } using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = "INSERT INTO dbo.[Mate] (Id, Range, RankId)" + "VALUES (@Id, @Rng, @Rkid); SELECT @@Identity"; cmd.Parameters.Add("@Id", SqlDbType.Int).Value = model.Id; cmd.Parameters.Add("@Rng", SqlDbType.Int).Value = model.Range; cmd.Parameters.Add("Rkid", SqlDbType.Int).Value = model.Rank; cmd.ExecuteScalar(); } foreach (Categories category in model.Categories) { using (SqlCommand cmd = _connection.Fetch().CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = "INSERT INTO dbo.[CategoriesFromM8] (MateId, CategoryId)" + "VALUES (@MtId, @CtgId); SELECT @@Identity"; cmd.Parameters.Add("@MtId", SqlDbType.Int).Value = model.Id; cmd.Parameters.Add("@CtgId", SqlDbType.Int).Value = category; cmd.ExecuteScalar(); } } return(model); } catch (Exception ex) { throw new Exception(ex.Message); } }