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