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