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

   1 //
   2 // System.Web.Security.SqlMembershipProvider
   3 //
   4 // Authors:
   5 //      Ben Maurer (bmaurer@users.sourceforge.net)
   6 //      Lluis Sanchez Gual (lluis@novell.com)
   7 //      Chris Toshok (toshok@ximian.com)
   8 //
   9 // (C) 2003 Ben Maurer
  10 // Copyright (c) 2005,2006 Novell, Inc (http://www.novell.com)
  11 //
  12 // Permission is hereby granted, free of charge, to any person obtaining
  13 // a copy of this software and associated documentation files (the
  14 // "Software"), to deal in the Software without restriction, including
  15 // without limitation the rights to use, copy, modify, merge, publish,
  16 // distribute, sublicense, and/or sell copies of the Software, and to
  17 // permit persons to whom the Software is furnished to do so, subject to
  18 // the following conditions:
  19 //
  20 // The above copyright notice and this permission notice shall be
  21 // included in all copies or substantial portions of the Software.
  22 //
  23 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  24 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  25 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  26 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  27 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  28 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  29 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  30 //
  31
  32 #if NET_2_0
  33 using System.Collections;
  34 using System.Collections.Specialized;
  35 using System.Configuration;
  36 using System.Configuration.Provider;
  37 using System.Data;
  38 using System.Data.Common;
  39 using System.Text;
  40 using System.Web.Configuration;
  41 using System.Security.Cryptography;
  42
  43 namespace System.Web.Security {
  44         public class SqlMembershipProvider : MembershipProvider {
  45
  46                 const int SALT_BYTES = 16;
  47
  48                 /* can this be done just by setting the datetime fields to 0? */
  49                 DateTime DefaultDateTime = new DateTime (1754,1,1).ToUniversalTime();
  50
  51                 bool enablePasswordReset;
  52                 bool enablePasswordRetrieval;
  53                 int maxInvalidPasswordAttempts;
  54                 MembershipPasswordFormat passwordFormat;
  55                 bool requiresQuestionAndAnswer;
  56                 bool requiresUniqueEmail;
  57                 int minRequiredNonAlphanumericCharacters;
  58                 int minRequiredPasswordLength;
  59                 int passwordAttemptWindow;
  60                 string passwordStrengthRegularExpression;
  61                 TimeSpan userIsOnlineTimeWindow;
  62
  63                 ConnectionStringSettings connectionString;
  64                 DbProviderFactory factory;
  65                 DbConnection connection;
  66
  67                 string applicationName;
  68
  69                 static object lockobj = new object();
  70
  71                 void InitConnection ()
  72                 {
  73                         if (connection == null) {
  74                                 lock (lockobj) {
  75                                         if (connection != null)
  76                                                 return;
  77
  78                                         factory = ProvidersHelper.GetDbProviderFactory (connectionString.ProviderName);
  79                                         connection = factory.CreateConnection();
  80                                         connection.ConnectionString = connectionString.ConnectionString;
  81
  82                                         connection.Open ();
  83                                 }
  84                         }
  85                 }
  86
  87                 void AddParameter (DbCommand command, string parameterName, string parameterValue)
  88                 {
  89                         DbParameter dbp = command.CreateParameter ();
  90                         dbp.ParameterName = parameterName;
  91                         dbp.Value = parameterValue;
  92                         dbp.Direction = ParameterDirection.Input;
  93                         command.Parameters.Add (dbp);
  94                 }
  95
  96                 void CheckParam (string pName, string p, int length)
  97                 {
  98                         if (p == null)
  99                                 throw new ArgumentNullException (pName);
 100                         if (p.Length == 0 || p.Length > length || p.IndexOf (",") != -1)
 101                                 throw new ArgumentException (String.Format ("invalid format for {0}", pName));
 102                 }
 103
 104                 SymmetricAlgorithm GetAlg (out byte[] decryptionKey)
 105                 {
 106                         MachineKeySection section = (MachineKeySection)WebConfigurationManager.GetSection ("system.web/machineKey");
 107
 108                         if (section.DecryptionKey.StartsWith ("AutoGenerate"))
 109                                 throw new ProviderException ("You must explicitly specify a decryption key in the <machineKey> section when using encrypted passwords.");
 110
 111                         string alg_type = section.Decryption;
 112                         if (alg_type == "Auto")
 113                                 alg_type = "AES";
 114
 115                         SymmetricAlgorithm alg = null;
 116                         if (alg_type == "AES")
 117                                 alg = Rijndael.Create ();
 118                         else if (alg_type == "3DES")
 119                                 alg = TripleDES.Create ();
 120                         else
 121                                 throw new ProviderException (String.Format ("Unsupported decryption attribute '{0}' in <machineKey> configuration section", alg_type));
 122
 123                         decryptionKey = section.DecryptionKey192Bits;
 124                         return alg;
 125                 }
 126
 127                 protected override byte[] DecryptPassword (byte[] encodedPassword)
 128                 {
 129                         byte[] decryptionKey;
 130
 131                         using (SymmetricAlgorithm alg = GetAlg (out decryptionKey)) {
 132                                 alg.Key = decryptionKey;
 133
 134                                 using (ICryptoTransform decryptor = alg.CreateDecryptor ()) {
 135
 136                                         byte[] buf = decryptor.TransformFinalBlock (encodedPassword, 0, encodedPassword.Length);
 137                                         byte[] rv = new byte[buf.Length - SALT_BYTES];
 138
 139                                         Array.Copy (buf, 16, rv, 0, buf.Length - 16);
 140                                         return rv;
 141                                 }
 142                         }
 143                 }
 144
 145                 protected override byte[] EncryptPassword (byte[] password)
 146                 {
 147                         byte[] decryptionKey;
 148                         byte[] iv = new byte[SALT_BYTES];
 149
 150                         Array.Copy (password, 0, iv, 0, SALT_BYTES);
 151                         Array.Clear (password, 0, SALT_BYTES);
 152
 153                         using (SymmetricAlgorithm alg = GetAlg (out decryptionKey)) {
 154                                 using (ICryptoTransform encryptor = alg.CreateEncryptor (decryptionKey, iv)) {
 155                                         return encryptor.TransformFinalBlock (password, 0, password.Length);
 156                                 }
 157                         }
 158                 }
 159
 160                 public override bool ChangePassword (string username, string oldPwd, string newPwd)
 161                 {
 162                         if (username != null) username = username.Trim ();
 163                         if (oldPwd != null) oldPwd = oldPwd.Trim ();
 164                         if (newPwd != null) newPwd = newPwd.Trim ();
 165
 166                         CheckParam ("username", username, 256);
 167                         CheckParam ("oldPwd", oldPwd, 128);
 168                         CheckParam ("newPwd", newPwd, 128);
 169
 170                         MembershipUser user = GetUser (username, false);
 171                         if (user == null) throw new ProviderException ("could not find user in membership database");
 172                         if (user.IsLockedOut) throw new MembershipPasswordException ("user is currently locked out");
 173
 174                         InitConnection();
 175
 176                         DbTransaction trans = connection.BeginTransaction ();
 177
 178                         string commandText;
 179                         DbCommand command;
 180
 181                         try {
 182                                 MembershipPasswordFormat passwordFormat;
 183                                 string db_salt;
 184
 185                                 bool valid = ValidateUsingPassword (trans, username, oldPwd, out passwordFormat, out db_salt);
 186                                 if (valid) {
 187
 188                                         EmitValidatingPassword (username, newPwd, false);
 189
 190                                         string db_password = EncodePassword (newPwd, passwordFormat, db_salt);
 191
 192                                         DateTime now = DateTime.Now.ToUniversalTime ();
 193
 194                                         commandText = @"
 195 UPDATE m
 196    SET Password = @Password,
 197        FailedPasswordAttemptCount = 0,
 198        FailedPasswordAttemptWindowStart = @DefaultDateTime,
 199        LastPasswordChangedDate = @Now
 200   FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
 201  WHERE m.ApplicationId = a.ApplicationId
 202    AND u.ApplicationId = a.ApplicationId
 203    AND m.UserId = u.UserId
 204    AND u.LoweredUserName = LOWER(@UserName)
 205    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
 206
 207                                         command = factory.CreateCommand ();
 208                                         command.Transaction = trans;
 209                                         command.CommandText = commandText;
 210                                         command.Connection = connection;
 211                                         command.CommandType = CommandType.Text;
 212                                         AddParameter (command, "UserName", user.UserName);
 213                                         AddParameter (command, "Now", now.ToString ());
 214                                         AddParameter (command, "Password", db_password);
 215                                         AddParameter (command, "ApplicationName", ApplicationName);
 216                                         AddParameter (command, "DefaultDateTime", DefaultDateTime.ToString());
 217
 218                                         if (1 != (int)command.ExecuteNonQuery ())
 219                                                 throw new ProviderException ("failed to update Membership table");
 220                                 }
 221
 222                                 trans.Commit ();
 223                                 return valid;
 224                         }
 225                         catch (ProviderException) {
 226                                 trans.Rollback ();
 227                                 throw;
 228                         }
 229                         catch (Exception e) {
 230                                 trans.Rollback ();
 231                                 throw new ProviderException ("error changing password", e);
 232                         }
 233                 }
 234
 235                 public override bool ChangePasswordQuestionAndAnswer (string username, string password, string newPwdQuestion, string newPwdAnswer)
 236                 {
 237                         if (username != null) username = username.Trim ();
 238                         if (newPwdQuestion != null) newPwdQuestion = newPwdQuestion.Trim ();
 239                         if (newPwdAnswer != null) newPwdAnswer = newPwdAnswer.Trim ();
 240
 241                         CheckParam ("username", username, 256);
 242                         if (RequiresQuestionAndAnswer)
 243                                 CheckParam ("newPwdQuestion", newPwdQuestion, 128);
 244                         if (RequiresQuestionAndAnswer)
 245                                 CheckParam ("newPwdAnswer", newPwdAnswer, 128);
 246
 247                         MembershipUser user = GetUser (username, false);
 248                         if (user == null) throw new ProviderException ("could not find user in membership database");
 249                         if (user.IsLockedOut) throw new MembershipPasswordException ("user is currently locked out");
 250
 251                         InitConnection();
 252
 253                         DbTransaction trans = connection.BeginTransaction ();
 254
 255                         string commandText;
 256                         DbCommand command;
 257
 258                         try {
 259                                 MembershipPasswordFormat passwordFormat;
 260                                 string db_salt;
 261
 262                                 bool valid = ValidateUsingPassword (trans, username, password, out passwordFormat, out db_salt);
 263                                 if (valid) {
 264
 265                                         string db_passwordAnswer = EncodePassword (newPwdAnswer, passwordFormat, db_salt);
 266
 267                                         commandText = @"
 268 UPDATE m
 269    SET PasswordQuestion = @PasswordQuestion,
 270        PasswordAnswer = @PasswordAnswer,
 271        FailedPasswordAttemptCount = 0,
 272        FailedPasswordAttemptWindowStart = @DefaultDateTime,
 273        FailedPasswordAnswerAttemptCount = 0,
 274        FailedPasswordAnswerAttemptWindowStart = @DefaultDateTime
 275   FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
 276  WHERE m.ApplicationId = a.ApplicationId
 277    AND u.ApplicationId = a.ApplicationId
 278    AND m.UserId = u.UserId
 279    AND u.LoweredUserName = LOWER(@UserName)
 280    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
 281
 282                                         command = factory.CreateCommand ();
 283                                         command.Transaction = trans;
 284                                         command.CommandText = commandText;
 285                                         command.Connection = connection;
 286                                         command.CommandType = CommandType.Text;
 287                                         AddParameter (command, "UserName", user.UserName);
 288                                         AddParameter (command, "PasswordQuestion", newPwdQuestion);
 289                                         AddParameter (command, "PasswordAnswer", db_passwordAnswer);
 290                                         AddParameter (command, "ApplicationName", ApplicationName);
 291                                         AddParameter (command, "DefaultDateTime", DefaultDateTime.ToString());
 292
 293                                         if (1 != (int)command.ExecuteNonQuery ())
 294                                                 throw new ProviderException ("failed to update Membership table");
 295
 296                                 }
 297
 298                                 trans.Commit ();
 299                                 return valid;
 300                         }
 301                         catch (ProviderException) {
 302                                 trans.Rollback ();
 303                                 throw;
 304                         }
 305                         catch (Exception e) {
 306                                 trans.Rollback ();
 307                                 throw new ProviderException ("error changing password question and answer", e);
 308                         }
 309                 }
 310
 311                 public override MembershipUser CreateUser (string username,
 312                                                            string password,
 313                                                            string email,
 314                                                            string pwdQuestion,
 315                                                            string pwdAnswer,
 316                                                            bool isApproved,
 317                                                            object providerUserKey,
 318                                                            out MembershipCreateStatus status)
 319                 {
 320                         if (username != null) username = username.Trim ();
 321                         if (password != null) password = password.Trim ();
 322                         if (email != null) email = email.Trim ();
 323                         if (pwdQuestion != null) pwdQuestion = pwdQuestion.Trim ();
 324                         if (pwdAnswer != null) pwdAnswer = pwdAnswer.Trim ();
 325
 326                         /* some initial validation */
 327                         if (username == null || username.Length == 0 || username.Length > 256 || username.IndexOf (",") != -1) {
 328                                 status = MembershipCreateStatus.InvalidUserName;
 329                                 return null;
 330                         }
 331                         if (password == null || password.Length == 0 || password.Length > 128) {
 332                                 status = MembershipCreateStatus.InvalidPassword;
 333                                 return null;
 334                         }
 335                         if (RequiresUniqueEmail && (email == null || email.Length == 0)) {
 336                                 status = MembershipCreateStatus.InvalidEmail;
 337                                 return null;
 338                         }
 339                         if (RequiresQuestionAndAnswer &&
 340                             (pwdQuestion == null ||
 341                              pwdQuestion.Length == 0 || pwdQuestion.Length > 256)) {
 342                                 status = MembershipCreateStatus.InvalidQuestion;
 343                                 return null;
 344                         }
 345                         if (RequiresQuestionAndAnswer &&
 346                             (pwdAnswer == null ||
 347                              pwdAnswer.Length == 0 || pwdAnswer.Length > 128)) {
 348                                 status = MembershipCreateStatus.InvalidAnswer;
 349                                 return null;
 350                         }
 351                         if (providerUserKey != null && ! (providerUserKey is Guid)) {
 352                                 status = MembershipCreateStatus.InvalidProviderUserKey;
 353                                 return null;
 354                         }
 355
 356                         /* encode our password/answer using the
 357                          * "passwordFormat" configuration option */
 358                         string passwordSalt = "";
 359
 360                         RandomNumberGenerator rng = RandomNumberGenerator.Create ();
 361                         byte[] salt = new byte[SALT_BYTES];
 362                         rng.GetBytes (salt);
 363                         passwordSalt = Convert.ToBase64String (salt);
 364
 365                         password = EncodePassword (password, PasswordFormat, passwordSalt);
 366                         if (RequiresQuestionAndAnswer)
 367                                 pwdAnswer = EncodePassword (pwdAnswer, PasswordFormat, passwordSalt);
 368
 369                         /* make sure the hashed/encrypted password and
 370                          * answer are still under 128 characters. */
 371                         if (password.Length > 128) {
 372                                 status = MembershipCreateStatus.InvalidPassword;
 373                                 return null;
 374                         }
 375
 376                         if (RequiresQuestionAndAnswer) {
 377                                 if (pwdAnswer.Length > 128) {
 378                                         status = MembershipCreateStatus.InvalidAnswer;
 379                                         return null;
 380                                 }
 381                         }
 382
 383                         InitConnection();
 384
 385                         DbTransaction trans = connection.BeginTransaction ();
 386
 387                         string commandText;
 388                         DbCommand command;
 389
 390                         try {
 391
 392                                 Guid applicationId;
 393                                 Guid userId;
 394
 395                                 /* get the application id since it seems that inside transactions we
 396                                    can't insert using subqueries.. */
 397
 398                                 commandText = @"
 399 SELECT ApplicationId
 400   FROM dbo.aspnet_Applications
 401  WHERE dbo.aspnet_Applications.LoweredApplicationName = LOWER(@ApplicationName)
 402 ";
 403                                 command = factory.CreateCommand ();
 404                                 command.Transaction = trans;
 405                                 command.CommandText = commandText;
 406                                 command.Connection = connection;
 407                                 command.CommandType = CommandType.Text;
 408                                 AddParameter (command, "ApplicationName", ApplicationName);
 409
 410                                 DbDataReader reader = command.ExecuteReader ();
 411                                 reader.Read ();
 412                                 applicationId = reader.GetGuid (0);
 413                                 reader.Close ();
 414
 415                                 /* check for unique username, email and
 416                                  * provider user key, if applicable */
 417
 418                                 commandText = @"
 419 SELECT COUNT(*)
 420   FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
 421  WHERE u.LoweredUserName = LOWER(@UserName)
 422    AND u.ApplicationId = a.ApplicationId
 423    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
 424
 425                                 command = factory.CreateCommand ();
 426                                 command.Transaction = trans;
 427                                 command.CommandText = commandText;
 428                                 command.Connection = connection;
 429                                 command.CommandType = CommandType.Text;
 430                                 AddParameter (command, "UserName", username);
 431                                 AddParameter (command, "ApplicationName", ApplicationName);
 432
 433                                 if (0 != (int)command.ExecuteScalar()) {
 434                                         status = MembershipCreateStatus.DuplicateUserName;
 435                                         trans.Rollback ();
 436                                         return null;
 437                                 }
 438
 439
 440                                 if (requiresUniqueEmail) {
 441                                         commandText = @"
 442 SELECT COUNT(*)
 443   FROM dbo.aspnet_Membership, dbo.aspnet_Applications
 444  WHERE dbo.aspnet_Membership.Email = @Email
 445    AND dbo.aspnet_Membership.ApplicationId = dbo.aspnet_Applications.ApplicationId
 446    AND dbo.aspnet_Applications.LoweredApplicationName = LOWER(@ApplicationName)";
 447
 448                                         command = factory.CreateCommand ();
 449                                         command.Transaction = trans;
 450                                         command.CommandText = commandText;
 451                                         command.Connection = connection;
 452                                         command.CommandType = CommandType.Text;
 453                                         AddParameter (command, "Email", email);
 454                                         AddParameter (command, "ApplicationName", ApplicationName);
 455
 456                                         if (0 != (int)command.ExecuteScalar()) {
 457                                                 status = MembershipCreateStatus.DuplicateEmail;
 458                                                 trans.Rollback ();
 459                                                 return null;
 460                                         }
 461                                 }
 462
 463                                 if (providerUserKey != null) {
 464                                         commandText = @"
 465 SELECT COUNT(*)
 466   FROM dbo.aspnet_Membership, dbo.aspnet_Applications
 467  WHERE dbo.aspnet_Membership.UserId = @ProviderUserKey
 468    AND dbo.aspnet_Membership.ApplicationId = dbo.aspnet_Applications.ApplicationId
 469    AND dbo.aspnet_Applications.LoweredApplicationName = LOWER(@ApplicationName)";
 470
 471                                         command = factory.CreateCommand ();
 472                                         command.Transaction = trans;
 473                                         command.CommandText = commandText;
 474                                         command.Connection = connection;
 475                                         command.CommandType = CommandType.Text;
 476                                         AddParameter (command, "Email", email);
 477                                         AddParameter (command, "ApplicationName", ApplicationName);
 478
 479                                         if (0 != (int)command.ExecuteScalar()) {
 480                                                 status = MembershipCreateStatus.DuplicateProviderUserKey;
 481                                                 trans.Rollback ();
 482                                                 return null;
 483                                         }
 484                                 }
 485
 486                                 /* first into the Users table */
 487                                 commandText = @"
 488 INSERT into dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, LastActivityDate)
 489 VALUES (@ApplicationId, NEWID(), @UserName, LOWER(@UserName), GETDATE())
 490 ";
 491
 492                                 command = factory.CreateCommand ();
 493                                 command.Transaction = trans;
 494                                 command.CommandText = commandText;
 495                                 command.Connection = connection;
 496                                 command.CommandType = CommandType.Text;
 497                                 AddParameter (command, "UserName", username);
 498                                 AddParameter (command, "ApplicationId", applicationId.ToString());
 499
 500                                 if (command.ExecuteNonQuery() != 1) {
 501                                         status = MembershipCreateStatus.UserRejected; /* XXX */
 502                                         trans.Rollback ();
 503                                         return null;
 504                                 }
 505
 506                                 /* then get the newly created userid */
 507
 508                                 commandText = @"
 509 SELECT UserId
 510   FROM dbo.aspnet_Users
 511  WHERE dbo.aspnet_Users.LoweredUserName = LOWER(@UserName)
 512 ";
 513                                 command = factory.CreateCommand ();
 514                                 command.Transaction = trans;
 515                                 command.CommandText = commandText;
 516                                 command.Connection = connection;
 517                                 command.CommandType = CommandType.Text;
 518                                 AddParameter (command, "UserName", username);
 519
 520                                 reader = command.ExecuteReader ();
 521                                 reader.Read ();
 522                                 userId = reader.GetGuid (0);
 523                                 reader.Close ();
 524
 525                                 /* then insert into the Membership table */
 526                                 commandText = String.Format (@"
 527 INSERT into dbo.aspnet_Membership
 528 VALUES (@ApplicationId,
 529         @UserId,
 530         @Password, @PasswordFormat, @PasswordSalt,
 531         NULL,
 532         {0}, {1},
 533         {2}, {3},
 534         0, 0,
 535         GETDATE(), GETDATE(), @DefaultDateTime,
 536         @DefaultDateTime,
 537         0, @DefaultDateTime, 0, @DefaultDateTime, NULL)",
 538                                                              email == null ? "NULL" : "@Email",
 539                                                              email == null ? "NULL" : "LOWER(@Email)",
 540                                                              pwdQuestion == null ? "NULL" : "@PasswordQuestion",
 541                                                              pwdAnswer == null ? "NULL" : "@PasswordAnswer");
 542
 543                                 command = factory.CreateCommand ();
 544                                 command.Transaction = trans;
 545                                 command.CommandText = commandText;
 546                                 command.Connection = connection;
 547                                 command.CommandType = CommandType.Text;
 548                                 AddParameter (command, "ApplicationId", applicationId.ToString());
 549                                 AddParameter (command, "UserId", userId.ToString());
 550                                 if (email != null)
 551                                         AddParameter (command, "Email", email);
 552                                 AddParameter (command, "Password", password);
 553                                 AddParameter (command, "PasswordFormat", ((int)PasswordFormat).ToString());
 554                                 AddParameter (command, "PasswordSalt", passwordSalt);
 555                                 if (pwdQuestion != null)
 556                                         AddParameter (command, "PasswordQuestion", pwdQuestion);
 557                                 if (pwdAnswer != null)
 558                                         AddParameter (command, "PasswordAnswer", pwdAnswer);
 559                                 AddParameter (command, "DefaultDateTime", DefaultDateTime.ToString());
 560
 561                                 if (command.ExecuteNonQuery() != 1) {
 562                                         status = MembershipCreateStatus.UserRejected; /* XXX */
 563                                         return null;
 564                                 }
 565
 566                                 trans.Commit ();
 567
 568                                 status = MembershipCreateStatus.Success;
 569
 570                                 return GetUser (username, false);
 571                         }
 572                         catch {
 573                                 status = MembershipCreateStatus.ProviderError;
 574                                 trans.Rollback ();
 575                                 return null;
 576                         }
 577                 }
 578
 579                 [MonoTODO]
 580                 public override bool DeleteUser (string username, bool deleteAllRelatedData)
 581                 {
 582                         CheckParam ("username", username, 256);
 583
 584                         if (deleteAllRelatedData) {
 585                                 /* delete everything from the
 586                                  * following features as well:
 587                                  *
 588                                  * Roles
 589                                  * Profile
 590                                  * WebParts Personalization
 591                                  */
 592                         }
 593
 594                         DbTransaction trans = connection.BeginTransaction ();
 595
 596                         DbCommand command;
 597                         string commandText;
 598
 599                         InitConnection();
 600
 601                         try {
 602                                 /* delete from the Membership table */
 603                                 commandText = @"
 604 DELETE dbo.aspnet_Membership
 605   FROM dbo.aspnet_Membership, dbo.aspnet_Users, dbo.aspnet_Applications
 606  WHERE dbo.aspnet_Membership.UserId = dbo.aspnet_Users.UserId
 607    AND dbo.aspnet_Membership.ApplicationId = dbo.aspnet_Applications.ApplicationId
 608    AND dbo.aspnet_Users.LoweredUserName = LOWER (@UserName)
 609    AND dbo.aspnet_Users.ApplicationId = dbo.aspnet_Applications.ApplicationId
 610    AND dbo.aspnet_Applications.LoweredApplicationName = LOWER(@ApplicationName)";
 611
 612                                 command = factory.CreateCommand ();
 613                                 command.Transaction = trans;
 614                                 command.CommandText = commandText;
 615                                 command.Connection = connection;
 616                                 command.CommandType = CommandType.Text;
 617                                 AddParameter (command, "UserName", username);
 618                                 AddParameter (command, "ApplicationName", ApplicationName);
 619
 620                                 if (1 != command.ExecuteNonQuery())
 621                                         throw new ProviderException ("failed to delete from Membership table");
 622
 623                                 /* delete from the User table */
 624                                 commandText = @"
 625 DELETE dbo.aspnet_Users
 626   FROM dbo.aspnet_Users, dbo.aspnet_Applications
 627  WHERE dbo.aspnet_Users.LoweredUserName = LOWER (@UserName)
 628    AND dbo.aspnet_Users.ApplicationId = dbo.aspnet_Applications.ApplicationId
 629    AND dbo.aspnet_Applications.LoweredApplicationName = LOWER(@ApplicationName)";
 630
 631                                 command = factory.CreateCommand ();
 632                                 command.Transaction = trans;
 633                                 command.CommandText = commandText;
 634                                 command.Connection = connection;
 635                                 command.CommandType = CommandType.Text;
 636                                 AddParameter (command, "UserName", username);
 637                                 AddParameter (command, "ApplicationName", ApplicationName);
 638
 639                                 if (1 != command.ExecuteNonQuery())
 640                                         throw new ProviderException ("failed to delete from User table");
 641
 642                                 trans.Commit ();
 643
 644                                 return true;
 645                         }
 646                         catch {
 647                                 trans.Rollback ();
 648                                 return false;
 649                         }
 650                 }
 651
 652                 public virtual string GeneratePassword ()
 653                 {
 654                         return Membership.GeneratePassword (minRequiredPasswordLength, minRequiredNonAlphanumericCharacters);
 655                 }
 656
 657                 public override MembershipUserCollection FindUsersByEmail (string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
 658                 {
 659                         CheckParam ("emailToMatch", emailToMatch, 256);
 660
 661                         if (pageIndex < 0)
 662                                 throw new ArgumentException ("pageIndex must be >= 0");
 663                         if (pageSize < 0)
 664                                 throw new ArgumentException ("pageSize must be >= 0");
 665                         if (pageIndex * pageSize + pageSize - 1 > Int32.MaxValue)
 666                                 throw new ArgumentException ("pageIndex and pageSize are too large");
 667
 668                         string commandText;
 669
 670                         InitConnection();
 671
 672                         commandText = @"
 673 SELECT u.UserName, m.UserId, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
 674        m.IsLockedOut, m.CreateDate, m.LastLoginDate, u.LastActivityDate,
 675        m.LastPasswordChangedDate, m.LastLockoutDate
 676   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
 677  WHERE m.ApplicationId = a.ApplicationId
 678    AND u.ApplicationId = a.ApplicationId
 679    AND m.UserId = u.UserId
 680    AND m.Email LIKE @Email
 681    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
 682
 683                         DbCommand command = factory.CreateCommand ();
 684                         command.CommandText = commandText;
 685                         command.Connection = connection;
 686                         command.CommandType = CommandType.Text;
 687                         AddParameter (command, "Email", emailToMatch);
 688                         AddParameter (command, "ApplicationName", ApplicationName);
 689
 690                         MembershipUserCollection c = BuildMembershipUserCollection (command, pageIndex, pageSize, out totalRecords);
 691
 692                         return c;
 693                 }
 694
 695                 public override MembershipUserCollection FindUsersByName (string nameToMatch, int pageIndex, int pageSize, out int totalRecords)
 696                 {
 697                         CheckParam ("nameToMatch", nameToMatch, 256);
 698
 699                         if (pageIndex < 0)
 700                                 throw new ArgumentException ("pageIndex must be >= 0");
 701                         if (pageSize < 0)
 702                                 throw new ArgumentException ("pageSize must be >= 0");
 703                         if (pageIndex * pageSize + pageSize - 1 > Int32.MaxValue)
 704                                 throw new ArgumentException ("pageIndex and pageSize are too large");
 705
 706                         string commandText;
 707
 708                         InitConnection();
 709
 710                         commandText = @"
 711 SELECT u.UserName, m.UserId, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
 712        m.IsLockedOut, m.CreateDate, m.LastLoginDate, u.LastActivityDate,
 713        m.LastPasswordChangedDate, m.LastLockoutDate
 714   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
 715  WHERE m.ApplicationId = a.ApplicationId
 716    AND u.ApplicationId = a.ApplicationId
 717    AND m.UserId = u.UserId
 718    AND u.UserName LIKE @UserName
 719    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
 720
 721                         DbCommand command = factory.CreateCommand ();
 722                         command.CommandText = commandText;
 723                         command.Connection = connection;
 724                         command.CommandType = CommandType.Text;
 725                         AddParameter (command, "UserName", nameToMatch);
 726                         AddParameter (command, "ApplicationName", ApplicationName);
 727
 728                         MembershipUserCollection c = BuildMembershipUserCollection (command, pageIndex, pageSize, out totalRecords);
 729
 730                         return c;
 731                 }
 732
 733                 public override MembershipUserCollection GetAllUsers (int pageIndex, int pageSize, out int totalRecords)
 734                 {
 735                         if (pageIndex < 0)
 736                                 throw new ArgumentException ("pageIndex must be >= 0");
 737                         if (pageSize < 0)
 738                                 throw new ArgumentException ("pageSize must be >= 0");
 739                         if (pageIndex * pageSize + pageSize - 1 > Int32.MaxValue)
 740                                 throw new ArgumentException ("pageIndex and pageSize are too large");
 741
 742                         string commandText;
 743
 744                         InitConnection();
 745
 746                         commandText = @"
 747 SELECT u.UserName, m.UserId, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
 748        m.IsLockedOut, m.CreateDate, m.LastLoginDate, u.LastActivityDate,
 749        m.LastPasswordChangedDate, m.LastLockoutDate
 750   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
 751  WHERE m.ApplicationId = a.ApplicationId
 752    AND u.ApplicationId = a.ApplicationId
 753    AND m.UserId = u.UserId
 754    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
 755
 756                         DbCommand command = factory.CreateCommand ();
 757                         command.CommandText = commandText;
 758                         command.Connection = connection;
 759                         command.CommandType = CommandType.Text;
 760                         AddParameter (command, "ApplicationName", ApplicationName);
 761
 762                         MembershipUserCollection c = BuildMembershipUserCollection (command, pageIndex, pageSize, out totalRecords);
 763
 764                         return c;
 765                 }
 766
 767                 MembershipUserCollection BuildMembershipUserCollection (DbCommand command, int pageIndex, int pageSize, out int totalRecords)
 768                 {
 769                         DbDataReader reader = null;
 770                         try {
 771                                 int num_read = 0;
 772                                 int num_added = 0;
 773                                 int num_to_skip = pageIndex * pageSize;
 774                                 MembershipUserCollection users = new MembershipUserCollection ();
 775                                 reader = command.ExecuteReader ();
 776                                 while (reader.Read()) {
 777                                         if (num_read >= num_to_skip) {
 778                                                 if (num_added < pageSize) {
 779                                                         users.Add (GetUserFromReader (reader));
 780                                                         num_added ++;
 781                                                 }
 782                                                 num_read ++;
 783                                         }
 784                                 }
 785                                 totalRecords = num_read;
 786                                 return users;
 787                         }
 788                         catch {
 789                                 totalRecords = 0;
 790                                 return null; /* should we let the exception through? */
 791                         }
 792                         finally {
 793                                 if (reader != null)
 794                                         reader.Close();
 795                         }
 796                 }
 797
 798
 799                 public override int GetNumberOfUsersOnline ()
 800                 {
 801                         string commandText;
 802
 803                         InitConnection();
 804
 805                         DateTime now = DateTime.Now.ToUniversalTime ();
 806
 807                         commandText = String.Format (@"
 808 SELECT COUNT (*)
 809   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
 810  WHERE m.ApplicationId = a.ApplicationId
 811    AND u.ApplicationId = a.ApplicationId
 812    AND m.UserId = u.UserId
 813    AND DATEADD(minute,{0},u.LastActivityDate) >= @Now
 814    AND a.LoweredApplicationName = LOWER(@ApplicationName)",
 815                                                      userIsOnlineTimeWindow.Minutes);
 816
 817                         DbCommand command = factory.CreateCommand ();
 818                         command.CommandText = commandText;
 819                         command.Connection = connection;
 820                         command.CommandType = CommandType.Text;
 821                         AddParameter (command, "Now", now.ToString ());
 822                         AddParameter (command, "ApplicationName", ApplicationName);
 823
 824                         return (int)command.ExecuteScalar ();
 825                 }
 826
 827                 public override string GetPassword (string username, string answer)
 828                 {
 829                         if (!enablePasswordRetrieval)
 830                                 throw new NotSupportedException ("this provider has not been configured to allow the retrieval of passwords");
 831
 832                         CheckParam ("username", username, 256);
 833                         if (RequiresQuestionAndAnswer)
 834                                 CheckParam ("answer", answer, 128);
 835
 836                         MembershipUser user = GetUser (username, false);
 837                         if (user == null) throw new ProviderException ("could not find user in membership database");
 838                         if (user.IsLockedOut) throw new MembershipPasswordException ("user is currently locked out");
 839
 840                         InitConnection();
 841
 842                         DbTransaction trans = connection.BeginTransaction ();
 843
 844                         try {
 845                                 MembershipPasswordFormat passwordFormat;
 846                                 string salt;
 847                                 string password = null;
 848
 849                                 if (ValidateUsingPasswordAnswer (trans, username, answer,
 850                                                                  out passwordFormat, out salt)) {
 851
 852
 853                                         /* if the validation succeeds:
 854
 855                                            set LastLoginDate to DateTime.Now
 856                                            set FailedPasswordAnswerAttemptCount to 0
 857                                            set FailedPasswordAnswerAttemptWindowStart to DefaultDateTime
 858                                         */
 859
 860                                         string commandText = @"
 861 SELECT m.Password
 862   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
 863  WHERE m.ApplicationId = a.ApplicationId
 864    AND u.ApplicationId = a.ApplicationId
 865    AND m.UserId = u.UserId
 866    AND u.LoweredUserName = LOWER(@UserName)
 867    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
 868
 869                                         DbCommand command = factory.CreateCommand ();
 870                                         command.Transaction = trans;
 871                                         command.CommandText = commandText;
 872                                         command.Connection = connection;
 873                                         command.CommandType = CommandType.Text;
 874                                         AddParameter (command, "UserName", username);
 875                                         AddParameter (command, "ApplicationName", ApplicationName);
 876
 877                                         DbDataReader reader = command.ExecuteReader ();
 878                                         reader.Read ();
 879                                         password = reader.GetString (0);
 880                                         reader.Close();
 881
 882                                         password = DecodePassword (password, passwordFormat);
 883                                 }
 884                                 else {
 885                                         throw new MembershipPasswordException ("The password-answer supplied is wrong.");
 886                                 }
 887
 888                                 trans.Commit ();
 889                                 return password;
 890                         }
 891                         catch (MembershipPasswordException) {
 892                                 trans.Commit ();
 893                                 throw;
 894                         }
 895                         catch {
 896                                 trans.Rollback ();
 897                                 throw;
 898                         }
 899                 }
 900
 901                 MembershipUser GetUserFromReader (DbDataReader reader)
 902                 {
 903                         return new MembershipUser (this.Name, /* XXX is this right?  */
 904                                                    reader.GetString (0), /* name */
 905                                                    reader.GetGuid (1), /* providerUserKey */
 906                                                    reader.IsDBNull (2) ? null : reader.GetString (2), /* email */
 907                                                    reader.IsDBNull (3) ? null : reader.GetString (3), /* passwordQuestion */
 908                                                    reader.IsDBNull (4) ? null : reader.GetString (4), /* comment */
 909                                                    reader.GetBoolean (5), /* isApproved */
 910                                                    reader.GetBoolean (6), /* isLockedOut */
 911                                                    reader.GetDateTime (7).ToLocalTime (), /* creationDate */
 912                                                    reader.GetDateTime (8).ToLocalTime (), /* lastLoginDate */
 913                                                    reader.GetDateTime (9).ToLocalTime (), /* lastActivityDate */
 914                                                    reader.GetDateTime (10).ToLocalTime (), /* lastPasswordChangedDate */
 915                                                    reader.GetDateTime (11).ToLocalTime () /* lastLockoutDate */);
 916                 }
 917
 918                 MembershipUser BuildMembershipUser (DbCommand query, bool userIsOnline)
 919                 {
 920                         DbDataReader reader = null;
 921                         try {
 922                                 reader = query.ExecuteReader ();
 923                                 if (!reader.Read ())
 924                                         return null;
 925
 926                                 MembershipUser user = GetUserFromReader (reader);
 927
 928                                 if (user != null && userIsOnline) {
 929
 930                                         string commandText;
 931                                         DbCommand command;
 932
 933                                         commandText = @"
 934 UPDATE dbo.aspnet_Users u, dbo.aspnet_Application a
 935    SET u.LastActivityDate = GETDATE()
 936  WHERE u.ApplicationId = a.ApplicationId
 937    AND u.UserName = @UserName
 938    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
 939
 940                                         command = factory.CreateCommand ();
 941                                         command.CommandText = commandText;
 942                                         command.Connection = connection;
 943                                         command.CommandType = CommandType.Text;
 944                                         AddParameter (command, "UserName", user.UserName);
 945                                         AddParameter (command, "ApplicationName", ApplicationName);
 946
 947                                         command.ExecuteNonQuery();
 948                                 }
 949
 950                                 return user;
 951                         }
 952                         catch {
 953                                 return null; /* should we let the exception through? */
 954                         }
 955                         finally {
 956                                 if (reader != null)
 957                                         reader.Close ();
 958                         }
 959                 }
 960
 961                 [MonoTODO]
 962                 public override MembershipUser GetUser (string username, bool userIsOnline)
 963                 {
 964                         CheckParam ("username", username, 256);
 965
 966                         string commandText;
 967                         DbCommand command;
 968
 969                         InitConnection();
 970
 971                         commandText = @"
 972 SELECT u.UserName, m.UserId, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
 973        m.IsLockedOut, m.CreateDate, m.LastLoginDate, u.LastActivityDate,
 974        m.LastPasswordChangedDate, m.LastLockoutDate
 975   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
 976  WHERE m.ApplicationId = a.ApplicationId
 977    AND u.ApplicationId = a.ApplicationId
 978    AND m.UserId = u.UserId
 979    AND u.UserName = @UserName
 980    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
 981
 982                         command = factory.CreateCommand ();
 983                         command.CommandText = commandText;
 984                         command.Connection = connection;
 985                         command.CommandType = CommandType.Text;
 986                         AddParameter (command, "UserName", username);
 987                         AddParameter (command, "ApplicationName", ApplicationName);
 988
 989                         MembershipUser u = BuildMembershipUser (command, userIsOnline);
 990
 991                         return u;
 992                 }
 993
 994                 [MonoTODO]
 995                 public override MembershipUser GetUser (object providerUserKey, bool userIsOnline)
 996                 {
 997                         string commandText;
 998                         DbCommand command;
 999
1000                         InitConnection();
1001
1002                         commandText = @"
1003 SELECT u.UserName, m.UserId, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
1004        m.IsLockedOut, m.CreateDate, m.LastLoginDate, u.LastActivityDate,
1005        m.LastPasswordChangedDate, m.LastLockoutDate
1006   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
1007  WHERE m.ApplicationId = a.ApplicationId
1008    AND u.ApplicationId = a.ApplicationId
1009    AND m.UserId = u.UserId
1010    AND u.UserId = @UserKey
1011    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
1012
1013                         command = factory.CreateCommand ();
1014                         command.CommandText = commandText;
1015                         command.Connection = connection;
1016                         command.CommandType = CommandType.Text;
1017                         AddParameter (command, "UserKey", providerUserKey.ToString());
1018                         AddParameter (command, "ApplicationName", ApplicationName);
1019
1020                         MembershipUser u = BuildMembershipUser (command, userIsOnline);
1021
1022                         return u;
1023                 }
1024
1025                 [MonoTODO]
1026                 public override string GetUserNameByEmail (string email)
1027                 {
1028                         CheckParam ("email", email, 256);
1029
1030                         string commandText;
1031                         DbCommand command;
1032
1033                         InitConnection();
1034
1035                         commandText = @"
1036 SELECT u.UserName
1037   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
1038  WHERE m.ApplicationId = a.ApplicationId
1039    AND u.ApplicationId = a.ApplicationId
1040    AND m.UserId = u.UserId
1041    AND m.Email = @Email
1042    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
1043
1044                         command = factory.CreateCommand ();
1045                         command.CommandText = commandText;
1046                         command.Connection = connection;
1047                         command.CommandType = CommandType.Text;
1048                         AddParameter (command, "Email", email);
1049                         AddParameter (command, "ApplicationName", ApplicationName);
1050
1051                         try {
1052                                 DbDataReader reader = command.ExecuteReader ();
1053                                 string rv = null;
1054                                 while (reader.Read())
1055                                         rv = reader.GetString(0);
1056                                 reader.Close();
1057                                 return rv;
1058                         }
1059                         catch {
1060                                 return null; /* should we allow the exception through? */
1061                         }
1062                 }
1063
1064                 bool GetBoolConfigValue (NameValueCollection config, string name, bool def)
1065                 {
1066                         bool rv = def;
1067                         string val = config[name];
1068                         if (val != null) {
1069                                 try { rv = Boolean.Parse (val); }
1070                                 catch (Exception e) {
1071                                         throw new ProviderException (String.Format ("{0} must be true or false", name), e); }
1072                         }
1073                         return rv;
1074                 }
1075
1076                 int GetIntConfigValue (NameValueCollection config, string name, int def)
1077                 {
1078                         int rv = def;
1079                         string val = config[name];
1080                         if (val != null) {
1081                                 try { rv = Int32.Parse (val); }
1082                                 catch (Exception e) {
1083                                         throw new ProviderException (String.Format ("{0} must be an integer", name), e); }
1084                         }
1085                         return rv;
1086                 }
1087
1088                 int GetEnumConfigValue (NameValueCollection config, string name, Type enumType, int def)
1089                 {
1090                         int rv = def;
1091                         string val = config[name];
1092                         if (val != null) {
1093                                 try { rv = (int)Enum.Parse (enumType, val); }
1094                                 catch (Exception e) {
1095                                         throw new ProviderException (String.Format ("{0} must be one of the following values: {1}", name, String.Join (",", Enum.GetNames (enumType))), e); }
1096                         }
1097                         return rv;
1098                 }
1099
1100                 string GetStringConfigValue (NameValueCollection config, string name, string def)
1101                 {
1102                         string rv = def;
1103                         string val = config[name];
1104                         if (val != null)
1105                                 rv = val;
1106                         return rv;
1107                 }
1108
1109                 void EmitValidatingPassword (string username, string password, bool isNewUser)
1110                 {
1111                         ValidatePasswordEventArgs args = new ValidatePasswordEventArgs (username, password, isNewUser);
1112                         OnValidatingPassword (args);
1113
1114                         /* if we're canceled.. */
1115                         if (args.Cancel) {
1116                                 if (args.FailureInformation == null)
1117                                         throw new ProviderException ("Password validation canceled");
1118                                 else
1119                                         throw args.FailureInformation;
1120                         }
1121                 }
1122
1123                 public override void Initialize (string name, NameValueCollection config)
1124                 {
1125                         if (config == null)
1126                                 throw new ArgumentNullException ("config");
1127
1128                         base.Initialize (name, config);
1129
1130                         applicationName = GetStringConfigValue (config, "applicationName", "/");
1131                         enablePasswordReset = GetBoolConfigValue (config, "enablePasswordReset", true);
1132                         enablePasswordRetrieval = GetBoolConfigValue (config, "enablePasswordRetrieval", false);
1133                         requiresQuestionAndAnswer = GetBoolConfigValue (config, "requiresQuestionAndAnswer", true);
1134                         requiresUniqueEmail = GetBoolConfigValue (config, "requiresUniqueEmail", false);
1135                         passwordFormat = (MembershipPasswordFormat)GetEnumConfigValue (config, "passwordFormat", typeof (MembershipPasswordFormat),
1136                                                                                        (int)MembershipPasswordFormat.Hashed);
1137                         maxInvalidPasswordAttempts = GetIntConfigValue (config, "maxInvalidPasswordAttempts", 5);
1138                         minRequiredPasswordLength = GetIntConfigValue (config, "minRequiredPasswordLength", 7);
1139                         minRequiredNonAlphanumericCharacters = GetIntConfigValue (config, "minRequiredNonAlphanumericCharacters", 1);
1140                         passwordAttemptWindow = GetIntConfigValue (config, "passwordAttemptWindow", 10);
1141                         passwordStrengthRegularExpression = GetStringConfigValue (config, "passwordStrengthRegularExpression", "");
1142
1143                         MembershipSection section = (MembershipSection)WebConfigurationManager.GetSection ("system.web/membership");
1144
1145                         userIsOnlineTimeWindow = section.UserIsOnlineTimeWindow;
1146
1147                         /* we can't support password retrieval with hashed passwords */
1148                         if (passwordFormat == MembershipPasswordFormat.Hashed && enablePasswordRetrieval)
1149                                 throw new ProviderException ("password retrieval cannot be used with hashed passwords");
1150
1151                         string connectionStringName = config["connectionStringName"];
1152
1153                         if (applicationName.Length > 256)
1154                                 throw new ProviderException ("The ApplicationName attribute must be 256 characters long or less.");
1155                         if (connectionStringName == null || connectionStringName.Length == 0)
1156                                 throw new ProviderException ("The ConnectionStringName attribute must be present and non-zero length.");
1157
1158                         connectionString = WebConfigurationManager.ConnectionStrings[connectionStringName];
1159                 }
1160
1161                 public override string ResetPassword (string username, string answer)
1162                 {
1163                         if (!enablePasswordReset)
1164                                 throw new NotSupportedException ("this provider has not been configured to allow the resetting of passwords");
1165
1166                         CheckParam ("username", username, 256);
1167                         if (RequiresQuestionAndAnswer)
1168                                 CheckParam ("answer", answer, 128);
1169
1170                         MembershipUser user = GetUser (username, false);
1171                         if (user == null) throw new ProviderException ("could not find user in membership database");
1172                         if (user.IsLockedOut) throw new MembershipPasswordException ("user is currently locked out");
1173
1174                         InitConnection();
1175
1176                         string commandText;
1177                         DbCommand command;
1178
1179                         DbTransaction trans = connection.BeginTransaction ();
1180
1181                         try {
1182                                 MembershipPasswordFormat db_passwordFormat;
1183                                 string db_salt;
1184                                 string newPassword = null;
1185
1186                                 if (ValidateUsingPasswordAnswer (trans, user.UserName, answer, out db_passwordFormat, out db_salt)) {
1187
1188                                         newPassword = GeneratePassword ();
1189                                         string db_password;
1190
1191                                         EmitValidatingPassword (username, newPassword, false);
1192
1193                                         /* otherwise update the user's password in the db */
1194
1195                                         db_password = EncodePassword (newPassword, db_passwordFormat, db_salt);
1196
1197                                         commandText = @"
1198 UPDATE m
1199    SET Password = @Password,
1200        FailedPasswordAnswerAttemptCount = 0,
1201        FailedPasswordAnswerAttemptWindowStart = @DefaultDateTime
1202   FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
1203  WHERE m.ApplicationId = a.ApplicationId
1204    AND u.ApplicationId = a.ApplicationId
1205    AND m.UserId = u.UserId
1206    AND u.LoweredUserName = LOWER(@UserName)
1207    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
1208
1209                                         command = factory.CreateCommand ();
1210                                         command.Transaction = trans;
1211                                         command.CommandText = commandText;
1212                                         command.Connection = connection;
1213                                         command.CommandType = CommandType.Text;
1214                                         AddParameter (command, "UserName", user.UserName);
1215                                         AddParameter (command, "Password", db_password);
1216                                         AddParameter (command, "ApplicationName", ApplicationName);
1217                                         AddParameter (command, "DefaultDateTime", DefaultDateTime.ToString());
1218
1219                                         if (1 != (int)command.ExecuteNonQuery ())
1220                                                 throw new ProviderException ("failed to update Membership table");
1221
1222                                         trans.Commit ();
1223                                 }
1224                                 else {
1225                                         throw new MembershipPasswordException ("The password-answer supplied is wrong.");
1226                                 }
1227
1228                                 return newPassword;
1229                         }
1230                         catch (MembershipPasswordException) {
1231                                 trans.Commit ();
1232                                 throw;
1233                         }
1234                         catch (ProviderException) {
1235                                 trans.Rollback ();
1236                                 throw;
1237                         }
1238                         catch (Exception e) {
1239                                 trans.Rollback ();
1240
1241                                 throw new ProviderException ("Failed to reset password", e);
1242                         }
1243                 }
1244
1245                 public override void UpdateUser (MembershipUser user)
1246                 {
1247                         if (user == null) throw new ArgumentNullException ("user");
1248                         if (user.UserName == null) throw new ArgumentNullException ("user.UserName");
1249                         if (RequiresUniqueEmail && user.Email == null) throw new ArgumentNullException ("user.Email");
1250
1251                         CheckParam ("user.UserName", user.UserName, 256);
1252                         if (user.Email.Length > 256 || (RequiresUniqueEmail && user.Email.Length == 0))
1253                                 throw new ArgumentException ("invalid format for user.Email");
1254
1255                         DbTransaction trans = connection.BeginTransaction ();
1256
1257                         string commandText;
1258                         DbCommand command;
1259
1260                         InitConnection();
1261
1262                         try {
1263                                 DateTime now = DateTime.Now.ToUniversalTime ();
1264
1265                                 commandText = String.Format (@"
1266 UPDATE m
1267    SET Email = {0},
1268        Comment = {1},
1269        IsApproved = @IsApproved,
1270        LastLoginDate = @Now
1271   FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
1272  WHERE m.ApplicationId = a.ApplicationId
1273    AND u.ApplicationId = a.ApplicationId
1274    AND m.UserId = u.UserId
1275    AND u.LoweredUserName = LOWER(@UserName)
1276    AND a.LoweredApplicationName = LOWER(@ApplicationName)",
1277                                                              user.Email == null ? "NULL" : "@Email",
1278                                                              user.Comment == null ? "NULL" : "@Comment");
1279
1280                                 command = factory.CreateCommand ();
1281                                 command.Transaction = trans;
1282                                 command.CommandText = commandText;
1283                                 command.Connection = connection;
1284                                 command.CommandType = CommandType.Text;
1285                                 if (user.Email != null)
1286                                         AddParameter (command, "Email", user.Email);
1287                                 if (user.Comment != null)
1288                                         AddParameter (command, "Comment", user.Comment);
1289                                 AddParameter (command, "IsApproved", user.IsApproved.ToString());
1290                                 AddParameter (command, "UserName", user.UserName);
1291                                 AddParameter (command, "ApplicationName", ApplicationName);
1292                                 AddParameter (command, "Now", now.ToString ());
1293
1294                                 if (0 == command.ExecuteNonQuery())
1295                                         throw new ProviderException ("failed to membership table");
1296
1297
1298                                 commandText = @"
1299 UPDATE dbo.aspnet_Users
1300    SET LastActivityDate = @Now
1301   FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
1302  WHERE a.ApplicationId = a.ApplicationId
1303    AND u.LoweredUserName = LOWER(@UserName)
1304    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
1305
1306                                 command = factory.CreateCommand ();
1307                                 command.Transaction = trans;
1308                                 command.CommandText = commandText;
1309                                 command.Connection = connection;
1310                                 command.CommandType = CommandType.Text;
1311                                 AddParameter (command, "UserName", user.UserName);
1312                                 AddParameter (command, "ApplicationName", ApplicationName);
1313                                 AddParameter (command, "Now", now.ToString ());
1314
1315                                 if (0 == command.ExecuteNonQuery())
1316                                         throw new ProviderException ("failed to user table");
1317
1318                                 trans.Commit ();
1319                         }
1320                         catch (ProviderException) {
1321                                 trans.Rollback ();
1322                                 throw;
1323                         }
1324                         catch (Exception e) {
1325                                 trans.Rollback ();
1326                                 throw new ProviderException ("failed to update user", e);
1327                         }
1328                 }
1329
1330                 public override bool ValidateUser (string username, string password)
1331                 {
1332                         MembershipUser user = GetUser (username, false);
1333
1334                         /* if the user is locked out, return false immediately */
1335                         if (user.IsLockedOut)
1336                                 return false;
1337
1338                         /* if the user is not yet approved, return false */
1339                         if (!user.IsApproved)
1340                                 return false;
1341
1342                         EmitValidatingPassword (username, password, false);
1343
1344                         InitConnection();
1345
1346                         DbTransaction trans = connection.BeginTransaction ();
1347
1348                         string commandText;
1349                         DbCommand command;
1350
1351                         try {
1352                                 MembershipPasswordFormat passwordFormat;
1353                                 string salt;
1354
1355                                 bool valid = ValidateUsingPassword (trans, username, password, out passwordFormat, out salt);
1356                                 if (valid) {
1357
1358                                         DateTime now = DateTime.Now.ToUniversalTime ();
1359
1360                                         /* if the validation succeeds:
1361                                            set LastLoginDate to DateTime.Now
1362                                            set FailedPasswordAttemptCount to 0
1363                                            set FailedPasswordAttemptWindow to DefaultDateTime
1364                                            set FailedPasswordAnswerAttemptCount to 0
1365                                            set FailedPasswordAnswerAttemptWindowStart to DefaultDateTime
1366                                         */
1367
1368                                         commandText = @"
1369 UPDATE dbo.aspnet_Membership
1370    SET LastLoginDate = @Now,
1371        FailedPasswordAttemptCount = 0,
1372        FailedPasswordAttemptWindowStart = @DefaultDateTime,
1373        FailedPasswordAnswerAttemptCount = 0,
1374        FailedPasswordAnswerAttemptWindowStart = @DefaultDateTime
1375   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
1376  WHERE m.ApplicationId = a.ApplicationId
1377    AND u.ApplicationId = a.ApplicationId
1378    AND m.UserId = u.UserId
1379    AND u.LoweredUserName = LOWER(@UserName)
1380    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
1381
1382                                         command = factory.CreateCommand ();
1383                                         command.Transaction = trans;
1384                                         command.CommandText = commandText;
1385                                         command.Connection = connection;
1386                                         command.CommandType = CommandType.Text;
1387                                         AddParameter (command, "UserName", user.UserName);
1388                                         AddParameter (command, "ApplicationName", ApplicationName);
1389                                         AddParameter (command, "Now", now.ToString ());
1390                                         AddParameter (command, "DefaultDateTime", DefaultDateTime.ToString());
1391
1392                                         if (1 != (int)command.ExecuteNonQuery ())
1393                                                 throw new ProviderException ("failed to update Membership table");
1394
1395                                         commandText = @"
1396 UPDATE dbo.aspnet_Users
1397    SET LastActivityDate = @Now
1398   FROM dbo.aspnet_Applications a, dbo.aspnet_Users u
1399  WHERE u.ApplicationId = a.ApplicationId
1400    AND u.LoweredUserName = LOWER(@UserName)
1401    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
1402
1403                                         command = factory.CreateCommand ();
1404                                         command.Transaction = trans;
1405                                         command.CommandText = commandText;
1406                                         command.Connection = connection;
1407                                         command.CommandType = CommandType.Text;
1408                                         AddParameter (command, "UserName", user.UserName);
1409                                         AddParameter (command, "ApplicationName", ApplicationName);
1410                                         AddParameter (command, "Now", now.ToString ());
1411
1412                                         if (1 != (int)command.ExecuteNonQuery ())
1413                                                 throw new ProviderException ("failed to update User table");
1414                                 }
1415
1416                                 trans.Commit ();
1417
1418                                 return valid;
1419                         }
1420                         catch {
1421                                 trans.Rollback ();
1422
1423                                 throw;
1424                         }
1425                 }
1426
1427                 public override bool UnlockUser (string userName)
1428                 {
1429                         CheckParam ("userName", userName, 256);
1430
1431                         string commandText = @"
1432 UPDATE dbo.aspnet_Membership
1433    SET IsLockedOut = 0,
1434        LastLockoutDate = @DefaultDateTime,
1435        FailedPasswordAttemptCount = 0,
1436        FailedPasswordAttemptWindowStart = @DefaultDateTime,
1437        FailedPasswordAnswerAttemptCount = 0,
1438        FailedPasswordAnswerAttemptWindowStart = @DefaultDateTime
1439   FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
1440  WHERE m.UserId = u.UserId
1441    AND m.ApplicationId = a.ApplicationId
1442    AND u.ApplicationId = a.ApplicationId
1443    AND u.LoweredUserName = LOWER (@UserName)
1444    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
1445
1446                         InitConnection();
1447
1448                         DbCommand command = factory.CreateCommand ();
1449                         command.CommandText = commandText;
1450                         command.Connection = connection;
1451                         command.CommandType = CommandType.Text;
1452                         AddParameter (command, "UserName", userName);
1453                         AddParameter (command, "ApplicationName", ApplicationName);
1454                         AddParameter (command, "DefaultDateTime", DefaultDateTime.ToString());
1455
1456                         return command.ExecuteNonQuery() == 1;
1457                 }
1458
1459                 void IncrementFailureAndMaybeLockout (DbTransaction trans, string username,
1460                                                       string failureCountAttribute, string failureWindowAttribute)
1461                 {
1462                         DateTime now = DateTime.Now;
1463
1464                         /* if validation fails:
1465                            if (FailedPasswordAttemptWindowStart - DateTime.Now < PasswordAttemptWindow)
1466                              increment FailedPasswordAttemptCount
1467                            FailedPasswordAttemptWindowStart = DateTime.Now
1468                            if (FailedPasswordAttemptCount > MaxInvalidPasswordAttempts)
1469                              set IsLockedOut = true.
1470                              set LastLockoutDate = DateTime.Now
1471                         */
1472
1473                         string commandText = String.Format (@"
1474 SELECT m.{0}, m.{1}
1475   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
1476  WHERE m.ApplicationId = a.ApplicationId
1477    AND u.ApplicationId = a.ApplicationId
1478    AND m.UserId = u.UserId
1479    AND u.LoweredUserName = LOWER(@UserName)
1480    AND a.LoweredApplicationName = LOWER(@ApplicationName)",
1481                                                      failureCountAttribute, failureWindowAttribute);
1482
1483                         DbCommand command = factory.CreateCommand ();
1484                         command.Transaction = trans;
1485                         command.CommandText = commandText;
1486                         command.Connection = connection;
1487                         command.CommandType = CommandType.Text;
1488                         AddParameter (command, "UserName", username);
1489                         AddParameter (command, "ApplicationName", ApplicationName);
1490
1491                         DateTime db_FailedWindowStart;
1492                         int db_FailedCount;
1493
1494                         DbDataReader reader = command.ExecuteReader ();
1495                         reader.Read ();
1496                         db_FailedCount = reader.GetInt32 (0);
1497                         db_FailedWindowStart = reader.GetDateTime (1).ToLocalTime ();
1498                         reader.Close();
1499
1500                         TimeSpan diff = now.Subtract (db_FailedWindowStart);
1501                         if ((db_FailedWindowStart == DefaultDateTime.ToLocalTime ())
1502                             || diff.Minutes < PasswordAttemptWindow)
1503                                 db_FailedCount ++;
1504
1505                         if (db_FailedCount > MaxInvalidPasswordAttempts) {
1506                                 /* lock the user out */
1507                                 commandText = @"
1508 UPDATE dbo.aspnet_Membership
1509    SET IsLockedOut = 1,
1510        LastLockoutDate = @LastLockoutDate
1511   FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
1512  WHERE m.ApplicationId = a.ApplicationId
1513    AND u.ApplicationId = a.ApplicationId
1514    AND m.UserId = u.UserId
1515    AND u.LoweredUserName = LOWER(@UserName)
1516    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
1517
1518                                 command = factory.CreateCommand ();
1519                                 command.Transaction = trans;
1520                                 command.CommandText = commandText;
1521                                 command.Connection = connection;
1522                                 command.CommandType = CommandType.Text;
1523                                 AddParameter (command, "UserName", username);
1524                                 AddParameter (command, "ApplicationName", ApplicationName);
1525                                 AddParameter (command, "LastLockoutDate", now.ToUniversalTime().ToString ());
1526                         }
1527                         else {
1528                                 /* just store back the updated window start and count */
1529                                 commandText = String.Format (@"
1530 UPDATE dbo.aspnet_Membership
1531    SET {0} = @{0},
1532        {1} = @{1}
1533   FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
1534  WHERE m.ApplicationId = a.ApplicationId
1535    AND u.ApplicationId = a.ApplicationId
1536    AND m.UserId = u.UserId
1537    AND u.LoweredUserName = LOWER(@UserName)
1538    AND a.LoweredApplicationName = LOWER(@ApplicationName)",
1539                                                      failureCountAttribute, failureWindowAttribute);
1540
1541                                 command = factory.CreateCommand ();
1542                                 command.Transaction = trans;
1543                                 command.CommandText = commandText;
1544                                 command.Connection = connection;
1545                                 command.CommandType = CommandType.Text;
1546                                 AddParameter (command, "UserName", username);
1547                                 AddParameter (command, "ApplicationName", ApplicationName);
1548                                 AddParameter (command, failureCountAttribute, db_FailedCount.ToString());
1549                                 AddParameter (command, failureWindowAttribute, now.ToUniversalTime().ToString ());
1550                         }
1551
1552                         if (1 != (int)command.ExecuteNonQuery ())
1553                                 throw new ProviderException ("failed to update Membership table");
1554                 }
1555
1556                 bool ValidateUsingPassword (DbTransaction trans, string username, string password,
1557                                             out MembershipPasswordFormat passwordFormat,
1558                                             out string salt)
1559                 {
1560                         string commandText = @"
1561 SELECT m.Password, m.PasswordFormat, m.PasswordSalt
1562   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
1563  WHERE m.ApplicationId = a.ApplicationId
1564    AND u.ApplicationId = a.ApplicationId
1565    AND m.UserId = u.UserId
1566    AND u.LoweredUserName = LOWER(@UserName)
1567    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
1568
1569                         DbCommand command = factory.CreateCommand ();
1570                         command.Transaction = trans;
1571                         command.CommandText = commandText;
1572                         command.Connection = connection;
1573                         command.CommandType = CommandType.Text;
1574                         AddParameter (command, "UserName", username);
1575                         AddParameter (command, "ApplicationName", ApplicationName);
1576
1577                         string db_password;
1578
1579                         DbDataReader reader = command.ExecuteReader ();
1580                         reader.Read ();
1581                         db_password = reader.GetString (0);
1582                         passwordFormat = (MembershipPasswordFormat)reader.GetInt32 (1);
1583                         salt = reader.GetString (2);
1584                         reader.Close();
1585
1586                         /* do the actual validation */
1587                         password = EncodePassword (password, passwordFormat, salt);
1588
1589                         bool valid = (password == db_password);
1590
1591                         if (!valid)
1592                                 IncrementFailureAndMaybeLockout (trans, username,
1593                                                                  "FailedPasswordAttemptCount", "FailedPasswordAttemptWindowStart");
1594
1595                         return valid;
1596                 }
1597
1598
1599                 bool ValidateUsingPasswordAnswer (DbTransaction trans, string username, string answer,
1600                                                   out MembershipPasswordFormat passwordFormat,
1601                                                   out string salt)
1602                 {
1603                         string commandText = @"
1604 SELECT m.PasswordAnswer, m.PasswordFormat, m.PasswordSalt
1605   FROM dbo.aspnet_Membership m, dbo.aspnet_Applications a, dbo.aspnet_Users u
1606  WHERE m.ApplicationId = a.ApplicationId
1607    AND u.ApplicationId = a.ApplicationId
1608    AND m.UserId = u.UserId
1609    AND u.LoweredUserName = LOWER(@UserName)
1610    AND a.LoweredApplicationName = LOWER(@ApplicationName)";
1611
1612                         DbCommand command = factory.CreateCommand ();
1613                         command.Transaction = trans;
1614                         command.CommandText = commandText;
1615                         command.Connection = connection;
1616                         command.CommandType = CommandType.Text;
1617                         AddParameter (command, "UserName", username);
1618                         AddParameter (command, "ApplicationName", ApplicationName);
1619
1620                         string db_answer;
1621
1622                         DbDataReader reader = command.ExecuteReader ();
1623                         reader.Read ();
1624                         db_answer = reader.GetString (0);
1625                         passwordFormat = (MembershipPasswordFormat)reader.GetInt32 (1);
1626                         salt = reader.GetString (2);
1627                         reader.Close();
1628
1629                         /* do the actual password answer check */
1630                         answer = EncodePassword (answer, passwordFormat, salt);
1631
1632                         if (answer.Length > 128)
1633                                 throw new ArgumentException (String.Format ("password answer hashed to longer than 128 characters"));
1634
1635                         bool valid = (answer == db_answer);
1636
1637                         if (!valid)
1638                                 IncrementFailureAndMaybeLockout (trans, username,
1639                                                                  "FailedPasswordAnswerAttemptCount",
1640                                                                  "FailedPasswordAnswerAttemptWindowStart");
1641
1642                         return valid;
1643                 }
1644
1645                 [MonoTODO]
1646                 public override string ApplicationName {
1647                         get { return applicationName; }
1648                         set { applicationName = value; }
1649                 }
1650
1651                 public override bool EnablePasswordReset {
1652                         get { return enablePasswordReset; }
1653                 }
1654
1655                 public override bool EnablePasswordRetrieval {
1656                         get { return enablePasswordRetrieval; }
1657                 }
1658
1659                 public override MembershipPasswordFormat PasswordFormat {
1660                         get { return passwordFormat; }
1661                 }
1662
1663                 public override bool RequiresQuestionAndAnswer {
1664                         get { return requiresQuestionAndAnswer; }
1665                 }
1666
1667                 public override bool RequiresUniqueEmail {
1668                         get { return requiresUniqueEmail; }
1669                 }
1670
1671                 public override int MaxInvalidPasswordAttempts {
1672                         get { return maxInvalidPasswordAttempts; }
1673                 }
1674
1675                 public override int MinRequiredNonAlphanumericCharacters {
1676                         get { return minRequiredNonAlphanumericCharacters; }
1677                 }
1678
1679                 public override int MinRequiredPasswordLength {
1680                         get { return minRequiredPasswordLength; }
1681                 }
1682
1683                 public override int PasswordAttemptWindow {
1684                         get { return passwordAttemptWindow; }
1685                 }
1686
1687                 public override string PasswordStrengthRegularExpression {
1688                         get { return passwordStrengthRegularExpression; }
1689                 }
1690         }
1691 }
1692 #endif
1693