PageRenderTime 58ms CodeModel.GetById 3ms app.highlight 45ms RepoModel.GetById 1ms app.codeStats 0ms

/mcs/class/System.Web/System.Web.Security/SqliteMembershipProvider.cs

https://github.com/ztfuqingvip/mono
C# | 1639 lines | 1238 code | 246 blank | 155 comment | 142 complexity | 2b79de0dcfce5548cf63c02975fefea9 MD5 | raw file

Large files files are truncated, but you can click here to view the full file

   1//
   2//
   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:
  10// 
  11// The above copyright notice and this permission notice shall be
  12// included in all copies or substantial portions of the Software.
  13// 
  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.
  21//
  22// Copyright � 2006, 2007 Nauck IT KG		http://www.nauck-it.de
  23//
  24// Author:
  25//	Daniel Nauck		<d.nauck(at)nauck-it.de>
  26//
  27// Adapted to Sqlite by Marek Habersack <mhabersack@novell.com>
  28//
  29
  30#if NET_2_0
  31using System;
  32using System.Data;
  33using System.Data.Common;
  34using System.Collections.Generic;
  35using System.Collections.Specialized;
  36using System.Text;
  37using System.Security.Cryptography;
  38using System.Web.Hosting;
  39using System.Web.Configuration;
  40using System.Web.Security;
  41using System.Configuration;
  42using System.Configuration.Provider;
  43using System.Diagnostics;
  44
  45using Mono.Data.Sqlite;
  46
  47namespace System.Web.Security
  48{
  49	internal class SqliteMembershipProvider : MembershipProvider
  50	{
  51		const string m_TableName = "Users";
  52		string m_ConnectionString = string.Empty;
  53		const int m_NewPasswordLength = 8;
  54		bool machineKeyIsAutoGenerated;
  55		
  56		// Used when determining encryption key values.
  57		MachineKeySection m_MachineKey = null;
  58
  59		DbParameter AddParameter (DbCommand command, string parameterName, object parameterValue)
  60                {
  61                        return AddParameter (command, parameterName, ParameterDirection.Input, parameterValue);
  62                }
  63
  64                DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, object parameterValue)
  65                {
  66                        DbParameter dbp = command.CreateParameter ();
  67                        dbp.ParameterName = parameterName;
  68                        dbp.Value = parameterValue;
  69                        dbp.Direction = direction;
  70                        command.Parameters.Add (dbp);
  71                        return dbp;
  72                }
  73		
  74		/// <summary>
  75		/// System.Configuration.Provider.ProviderBase.Initialize Method.
  76		/// </summary>
  77		public override void Initialize(string name, NameValueCollection config)
  78		{
  79			// Initialize values from web.config.
  80			if (config == null)
  81				throw new ArgumentNullException("Config", Properties.Resources.ErrArgumentNull);
  82
  83			if (string.IsNullOrEmpty(name))
  84				name = Properties.Resources.MembershipProviderDefaultName;
  85
  86			if (string.IsNullOrEmpty(config["description"]))
  87			{
  88				config.Remove("description");
  89				config.Add("description", Properties.Resources.MembershipProviderDefaultDescription);
  90			}
  91
  92			// Initialize the abstract base class.
  93			base.Initialize(name, config);
  94
  95			m_ApplicationName = GetConfigValue(config["applicationName"], HostingEnvironment.ApplicationVirtualPath);
  96			m_MaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
  97			m_PasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
  98			m_MinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"));
  99			m_MinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
 100			m_PasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""));
 101			m_EnablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
 102			m_EnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
 103			m_RequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
 104			m_RequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
 105
 106			// Get password encryption type.
 107			string pwFormat = GetConfigValue(config["passwordFormat"], "Hashed");
 108			switch (pwFormat)
 109			{
 110				case "Hashed":
 111					m_PasswordFormat = MembershipPasswordFormat.Hashed;
 112					break;
 113				case "Encrypted":
 114					m_PasswordFormat = MembershipPasswordFormat.Encrypted;
 115					break;
 116				case "Clear":
 117					m_PasswordFormat = MembershipPasswordFormat.Clear;
 118					break;
 119				default:
 120					throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
 121			}
 122
 123			// Get connection string.
 124			string connStrName = config["connectionStringName"];
 125			
 126			if (string.IsNullOrEmpty(connStrName))
 127			{
 128				throw new ArgumentOutOfRangeException("ConnectionStringName", Properties.Resources.ErrArgumentNullOrEmpty);
 129			}
 130			else
 131			{
 132				ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[connStrName];
 133
 134				if (ConnectionStringSettings == null || string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
 135				{
 136					throw new ProviderException(Properties.Resources.ErrConnectionStringNullOrEmpty);
 137				}
 138
 139				m_ConnectionString = ConnectionStringSettings.ConnectionString;
 140			}
 141
 142			// Get encryption and decryption key information from the configuration.
 143			m_MachineKey = (MachineKeySection)WebConfigurationManager.GetSection("system.web/machineKey", null);
 144
 145			if (!m_PasswordFormat.Equals(MembershipPasswordFormat.Clear))
 146			{
 147				if (m_MachineKey == null)
 148					throw new ArgumentNullException("system.web/machineKey", Properties.Resources.ErrArgumentNull);
 149
 150				if (m_MachineKey.ValidationKey.Contains("AutoGenerate")) {
 151					machineKeyIsAutoGenerated = true;
 152					if (m_PasswordFormat.Equals (MembershipPasswordFormat.Encrypted))
 153						throw new ProviderException(Properties.Resources.ErrAutoGeneratedKeyNotSupported);
 154				}
 155			}
 156		}
 157
 158		/// <summary>
 159		/// System.Web.Security.MembershipProvider properties.
 160		/// </summary>
 161		#region System.Web.Security.MembershipProvider properties
 162		string m_ApplicationName = string.Empty;
 163		bool m_EnablePasswordReset = false;
 164		bool m_EnablePasswordRetrieval = false;
 165		bool m_RequiresQuestionAndAnswer = false;
 166		bool m_RequiresUniqueEmail = false;
 167		int m_MaxInvalidPasswordAttempts = 0;
 168		int m_PasswordAttemptWindow = 0;
 169		MembershipPasswordFormat m_PasswordFormat = MembershipPasswordFormat.Clear;
 170		int m_MinRequiredNonAlphanumericCharacters = 0;
 171		int m_MinRequiredPasswordLength = 0;
 172		string m_PasswordStrengthRegularExpression = string.Empty;
 173
 174		public override string ApplicationName
 175		{
 176			get { return m_ApplicationName; }
 177			set { m_ApplicationName = value; }
 178		}
 179
 180		public override bool EnablePasswordReset
 181		{
 182			get { return m_EnablePasswordReset; }
 183		}
 184
 185		public override bool EnablePasswordRetrieval
 186		{
 187			get { return m_EnablePasswordRetrieval; }
 188		}
 189
 190		public override bool RequiresQuestionAndAnswer
 191		{
 192			get { return m_RequiresQuestionAndAnswer; }
 193		}
 194
 195		public override bool RequiresUniqueEmail
 196		{
 197			get { return m_RequiresUniqueEmail; }
 198		}
 199
 200		public override int MaxInvalidPasswordAttempts
 201		{
 202			get { return m_MaxInvalidPasswordAttempts; }
 203		}
 204
 205		public override int PasswordAttemptWindow
 206		{
 207			get { return m_PasswordAttemptWindow; }
 208		}
 209
 210		public override MembershipPasswordFormat PasswordFormat
 211		{
 212			get { return m_PasswordFormat; }
 213		}
 214
 215		public override int MinRequiredNonAlphanumericCharacters
 216		{
 217			get { return m_MinRequiredNonAlphanumericCharacters; }
 218		}
 219
 220		public override int MinRequiredPasswordLength
 221		{
 222			get { return m_MinRequiredPasswordLength; }
 223		}
 224
 225		public override string PasswordStrengthRegularExpression
 226		{
 227			get { return m_PasswordStrengthRegularExpression; }
 228		}
 229		#endregion
 230
 231		
 232		/// <summary>
 233		/// System.Web.Security.MembershipProvider methods.
 234		/// </summary>
 235		#region System.Web.Security.MembershipProvider methods
 236
 237		/// <summary>
 238		/// MembershipProvider.ChangePassword
 239		/// </summary>
 240		public override bool ChangePassword(string username, string oldPassword, string newPassword)
 241		{
 242			if (!ValidateUser(username, oldPassword))
 243				return false;
 244
 245			ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
 246
 247			OnValidatingPassword(args);
 248
 249			if (args.Cancel)
 250			{
 251				if (args.FailureInformation != null)
 252					throw args.FailureInformation;
 253				else
 254					throw new MembershipPasswordException(Properties.Resources.ErrPasswordChangeCanceled);
 255			}
 256
 257			int rowsAffected = 0;
 258
 259			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 260			{
 261				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 262				{
 263					dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"Password\" = @Password, \"LastPasswordChangedDate\" = @LastPasswordChangedDate WHERE \"Username\" = @Username AND  \"ApplicationName\" = @ApplicationName", m_TableName);
 264
 265					AddParameter (dbCommand,"@Password", EncodePassword(newPassword));
 266					AddParameter (dbCommand,"@LastPasswordChangedDate", DateTime.Now);
 267					AddParameter (dbCommand,"@Username", username);
 268					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 269
 270					try
 271					{
 272						dbConn.Open();
 273						dbCommand.Prepare();
 274
 275						rowsAffected = dbCommand.ExecuteNonQuery();
 276					}
 277					catch (SqliteException e)
 278					{
 279						Trace.WriteLine(e.ToString());
 280						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 281					}
 282					finally
 283					{
 284						if (dbConn != null)
 285							dbConn.Close();
 286					}
 287				}
 288			}
 289
 290			if (rowsAffected > 0)
 291				return true;
 292			else
 293				return false;
 294		}
 295
 296		/// <summary>
 297		/// MembershipProvider.ChangePasswordQuestionAndAnswer
 298		/// </summary>
 299		public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
 300		{
 301			if (!ValidateUser(username, password))
 302				return false;
 303
 304			int rowsAffected = 0;
 305
 306			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 307			{
 308				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 309				{
 310					dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"PasswordQuestion\" = @PasswordQuestion, \"PasswordAnswer\" = @PasswordAnswer WHERE \"Username\" = @Username AND  \"ApplicationName\" = @ApplicationName", m_TableName);
 311
 312					AddParameter (dbCommand,"@PasswordQuestion", newPasswordQuestion);
 313					AddParameter (dbCommand,"@PasswordAnswer", EncodePassword(newPasswordAnswer));
 314					AddParameter (dbCommand,"@Username", username);
 315					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 316
 317					try
 318					{
 319						dbConn.Open();
 320						dbCommand.Prepare();
 321
 322						rowsAffected = dbCommand.ExecuteNonQuery();
 323					}
 324					catch (SqliteException e)
 325					{
 326						Trace.WriteLine(e.ToString());
 327						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 328					}
 329					finally
 330					{
 331						if (dbConn != null)
 332							dbConn.Close();
 333					}
 334				}
 335			}
 336
 337			if (rowsAffected > 0)
 338				return true;
 339			else
 340				return false;
 341		}
 342
 343		/// <summary>
 344		/// MembershipProvider.CreateUser
 345		/// </summary>
 346		public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved,
 347							  object providerUserKey, out MembershipCreateStatus status)
 348		{
 349			ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);
 350
 351			OnValidatingPassword(args);
 352
 353			if (args.Cancel)
 354			{
 355				status = MembershipCreateStatus.InvalidPassword;
 356				return null;
 357			}
 358
 359			if (RequiresUniqueEmail && string.IsNullOrEmpty(email))
 360			{
 361				status = MembershipCreateStatus.InvalidEmail;
 362				return null;
 363			}
 364
 365			if (RequiresUniqueEmail && !string.IsNullOrEmpty(GetUserNameByEmail(email)))
 366			{
 367				status = MembershipCreateStatus.DuplicateEmail;
 368				return null;
 369			}
 370
 371			if (GetUser(username, false) == null)
 372			{
 373				DateTime createDate = DateTime.Now;
 374
 375				if (providerUserKey == null)
 376				{
 377					providerUserKey = Guid.NewGuid();
 378				}
 379				else
 380				{
 381					if (!(providerUserKey is Guid))
 382					{
 383						status = MembershipCreateStatus.InvalidProviderUserKey;
 384						return null;
 385					}
 386				}
 387				
 388				// Create user in database
 389				using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 390				{
 391					using (SqliteCommand dbCommand = dbConn.CreateCommand())
 392					{
 393						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
 395						AddParameter (dbCommand,"@pId", providerUserKey);
 396						AddParameter (dbCommand,"@Username", username);
 397						AddParameter (dbCommand,"@Password", EncodePassword(password));
 398						AddParameter (dbCommand,"@Email", email);
 399						AddParameter (dbCommand,"@PasswordQuestion", passwordQuestion);
 400						AddParameter (dbCommand,"@PasswordAnswer", EncodePassword(passwordAnswer));
 401						AddParameter (dbCommand,"@IsApproved", isApproved);
 402						AddParameter (dbCommand,"@CreationDate", createDate);
 403						AddParameter (dbCommand,"@LastPasswordChangedDate", createDate);
 404						AddParameter (dbCommand,"@LastActivityDate", createDate);
 405						AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 406						AddParameter (dbCommand,"@IsLockedOut", false);
 407						AddParameter (dbCommand,"@LastLockedOutDate", createDate);
 408						AddParameter (dbCommand,"@FailedPasswordAttemptCount", 0);
 409						AddParameter (dbCommand,"@FailedPasswordAttemptWindowStart", createDate);
 410						AddParameter (dbCommand,"@FailedPasswordAnswerAttemptCount", 0);
 411						AddParameter (dbCommand,"@FailedPasswordAnswerAttemptWindowStart", createDate);
 412
 413						try
 414						{
 415							dbConn.Open();
 416							dbCommand.Prepare();
 417
 418							if (dbCommand.ExecuteNonQuery() > 0)
 419							{
 420								status = MembershipCreateStatus.Success;
 421							}
 422							else
 423							{
 424								status = MembershipCreateStatus.UserRejected;
 425							}
 426						}
 427						catch (SqliteException e)
 428						{
 429							status = MembershipCreateStatus.ProviderError;
 430							Trace.WriteLine(e.ToString());
 431							throw new ProviderException(Properties.Resources.ErrOperationAborted);
 432						}
 433						finally
 434						{
 435							if (dbConn != null)
 436								dbConn.Close();
 437						}
 438
 439						return GetUser(username, false);
 440					}
 441				}
 442			}
 443			else
 444			{
 445				status = MembershipCreateStatus.DuplicateUserName;
 446			}
 447			return null;
 448		}
 449
 450		/// <summary>
 451		/// MembershipProvider.DeleteUser
 452		/// </summary>
 453		public override bool DeleteUser(string username, bool deleteAllRelatedData)
 454		{
 455			int rowsAffected = 0;
 456
 457			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 458			{
 459				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 460				{
 461					dbCommand.CommandText = string.Format("DELETE FROM \"{0}\" WHERE \"Username\" = @Username AND  \"ApplicationName\" = @ApplicationName", m_TableName);
 462
 463					AddParameter (dbCommand,"@Username", username);
 464					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 465
 466					try
 467					{
 468						dbConn.Open();
 469						dbCommand.Prepare();
 470
 471						rowsAffected = dbCommand.ExecuteNonQuery();
 472
 473						if (deleteAllRelatedData)
 474						{
 475							// Process commands to delete all data for the user in the database.
 476						}
 477					}
 478					catch (SqliteException e)
 479					{
 480						Trace.WriteLine(e.ToString());
 481						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 482					}
 483					finally
 484					{
 485						if (dbConn != null)
 486							dbConn.Close();
 487					}
 488				}
 489			}
 490
 491			if (rowsAffected > 0)
 492				return true;
 493			else
 494				return false;
 495		}
 496
 497		/// <summary>
 498		/// MembershipProvider.FindUsersByEmail
 499		/// </summary>
 500		public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
 501		{
 502			totalRecords = 0;
 503			MembershipUserCollection users = new MembershipUserCollection();
 504
 505			// replace permitted wildcard characters 
 506			emailToMatch = emailToMatch.Replace('*','%');
 507			emailToMatch = emailToMatch.Replace('?', '_');
 508
 509			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 510			{
 511				// Get user count
 512				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 513				{
 514					dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"Email\" LIKE @Email AND  \"ApplicationName\" = @ApplicationName", m_TableName);
 515
 516					AddParameter (dbCommand,"@Email", emailToMatch);
 517					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 518
 519					try
 520					{
 521						dbConn.Open();
 522						dbCommand.Prepare();
 523
 524						Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
 525
 526						if (totalRecords <= 0) { return users; }
 527					}
 528					catch (SqliteException e)
 529					{
 530						Trace.WriteLine(e.ToString());
 531						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 532					}
 533					finally
 534					{
 535						if (dbConn != null)
 536							dbConn.Close();
 537					}
 538				}
 539
 540				// Fetch user from database
 541				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 542				{
 543					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
 545					AddParameter (dbCommand,"@Email", emailToMatch);
 546					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 547					AddParameter (dbCommand,"@MaxCount", pageSize);
 548					AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
 549
 550					try
 551					{
 552						dbConn.Open();
 553						dbCommand.Prepare();
 554
 555						using (SqliteDataReader reader = dbCommand.ExecuteReader())
 556						{
 557							while (reader.Read())
 558							{
 559								MembershipUser u = GetUserFromReader(reader);
 560								users.Add(u);
 561							}
 562						}
 563					}
 564					catch (SqliteException e)
 565					{
 566						Trace.WriteLine(e.ToString());
 567						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 568					}
 569					finally
 570					{
 571						if (dbConn != null)
 572							dbConn.Close();
 573					}
 574				}
 575			}
 576
 577			return users;
 578		}
 579
 580		/// <summary>
 581		/// MembershipProvider.FindUsersByName
 582		/// </summary>
 583		public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
 584		{
 585			totalRecords = 0;
 586			MembershipUserCollection users = new MembershipUserCollection();
 587
 588			// replace permitted wildcard characters 
 589			usernameToMatch = usernameToMatch.Replace('*', '%');
 590			usernameToMatch = usernameToMatch.Replace('?', '_');
 591
 592			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 593			{
 594				// Get user count
 595				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 596				{
 597					dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"Username\" LIKE @Username AND  \"ApplicationName\" = @ApplicationName", m_TableName);
 598
 599					AddParameter (dbCommand,"@Username", usernameToMatch);
 600					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 601
 602					try
 603					{
 604						dbConn.Open();
 605						dbCommand.Prepare();
 606
 607						Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
 608
 609						if (totalRecords <= 0) { return users; }
 610					}
 611					catch (SqliteException e)
 612					{
 613						Trace.WriteLine(e.ToString());
 614						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 615					}
 616					finally
 617					{
 618						if (dbConn != null)
 619							dbConn.Close();
 620					}
 621				}
 622
 623				// Fetch user from database
 624				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 625				{
 626					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
 628					AddParameter (dbCommand,"@Username", usernameToMatch);
 629					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 630					AddParameter (dbCommand,"@MaxCount", pageSize);
 631					AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
 632
 633					try
 634					{
 635						dbConn.Open();
 636						dbCommand.Prepare();
 637
 638						using (SqliteDataReader reader = dbCommand.ExecuteReader())
 639						{
 640							while (reader.Read())
 641							{
 642								MembershipUser u = GetUserFromReader(reader);
 643								users.Add(u);
 644							}
 645						}
 646					}
 647					catch (SqliteException e)
 648					{
 649						Trace.WriteLine(e.ToString());
 650						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 651					}
 652					finally
 653					{
 654						if (dbConn != null)
 655							dbConn.Close();
 656					}
 657				}
 658			}
 659
 660			return users;
 661		}
 662
 663		/// <summary>
 664		/// MembershipProvider.GetAllUsers
 665		/// </summary>
 666		public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
 667		{
 668			totalRecords = 0;
 669			MembershipUserCollection users = new MembershipUserCollection();
 670
 671			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 672			{
 673				// Get user count
 674				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 675				{
 676					dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"ApplicationName\" = @ApplicationName", m_TableName);
 677
 678					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 679
 680					try
 681					{
 682						dbConn.Open();
 683						dbCommand.Prepare();
 684
 685						Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
 686
 687						if (totalRecords <= 0) { return users; }
 688					}
 689					catch (SqliteException e)
 690					{
 691						Trace.WriteLine(e.ToString());
 692						throw e;
 693					}
 694					finally
 695					{
 696						if (dbConn != null)
 697							dbConn.Close();
 698					}
 699				}
 700
 701				// Fetch user from database
 702				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 703				{
 704					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					
 706					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 707					AddParameter (dbCommand,"@MaxCount", pageSize);
 708					AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
 709
 710					try
 711					{
 712						dbConn.Open();
 713						dbCommand.Prepare();
 714
 715						using (SqliteDataReader reader = dbCommand.ExecuteReader())
 716						{
 717							while (reader.Read())
 718							{
 719								MembershipUser u = GetUserFromReader(reader);
 720								users.Add(u);
 721							}
 722						}
 723					}
 724					catch (SqliteException e)
 725					{
 726						Trace.WriteLine(e.ToString());
 727						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 728					}
 729					finally
 730					{
 731						if (dbConn != null)
 732							dbConn.Close();
 733					}
 734				}
 735			}
 736
 737			return users;
 738		}
 739
 740		/// <summary>
 741		/// MembershipProvider.GetNumberOfUsersOnline
 742		/// </summary>
 743		public override int GetNumberOfUsersOnline()
 744		{
 745			int numOnline = 0;
 746
 747			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 748			{
 749				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 750				{
 751					TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
 752					DateTime compareTime = DateTime.Now.Subtract(onlineSpan);
 753
 754					dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"LastActivityDate\" > @CompareTime AND  \"ApplicationName\" = @ApplicationName", m_TableName);
 755
 756					AddParameter (dbCommand,"@CompareTime", compareTime);
 757					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 758
 759					try
 760					{
 761						dbConn.Open();
 762						dbCommand.Prepare();
 763
 764						numOnline = (int)dbCommand.ExecuteScalar();
 765					}
 766					catch (SqliteException e)
 767					{
 768						Trace.WriteLine(e.ToString());
 769						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 770					}
 771					finally
 772					{
 773						if (dbConn != null)
 774							dbConn.Close();
 775					}
 776				}
 777			}
 778
 779			return numOnline;
 780		}
 781
 782		/// <summary>
 783		/// MembershipProvider.GetPassword
 784		/// </summary>
 785		public override string GetPassword(string username, string answer)
 786		{
 787			if (!EnablePasswordRetrieval)
 788			{
 789				throw new ProviderException(Properties.Resources.ErrPasswordRetrievalNotEnabled);
 790			}
 791
 792			if (PasswordFormat == MembershipPasswordFormat.Hashed)
 793			{
 794				throw new ProviderException(Properties.Resources.ErrCantRetrieveHashedPw);
 795			}
 796
 797			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 798			{
 799				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 800				{
 801					dbCommand.CommandText = string.Format("SELECT \"Password\", \"PasswordAnswer\", \"IsLockedOut\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
 802
 803					AddParameter (dbCommand,"@Username", username);
 804					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 805
 806					try
 807					{
 808						dbConn.Open();
 809						dbCommand.Prepare();
 810
 811						using (SqliteDataReader reader = dbCommand.ExecuteReader())
 812						{
 813							if (reader.HasRows)
 814							{
 815								reader.Read();
 816
 817								string password = reader.GetString(0);
 818								string passwordAnswer = reader.GetString(1);
 819								bool isLockedOut = reader.GetBoolean(2);
 820
 821								reader.Close();
 822
 823								if (isLockedOut)
 824									throw new MembershipPasswordException(Properties.Resources.ErrUserIsLoggedOut);
 825
 826								if (m_RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
 827								{
 828									UpdateFailureCount(username, FailureType.PasswordAnswer);
 829
 830									throw new MembershipPasswordException(Properties.Resources.ErrIncorrectPasswordAnswer);
 831								}
 832
 833								if (m_PasswordFormat == MembershipPasswordFormat.Encrypted)
 834								{
 835									password = UnEncodePassword(password);
 836								}
 837
 838								return password;
 839							}
 840							else
 841							{
 842								throw new MembershipPasswordException(Properties.Resources.ErrUserNotFound);
 843							}
 844						}
 845					}
 846					catch (SqliteException e)
 847					{
 848						Trace.WriteLine(e.ToString());
 849						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 850					}
 851					finally
 852					{
 853						if (dbConn != null)
 854							dbConn.Close();
 855					}
 856				}
 857			}
 858		}
 859
 860		/// <summary>
 861		/// MembershipProvider.GetUser
 862		/// </summary>
 863		public override MembershipUser GetUser(string username, bool userIsOnline)
 864		{
 865			MembershipUser u = null;
 866
 867			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 868			{
 869				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 870				{
 871					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
 873					AddParameter (dbCommand,"@Username", username);
 874					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 875
 876					try
 877					{
 878						dbConn.Open();
 879						dbCommand.Prepare();
 880
 881						using (SqliteDataReader reader = dbCommand.ExecuteReader())
 882						{
 883							if (reader.HasRows)
 884							{
 885								reader.Read();
 886								u = GetUserFromReader(reader);
 887
 888								if (userIsOnline)
 889								{
 890									// Update user online status
 891									using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
 892									{
 893										dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastActivityDate\" = @LastActivityDate WHERE \"pId\" = @pId", m_TableName);
 894
 895										AddParameter (dbUpdateCommand, "@LastActivityDate", DateTime.Now);
 896										AddParameter (dbUpdateCommand, "@pId", u.ProviderUserKey);
 897
 898										dbUpdateCommand.Prepare();
 899
 900										dbUpdateCommand.ExecuteNonQuery();
 901									}
 902								}
 903							}
 904						}
 905					}
 906					catch (SqliteException e)
 907					{
 908						Trace.WriteLine(e.ToString());
 909						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 910					}
 911					finally
 912					{
 913						if (dbConn != null)
 914							dbConn.Close();
 915					}
 916				}
 917			}
 918
 919			return u;
 920		}
 921
 922		/// <summary>
 923		/// MembershipProvider.GetUser
 924		/// </summary>
 925		public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
 926		{
 927			MembershipUser u = null;
 928
 929			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 930			{
 931				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 932				{
 933					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
 935					AddParameter (dbCommand,"@pId", providerUserKey);
 936
 937					try
 938					{
 939						dbConn.Open();
 940						dbCommand.Prepare();
 941
 942						using (SqliteDataReader reader = dbCommand.ExecuteReader())
 943						{
 944							if (reader.HasRows)
 945							{
 946								reader.Read();
 947								u = GetUserFromReader(reader);
 948
 949								if (userIsOnline)
 950								{
 951									// Update user online status
 952									using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
 953									{
 954										dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastActivityDate\" = @LastActivityDate WHERE \"pId\" = @pId", m_TableName);
 955
 956										AddParameter (dbUpdateCommand, "@LastActivityDate", DateTime.Now);
 957										AddParameter (dbUpdateCommand, "@pId", u.ProviderUserKey);
 958
 959										dbUpdateCommand.Prepare();
 960
 961										dbUpdateCommand.ExecuteNonQuery();
 962									}
 963								}
 964							}
 965						}
 966					}
 967					catch (SqliteException e)
 968					{
 969						Trace.WriteLine(e.ToString());
 970						throw new ProviderException(Properties.Resources.ErrOperationAborted);
 971					}
 972					finally
 973					{
 974						if (dbConn != null)
 975							dbConn.Close();
 976					}
 977				}
 978			}
 979
 980			return u;
 981		}
 982
 983		/// <summary>
 984		/// MembershipProvider.GetUserNameByEmail
 985		/// </summary>
 986		public override string GetUserNameByEmail(string email)
 987		{
 988			string username = string.Empty;
 989
 990			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
 991			{
 992				using (SqliteCommand dbCommand = dbConn.CreateCommand())
 993				{
 994					dbCommand.CommandText = string.Format("SELECT \"Username\" FROM \"{0}\" WHERE \"Email\" = @Email AND \"ApplicationName\" = @ApplicationName", m_TableName);
 995
 996					AddParameter (dbCommand,"@Email", email);
 997					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
 998
 999					try
1000					{
1001						dbConn.Open();
1002						dbCommand.Prepare();
1003
1004						username = (dbCommand.ExecuteScalar() as string) ?? string.Empty;
1005					}
1006					catch (SqliteException e)
1007					{
1008						Trace.WriteLine(e.ToString());
1009						throw new ProviderException(Properties.Resources.ErrOperationAborted);
1010					}
1011					finally
1012					{
1013						if (dbConn != null)
1014							dbConn.Close();
1015					}
1016				}
1017			}
1018
1019			return username;
1020		}
1021
1022		/// <summary>
1023		/// MembershipProvider.ResetPassword
1024		/// </summary>
1025		public override string ResetPassword(string username, string answer)
1026		{
1027			if (!m_EnablePasswordReset)
1028			{
1029				throw new NotSupportedException(Properties.Resources.ErrPasswordResetNotEnabled);
1030			}
1031
1032			if (string.IsNullOrEmpty(answer) && m_RequiresQuestionAndAnswer)
1033			{
1034				UpdateFailureCount(username, FailureType.PasswordAnswer);
1035
1036				throw new ProviderException(Properties.Resources.ErrPasswordAnswerRequired);
1037			}
1038
1039			string newPassword = Membership.GeneratePassword(m_NewPasswordLength, m_MinRequiredNonAlphanumericCharacters);
1040
1041
1042			ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
1043
1044			OnValidatingPassword(args);
1045
1046			if (args.Cancel)
1047			{
1048				if (args.FailureInformation != null)
1049					throw args.FailureInformation;
1050				else
1051					throw new MembershipPasswordException(Properties.Resources.ErrPasswordResetCanceled);
1052			}
1053
1054			int rowsAffected = 0;
1055
1056			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
1057			{
1058				using (SqliteCommand dbCommand = dbConn.CreateCommand())
1059				{
1060					dbCommand.CommandText = string.Format("SELECT \"PasswordAnswer\", \"IsLockedOut\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1061
1062					AddParameter (dbCommand,"@Username", username);
1063					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1064
1065					try
1066					{
1067						string passwordAnswer = string.Empty;
1068
1069						dbConn.Open();
1070						dbCommand.Prepare();
1071
1072						using (SqliteDataReader reader = dbCommand.ExecuteReader())
1073						{
1074							if (reader.HasRows)
1075							{
1076								reader.Read();
1077
1078								passwordAnswer = reader.GetString(0);
1079								bool isLockedOut = reader.GetBoolean(1);
1080
1081								reader.Close();
1082
1083								if (isLockedOut)
1084									throw new MembershipPasswordException(Properties.Resources.ErrUserIsLoggedOut);
1085
1086								if (m_RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
1087								{
1088									UpdateFailureCount(username, FailureType.PasswordAnswer);
1089
1090									throw new MembershipPasswordException(Properties.Resources.ErrIncorrectPasswordAnswer);
1091								}
1092							}
1093							else
1094							{
1095								throw new MembershipPasswordException(Properties.Resources.ErrUserNotFound);
1096							}
1097						}
1098
1099						// Reset Password
1100						using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
1101						{
1102							dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"Password\" = @Password, \"LastPasswordChangedDate\" = @LastPasswordChangedDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsLockedOut\" = @IsLockedOut", m_TableName);
1103
1104							AddParameter (dbUpdateCommand, "@Password", EncodePassword(newPassword));
1105							AddParameter (dbUpdateCommand, "@LastPasswordChangedDate", DateTime.Now);
1106							AddParameter (dbUpdateCommand, "@Username", username);
1107							AddParameter (dbUpdateCommand, "@ApplicationName", m_ApplicationName);
1108							AddParameter (dbUpdateCommand, "@IsLockedOut", false);
1109
1110							dbUpdateCommand.Prepare();
1111
1112							rowsAffected = dbUpdateCommand.ExecuteNonQuery();
1113						}
1114
1115					}
1116					catch (SqliteException e)
1117					{
1118						Trace.WriteLine(e.ToString());
1119						throw new ProviderException(Properties.Resources.ErrOperationAborted);
1120					}
1121					finally
1122					{
1123						if (dbConn != null)
1124							dbConn.Close();
1125					}
1126				}
1127			}
1128
1129			if (rowsAffected > 0)
1130				return newPassword;
1131			
1132			else
1133				throw new MembershipPasswordException(Properties.Resources.ErrPasswordResetAborted);
1134		}
1135
1136		/// <summary>
1137		/// MembershipProvider.UnlockUser
1138		/// </summary>
1139		public override bool UnlockUser(string userName)
1140		{
1141			int rowsAffected = 0;
1142
1143			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
1144			{
1145				using (SqliteCommand dbCommand = dbConn.CreateCommand())
1146				{
1147					dbCommand.CommandText = string.Format("UPDATE  \"{0}\" SET \"IsLockedOut\" = @IsLockedOut, \"LastLockedOutDate\" = @LastLockedOutDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1148
1149					AddParameter (dbCommand,"@IsLockedOut", false);
1150					AddParameter (dbCommand,"@LastLockedOutDate", DateTime.Now);
1151					AddParameter (dbCommand,"@Username", userName);
1152					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1153
1154					try
1155					{
1156						dbConn.Open();
1157						dbCommand.Prepare();
1158
1159						rowsAffected = dbCommand.ExecuteNonQuery();
1160					}
1161					catch (SqliteException e)
1162					{
1163						Trace.WriteLine(e.ToString());
1164						throw new ProviderException(Properties.Resources.ErrOperationAborted);
1165					}
1166					finally
1167					{
1168						if (dbConn != null)
1169							dbConn.Close();
1170					}
1171				}
1172			}
1173
1174			if (rowsAffected > 0)
1175				return true;
1176
1177			else
1178				return false;
1179		}
1180
1181		/// <summary>
1182		/// MembershipProvider.UpdateUser
1183		/// </summary>
1184		public override void UpdateUser(MembershipUser user)
1185		{
1186			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
1187			{
1188				using (SqliteCommand dbCommand = dbConn.CreateCommand())
1189				{
1190					dbCommand.CommandText = string.Format("UPDATE  \"{0}\" SET \"Email\" = @Email, \"Comment\" = @Comment, \"IsApproved\" = @IsApproved WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1191
1192					AddParameter (dbCommand,"@Email", user.Email);
1193					AddParameter (dbCommand,"@Comment", user.Comment);
1194					AddParameter (dbCommand,"@IsApproved", user.IsApproved);
1195					AddParameter (dbCommand,"@Username", user.UserName);
1196					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1197
1198					try
1199					{
1200						dbConn.Open();
1201						dbCommand.Prepare();
1202
1203						dbCommand.ExecuteNonQuery();
1204					}
1205					catch (SqliteException e)
1206					{
1207						Trace.WriteLine(e.ToString());
1208						throw new ProviderException(Properties.Resources.ErrOperationAborted);
1209					}
1210					finally
1211					{
1212						if (dbConn != null)
1213							dbConn.Close();
1214					}
1215				}
1216			}
1217		}
1218
1219		/// <summary>
1220		/// MembershipProvider.ValidateUser
1221		/// </summary>
1222		public override bool ValidateUser(string username, string password)
1223		{
1224			string dbPassword = string.Empty;
1225			bool dbIsApproved = false;
1226
1227			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
1228			{
1229				// Fetch user data from database
1230				using (SqliteCommand dbCommand = dbConn.CreateCommand())
1231				{
1232					dbCommand.CommandText = string.Format("SELECT \"Password\", \"IsApproved\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsLockedOut\" = @IsLockedOut", m_TableName);
1233
1234					AddParameter (dbCommand,"@Username", username);
1235					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1236					AddParameter (dbCommand,"@IsLockedOut", false);
1237
1238					try
1239					{
1240						dbConn.Open();
1241						dbCommand.Prepare();
1242
1243						using (SqliteDataReader reader = dbCommand.ExecuteReader())
1244						{
1245							if (reader.HasRows)
1246							{
1247								reader.Read();
1248								dbPassword = reader.GetString(0);
1249								dbIsApproved = reader.GetBoolean(1);
1250							}
1251							else
1252							{
1253								return false;
1254							}
1255						}
1256					}
1257					catch (SqliteException e)
1258					{
1259						Trace.WriteLine(e.ToString());
1260						throw new ProviderException(Properties.Resources.ErrOperationAborted);
1261					}
1262					finally
1263					{
1264						if (dbConn != null)
1265							dbConn.Close();
1266					}
1267				}
1268
1269				if (CheckPassword(password, dbPassword))
1270				{
1271					if (dbIsApproved)
1272					{
1273						// Update last login date
1274						using (SqliteCommand dbCommand = dbConn.CreateCommand())
1275						{
1276							dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastLoginDate\" = @LastLoginDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1277
1278							AddParameter (dbCommand,"@LastLoginDate", DateTime.Now);
1279							AddParameter (dbCommand,"@Username", username);
1280							AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1281
1282							try
1283							{
1284								dbConn.Open();
1285								dbCommand.Prepare();
1286
1287								dbCommand.ExecuteNonQuery();
1288
1289								return true;
1290							}
1291							catch (SqliteException e)
1292							{
1293								Trace.WriteLine(e.ToString());
1294								throw new ProviderException(Properties.Resources.ErrOperationAborted);
1295							}
1296							finally
1297							{
1298								if (dbConn != null)
1299									dbConn.Close();
1300							}
1301						}
1302					}
1303				}
1304
1305				return false;
1306			}
1307		}
1308		#endregion
1309
1310		#region private methods
1311		/// <summary>
1312		/// A helper function to retrieve config values from the configuration file.
1313		/// </summary>
1314		/// <param name="configValue"></param>
1315		/// <param name="defaultValue"></param>
1316		/// <returns></returns>
1317		string GetConfigValue(string configValue, string defaultValue)
1318		{
1319			if (string.IsNullOrEmpty(configValue))
1320				return defaultValue;
1321
1322			return configValue;
1323		}
1324
1325		/// <summary>
1326		/// A helper function that takes the current row from the SqliteDataReader
1327		/// and hydrates a MembershipUser from the values. Called by the 
1328		/// MembershipUser.GetUser implementation.
1329		/// </summary>
1330		/// <param name="reader">SqliteDataReader object</param>
1331		/// <returns>MembershipUser object</returns>
1332		MembershipUser GetUserFromReader(SqliteDataReader reader)
1333		{
1334			object providerUserKey = reader.GetValue(0);
1335			string username = reader.GetString(1);
1336			string email = string.Empty;
1337			if (!reader.IsDBNull(2))
1338				email = reader.GetString(2);
1339
1340			string passwordQuestion = string.Empty;
1341			if (!reader.IsDBNull(3))
1342				passwordQuestion = reader.GetString(3);
1343			
1344			string comment = string.Empty;
1345			if (!reader.IsDBNull(4))
1346				comment = reader.GetString(4);
1347
1348			bool isApproved = reader.GetBoolean(5);
1349			bool isLockedOut = reader.GetBoolean(6);
1350			DateTime creationDate = reader.GetDateTime(7);
1351
1352			DateTime lastLoginDate = new DateTime();
1353			if (!reader.IsDBNull(8))
1354				lastLoginDate = reader.GetDateTime(8);
1355
1356			DateTime lastActivityDate = reader.GetDateTime(9);
1357			DateTime lastPasswordChangedDate = reader.GetDateTime(10);
1358
1359			DateTime lastLockedOutDate = new DateTime();
1360			if (!reader.IsDBNull(11))
1361				lastLockedOutDate = reader.GetDateTime(11);
1362
1363			MembershipUser u = new MembershipUser(this.Name,
1364												  username,
1365												  providerUserKey,
1366												  email,
1367												  passwordQuestion,
1368												  comment,
1369												  isApproved,
1370												  isLockedOut,
1371												  creationDate,
1372												  lastLoginDate,
1373												  lastActivityDate,
1374												  lastPasswordChangedDate,
1375												  lastLockedOutDate);
1376			
1377			return u;
1378		}
1379
1380		/// <summary>
1381		/// Compares password values based on the MembershipPasswordFormat.
1382		/// </summary>
1383		/// <param name="password"></param>
1384		/// <param name="dbpassword"></param>
1385		/// <returns></returns>
1386		bool CheckPassword(string password, string dbpassword)
1387		{
1388			string pass1 = password;
1389			string pass2 = dbpassword;
1390
1391			switch (PasswordFormat)
1392			{
1393				case MembershipPasswordFormat.Encrypted:
1394					pass2 = UnEncodePassword(dbpassword);
1395					break;
1396
1397				case MembershipPasswordFormat.Hashed:
1398					pass1 = EncodePassword(password);
1399					break;
1400
1401				default:
1402					break;
1403			}
1404
1405			if (pass1.Equals(pass2))
1406				return true;
1407			else
1408				return false;
1409		}
1410
1411		/// <summary>
1412		/// Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.
1413		/// </summary>
1414		/// <param name="password"></param>
1415		/// <returns></returns>
1416		string EncodePassword(string password)
1417		{
1418			if (string.IsNullOrEmpty(password))
1419				return password;
1420
1421			string encodedPassword = password;
1422
1423			switch (PasswordFormat)
1424			{
1425				case MembershipPasswordFormat.Clear:
1426					break;
1427
1428				case MembershipPasswordFormat.Encrypted:
1429					encodedPassword = Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
1430					break;
1431
1432				case MembershipPasswordFormat.Hashed:
1433					HMACSHA1 hash = new HMACSHA1();
1434					if (machineKeyIsAutoGenerated)
1435						hash.Key = MachineKeySection.Config.GetValidationKey ();
1436					else
1437						hash.Key = HexToByte(m_MachineKey.ValidationKey);
1438					encodedPassword = Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
1439					break;
1440
1441				default:
1442					throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
1443			}
1444
1445			return encodedPassword;
1446		}
1447
1448		/// <summary>
1449		/// Decrypts or leaves the password clear based on the PasswordFormat.
1450		/// </summary>
1451		/// <param name="encodedPassword"></param>
1452		/// <returns></returns>
1453		string UnEncodePassword(string encodedPassword)
1454		{
1455			string password = encodedPassword;
1456
1457			switch (PasswordFormat)
1458			{
1459				case MembershipPasswordFormat.Clear:
1460					break;
1461
1462				case MembershipPasswordFormat.Encrypted:
1463					password = Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
1464					break;
1465
1466				case MembershipPasswordFormat.Hashed:
1467					throw new ProviderException(Properties.Resources.ErrCantDecodeHashedPw);
1468
1469				default:
1470					throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
1471			}
1472
1473			return password;
1474		}
1475
1476		/// <summary>
1477		/// Converts a hexadecimal string to a byte array. Used to convert encryption
1478		/// key values from the configuration.
1479		/// </summary>
1480		/// <param name="hexString"></param>
1481		/// <returns></returns>
1482		byte[] HexToByte(string hexString)
1483		{
1484			byte[] returnBytes = new byte[hexString.Length / 2];
1485			for (int i = 0; i < returnBytes.Length; i++)
1486				returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
1487
1488			return returnBytes;
1489		}
1490
1491		/// <summary>
1492		/// A helper method that performs the checks and updates associated with
1493		/// password failure tracking.
1494		/// </summary>
1495		/// <param name="username"></param>
1496		/// <param name="failType"></param>
1497		void UpdateFailureCount(string username, FailureType failType)
1498		{
1499			DateTime windowStart = new DateTime();
1500			int failureCount = 0;
1501
1502			using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
1503			{
1504				// Fetch user data from database
1505				using (SqliteCommand dbCommand = dbConn.CreateCommand())
1506				{
1507					dbCommand.CommandText = string.Format("SELECT \"FailedPasswordAttemptCount\", \"FailedPasswordAttemptWindowStart\", \"FailedPasswordAnswerAttemptCount\", \"FailedPasswordAnswerAttemptWindowStart\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1508
1509					AddParameter (dbCommand,"@Username", username);
1510					AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1511
1512					try
1513					{
1514						dbConn.Open();
1515						dbCommand.Prepare();
1516
1517						using (SqliteDataReader reader = dbCommand.ExecuteReader())
1518						{
1519							if (reader.HasRows)
1520							{
1521								reader.Read();
1522
1523								if (failType.Equals(FailureType.Password))
1524								{
1525									failureCount = reader.GetInt32(0);
1526									windowStart = reader.GetDateTime(1);
1527								}
1528								else if (failType.Equals(FailureType.PasswordAnswer))
1529								{
1530									failureCount = reader.GetInt32(2);
1531									windowStart = reader.GetDateTime(3);
1532								}
1533							}
1534						}
1535					}
1536					catch (SqliteException e)
1537					{
1538						Trace.WriteLine(e.ToString());
1539						throw new ProviderException(Properties.Resources.ErrOperationAborted);
1540					}
1541					finally
1542					{
1543						if (dbConn != null)
1544							dbConn.Close();
1545					}
1546				}
1547
1548				// Calculate failture count and update database
1549				using (SqliteCommand dbCommand = dbConn.CreateCommand())
1550				{
1551					DateTime windowEnd = windowStart.AddMinutes(m_PasswordAttemptWindow);
1552
1553					try
1554					{
1555						if (failureCount == 0 || DateTime.Now > windowEnd)
1556						{
1557							// First password failure or outside of PasswordAttemptWindow. 
1558							// Start a new password failure count from 1 and a new window starting now.
1559
1560							if (failType.Equals(FailureType.Password))
1561							{
1562								dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAttemptCount\" = @Count, \"FailedPasswordAttemptWindowStart\" = @WindowStart WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1563							}
1564							else if (failType.Equals(FailureType.PasswordAnswer))
1565							{
1566								dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAnswerAttemptCount\" = @Count, \"FailedPasswordAnswerAttemptWindowStart\" = @WindowStart WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1567							}
1568
1569							AddParameter (dbCommand,"@Count", 1);
1570							AddParameter (dbCommand,"@WindowStart", DateTime.Now);
1571							AddParameter (dbCommand,"@Username", username);
1572							AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1573
1574							if (dbCommand.ExecuteNonQuery() < 0)
1575								throw new ProviderException(Properties.Resources.ErrCantUpdateFailtureCountAndWindowStart);
1576						}
1577						else
1578						{
1579							failureCount++;
1580
1581							if (failureCount >= m_MaxInvalidPasswordAttempts)
1582							{
1583								// Password attempts have exceeded the failure threshold. Lock out
1584								// the user.
1585								dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"IsLockedOut\" = @IsLockedOut, \"LastLockedOutDate\" = @LastLockedOutDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1586
1587								AddParameter (dbCommand,"@IsLockedOut", true);
1588								AddParameter (dbCommand,"@LastLockedOutDate", DateTime.Now);
1589								AddParameter (dbCommand,"@Username", username);
1590								AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1591
1592								if (dbCommand.ExecuteNonQuery() < 0)
1593									throw new ProviderException(string.Format(Properties.Resources.ErrCantLogoutUser, username));
1594							}
1595							else
1596							{
1597								// Password attempts have not exceeded the failu…

Large files files are truncated, but you can click here to view the full file