// // MembershipProvider.GetPassword // public override string GetPassword(string username, string answer) { if (!EnablePasswordRetrieval) { throw new ProviderException("Password Retrieval Not Enabled."); } if (PasswordFormat == MembershipPasswordFormat.Hashed) { throw new ProviderException("Cannot retrieve Hashed passwords."); } EnsureTableIsCreated(); NuoDbConnection conn = null; string password = ""; string passwordAnswer = ""; DbDataReader reader = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT Password, PasswordAnswer, IsLockedOut " + "FROM Users " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { if (reader.GetBoolean(2)) throw new MembershipPasswordException("The supplied user is locked out."); password = reader.GetString(0); passwordAnswer = reader.GetString(1); } else { throw new MembershipPasswordException("The supplied user name is not found."); } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetPassword"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer)) { UpdateFailureCount(username, "passwordAnswer"); throw new MembershipPasswordException("Incorrect password answer."); } if (PasswordFormat == MembershipPasswordFormat.Encrypted) { password = UnEncodePassword(password); } return password; }
// // MembershipProvider.GetUser(object, bool) // public override MembershipUser GetUser(object providerUserKey, bool userIsOnline) { EnsureTableIsCreated(); NuoDbConnection conn = null; MembershipUser u = null; DbDataReader reader = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," + " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," + " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " + "FROM Users " + "WHERE PKID = ?"; IDbDataParameter pkidParameter = cmd.CreateParameter(); pkidParameter.Value = providerUserKey; pkidParameter.DbType = DbType.String; cmd.Parameters.Add(pkidParameter); reader = cmd.ExecuteReader(); if (reader.Read()) { u = GetUserFromReader(reader); if (userIsOnline) { using (DbCommand updateCmd = conn.CreateCommand()) { updateCmd.CommandText = "UPDATE Users " + "SET LastActivityDate = ? " + "WHERE PKID = ?"; IDbDataParameter lastActivityDateParameter = updateCmd.CreateParameter(); lastActivityDateParameter.Value = DateTime.Now; lastActivityDateParameter.DbType = DbType.DateTime; updateCmd.Parameters.Add(lastActivityDateParameter); updateCmd.Parameters.Add(pkidParameter); updateCmd.ExecuteNonQuery(); } } } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetUser(Object, Boolean)"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } return u; }
// // MembershipProvider.GetAllUsers // public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords) { EnsureTableIsCreated(); NuoDbConnection conn = null; MembershipUserCollection users = new MembershipUserCollection(); DbDataReader reader = null; totalRecords = 0; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT Count(*) " + "FROM Users " + "WHERE ApplicationName = ?"; IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); totalRecords = (int)cmd.ExecuteScalar(); if (totalRecords <= 0) { return users; } cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," + " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," + " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " + "FROM Users " + "WHERE ApplicationName = ? " + "ORDER BY Username Asc"; reader = cmd.ExecuteReader(); int counter = 0; int startIndex = pageSize * pageIndex; int endIndex = startIndex + pageSize - 1; while (reader.Read()) { if (counter >= startIndex) { MembershipUser u = GetUserFromReader(reader); users.Add(u); } if (counter >= endIndex) { break; } counter++; } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetAllUsers "); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } return users; }
// // MembershipProvider.GetNumberOfUsersOnline // public override int GetNumberOfUsersOnline() { TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0); DateTime compareTime = DateTime.Now.Subtract(onlineSpan); EnsureTableIsCreated(); NuoDbConnection conn = null; int numOnline = 0; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT Count(*) " + "FROM Users " + "WHERE LastActivityDate > ? AND ApplicationName = ?"; IDbDataParameter compareDateParameter = cmd.CreateParameter(); compareDateParameter.Value = compareTime; compareDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(compareDateParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); numOnline = (int)cmd.ExecuteScalar(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetNumberOfUsersOnline"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } return numOnline; }
// // MembershipProvider.CreateUser // public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status) { ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true); OnValidatingPassword(args); if (args.Cancel) { status = MembershipCreateStatus.InvalidPassword; return null; } if (RequiresUniqueEmail && GetUserNameByEmail(email) != "") { status = MembershipCreateStatus.DuplicateEmail; return null; } MembershipUser u = GetUser(username, false); if (u == null) { DateTime createDate = DateTime.Now; if (providerUserKey == null) { providerUserKey = Guid.NewGuid(); } else { if (!(providerUserKey is Guid)) { status = MembershipCreateStatus.InvalidProviderUserKey; return null; } } EnsureTableIsCreated(); NuoDbConnection conn = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "INSERT INTO Users " + " (PKID, Username, Password, Email, PasswordQuestion, " + " PasswordAnswer, IsApproved," + " Comment, CreationDate, LastPasswordChangedDate, LastActivityDate," + " ApplicationName, IsLockedOut, LastLockedOutDate," + " FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, " + " FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; IDbDataParameter pkidParameter = cmd.CreateParameter(); pkidParameter.Value = providerUserKey; pkidParameter.DbType = DbType.String; cmd.Parameters.Add(pkidParameter); IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter passwordParameter = cmd.CreateParameter(); passwordParameter.Value = EncodePassword(password); passwordParameter.DbType = DbType.String; cmd.Parameters.Add(passwordParameter); IDbDataParameter emailParameter = cmd.CreateParameter(); emailParameter.Value = email; emailParameter.DbType = DbType.String; cmd.Parameters.Add(emailParameter); IDbDataParameter passwordQuestionParameter = cmd.CreateParameter(); passwordQuestionParameter.Value = passwordQuestion; passwordQuestionParameter.DbType = DbType.String; cmd.Parameters.Add(passwordQuestionParameter); IDbDataParameter passwordAnswerParameter = cmd.CreateParameter(); passwordAnswerParameter.Value = EncodePassword(passwordAnswer); passwordAnswerParameter.DbType = DbType.String; cmd.Parameters.Add(passwordAnswerParameter); IDbDataParameter isApprovedParameter = cmd.CreateParameter(); isApprovedParameter.Value = isApproved; isApprovedParameter.DbType = DbType.Boolean; cmd.Parameters.Add(isApprovedParameter); IDbDataParameter commentParameter = cmd.CreateParameter(); commentParameter.Value = ""; commentParameter.DbType = DbType.String; cmd.Parameters.Add(commentParameter); IDbDataParameter creationDateParameter = cmd.CreateParameter(); creationDateParameter.Value = createDate; creationDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(creationDateParameter); IDbDataParameter lastPasswordChangedDateParameter = cmd.CreateParameter(); lastPasswordChangedDateParameter.Value = createDate; lastPasswordChangedDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(lastPasswordChangedDateParameter); IDbDataParameter lastActivityDateParameter = cmd.CreateParameter(); lastActivityDateParameter.Value = createDate; lastActivityDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(lastActivityDateParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); IDbDataParameter isLockedOutParameter = cmd.CreateParameter(); isLockedOutParameter.Value = false; isLockedOutParameter.DbType = DbType.Boolean; cmd.Parameters.Add(isLockedOutParameter); IDbDataParameter lastLockedOutDateParameter = cmd.CreateParameter(); lastLockedOutDateParameter.Value = createDate; lastLockedOutDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(lastLockedOutDateParameter); IDbDataParameter failedPasswordAttemptCountParameter = cmd.CreateParameter(); failedPasswordAttemptCountParameter.Value = 0; failedPasswordAttemptCountParameter.DbType = DbType.Int32; cmd.Parameters.Add(failedPasswordAttemptCountParameter); IDbDataParameter failedPasswordAttemptWindowStartParameter = cmd.CreateParameter(); failedPasswordAttemptWindowStartParameter.Value = createDate; failedPasswordAttemptWindowStartParameter.DbType = DbType.DateTime; cmd.Parameters.Add(failedPasswordAttemptWindowStartParameter); IDbDataParameter failedPasswordAnswerAttemptCountParameter = cmd.CreateParameter(); failedPasswordAnswerAttemptCountParameter.Value = 0; failedPasswordAnswerAttemptCountParameter.DbType = DbType.Int32; cmd.Parameters.Add(failedPasswordAnswerAttemptCountParameter); IDbDataParameter failedPasswordAnswerAttemptWindowStartParameter = cmd.CreateParameter(); failedPasswordAnswerAttemptWindowStartParameter.Value = createDate; failedPasswordAnswerAttemptWindowStartParameter.DbType = DbType.DateTime; cmd.Parameters.Add(failedPasswordAnswerAttemptWindowStartParameter); int recAdded = cmd.ExecuteNonQuery(); if (recAdded > 0) { status = MembershipCreateStatus.Success; } else { status = MembershipCreateStatus.UserRejected; } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "CreateUser"); } status = MembershipCreateStatus.ProviderError; } finally { conn.Close(); } return GetUser(username, false); } else { status = MembershipCreateStatus.DuplicateUserName; } return null; }
// // MembershipProvider.DeleteUser // public override bool DeleteUser(string username, bool deleteAllRelatedData) { EnsureTableIsCreated(); NuoDbConnection conn = null; int rowsAffected = 0; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "DELETE " + "FROM Users " + "WHERE Username = ? AND Applicationname = ?"; IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); rowsAffected = cmd.ExecuteNonQuery(); if (deleteAllRelatedData) { // Process commands to delete all data for the user in the database. } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "DeleteUser"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } if (rowsAffected > 0) return true; return false; }
// // UpdateFailureCount // A helper method that performs the checks and updates associated with // password failure tracking. // private void UpdateFailureCount(string username, string failureType) { EnsureTableIsCreated(); NuoDbConnection conn = null; DbDataReader reader = null; DateTime windowStart = new DateTime(); int failureCount = 0; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT FailedPasswordAttemptCount, " + " FailedPasswordAttemptWindowStart, " + " FailedPasswordAnswerAttemptCount, " + " FailedPasswordAnswerAttemptWindowStart " + "FROM Users " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.HasRows) { reader.Read(); if (failureType == "password") { failureCount = reader.GetInt32(0); windowStart = reader.GetDateTime(1); } if (failureType == "passwordAnswer") { failureCount = reader.GetInt32(2); windowStart = reader.GetDateTime(3); } } reader.Close(); DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow); if (failureCount == 0 || DateTime.Now > windowEnd) { // First password failure or outside of PasswordAttemptWindow. // Start a new password failure count from 1 and a new window starting now. using (DbCommand updateCmd = conn.CreateCommand()) { if (failureType == "password") updateCmd.CommandText = "UPDATE Users " + "SET FailedPasswordAttemptCount = ?, " + " FailedPasswordAttemptWindowStart = ? " + "WHERE Username = ? AND ApplicationName = ?"; if (failureType == "passwordAnswer") updateCmd.CommandText = "UPDATE Users " + "SET FailedPasswordAnswerAttemptCount = ?, " + " FailedPasswordAnswerAttemptWindowStart = ? " + "WHERE Username = ? AND ApplicationName = ?"; updateCmd.Parameters.Clear(); IDbDataParameter countParameter = updateCmd.CreateParameter(); countParameter.Value = 1; countParameter.DbType = DbType.Int32; updateCmd.Parameters.Add(countParameter); IDbDataParameter windowStartParameter = updateCmd.CreateParameter(); windowStartParameter.Value = DateTime.Now; windowStartParameter.DbType = DbType.DateTime; updateCmd.Parameters.Add(windowStartParameter); IDbDataParameter updateUsernameParameter = updateCmd.CreateParameter(); updateUsernameParameter.Value = username; updateUsernameParameter.DbType = DbType.String; updateCmd.Parameters.Add(updateUsernameParameter); updateCmd.Parameters.Add(applicationNameParameter); if (cmd.ExecuteNonQuery() < 0) throw new ProviderException("Unable to update failure count and window start."); } } else { if (failureCount++ >= MaxInvalidPasswordAttempts) { // Password attempts have exceeded the failure threshold. Lock out // the user. using (DbCommand updateCmd = conn.CreateCommand()) { updateCmd.CommandText = "UPDATE Users " + "SET IsLockedOut = ?, LastLockedOutDate = ? " + "WHERE Username = ? AND ApplicationName = ?"; updateCmd.Parameters.Clear(); IDbDataParameter isLockedOutParameter = updateCmd.CreateParameter(); isLockedOutParameter.Value = true; isLockedOutParameter.DbType = DbType.Boolean; updateCmd.Parameters.Add(isLockedOutParameter); IDbDataParameter lastLockedOutDateParameter = updateCmd.CreateParameter(); lastLockedOutDateParameter.Value = DateTime.Now; lastLockedOutDateParameter.DbType = DbType.DateTime; updateCmd.Parameters.Add(lastLockedOutDateParameter); IDbDataParameter updateUsernameParameter = updateCmd.CreateParameter(); updateUsernameParameter.Value = username; updateUsernameParameter.DbType = DbType.String; updateCmd.Parameters.Add(updateUsernameParameter); updateCmd.Parameters.Add(applicationNameParameter); if (cmd.ExecuteNonQuery() < 0) throw new ProviderException("Unable to lock out user."); } } else { // Password attempts have not exceeded the failure threshold. Update // the failure counts. Leave the window the same. using (DbCommand updateCmd = conn.CreateCommand()) { if (failureType == "password") updateCmd.CommandText = "UPDATE Users " + "SET FailedPasswordAttemptCount = ? " + "WHERE Username = ? AND ApplicationName = ?"; if (failureType == "passwordAnswer") updateCmd.CommandText = "UPDATE Users " + "SET FailedPasswordAnswerAttemptCount = ? " + "WHERE Username = ? AND ApplicationName = ?"; updateCmd.Parameters.Clear(); IDbDataParameter countParameter = updateCmd.CreateParameter(); countParameter.Value = failureCount; countParameter.DbType = DbType.Int32; updateCmd.Parameters.Add(countParameter); IDbDataParameter updateUsernameParameter = updateCmd.CreateParameter(); updateUsernameParameter.Value = username; updateUsernameParameter.DbType = DbType.String; updateCmd.Parameters.Add(updateUsernameParameter); updateCmd.Parameters.Add(applicationNameParameter); if (cmd.ExecuteNonQuery() < 0) throw new ProviderException("Unable to update failure count."); } } } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "UpdateFailureCount"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } }
// // MembershipProvider.ChangePasswordQuestionAndAnswer // public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPwdQuestion, string newPwdAnswer) { if (!ValidateUser(username, password)) return false; int rowsAffected = 0; EnsureTableIsCreated(); NuoDbConnection conn = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "UPDATE Users " + "SET PasswordQuestion = ?, PasswordAnswer = ? " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter questionParameter = cmd.CreateParameter(); questionParameter.Value = newPwdQuestion; questionParameter.DbType = DbType.String; cmd.Parameters.Add(questionParameter); IDbDataParameter passwordParameter = cmd.CreateParameter(); passwordParameter.Value = EncodePassword(newPwdAnswer); passwordParameter.DbType = DbType.String; cmd.Parameters.Add(passwordParameter); IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); rowsAffected = cmd.ExecuteNonQuery(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "ChangePasswordQuestionAndAnswer"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } if (rowsAffected > 0) { return true; } return false; }
// // MembershipProvider.ValidateUser // public override bool ValidateUser(string username, string password) { bool isValid = false; EnsureTableIsCreated(); NuoDbConnection conn = null; DbDataReader reader = null; bool isApproved = false; string pwd = ""; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT Password, IsApproved " + "FROM Users " + "WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False"; IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { pwd = reader.GetString(0); isApproved = reader.GetBoolean(1); } else { return false; } reader.Close(); if (CheckPassword(password, pwd)) { if (isApproved) { isValid = true; using (DbCommand updateCmd = conn.CreateCommand()) { updateCmd.CommandText = "UPDATE Users " + "SET LastLoginDate = ? " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter lastLoginDateParameter = updateCmd.CreateParameter(); lastLoginDateParameter.Value = DateTime.Now; lastLoginDateParameter.DbType = DbType.DateTime; updateCmd.Parameters.Add(lastLoginDateParameter); updateCmd.Parameters.Add(usernameParameter); updateCmd.Parameters.Add(applicationNameParameter); updateCmd.ExecuteNonQuery(); } } else { conn.Close(); UpdateFailureCount(username, "password"); } } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "ValidateUser"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } return isValid; }
// // EnsureTableIsCreated // A helper function that creates the table if it is not yet present // private void EnsureTableIsCreated() { NuoDbConnection conn = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "CREATE TABLE IF NOT EXISTS Users " + " (PKID String NOT NULL PRIMARY KEY, "+ " Username Text (255) NOT NULL, " + " ApplicationName Text (255) NOT NULL, " + " Email Text (128) NOT NULL, " + " Comment Text (255), " + " Password Text (128) NOT NULL, " + " PasswordQuestion Text (255), " + " PasswordAnswer Text (255), " + " IsApproved Boolean, " + " LastActivityDate DateTime, " + " LastLoginDate DateTime, " + " LastPasswordChangedDate DateTime, " + " CreationDate DateTime, " + " IsOnLine Boolean, " + " IsLockedOut Boolean, " + " LastLockedOutDate DateTime, " + " FailedPasswordAttemptCount Integer, " + " FailedPasswordAttemptWindowStart DateTime, " + " FailedPasswordAnswerAttemptCount Integer, " + " FailedPasswordAnswerAttemptWindowStart DateTime " + ")"; cmd.ExecuteNonQuery(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "EnsureTableIsCreated"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } }
// // MembershipProvider.UpdateUser // public override void UpdateUser(MembershipUser user) { EnsureTableIsCreated(); NuoDbConnection conn = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "UPDATE Users " + "SET Email = ?, Comment = ?, IsApproved = ? " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter emailParameter = cmd.CreateParameter(); emailParameter.Value = user.Email; emailParameter.DbType = DbType.String; cmd.Parameters.Add(emailParameter); IDbDataParameter commentParameter = cmd.CreateParameter(); commentParameter.Value = user.Comment; commentParameter.DbType = DbType.String; cmd.Parameters.Add(commentParameter); IDbDataParameter isApprovedParameter = cmd.CreateParameter(); isApprovedParameter.Value = user.IsApproved; isApprovedParameter.DbType = DbType.Boolean; cmd.Parameters.Add(isApprovedParameter); IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = user.UserName; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); cmd.ExecuteNonQuery(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "UpdateUser"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } }
// // System.Web.Security.MembershipProvider methods. // // // MembershipProvider.ChangePassword // public override bool ChangePassword(string username, string oldPwd, string newPwd) { if (!ValidateUser(username, oldPwd)) return false; ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPwd, true); OnValidatingPassword(args); if (args.Cancel) if (args.FailureInformation != null) throw args.FailureInformation; else throw new MembershipPasswordException("Change password canceled due to new password validation failure."); int rowsAffected = 0; EnsureTableIsCreated(); NuoDbConnection conn = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "UPDATE Users " + "SET Password = ?, LastPasswordChangedDate = ? " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter passwordParameter = cmd.CreateParameter(); passwordParameter.Value = EncodePassword(newPwd); passwordParameter.DbType = DbType.String; cmd.Parameters.Add(passwordParameter); IDbDataParameter lastPasswordChangedDataParameter = cmd.CreateParameter(); lastPasswordChangedDataParameter.Value = DateTime.Now; lastPasswordChangedDataParameter.DbType = DbType.DateTime; cmd.Parameters.Add(lastPasswordChangedDataParameter); IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); rowsAffected = cmd.ExecuteNonQuery(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "ChangePassword"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } if (rowsAffected > 0) { return true; } return false; }
// // MembershipProvider.UnlockUser // public override bool UnlockUser(string username) { EnsureTableIsCreated(); NuoDbConnection conn = null; int rowsAffected = 0; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "UPDATE Users " + "SET IsLockedOut = False, LastLockedOutDate = ? " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter lastLockedOutDateParameter = cmd.CreateParameter(); lastLockedOutDateParameter.Value = DateTime.Now; lastLockedOutDateParameter.DbType = DbType.DateTime; cmd.Parameters.Add(lastLockedOutDateParameter); IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); rowsAffected = cmd.ExecuteNonQuery(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "UnlockUser"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } if (rowsAffected > 0) return true; return false; }
// // MembershipProvider.ResetPassword // public override string ResetPassword(string username, string answer) { if (!EnablePasswordReset) { throw new NotSupportedException("Password reset is not enabled."); } if (answer == null && RequiresQuestionAndAnswer) { UpdateFailureCount(username, "passwordAnswer"); throw new ProviderException("Password answer required for password reset."); } string newPassword = System.Web.Security.Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters); ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true); OnValidatingPassword(args); if (args.Cancel) if (args.FailureInformation != null) throw args.FailureInformation; else throw new MembershipPasswordException("Reset password canceled due to password validation failure."); EnsureTableIsCreated(); NuoDbConnection conn = null; int rowsAffected = 0; string passwordAnswer = ""; DbDataReader reader = null; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT PasswordAnswer, IsLockedOut " + "FROM Users " + "WHERE Username = ? AND ApplicationName = ?"; IDbDataParameter usernameParameter = cmd.CreateParameter(); usernameParameter.Value = username; usernameParameter.DbType = DbType.String; cmd.Parameters.Add(usernameParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { if (reader.GetBoolean(1)) throw new MembershipPasswordException("The supplied user is locked out."); passwordAnswer = reader.GetString(0); } else { throw new MembershipPasswordException("The supplied user name is not found."); } if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer)) { UpdateFailureCount(username, "passwordAnswer"); throw new MembershipPasswordException("Incorrect password answer."); } using (DbCommand updateCmd = conn.CreateCommand()) { updateCmd.CommandText = "UPDATE Users " + "SET Password = ?, LastPasswordChangedDate = ? " + "WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False"; IDbDataParameter passwordParameter = updateCmd.CreateParameter(); passwordParameter.Value = EncodePassword(newPassword); passwordParameter.DbType = DbType.String; updateCmd.Parameters.Add(passwordParameter); IDbDataParameter lastPasswordChangedDateParameter = updateCmd.CreateParameter(); lastPasswordChangedDateParameter.Value = DateTime.Now; lastPasswordChangedDateParameter.DbType = DbType.DateTime; updateCmd.Parameters.Add(lastPasswordChangedDateParameter); updateCmd.Parameters.Add(usernameParameter); updateCmd.Parameters.Add(applicationNameParameter); rowsAffected = updateCmd.ExecuteNonQuery(); } } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "ResetPassword"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } if (rowsAffected > 0) { return newPassword; } else { throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset."); } }
// // MembershipProvider.GetUserNameByEmail // public override string GetUserNameByEmail(string email) { EnsureTableIsCreated(); NuoDbConnection conn = null; string username = ""; try { conn = new NuoDbConnection(connectionString); conn.Open(); using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT Username " + "FROM Users " + "WHERE Email = ? AND ApplicationName = ?"; IDbDataParameter emailParameter = cmd.CreateParameter(); emailParameter.Value = email; emailParameter.DbType = DbType.String; cmd.Parameters.Add(emailParameter); IDbDataParameter applicationNameParameter = cmd.CreateParameter(); applicationNameParameter.Value = pApplicationName; applicationNameParameter.DbType = DbType.String; cmd.Parameters.Add(applicationNameParameter); username = (string)cmd.ExecuteScalar(); } } catch (NuoDbSqlException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetUserNameByEmail"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } if (username == null) username = ""; return username; }