/mcs/class/System.Web/System.Web.Security/SqliteMembershipProvider.cs
C# | 1639 lines | 1238 code | 246 blank | 155 comment | 142 complexity | 2b79de0dcfce5548cf63c02975fefea9 MD5 | raw file
Possible License(s): GPL-2.0, Unlicense, MPL-2.0-no-copyleft-exception, CC-BY-SA-3.0
- //
- //
- // Permission is hereby granted, free of charge, to any person obtaining
- // a copy of this software and associated documentation files (the
- // "Software"), to deal in the Software without restriction, including
- // without limitation the rights to use, copy, modify, merge, publish,
- // distribute, sublicense, and/or sell copies of the Software, and to
- // permit persons to whom the Software is furnished to do so, subject to
- // the following conditions:
- //
- // The above copyright notice and this permission notice shall be
- // included in all copies or substantial portions of the Software.
- //
- // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
- // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
- // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
- // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
- // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
- // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
- // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
- //
- // Copyright � 2006, 2007 Nauck IT KG http://www.nauck-it.de
- //
- // Author:
- // Daniel Nauck <d.nauck(at)nauck-it.de>
- //
- // Adapted to Sqlite by Marek Habersack <mhabersack@novell.com>
- //
- #if NET_2_0
- using System;
- using System.Data;
- using System.Data.Common;
- using System.Collections.Generic;
- using System.Collections.Specialized;
- using System.Text;
- using System.Security.Cryptography;
- using System.Web.Hosting;
- using System.Web.Configuration;
- using System.Web.Security;
- using System.Configuration;
- using System.Configuration.Provider;
- using System.Diagnostics;
- using Mono.Data.Sqlite;
- namespace System.Web.Security
- {
- internal class SqliteMembershipProvider : MembershipProvider
- {
- const string m_TableName = "Users";
- string m_ConnectionString = string.Empty;
- const int m_NewPasswordLength = 8;
- bool machineKeyIsAutoGenerated;
-
- // Used when determining encryption key values.
- MachineKeySection m_MachineKey = null;
- DbParameter AddParameter (DbCommand command, string parameterName, object parameterValue)
- {
- return AddParameter (command, parameterName, ParameterDirection.Input, parameterValue);
- }
- DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, object parameterValue)
- {
- DbParameter dbp = command.CreateParameter ();
- dbp.ParameterName = parameterName;
- dbp.Value = parameterValue;
- dbp.Direction = direction;
- command.Parameters.Add (dbp);
- return dbp;
- }
-
- /// <summary>
- /// System.Configuration.Provider.ProviderBase.Initialize Method.
- /// </summary>
- public override void Initialize(string name, NameValueCollection config)
- {
- // Initialize values from web.config.
- if (config == null)
- throw new ArgumentNullException("Config", Properties.Resources.ErrArgumentNull);
- if (string.IsNullOrEmpty(name))
- name = Properties.Resources.MembershipProviderDefaultName;
- if (string.IsNullOrEmpty(config["description"]))
- {
- config.Remove("description");
- config.Add("description", Properties.Resources.MembershipProviderDefaultDescription);
- }
- // Initialize the abstract base class.
- base.Initialize(name, config);
- m_ApplicationName = GetConfigValue(config["applicationName"], HostingEnvironment.ApplicationVirtualPath);
- m_MaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
- m_PasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
- m_MinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"));
- m_MinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
- m_PasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""));
- m_EnablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
- m_EnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
- m_RequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
- m_RequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
- // Get password encryption type.
- string pwFormat = GetConfigValue(config["passwordFormat"], "Hashed");
- switch (pwFormat)
- {
- case "Hashed":
- m_PasswordFormat = MembershipPasswordFormat.Hashed;
- break;
- case "Encrypted":
- m_PasswordFormat = MembershipPasswordFormat.Encrypted;
- break;
- case "Clear":
- m_PasswordFormat = MembershipPasswordFormat.Clear;
- break;
- default:
- throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
- }
- // Get connection string.
- string connStrName = config["connectionStringName"];
-
- if (string.IsNullOrEmpty(connStrName))
- {
- throw new ArgumentOutOfRangeException("ConnectionStringName", Properties.Resources.ErrArgumentNullOrEmpty);
- }
- else
- {
- ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[connStrName];
- if (ConnectionStringSettings == null || string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
- {
- throw new ProviderException(Properties.Resources.ErrConnectionStringNullOrEmpty);
- }
- m_ConnectionString = ConnectionStringSettings.ConnectionString;
- }
- // Get encryption and decryption key information from the configuration.
- m_MachineKey = (MachineKeySection)WebConfigurationManager.GetSection("system.web/machineKey", null);
- if (!m_PasswordFormat.Equals(MembershipPasswordFormat.Clear))
- {
- if (m_MachineKey == null)
- throw new ArgumentNullException("system.web/machineKey", Properties.Resources.ErrArgumentNull);
- if (m_MachineKey.ValidationKey.Contains("AutoGenerate")) {
- machineKeyIsAutoGenerated = true;
- if (m_PasswordFormat.Equals (MembershipPasswordFormat.Encrypted))
- throw new ProviderException(Properties.Resources.ErrAutoGeneratedKeyNotSupported);
- }
- }
- }
- /// <summary>
- /// System.Web.Security.MembershipProvider properties.
- /// </summary>
- #region System.Web.Security.MembershipProvider properties
- string m_ApplicationName = string.Empty;
- bool m_EnablePasswordReset = false;
- bool m_EnablePasswordRetrieval = false;
- bool m_RequiresQuestionAndAnswer = false;
- bool m_RequiresUniqueEmail = false;
- int m_MaxInvalidPasswordAttempts = 0;
- int m_PasswordAttemptWindow = 0;
- MembershipPasswordFormat m_PasswordFormat = MembershipPasswordFormat.Clear;
- int m_MinRequiredNonAlphanumericCharacters = 0;
- int m_MinRequiredPasswordLength = 0;
- string m_PasswordStrengthRegularExpression = string.Empty;
- public override string ApplicationName
- {
- get { return m_ApplicationName; }
- set { m_ApplicationName = value; }
- }
- public override bool EnablePasswordReset
- {
- get { return m_EnablePasswordReset; }
- }
- public override bool EnablePasswordRetrieval
- {
- get { return m_EnablePasswordRetrieval; }
- }
- public override bool RequiresQuestionAndAnswer
- {
- get { return m_RequiresQuestionAndAnswer; }
- }
- public override bool RequiresUniqueEmail
- {
- get { return m_RequiresUniqueEmail; }
- }
- public override int MaxInvalidPasswordAttempts
- {
- get { return m_MaxInvalidPasswordAttempts; }
- }
- public override int PasswordAttemptWindow
- {
- get { return m_PasswordAttemptWindow; }
- }
- public override MembershipPasswordFormat PasswordFormat
- {
- get { return m_PasswordFormat; }
- }
- public override int MinRequiredNonAlphanumericCharacters
- {
- get { return m_MinRequiredNonAlphanumericCharacters; }
- }
- public override int MinRequiredPasswordLength
- {
- get { return m_MinRequiredPasswordLength; }
- }
- public override string PasswordStrengthRegularExpression
- {
- get { return m_PasswordStrengthRegularExpression; }
- }
- #endregion
-
- /// <summary>
- /// System.Web.Security.MembershipProvider methods.
- /// </summary>
- #region System.Web.Security.MembershipProvider methods
- /// <summary>
- /// MembershipProvider.ChangePassword
- /// </summary>
- public override bool ChangePassword(string username, string oldPassword, string newPassword)
- {
- if (!ValidateUser(username, oldPassword))
- return false;
- ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
- OnValidatingPassword(args);
- if (args.Cancel)
- {
- if (args.FailureInformation != null)
- throw args.FailureInformation;
- else
- throw new MembershipPasswordException(Properties.Resources.ErrPasswordChangeCanceled);
- }
- int rowsAffected = 0;
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"Password\" = @Password, \"LastPasswordChangedDate\" = @LastPasswordChangedDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@Password", EncodePassword(newPassword));
- AddParameter (dbCommand,"@LastPasswordChangedDate", DateTime.Now);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- rowsAffected = dbCommand.ExecuteNonQuery();
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- if (rowsAffected > 0)
- return true;
- else
- return false;
- }
- /// <summary>
- /// MembershipProvider.ChangePasswordQuestionAndAnswer
- /// </summary>
- public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
- {
- if (!ValidateUser(username, password))
- return false;
- int rowsAffected = 0;
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"PasswordQuestion\" = @PasswordQuestion, \"PasswordAnswer\" = @PasswordAnswer WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@PasswordQuestion", newPasswordQuestion);
- AddParameter (dbCommand,"@PasswordAnswer", EncodePassword(newPasswordAnswer));
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- rowsAffected = dbCommand.ExecuteNonQuery();
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- if (rowsAffected > 0)
- return true;
- else
- return false;
- }
- /// <summary>
- /// MembershipProvider.CreateUser
- /// </summary>
- 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 && string.IsNullOrEmpty(email))
- {
- status = MembershipCreateStatus.InvalidEmail;
- return null;
- }
- if (RequiresUniqueEmail && !string.IsNullOrEmpty(GetUserNameByEmail(email)))
- {
- status = MembershipCreateStatus.DuplicateEmail;
- return null;
- }
- if (GetUser(username, false) == null)
- {
- DateTime createDate = DateTime.Now;
- if (providerUserKey == null)
- {
- providerUserKey = Guid.NewGuid();
- }
- else
- {
- if (!(providerUserKey is Guid))
- {
- status = MembershipCreateStatus.InvalidProviderUserKey;
- return null;
- }
- }
-
- // Create user in database
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("INSERT INTO \"{0}\" (\"pId\", \"Username\", \"Password\", \"Email\", \"PasswordQuestion\", \"PasswordAnswer\", \"IsApproved\", \"CreationDate\", \"LastPasswordChangedDate\", \"LastActivityDate\", \"ApplicationName\", \"IsLockedOut\", \"LastLockedOutDate\", \"FailedPasswordAttemptCount\", \"FailedPasswordAttemptWindowStart\", \"FailedPasswordAnswerAttemptCount\", \"FailedPasswordAnswerAttemptWindowStart\") Values (@pId, @Username, @Password, @Email, @PasswordQuestion, @PasswordAnswer, @IsApproved, @CreationDate, @LastPasswordChangedDate, @LastActivityDate, @ApplicationName, @IsLockedOut, @LastLockedOutDate, @FailedPasswordAttemptCount, @FailedPasswordAttemptWindowStart, @FailedPasswordAnswerAttemptCount, @FailedPasswordAnswerAttemptWindowStart)", m_TableName);
- AddParameter (dbCommand,"@pId", providerUserKey);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@Password", EncodePassword(password));
- AddParameter (dbCommand,"@Email", email);
- AddParameter (dbCommand,"@PasswordQuestion", passwordQuestion);
- AddParameter (dbCommand,"@PasswordAnswer", EncodePassword(passwordAnswer));
- AddParameter (dbCommand,"@IsApproved", isApproved);
- AddParameter (dbCommand,"@CreationDate", createDate);
- AddParameter (dbCommand,"@LastPasswordChangedDate", createDate);
- AddParameter (dbCommand,"@LastActivityDate", createDate);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- AddParameter (dbCommand,"@IsLockedOut", false);
- AddParameter (dbCommand,"@LastLockedOutDate", createDate);
- AddParameter (dbCommand,"@FailedPasswordAttemptCount", 0);
- AddParameter (dbCommand,"@FailedPasswordAttemptWindowStart", createDate);
- AddParameter (dbCommand,"@FailedPasswordAnswerAttemptCount", 0);
- AddParameter (dbCommand,"@FailedPasswordAnswerAttemptWindowStart", createDate);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- if (dbCommand.ExecuteNonQuery() > 0)
- {
- status = MembershipCreateStatus.Success;
- }
- else
- {
- status = MembershipCreateStatus.UserRejected;
- }
- }
- catch (SqliteException e)
- {
- status = MembershipCreateStatus.ProviderError;
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- return GetUser(username, false);
- }
- }
- }
- else
- {
- status = MembershipCreateStatus.DuplicateUserName;
- }
- return null;
- }
- /// <summary>
- /// MembershipProvider.DeleteUser
- /// </summary>
- public override bool DeleteUser(string username, bool deleteAllRelatedData)
- {
- int rowsAffected = 0;
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("DELETE FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- rowsAffected = dbCommand.ExecuteNonQuery();
- if (deleteAllRelatedData)
- {
- // Process commands to delete all data for the user in the database.
- }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- if (rowsAffected > 0)
- return true;
- else
- return false;
- }
- /// <summary>
- /// MembershipProvider.FindUsersByEmail
- /// </summary>
- public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
- {
- totalRecords = 0;
- MembershipUserCollection users = new MembershipUserCollection();
- // replace permitted wildcard characters
- emailToMatch = emailToMatch.Replace('*','%');
- emailToMatch = emailToMatch.Replace('?', '_');
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- // Get user count
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"Email\" LIKE @Email AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@Email", emailToMatch);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
- if (totalRecords <= 0) { return users; }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- // Fetch user from database
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT \"pId\", \"Username\", \"Email\", \"PasswordQuestion\", \"Comment\", \"IsApproved\", \"IsLockedOut\", \"CreationDate\", \"LastLoginDate\", \"LastActivityDate\", \"LastPasswordChangedDate\", \"LastLockedOutDate\" FROM \"{0}\" WHERE \"Email\" LIKE @Email AND \"ApplicationName\" = @ApplicationName ORDER BY \"Username\" ASC LIMIT @MaxCount OFFSET @StartIndex", m_TableName);
- AddParameter (dbCommand,"@Email", emailToMatch);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- AddParameter (dbCommand,"@MaxCount", pageSize);
- AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- using (SqliteDataReader reader = dbCommand.ExecuteReader())
- {
- while (reader.Read())
- {
- MembershipUser u = GetUserFromReader(reader);
- users.Add(u);
- }
- }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- return users;
- }
- /// <summary>
- /// MembershipProvider.FindUsersByName
- /// </summary>
- public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
- {
- totalRecords = 0;
- MembershipUserCollection users = new MembershipUserCollection();
- // replace permitted wildcard characters
- usernameToMatch = usernameToMatch.Replace('*', '%');
- usernameToMatch = usernameToMatch.Replace('?', '_');
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- // Get user count
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"Username\" LIKE @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@Username", usernameToMatch);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
- if (totalRecords <= 0) { return users; }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- // Fetch user from database
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT \"pId\", \"Username\", \"Email\", \"PasswordQuestion\", \"Comment\", \"IsApproved\", \"IsLockedOut\", \"CreationDate\", \"LastLoginDate\", \"LastActivityDate\", \"LastPasswordChangedDate\", \"LastLockedOutDate\" FROM \"{0}\" WHERE \"Username\" LIKE @Username AND \"ApplicationName\" = @ApplicationName ORDER BY \"Username\" ASC LIMIT @MaxCount OFFSET @StartIndex", m_TableName);
- AddParameter (dbCommand,"@Username", usernameToMatch);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- AddParameter (dbCommand,"@MaxCount", pageSize);
- AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- using (SqliteDataReader reader = dbCommand.ExecuteReader())
- {
- while (reader.Read())
- {
- MembershipUser u = GetUserFromReader(reader);
- users.Add(u);
- }
- }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- return users;
- }
- /// <summary>
- /// MembershipProvider.GetAllUsers
- /// </summary>
- public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
- {
- totalRecords = 0;
- MembershipUserCollection users = new MembershipUserCollection();
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- // Get user count
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
- if (totalRecords <= 0) { return users; }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw e;
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- // Fetch user from database
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT \"pId\", \"Username\", \"Email\", \"PasswordQuestion\", \"Comment\", \"IsApproved\", \"IsLockedOut\", \"CreationDate\", \"LastLoginDate\", \"LastActivityDate\", \"LastPasswordChangedDate\", \"LastLockedOutDate\" FROM \"{0}\" WHERE \"ApplicationName\" = @ApplicationName ORDER BY \"Username\" ASC LIMIT @MaxCount OFFSET @StartIndex", m_TableName);
-
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- AddParameter (dbCommand,"@MaxCount", pageSize);
- AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- using (SqliteDataReader reader = dbCommand.ExecuteReader())
- {
- while (reader.Read())
- {
- MembershipUser u = GetUserFromReader(reader);
- users.Add(u);
- }
- }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- return users;
- }
- /// <summary>
- /// MembershipProvider.GetNumberOfUsersOnline
- /// </summary>
- public override int GetNumberOfUsersOnline()
- {
- int numOnline = 0;
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
- DateTime compareTime = DateTime.Now.Subtract(onlineSpan);
- dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"LastActivityDate\" > @CompareTime AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@CompareTime", compareTime);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- numOnline = (int)dbCommand.ExecuteScalar();
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- return numOnline;
- }
- /// <summary>
- /// MembershipProvider.GetPassword
- /// </summary>
- public override string GetPassword(string username, string answer)
- {
- if (!EnablePasswordRetrieval)
- {
- throw new ProviderException(Properties.Resources.ErrPasswordRetrievalNotEnabled);
- }
- if (PasswordFormat == MembershipPasswordFormat.Hashed)
- {
- throw new ProviderException(Properties.Resources.ErrCantRetrieveHashedPw);
- }
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT \"Password\", \"PasswordAnswer\", \"IsLockedOut\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- using (SqliteDataReader reader = dbCommand.ExecuteReader())
- {
- if (reader.HasRows)
- {
- reader.Read();
- string password = reader.GetString(0);
- string passwordAnswer = reader.GetString(1);
- bool isLockedOut = reader.GetBoolean(2);
- reader.Close();
- if (isLockedOut)
- throw new MembershipPasswordException(Properties.Resources.ErrUserIsLoggedOut);
- if (m_RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
- {
- UpdateFailureCount(username, FailureType.PasswordAnswer);
- throw new MembershipPasswordException(Properties.Resources.ErrIncorrectPasswordAnswer);
- }
- if (m_PasswordFormat == MembershipPasswordFormat.Encrypted)
- {
- password = UnEncodePassword(password);
- }
- return password;
- }
- else
- {
- throw new MembershipPasswordException(Properties.Resources.ErrUserNotFound);
- }
- }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- }
- /// <summary>
- /// MembershipProvider.GetUser
- /// </summary>
- public override MembershipUser GetUser(string username, bool userIsOnline)
- {
- MembershipUser u = null;
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT \"pId\", \"Username\", \"Email\", \"PasswordQuestion\", \"Comment\", \"IsApproved\", \"IsLockedOut\", \"CreationDate\", \"LastLoginDate\", \"LastActivityDate\", \"LastPasswordChangedDate\", \"LastLockedOutDate\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- using (SqliteDataReader reader = dbCommand.ExecuteReader())
- {
- if (reader.HasRows)
- {
- reader.Read();
- u = GetUserFromReader(reader);
- if (userIsOnline)
- {
- // Update user online status
- using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
- {
- dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastActivityDate\" = @LastActivityDate WHERE \"pId\" = @pId", m_TableName);
- AddParameter (dbUpdateCommand, "@LastActivityDate", DateTime.Now);
- AddParameter (dbUpdateCommand, "@pId", u.ProviderUserKey);
- dbUpdateCommand.Prepare();
- dbUpdateCommand.ExecuteNonQuery();
- }
- }
- }
- }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- return u;
- }
- /// <summary>
- /// MembershipProvider.GetUser
- /// </summary>
- public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
- {
- MembershipUser u = null;
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT \"pId\", \"Username\", \"Email\", \"PasswordQuestion\", \"Comment\", \"IsApproved\", \"IsLockedOut\", \"CreationDate\", \"LastLoginDate\", \"LastActivityDate\", \"LastPasswordChangedDate\", \"LastLockedOutDate\" FROM \"{0}\" WHERE \"pId\" = @pId", m_TableName);
- AddParameter (dbCommand,"@pId", providerUserKey);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- using (SqliteDataReader reader = dbCommand.ExecuteReader())
- {
- if (reader.HasRows)
- {
- reader.Read();
- u = GetUserFromReader(reader);
- if (userIsOnline)
- {
- // Update user online status
- using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
- {
- dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastActivityDate\" = @LastActivityDate WHERE \"pId\" = @pId", m_TableName);
- AddParameter (dbUpdateCommand, "@LastActivityDate", DateTime.Now);
- AddParameter (dbUpdateCommand, "@pId", u.ProviderUserKey);
- dbUpdateCommand.Prepare();
- dbUpdateCommand.ExecuteNonQuery();
- }
- }
- }
- }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- return u;
- }
- /// <summary>
- /// MembershipProvider.GetUserNameByEmail
- /// </summary>
- public override string GetUserNameByEmail(string email)
- {
- string username = string.Empty;
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT \"Username\" FROM \"{0}\" WHERE \"Email\" = @Email AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@Email", email);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- username = (dbCommand.ExecuteScalar() as string) ?? string.Empty;
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- return username;
- }
- /// <summary>
- /// MembershipProvider.ResetPassword
- /// </summary>
- public override string ResetPassword(string username, string answer)
- {
- if (!m_EnablePasswordReset)
- {
- throw new NotSupportedException(Properties.Resources.ErrPasswordResetNotEnabled);
- }
- if (string.IsNullOrEmpty(answer) && m_RequiresQuestionAndAnswer)
- {
- UpdateFailureCount(username, FailureType.PasswordAnswer);
- throw new ProviderException(Properties.Resources.ErrPasswordAnswerRequired);
- }
- string newPassword = Membership.GeneratePassword(m_NewPasswordLength, m_MinRequiredNonAlphanumericCharacters);
- ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
- OnValidatingPassword(args);
- if (args.Cancel)
- {
- if (args.FailureInformation != null)
- throw args.FailureInformation;
- else
- throw new MembershipPasswordException(Properties.Resources.ErrPasswordResetCanceled);
- }
- int rowsAffected = 0;
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT \"PasswordAnswer\", \"IsLockedOut\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- string passwordAnswer = string.Empty;
- dbConn.Open();
- dbCommand.Prepare();
- using (SqliteDataReader reader = dbCommand.ExecuteReader())
- {
- if (reader.HasRows)
- {
- reader.Read();
- passwordAnswer = reader.GetString(0);
- bool isLockedOut = reader.GetBoolean(1);
- reader.Close();
- if (isLockedOut)
- throw new MembershipPasswordException(Properties.Resources.ErrUserIsLoggedOut);
- if (m_RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
- {
- UpdateFailureCount(username, FailureType.PasswordAnswer);
- throw new MembershipPasswordException(Properties.Resources.ErrIncorrectPasswordAnswer);
- }
- }
- else
- {
- throw new MembershipPasswordException(Properties.Resources.ErrUserNotFound);
- }
- }
- // Reset Password
- using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
- {
- dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"Password\" = @Password, \"LastPasswordChangedDate\" = @LastPasswordChangedDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsLockedOut\" = @IsLockedOut", m_TableName);
- AddParameter (dbUpdateCommand, "@Password", EncodePassword(newPassword));
- AddParameter (dbUpdateCommand, "@LastPasswordChangedDate", DateTime.Now);
- AddParameter (dbUpdateCommand, "@Username", username);
- AddParameter (dbUpdateCommand, "@ApplicationName", m_ApplicationName);
- AddParameter (dbUpdateCommand, "@IsLockedOut", false);
- dbUpdateCommand.Prepare();
- rowsAffected = dbUpdateCommand.ExecuteNonQuery();
- }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- if (rowsAffected > 0)
- return newPassword;
-
- else
- throw new MembershipPasswordException(Properties.Resources.ErrPasswordResetAborted);
- }
- /// <summary>
- /// MembershipProvider.UnlockUser
- /// </summary>
- public override bool UnlockUser(string userName)
- {
- int rowsAffected = 0;
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"IsLockedOut\" = @IsLockedOut, \"LastLockedOutDate\" = @LastLockedOutDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@IsLockedOut", false);
- AddParameter (dbCommand,"@LastLockedOutDate", DateTime.Now);
- AddParameter (dbCommand,"@Username", userName);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- rowsAffected = dbCommand.ExecuteNonQuery();
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- if (rowsAffected > 0)
- return true;
- else
- return false;
- }
- /// <summary>
- /// MembershipProvider.UpdateUser
- /// </summary>
- public override void UpdateUser(MembershipUser user)
- {
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"Email\" = @Email, \"Comment\" = @Comment, \"IsApproved\" = @IsApproved WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@Email", user.Email);
- AddParameter (dbCommand,"@Comment", user.Comment);
- AddParameter (dbCommand,"@IsApproved", user.IsApproved);
- AddParameter (dbCommand,"@Username", user.UserName);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- dbCommand.ExecuteNonQuery();
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- }
- /// <summary>
- /// MembershipProvider.ValidateUser
- /// </summary>
- public override bool ValidateUser(string username, string password)
- {
- string dbPassword = string.Empty;
- bool dbIsApproved = false;
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- // Fetch user data from database
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT \"Password\", \"IsApproved\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsLockedOut\" = @IsLockedOut", m_TableName);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- AddParameter (dbCommand,"@IsLockedOut", false);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- using (SqliteDataReader reader = dbCommand.ExecuteReader())
- {
- if (reader.HasRows)
- {
- reader.Read();
- dbPassword = reader.GetString(0);
- dbIsApproved = reader.GetBoolean(1);
- }
- else
- {
- return false;
- }
- }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- if (CheckPassword(password, dbPassword))
- {
- if (dbIsApproved)
- {
- // Update last login date
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastLoginDate\" = @LastLoginDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@LastLoginDate", DateTime.Now);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- dbCommand.ExecuteNonQuery();
- return true;
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- }
- return false;
- }
- }
- #endregion
- #region private methods
- /// <summary>
- /// A helper function to retrieve config values from the configuration file.
- /// </summary>
- /// <param name="configValue"></param>
- /// <param name="defaultValue"></param>
- /// <returns></returns>
- string GetConfigValue(string configValue, string defaultValue)
- {
- if (string.IsNullOrEmpty(configValue))
- return defaultValue;
- return configValue;
- }
- /// <summary>
- /// A helper function that takes the current row from the SqliteDataReader
- /// and hydrates a MembershipUser from the values. Called by the
- /// MembershipUser.GetUser implementation.
- /// </summary>
- /// <param name="reader">SqliteDataReader object</param>
- /// <returns>MembershipUser object</returns>
- MembershipUser GetUserFromReader(SqliteDataReader reader)
- {
- object providerUserKey = reader.GetValue(0);
- string username = reader.GetString(1);
- string email = string.Empty;
- if (!reader.IsDBNull(2))
- email = reader.GetString(2);
- string passwordQuestion = string.Empty;
- if (!reader.IsDBNull(3))
- passwordQuestion = reader.GetString(3);
-
- string comment = string.Empty;
- if (!reader.IsDBNull(4))
- comment = reader.GetString(4);
- bool isApproved = reader.GetBoolean(5);
- bool isLockedOut = reader.GetBoolean(6);
- DateTime creationDate = reader.GetDateTime(7);
- DateTime lastLoginDate = new DateTime();
- if (!reader.IsDBNull(8))
- lastLoginDate = reader.GetDateTime(8);
- DateTime lastActivityDate = reader.GetDateTime(9);
- DateTime lastPasswordChangedDate = reader.GetDateTime(10);
- DateTime lastLockedOutDate = new DateTime();
- if (!reader.IsDBNull(11))
- lastLockedOutDate = reader.GetDateTime(11);
- MembershipUser u = new MembershipUser(this.Name,
- username,
- providerUserKey,
- email,
- passwordQuestion,
- comment,
- isApproved,
- isLockedOut,
- creationDate,
- lastLoginDate,
- lastActivityDate,
- lastPasswordChangedDate,
- lastLockedOutDate);
-
- return u;
- }
- /// <summary>
- /// Compares password values based on the MembershipPasswordFormat.
- /// </summary>
- /// <param name="password"></param>
- /// <param name="dbpassword"></param>
- /// <returns></returns>
- bool CheckPassword(string password, string dbpassword)
- {
- string pass1 = password;
- string pass2 = dbpassword;
- switch (PasswordFormat)
- {
- case MembershipPasswordFormat.Encrypted:
- pass2 = UnEncodePassword(dbpassword);
- break;
- case MembershipPasswordFormat.Hashed:
- pass1 = EncodePassword(password);
- break;
- default:
- break;
- }
- if (pass1.Equals(pass2))
- return true;
- else
- return false;
- }
- /// <summary>
- /// Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.
- /// </summary>
- /// <param name="password"></param>
- /// <returns></returns>
- string EncodePassword(string password)
- {
- if (string.IsNullOrEmpty(password))
- return password;
- string encodedPassword = password;
- switch (PasswordFormat)
- {
- case MembershipPasswordFormat.Clear:
- break;
- case MembershipPasswordFormat.Encrypted:
- encodedPassword = Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
- break;
- case MembershipPasswordFormat.Hashed:
- HMACSHA1 hash = new HMACSHA1();
- if (machineKeyIsAutoGenerated)
- hash.Key = MachineKeySection.Config.GetValidationKey ();
- else
- hash.Key = HexToByte(m_MachineKey.ValidationKey);
- encodedPassword = Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
- break;
- default:
- throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
- }
- return encodedPassword;
- }
- /// <summary>
- /// Decrypts or leaves the password clear based on the PasswordFormat.
- /// </summary>
- /// <param name="encodedPassword"></param>
- /// <returns></returns>
- string UnEncodePassword(string encodedPassword)
- {
- string password = encodedPassword;
- switch (PasswordFormat)
- {
- case MembershipPasswordFormat.Clear:
- break;
- case MembershipPasswordFormat.Encrypted:
- password = Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
- break;
- case MembershipPasswordFormat.Hashed:
- throw new ProviderException(Properties.Resources.ErrCantDecodeHashedPw);
- default:
- throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
- }
- return password;
- }
- /// <summary>
- /// Converts a hexadecimal string to a byte array. Used to convert encryption
- /// key values from the configuration.
- /// </summary>
- /// <param name="hexString"></param>
- /// <returns></returns>
- byte[] HexToByte(string hexString)
- {
- byte[] returnBytes = new byte[hexString.Length / 2];
- for (int i = 0; i < returnBytes.Length; i++)
- returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
- return returnBytes;
- }
- /// <summary>
- /// A helper method that performs the checks and updates associated with
- /// password failure tracking.
- /// </summary>
- /// <param name="username"></param>
- /// <param name="failType"></param>
- void UpdateFailureCount(string username, FailureType failType)
- {
- DateTime windowStart = new DateTime();
- int failureCount = 0;
- using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
- {
- // Fetch user data from database
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- dbCommand.CommandText = string.Format("SELECT \"FailedPasswordAttemptCount\", \"FailedPasswordAttemptWindowStart\", \"FailedPasswordAnswerAttemptCount\", \"FailedPasswordAnswerAttemptWindowStart\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- try
- {
- dbConn.Open();
- dbCommand.Prepare();
- using (SqliteDataReader reader = dbCommand.ExecuteReader())
- {
- if (reader.HasRows)
- {
- reader.Read();
- if (failType.Equals(FailureType.Password))
- {
- failureCount = reader.GetInt32(0);
- windowStart = reader.GetDateTime(1);
- }
- else if (failType.Equals(FailureType.PasswordAnswer))
- {
- failureCount = reader.GetInt32(2);
- windowStart = reader.GetDateTime(3);
- }
- }
- }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- // Calculate failture count and update database
- using (SqliteCommand dbCommand = dbConn.CreateCommand())
- {
- DateTime windowEnd = windowStart.AddMinutes(m_PasswordAttemptWindow);
- try
- {
- 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.
- if (failType.Equals(FailureType.Password))
- {
- dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAttemptCount\" = @Count, \"FailedPasswordAttemptWindowStart\" = @WindowStart WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- }
- else if (failType.Equals(FailureType.PasswordAnswer))
- {
- dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAnswerAttemptCount\" = @Count, \"FailedPasswordAnswerAttemptWindowStart\" = @WindowStart WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- }
- AddParameter (dbCommand,"@Count", 1);
- AddParameter (dbCommand,"@WindowStart", DateTime.Now);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- if (dbCommand.ExecuteNonQuery() < 0)
- throw new ProviderException(Properties.Resources.ErrCantUpdateFailtureCountAndWindowStart);
- }
- else
- {
- failureCount++;
- if (failureCount >= m_MaxInvalidPasswordAttempts)
- {
- // Password attempts have exceeded the failure threshold. Lock out
- // the user.
- dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"IsLockedOut\" = @IsLockedOut, \"LastLockedOutDate\" = @LastLockedOutDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- AddParameter (dbCommand,"@IsLockedOut", true);
- AddParameter (dbCommand,"@LastLockedOutDate", DateTime.Now);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- if (dbCommand.ExecuteNonQuery() < 0)
- throw new ProviderException(string.Format(Properties.Resources.ErrCantLogoutUser, username));
- }
- else
- {
- // Password attempts have not exceeded the failure threshold. Update
- // the failure counts. Leave the window the same.
- if (failType.Equals(FailureType.Password))
- {
- dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAttemptCount\" = @Count WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- }
- else if (failType.Equals(FailureType.PasswordAnswer))
- {
- dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAnswerAttemptCount\" = @Count WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
- }
- AddParameter (dbCommand,"@Count", failureCount);
- AddParameter (dbCommand,"@Username", username);
- AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
- if (dbCommand.ExecuteNonQuery() < 0)
- throw new ProviderException(Properties.Resources.ErrCantUpdateFailtureCount);
- }
- }
- }
- catch (SqliteException e)
- {
- Trace.WriteLine(e.ToString());
- throw new ProviderException(Properties.Resources.ErrOperationAborted);
- }
- finally
- {
- if (dbConn != null)
- dbConn.Close();
- }
- }
- }
- }
- enum FailureType
- {
- Password,
- PasswordAnswer
- }
- #endregion
- }
- }
- #endif