3 // Permission is hereby granted, free of charge, to any person obtaining
4 // a copy of this software and associated documentation files (the
5 // "Software"), to deal in the Software without restriction, including
6 // without limitation the rights to use, copy, modify, merge, publish,
7 // distribute, sublicense, and/or sell copies of the Software, and to
8 // permit persons to whom the Software is furnished to do so, subject to
9 // the following conditions:
11 // The above copyright notice and this permission notice shall be
12 // included in all copies or substantial portions of the Software.
14 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
15 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
16 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
17 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
18 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
19 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
20 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
22 // Copyright © 2006, 2007 Nauck IT KG http://www.nauck-it.de
25 // Daniel Nauck <d.nauck(at)nauck-it.de>
27 // Adapted to Sqlite by Marek Habersack <mhabersack@novell.com>
32 using System.Data.Common;
33 using System.Collections.Generic;
34 using System.Collections.Specialized;
36 using System.Security.Cryptography;
37 using System.Web.Hosting;
38 using System.Web.Configuration;
39 using System.Web.Security;
40 using System.Configuration;
41 using System.Configuration.Provider;
42 using System.Diagnostics;
44 using Mono.Data.Sqlite;
46 namespace System.Web.Security
48 internal class SqliteMembershipProvider : MembershipProvider
50 const string m_TableName = "Users";
51 string m_ConnectionString = string.Empty;
52 const int m_NewPasswordLength = 8;
53 bool machineKeyIsAutoGenerated;
55 // Used when determining encryption key values.
56 MachineKeySection m_MachineKey = null;
58 DbParameter AddParameter (DbCommand command, string parameterName, object parameterValue)
60 return AddParameter (command, parameterName, ParameterDirection.Input, parameterValue);
63 DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, object parameterValue)
65 DbParameter dbp = command.CreateParameter ();
66 dbp.ParameterName = parameterName;
67 dbp.Value = parameterValue;
68 dbp.Direction = direction;
69 command.Parameters.Add (dbp);
74 /// System.Configuration.Provider.ProviderBase.Initialize Method.
76 public override void Initialize(string name, NameValueCollection config)
78 // Initialize values from web.config.
80 throw new ArgumentNullException("Config", Properties.Resources.ErrArgumentNull);
82 if (string.IsNullOrEmpty(name))
83 name = Properties.Resources.MembershipProviderDefaultName;
85 if (string.IsNullOrEmpty(config["description"]))
87 config.Remove("description");
88 config.Add("description", Properties.Resources.MembershipProviderDefaultDescription);
91 // Initialize the abstract base class.
92 base.Initialize(name, config);
94 m_ApplicationName = GetConfigValue(config["applicationName"], HostingEnvironment.ApplicationVirtualPath);
95 m_MaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
96 m_PasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
97 m_MinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"));
98 m_MinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
99 m_PasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""));
100 m_EnablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
101 m_EnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
102 m_RequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
103 m_RequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
105 // Get password encryption type.
106 string pwFormat = GetConfigValue(config["passwordFormat"], "Hashed");
110 m_PasswordFormat = MembershipPasswordFormat.Hashed;
113 m_PasswordFormat = MembershipPasswordFormat.Encrypted;
116 m_PasswordFormat = MembershipPasswordFormat.Clear;
119 throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
122 // Get connection string.
123 string connStrName = config["connectionStringName"];
125 if (string.IsNullOrEmpty(connStrName))
127 throw new ArgumentOutOfRangeException("ConnectionStringName", Properties.Resources.ErrArgumentNullOrEmpty);
131 ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[connStrName];
133 if (ConnectionStringSettings == null || string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
135 throw new ProviderException(Properties.Resources.ErrConnectionStringNullOrEmpty);
138 m_ConnectionString = ConnectionStringSettings.ConnectionString;
141 // Get encryption and decryption key information from the configuration.
142 m_MachineKey = (MachineKeySection)WebConfigurationManager.GetSection("system.web/machineKey", null);
144 if (!m_PasswordFormat.Equals(MembershipPasswordFormat.Clear))
146 if (m_MachineKey == null)
147 throw new ArgumentNullException("system.web/machineKey", Properties.Resources.ErrArgumentNull);
149 if (m_MachineKey.ValidationKey.Contains("AutoGenerate")) {
150 machineKeyIsAutoGenerated = true;
151 if (m_PasswordFormat.Equals (MembershipPasswordFormat.Encrypted))
152 throw new ProviderException(Properties.Resources.ErrAutoGeneratedKeyNotSupported);
158 /// System.Web.Security.MembershipProvider properties.
160 #region System.Web.Security.MembershipProvider properties
161 string m_ApplicationName = string.Empty;
162 bool m_EnablePasswordReset = false;
163 bool m_EnablePasswordRetrieval = false;
164 bool m_RequiresQuestionAndAnswer = false;
165 bool m_RequiresUniqueEmail = false;
166 int m_MaxInvalidPasswordAttempts = 0;
167 int m_PasswordAttemptWindow = 0;
168 MembershipPasswordFormat m_PasswordFormat = MembershipPasswordFormat.Clear;
169 int m_MinRequiredNonAlphanumericCharacters = 0;
170 int m_MinRequiredPasswordLength = 0;
171 string m_PasswordStrengthRegularExpression = string.Empty;
173 public override string ApplicationName
175 get { return m_ApplicationName; }
176 set { m_ApplicationName = value; }
179 public override bool EnablePasswordReset
181 get { return m_EnablePasswordReset; }
184 public override bool EnablePasswordRetrieval
186 get { return m_EnablePasswordRetrieval; }
189 public override bool RequiresQuestionAndAnswer
191 get { return m_RequiresQuestionAndAnswer; }
194 public override bool RequiresUniqueEmail
196 get { return m_RequiresUniqueEmail; }
199 public override int MaxInvalidPasswordAttempts
201 get { return m_MaxInvalidPasswordAttempts; }
204 public override int PasswordAttemptWindow
206 get { return m_PasswordAttemptWindow; }
209 public override MembershipPasswordFormat PasswordFormat
211 get { return m_PasswordFormat; }
214 public override int MinRequiredNonAlphanumericCharacters
216 get { return m_MinRequiredNonAlphanumericCharacters; }
219 public override int MinRequiredPasswordLength
221 get { return m_MinRequiredPasswordLength; }
224 public override string PasswordStrengthRegularExpression
226 get { return m_PasswordStrengthRegularExpression; }
232 /// System.Web.Security.MembershipProvider methods.
234 #region System.Web.Security.MembershipProvider methods
237 /// MembershipProvider.ChangePassword
239 public override bool ChangePassword(string username, string oldPassword, string newPassword)
241 if (!ValidateUser(username, oldPassword))
244 ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
246 OnValidatingPassword(args);
250 if (args.FailureInformation != null)
251 throw args.FailureInformation;
253 throw new MembershipPasswordException(Properties.Resources.ErrPasswordChangeCanceled);
256 int rowsAffected = 0;
258 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
260 using (SqliteCommand dbCommand = dbConn.CreateCommand())
262 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"Password\" = @Password, \"LastPasswordChangedDate\" = @LastPasswordChangedDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
264 AddParameter (dbCommand,"@Password", EncodePassword(newPassword));
265 AddParameter (dbCommand,"@LastPasswordChangedDate", DateTime.Now);
266 AddParameter (dbCommand,"@Username", username);
267 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
274 rowsAffected = dbCommand.ExecuteNonQuery();
276 catch (SqliteException e)
278 Trace.WriteLine(e.ToString());
279 throw new ProviderException(Properties.Resources.ErrOperationAborted);
289 if (rowsAffected > 0)
296 /// MembershipProvider.ChangePasswordQuestionAndAnswer
298 public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
300 if (!ValidateUser(username, password))
303 int rowsAffected = 0;
305 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
307 using (SqliteCommand dbCommand = dbConn.CreateCommand())
309 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"PasswordQuestion\" = @PasswordQuestion, \"PasswordAnswer\" = @PasswordAnswer WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
311 AddParameter (dbCommand,"@PasswordQuestion", newPasswordQuestion);
312 AddParameter (dbCommand,"@PasswordAnswer", EncodePassword(newPasswordAnswer));
313 AddParameter (dbCommand,"@Username", username);
314 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
321 rowsAffected = dbCommand.ExecuteNonQuery();
323 catch (SqliteException e)
325 Trace.WriteLine(e.ToString());
326 throw new ProviderException(Properties.Resources.ErrOperationAborted);
336 if (rowsAffected > 0)
343 /// MembershipProvider.CreateUser
345 public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved,
346 object providerUserKey, out MembershipCreateStatus status)
348 ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);
350 OnValidatingPassword(args);
354 status = MembershipCreateStatus.InvalidPassword;
358 if (RequiresUniqueEmail && string.IsNullOrEmpty(email))
360 status = MembershipCreateStatus.InvalidEmail;
364 if (RequiresUniqueEmail && !string.IsNullOrEmpty(GetUserNameByEmail(email)))
366 status = MembershipCreateStatus.DuplicateEmail;
370 if (GetUser(username, false) == null)
372 DateTime createDate = DateTime.Now;
374 if (providerUserKey == null)
376 providerUserKey = Guid.NewGuid();
380 if (!(providerUserKey is Guid))
382 status = MembershipCreateStatus.InvalidProviderUserKey;
387 // Create user in database
388 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
390 using (SqliteCommand dbCommand = dbConn.CreateCommand())
392 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);
394 AddParameter (dbCommand,"@pId", providerUserKey);
395 AddParameter (dbCommand,"@Username", username);
396 AddParameter (dbCommand,"@Password", EncodePassword(password));
397 AddParameter (dbCommand,"@Email", email);
398 AddParameter (dbCommand,"@PasswordQuestion", passwordQuestion);
399 AddParameter (dbCommand,"@PasswordAnswer", EncodePassword(passwordAnswer));
400 AddParameter (dbCommand,"@IsApproved", isApproved);
401 AddParameter (dbCommand,"@CreationDate", createDate);
402 AddParameter (dbCommand,"@LastPasswordChangedDate", createDate);
403 AddParameter (dbCommand,"@LastActivityDate", createDate);
404 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
405 AddParameter (dbCommand,"@IsLockedOut", false);
406 AddParameter (dbCommand,"@LastLockedOutDate", createDate);
407 AddParameter (dbCommand,"@FailedPasswordAttemptCount", 0);
408 AddParameter (dbCommand,"@FailedPasswordAttemptWindowStart", createDate);
409 AddParameter (dbCommand,"@FailedPasswordAnswerAttemptCount", 0);
410 AddParameter (dbCommand,"@FailedPasswordAnswerAttemptWindowStart", createDate);
417 if (dbCommand.ExecuteNonQuery() > 0)
419 status = MembershipCreateStatus.Success;
423 status = MembershipCreateStatus.UserRejected;
426 catch (SqliteException e)
428 status = MembershipCreateStatus.ProviderError;
429 Trace.WriteLine(e.ToString());
430 throw new ProviderException(Properties.Resources.ErrOperationAborted);
438 return GetUser(username, false);
444 status = MembershipCreateStatus.DuplicateUserName;
450 /// MembershipProvider.DeleteUser
452 public override bool DeleteUser(string username, bool deleteAllRelatedData)
454 int rowsAffected = 0;
456 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
458 using (SqliteCommand dbCommand = dbConn.CreateCommand())
460 dbCommand.CommandText = string.Format("DELETE FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
462 AddParameter (dbCommand,"@Username", username);
463 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
470 rowsAffected = dbCommand.ExecuteNonQuery();
472 if (deleteAllRelatedData)
474 // Process commands to delete all data for the user in the database.
477 catch (SqliteException e)
479 Trace.WriteLine(e.ToString());
480 throw new ProviderException(Properties.Resources.ErrOperationAborted);
490 if (rowsAffected > 0)
497 /// MembershipProvider.FindUsersByEmail
499 public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
502 MembershipUserCollection users = new MembershipUserCollection();
504 // replace permitted wildcard characters
505 emailToMatch = emailToMatch.Replace('*','%');
506 emailToMatch = emailToMatch.Replace('?', '_');
508 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
511 using (SqliteCommand dbCommand = dbConn.CreateCommand())
513 dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"Email\" LIKE @Email AND \"ApplicationName\" = @ApplicationName", m_TableName);
515 AddParameter (dbCommand,"@Email", emailToMatch);
516 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
523 Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
525 if (totalRecords <= 0) { return users; }
527 catch (SqliteException e)
529 Trace.WriteLine(e.ToString());
530 throw new ProviderException(Properties.Resources.ErrOperationAborted);
539 // Fetch user from database
540 using (SqliteCommand dbCommand = dbConn.CreateCommand())
542 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);
544 AddParameter (dbCommand,"@Email", emailToMatch);
545 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
546 AddParameter (dbCommand,"@MaxCount", pageSize);
547 AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
554 using (SqliteDataReader reader = dbCommand.ExecuteReader())
556 while (reader.Read())
558 MembershipUser u = GetUserFromReader(reader);
563 catch (SqliteException e)
565 Trace.WriteLine(e.ToString());
566 throw new ProviderException(Properties.Resources.ErrOperationAborted);
580 /// MembershipProvider.FindUsersByName
582 public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
585 MembershipUserCollection users = new MembershipUserCollection();
587 // replace permitted wildcard characters
588 usernameToMatch = usernameToMatch.Replace('*', '%');
589 usernameToMatch = usernameToMatch.Replace('?', '_');
591 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
594 using (SqliteCommand dbCommand = dbConn.CreateCommand())
596 dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"Username\" LIKE @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
598 AddParameter (dbCommand,"@Username", usernameToMatch);
599 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
606 Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
608 if (totalRecords <= 0) { return users; }
610 catch (SqliteException e)
612 Trace.WriteLine(e.ToString());
613 throw new ProviderException(Properties.Resources.ErrOperationAborted);
622 // Fetch user from database
623 using (SqliteCommand dbCommand = dbConn.CreateCommand())
625 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);
627 AddParameter (dbCommand,"@Username", usernameToMatch);
628 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
629 AddParameter (dbCommand,"@MaxCount", pageSize);
630 AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
637 using (SqliteDataReader reader = dbCommand.ExecuteReader())
639 while (reader.Read())
641 MembershipUser u = GetUserFromReader(reader);
646 catch (SqliteException e)
648 Trace.WriteLine(e.ToString());
649 throw new ProviderException(Properties.Resources.ErrOperationAborted);
663 /// MembershipProvider.GetAllUsers
665 public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
668 MembershipUserCollection users = new MembershipUserCollection();
670 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
673 using (SqliteCommand dbCommand = dbConn.CreateCommand())
675 dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"ApplicationName\" = @ApplicationName", m_TableName);
677 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
684 Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
686 if (totalRecords <= 0) { return users; }
688 catch (SqliteException e)
690 Trace.WriteLine(e.ToString());
700 // Fetch user from database
701 using (SqliteCommand dbCommand = dbConn.CreateCommand())
703 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);
705 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
706 AddParameter (dbCommand,"@MaxCount", pageSize);
707 AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
714 using (SqliteDataReader reader = dbCommand.ExecuteReader())
716 while (reader.Read())
718 MembershipUser u = GetUserFromReader(reader);
723 catch (SqliteException e)
725 Trace.WriteLine(e.ToString());
726 throw new ProviderException(Properties.Resources.ErrOperationAborted);
740 /// MembershipProvider.GetNumberOfUsersOnline
742 public override int GetNumberOfUsersOnline()
746 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
748 using (SqliteCommand dbCommand = dbConn.CreateCommand())
750 TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
751 DateTime compareTime = DateTime.Now.Subtract(onlineSpan);
753 dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"LastActivityDate\" > @CompareTime AND \"ApplicationName\" = @ApplicationName", m_TableName);
755 AddParameter (dbCommand,"@CompareTime", compareTime);
756 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
763 numOnline = (int)dbCommand.ExecuteScalar();
765 catch (SqliteException e)
767 Trace.WriteLine(e.ToString());
768 throw new ProviderException(Properties.Resources.ErrOperationAborted);
782 /// MembershipProvider.GetPassword
784 public override string GetPassword(string username, string answer)
786 if (!EnablePasswordRetrieval)
788 throw new ProviderException(Properties.Resources.ErrPasswordRetrievalNotEnabled);
791 if (PasswordFormat == MembershipPasswordFormat.Hashed)
793 throw new ProviderException(Properties.Resources.ErrCantRetrieveHashedPw);
796 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
798 using (SqliteCommand dbCommand = dbConn.CreateCommand())
800 dbCommand.CommandText = string.Format("SELECT \"Password\", \"PasswordAnswer\", \"IsLockedOut\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
802 AddParameter (dbCommand,"@Username", username);
803 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
810 using (SqliteDataReader reader = dbCommand.ExecuteReader())
816 string password = reader.GetString(0);
817 string passwordAnswer = reader.GetString(1);
818 bool isLockedOut = reader.GetBoolean(2);
823 throw new MembershipPasswordException(Properties.Resources.ErrUserIsLoggedOut);
825 if (m_RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
827 UpdateFailureCount(username, FailureType.PasswordAnswer);
829 throw new MembershipPasswordException(Properties.Resources.ErrIncorrectPasswordAnswer);
832 if (m_PasswordFormat == MembershipPasswordFormat.Encrypted)
834 password = UnEncodePassword(password);
841 throw new MembershipPasswordException(Properties.Resources.ErrUserNotFound);
845 catch (SqliteException e)
847 Trace.WriteLine(e.ToString());
848 throw new ProviderException(Properties.Resources.ErrOperationAborted);
860 /// MembershipProvider.GetUser
862 public override MembershipUser GetUser(string username, bool userIsOnline)
864 MembershipUser u = null;
866 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
868 using (SqliteCommand dbCommand = dbConn.CreateCommand())
870 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);
872 AddParameter (dbCommand,"@Username", username);
873 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
880 using (SqliteDataReader reader = dbCommand.ExecuteReader())
885 u = GetUserFromReader(reader);
889 // Update user online status
890 using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
892 dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastActivityDate\" = @LastActivityDate WHERE \"pId\" = @pId", m_TableName);
894 AddParameter (dbUpdateCommand, "@LastActivityDate", DateTime.Now);
895 AddParameter (dbUpdateCommand, "@pId", u.ProviderUserKey);
897 dbUpdateCommand.Prepare();
899 dbUpdateCommand.ExecuteNonQuery();
905 catch (SqliteException e)
907 Trace.WriteLine(e.ToString());
908 throw new ProviderException(Properties.Resources.ErrOperationAborted);
922 /// MembershipProvider.GetUser
924 public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
926 MembershipUser u = null;
928 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
930 using (SqliteCommand dbCommand = dbConn.CreateCommand())
932 dbCommand.CommandText = string.Format("SELECT \"pId\", \"Username\", \"Email\", \"PasswordQuestion\", \"Comment\", \"IsApproved\", \"IsLockedOut\", \"CreationDate\", \"LastLoginDate\", \"LastActivityDate\", \"LastPasswordChangedDate\", \"LastLockedOutDate\" FROM \"{0}\" WHERE \"pId\" = @pId", m_TableName);
934 AddParameter (dbCommand,"@pId", providerUserKey);
941 using (SqliteDataReader reader = dbCommand.ExecuteReader())
946 u = GetUserFromReader(reader);
950 // Update user online status
951 using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
953 dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastActivityDate\" = @LastActivityDate WHERE \"pId\" = @pId", m_TableName);
955 AddParameter (dbUpdateCommand, "@LastActivityDate", DateTime.Now);
956 AddParameter (dbUpdateCommand, "@pId", u.ProviderUserKey);
958 dbUpdateCommand.Prepare();
960 dbUpdateCommand.ExecuteNonQuery();
966 catch (SqliteException e)
968 Trace.WriteLine(e.ToString());
969 throw new ProviderException(Properties.Resources.ErrOperationAborted);
983 /// MembershipProvider.GetUserNameByEmail
985 public override string GetUserNameByEmail(string email)
987 string username = string.Empty;
989 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
991 using (SqliteCommand dbCommand = dbConn.CreateCommand())
993 dbCommand.CommandText = string.Format("SELECT \"Username\" FROM \"{0}\" WHERE \"Email\" = @Email AND \"ApplicationName\" = @ApplicationName", m_TableName);
995 AddParameter (dbCommand,"@Email", email);
996 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1001 dbCommand.Prepare();
1003 username = (dbCommand.ExecuteScalar() as string) ?? string.Empty;
1005 catch (SqliteException e)
1007 Trace.WriteLine(e.ToString());
1008 throw new ProviderException(Properties.Resources.ErrOperationAborted);
1022 /// MembershipProvider.ResetPassword
1024 public override string ResetPassword(string username, string answer)
1026 if (!m_EnablePasswordReset)
1028 throw new NotSupportedException(Properties.Resources.ErrPasswordResetNotEnabled);
1031 if (string.IsNullOrEmpty(answer) && m_RequiresQuestionAndAnswer)
1033 UpdateFailureCount(username, FailureType.PasswordAnswer);
1035 throw new ProviderException(Properties.Resources.ErrPasswordAnswerRequired);
1038 string newPassword = Membership.GeneratePassword(m_NewPasswordLength, m_MinRequiredNonAlphanumericCharacters);
1041 ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
1043 OnValidatingPassword(args);
1047 if (args.FailureInformation != null)
1048 throw args.FailureInformation;
1050 throw new MembershipPasswordException(Properties.Resources.ErrPasswordResetCanceled);
1053 int rowsAffected = 0;
1055 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
1057 using (SqliteCommand dbCommand = dbConn.CreateCommand())
1059 dbCommand.CommandText = string.Format("SELECT \"PasswordAnswer\", \"IsLockedOut\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1061 AddParameter (dbCommand,"@Username", username);
1062 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1066 string passwordAnswer = string.Empty;
1069 dbCommand.Prepare();
1071 using (SqliteDataReader reader = dbCommand.ExecuteReader())
1077 passwordAnswer = reader.GetString(0);
1078 bool isLockedOut = reader.GetBoolean(1);
1083 throw new MembershipPasswordException(Properties.Resources.ErrUserIsLoggedOut);
1085 if (m_RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
1087 UpdateFailureCount(username, FailureType.PasswordAnswer);
1089 throw new MembershipPasswordException(Properties.Resources.ErrIncorrectPasswordAnswer);
1094 throw new MembershipPasswordException(Properties.Resources.ErrUserNotFound);
1099 using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
1101 dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"Password\" = @Password, \"LastPasswordChangedDate\" = @LastPasswordChangedDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsLockedOut\" = @IsLockedOut", m_TableName);
1103 AddParameter (dbUpdateCommand, "@Password", EncodePassword(newPassword));
1104 AddParameter (dbUpdateCommand, "@LastPasswordChangedDate", DateTime.Now);
1105 AddParameter (dbUpdateCommand, "@Username", username);
1106 AddParameter (dbUpdateCommand, "@ApplicationName", m_ApplicationName);
1107 AddParameter (dbUpdateCommand, "@IsLockedOut", false);
1109 dbUpdateCommand.Prepare();
1111 rowsAffected = dbUpdateCommand.ExecuteNonQuery();
1115 catch (SqliteException e)
1117 Trace.WriteLine(e.ToString());
1118 throw new ProviderException(Properties.Resources.ErrOperationAborted);
1128 if (rowsAffected > 0)
1132 throw new MembershipPasswordException(Properties.Resources.ErrPasswordResetAborted);
1136 /// MembershipProvider.UnlockUser
1138 public override bool UnlockUser(string userName)
1140 int rowsAffected = 0;
1142 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
1144 using (SqliteCommand dbCommand = dbConn.CreateCommand())
1146 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"IsLockedOut\" = @IsLockedOut, \"LastLockedOutDate\" = @LastLockedOutDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1148 AddParameter (dbCommand,"@IsLockedOut", false);
1149 AddParameter (dbCommand,"@LastLockedOutDate", DateTime.Now);
1150 AddParameter (dbCommand,"@Username", userName);
1151 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1156 dbCommand.Prepare();
1158 rowsAffected = dbCommand.ExecuteNonQuery();
1160 catch (SqliteException e)
1162 Trace.WriteLine(e.ToString());
1163 throw new ProviderException(Properties.Resources.ErrOperationAborted);
1173 if (rowsAffected > 0)
1181 /// MembershipProvider.UpdateUser
1183 public override void UpdateUser(MembershipUser user)
1185 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
1187 using (SqliteCommand dbCommand = dbConn.CreateCommand())
1189 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"Email\" = @Email, \"Comment\" = @Comment, \"IsApproved\" = @IsApproved WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1191 AddParameter (dbCommand,"@Email", user.Email);
1192 AddParameter (dbCommand,"@Comment", user.Comment);
1193 AddParameter (dbCommand,"@IsApproved", user.IsApproved);
1194 AddParameter (dbCommand,"@Username", user.UserName);
1195 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1200 dbCommand.Prepare();
1202 dbCommand.ExecuteNonQuery();
1204 catch (SqliteException e)
1206 Trace.WriteLine(e.ToString());
1207 throw new ProviderException(Properties.Resources.ErrOperationAborted);
1219 /// MembershipProvider.ValidateUser
1221 public override bool ValidateUser(string username, string password)
1223 string dbPassword = string.Empty;
1224 bool dbIsApproved = false;
1226 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
1228 // Fetch user data from database
1229 using (SqliteCommand dbCommand = dbConn.CreateCommand())
1231 dbCommand.CommandText = string.Format("SELECT \"Password\", \"IsApproved\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsLockedOut\" = @IsLockedOut", m_TableName);
1233 AddParameter (dbCommand,"@Username", username);
1234 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1235 AddParameter (dbCommand,"@IsLockedOut", false);
1240 dbCommand.Prepare();
1242 using (SqliteDataReader reader = dbCommand.ExecuteReader())
1247 dbPassword = reader.GetString(0);
1248 dbIsApproved = reader.GetBoolean(1);
1256 catch (SqliteException e)
1258 Trace.WriteLine(e.ToString());
1259 throw new ProviderException(Properties.Resources.ErrOperationAborted);
1268 if (CheckPassword(password, dbPassword))
1272 // Update last login date
1273 using (SqliteCommand dbCommand = dbConn.CreateCommand())
1275 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastLoginDate\" = @LastLoginDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1277 AddParameter (dbCommand,"@LastLoginDate", DateTime.Now);
1278 AddParameter (dbCommand,"@Username", username);
1279 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1284 dbCommand.Prepare();
1286 dbCommand.ExecuteNonQuery();
1290 catch (SqliteException e)
1292 Trace.WriteLine(e.ToString());
1293 throw new ProviderException(Properties.Resources.ErrOperationAborted);
1309 #region private methods
1311 /// A helper function to retrieve config values from the configuration file.
1313 /// <param name="configValue"></param>
1314 /// <param name="defaultValue"></param>
1315 /// <returns></returns>
1316 string GetConfigValue(string configValue, string defaultValue)
1318 if (string.IsNullOrEmpty(configValue))
1319 return defaultValue;
1325 /// A helper function that takes the current row from the SqliteDataReader
1326 /// and hydrates a MembershipUser from the values. Called by the
1327 /// MembershipUser.GetUser implementation.
1329 /// <param name="reader">SqliteDataReader object</param>
1330 /// <returns>MembershipUser object</returns>
1331 MembershipUser GetUserFromReader(SqliteDataReader reader)
1333 object providerUserKey = reader.GetValue(0);
1334 string username = reader.GetString(1);
1335 string email = string.Empty;
1336 if (!reader.IsDBNull(2))
1337 email = reader.GetString(2);
1339 string passwordQuestion = string.Empty;
1340 if (!reader.IsDBNull(3))
1341 passwordQuestion = reader.GetString(3);
1343 string comment = string.Empty;
1344 if (!reader.IsDBNull(4))
1345 comment = reader.GetString(4);
1347 bool isApproved = reader.GetBoolean(5);
1348 bool isLockedOut = reader.GetBoolean(6);
1349 DateTime creationDate = reader.GetDateTime(7);
1351 DateTime lastLoginDate = new DateTime();
1352 if (!reader.IsDBNull(8))
1353 lastLoginDate = reader.GetDateTime(8);
1355 DateTime lastActivityDate = reader.GetDateTime(9);
1356 DateTime lastPasswordChangedDate = reader.GetDateTime(10);
1358 DateTime lastLockedOutDate = new DateTime();
1359 if (!reader.IsDBNull(11))
1360 lastLockedOutDate = reader.GetDateTime(11);
1362 MembershipUser u = new MembershipUser(this.Name,
1373 lastPasswordChangedDate,
1380 /// Compares password values based on the MembershipPasswordFormat.
1382 /// <param name="password"></param>
1383 /// <param name="dbpassword"></param>
1384 /// <returns></returns>
1385 bool CheckPassword(string password, string dbpassword)
1387 string pass1 = password;
1388 string pass2 = dbpassword;
1390 switch (PasswordFormat)
1392 case MembershipPasswordFormat.Encrypted:
1393 pass2 = UnEncodePassword(dbpassword);
1396 case MembershipPasswordFormat.Hashed:
1397 pass1 = EncodePassword(password);
1404 if (pass1.Equals(pass2))
1411 /// Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.
1413 /// <param name="password"></param>
1414 /// <returns></returns>
1415 string EncodePassword(string password)
1417 if (string.IsNullOrEmpty(password))
1420 string encodedPassword = password;
1422 switch (PasswordFormat)
1424 case MembershipPasswordFormat.Clear:
1427 case MembershipPasswordFormat.Encrypted:
1428 encodedPassword = Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
1431 case MembershipPasswordFormat.Hashed:
1432 HMACSHA1 hash = new HMACSHA1();
1433 if (machineKeyIsAutoGenerated)
1434 hash.Key = MachineKeySection.Config.GetValidationKey ();
1436 hash.Key = HexToByte(m_MachineKey.ValidationKey);
1437 encodedPassword = Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
1441 throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
1444 return encodedPassword;
1448 /// Decrypts or leaves the password clear based on the PasswordFormat.
1450 /// <param name="encodedPassword"></param>
1451 /// <returns></returns>
1452 string UnEncodePassword(string encodedPassword)
1454 string password = encodedPassword;
1456 switch (PasswordFormat)
1458 case MembershipPasswordFormat.Clear:
1461 case MembershipPasswordFormat.Encrypted:
1462 password = Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
1465 case MembershipPasswordFormat.Hashed:
1466 throw new ProviderException(Properties.Resources.ErrCantDecodeHashedPw);
1469 throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
1476 /// Converts a hexadecimal string to a byte array. Used to convert encryption
1477 /// key values from the configuration.
1479 /// <param name="hexString"></param>
1480 /// <returns></returns>
1481 byte[] HexToByte(string hexString)
1483 byte[] returnBytes = new byte[hexString.Length / 2];
1484 for (int i = 0; i < returnBytes.Length; i++)
1485 returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
1491 /// A helper method that performs the checks and updates associated with
1492 /// password failure tracking.
1494 /// <param name="username"></param>
1495 /// <param name="failType"></param>
1496 void UpdateFailureCount(string username, FailureType failType)
1498 DateTime windowStart = new DateTime();
1499 int failureCount = 0;
1501 using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
1503 // Fetch user data from database
1504 using (SqliteCommand dbCommand = dbConn.CreateCommand())
1506 dbCommand.CommandText = string.Format("SELECT \"FailedPasswordAttemptCount\", \"FailedPasswordAttemptWindowStart\", \"FailedPasswordAnswerAttemptCount\", \"FailedPasswordAnswerAttemptWindowStart\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1508 AddParameter (dbCommand,"@Username", username);
1509 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1514 dbCommand.Prepare();
1516 using (SqliteDataReader reader = dbCommand.ExecuteReader())
1522 if (failType.Equals(FailureType.Password))
1524 failureCount = reader.GetInt32(0);
1525 windowStart = reader.GetDateTime(1);
1527 else if (failType.Equals(FailureType.PasswordAnswer))
1529 failureCount = reader.GetInt32(2);
1530 windowStart = reader.GetDateTime(3);
1535 catch (SqliteException e)
1537 Trace.WriteLine(e.ToString());
1538 throw new ProviderException(Properties.Resources.ErrOperationAborted);
1547 // Calculate failture count and update database
1548 using (SqliteCommand dbCommand = dbConn.CreateCommand())
1550 DateTime windowEnd = windowStart.AddMinutes(m_PasswordAttemptWindow);
1554 if (failureCount == 0 || DateTime.Now > windowEnd)
1556 // First password failure or outside of PasswordAttemptWindow.
1557 // Start a new password failure count from 1 and a new window starting now.
1559 if (failType.Equals(FailureType.Password))
1561 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAttemptCount\" = @Count, \"FailedPasswordAttemptWindowStart\" = @WindowStart WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1563 else if (failType.Equals(FailureType.PasswordAnswer))
1565 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAnswerAttemptCount\" = @Count, \"FailedPasswordAnswerAttemptWindowStart\" = @WindowStart WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1568 AddParameter (dbCommand,"@Count", 1);
1569 AddParameter (dbCommand,"@WindowStart", DateTime.Now);
1570 AddParameter (dbCommand,"@Username", username);
1571 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1573 if (dbCommand.ExecuteNonQuery() < 0)
1574 throw new ProviderException(Properties.Resources.ErrCantUpdateFailtureCountAndWindowStart);
1580 if (failureCount >= m_MaxInvalidPasswordAttempts)
1582 // Password attempts have exceeded the failure threshold. Lock out
1584 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"IsLockedOut\" = @IsLockedOut, \"LastLockedOutDate\" = @LastLockedOutDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1586 AddParameter (dbCommand,"@IsLockedOut", true);
1587 AddParameter (dbCommand,"@LastLockedOutDate", DateTime.Now);
1588 AddParameter (dbCommand,"@Username", username);
1589 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1591 if (dbCommand.ExecuteNonQuery() < 0)
1592 throw new ProviderException(string.Format(Properties.Resources.ErrCantLogoutUser, username));
1596 // Password attempts have not exceeded the failure threshold. Update
1597 // the failure counts. Leave the window the same.
1598 if (failType.Equals(FailureType.Password))
1600 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAttemptCount\" = @Count WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1602 else if (failType.Equals(FailureType.PasswordAnswer))
1604 dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAnswerAttemptCount\" = @Count WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1607 AddParameter (dbCommand,"@Count", failureCount);
1608 AddParameter (dbCommand,"@Username", username);
1609 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1611 if (dbCommand.ExecuteNonQuery() < 0)
1612 throw new ProviderException(Properties.Resources.ErrCantUpdateFailtureCount);
1616 catch (SqliteException e)
1618 Trace.WriteLine(e.ToString());
1619 throw new ProviderException(Properties.Resources.ErrOperationAborted);