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

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