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

   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
  31 using System;
  32 using System.Data;
  33 using System.Data.Common;
  34 using System.Collections.Generic;
  35 using System.Collections.Specialized;
  36 using System.Text;
  37 using System.Security.Cryptography;
  38 using System.Web.Hosting;
  39 using System.Web.Configuration;
  40 using System.Web.Security;
  41 using System.Configuration;
  42 using System.Configuration.Provider;
  43 using System.Diagnostics;
  44
  45 using Mono.Data.Sqlite;
  46
  47 namespace 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 = MachineKeySectionUtils.ValidationKeyBytes ();
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 failure threshold. Update
1598                                                                 // the failure counts. Leave the window the same.
1599                                                                 if (failType.Equals(FailureType.Password))
1600                                                                 {
1601                                                                         dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAttemptCount\" = @Count WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1602                                                                 }
1603                                                                 else if (failType.Equals(FailureType.PasswordAnswer))
1604                                                                 {
1605                                                                         dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAnswerAttemptCount\" = @Count WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
1606                                                                 }
1607
1608                                                                 AddParameter (dbCommand,"@Count", failureCount);
1609                                                                 AddParameter (dbCommand,"@Username", username);
1610                                                                 AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
1611
1612                                                                 if (dbCommand.ExecuteNonQuery() < 0)
1613                                                                         throw new ProviderException(Properties.Resources.ErrCantUpdateFailtureCount);
1614                                                         }
1615                                                 }
1616                                         }
1617                                         catch (SqliteException e)
1618                                         {
1619                                                 Trace.WriteLine(e.ToString());
1620                                                 throw new ProviderException(Properties.Resources.ErrOperationAborted);
1621                                         }
1622                                         finally
1623                                         {
1624                                                 if (dbConn != null)
1625                                                         dbConn.Close();
1626                                         }
1627                                 }
1628                         }
1629                 }
1630
1631                 enum FailureType
1632                 {
1633                         Password,
1634                         PasswordAnswer
1635                 }
1636                 #endregion
1637         }
1638 }
1639 #endif