mcs/class/Mainsoft.Web/Mainsoft.Web.Security/DerbyMembershipHelper.cs

   1 //\r
   2 // Mainsoft.Web.Security.DerbyMembershipHelper\r
   3 //\r
   4 // Authors:\r
   5 //      Vladimir Krasnov (vladimirk@mainsoft.com)\r
   6 //\r
   7 // (C) 2006 Mainsoft\r
   8 //\r
   9 // Permission is hereby granted, free of charge, to any person obtaining\r
  10 // a copy of this software and associated documentation files (the\r
  11 // "Software"), to deal in the Software without restriction, including\r
  12 // without limitation the rights to use, copy, modify, merge, publish,\r
  13 // distribute, sublicense, and/or sell copies of the Software, and to\r
  14 // permit persons to whom the Software is furnished to do so, subject to\r
  15 // the following conditions:\r
  16 // \r
  17 // The above copyright notice and this permission notice shall be\r
  18 // included in all copies or substantial portions of the Software.\r
  19 // \r
  20 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,\r
  21 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF\r
  22 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND\r
  23 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE\r
  24 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION\r
  25 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION\r
  26 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.\r
  27 //\r
  28 \r
  29 #if NET_2_0\r
  30 \r
  31 using System;\r
  32 using System.Web.Security;\r
  33 using System.Data;\r
  34 using System.Data.OleDb;\r
  35 using System.Data.Common;\r
  36 using System.Collections.Generic;\r
  37 using System.Text;\r
  38 \r
  39 namespace Mainsoft.Web.Security\r
  40 {\r
  41         static class DerbyMembershipHelper\r
  42         {\r
  43                 static readonly DateTime DefaultDateTime = new DateTime (1754, 1, 1).ToUniversalTime ();\r
  44 \r
  45                 private static OleDbParameter AddParameter (OleDbCommand command, string paramName, object paramValue)\r
  46                 {\r
  47                         OleDbParameter prm = new OleDbParameter (paramName, paramValue);\r
  48                         command.Parameters.Add (prm);\r
  49                         return prm;\r
  50                 }\r
  51 \r
  52                 public static int Membership_ChangePasswordQuestionAndAnswer (DbConnection connection, string applicationName, string username, string newPwdQuestion, string newPwdAnswer)\r
  53                 {\r
  54                         string updateQuery = "UPDATE aspnet_Membership SET PasswordQuestion = ?, PasswordAnswer = ? WHERE UserId = ?";\r
  55 \r
  56                         string userId = GetUserId (connection, applicationName, username);\r
  57                         if (userId == null)\r
  58                                 return 1; // user not found\r
  59 \r
  60                         OleDbCommand updateCmd = new OleDbCommand (updateQuery, (OleDbConnection) connection);\r
  61                         AddParameter (updateCmd, "PasswordQuestion", newPwdQuestion);\r
  62                         AddParameter (updateCmd, "PasswordAnswer", newPwdAnswer);\r
  63                         AddParameter (updateCmd, "UserId", userId);\r
  64                         updateCmd.ExecuteNonQuery ();\r
  65                         return 0;\r
  66                 }\r
  67 \r
  68                 public static int Membership_CreateUser (DbConnection connection, string applicationName, string username, string password, string passwordSalt, string email, string pwdQuestion, string pwdAnswer, bool isApproved, DateTime currentTimeUtc, DateTime createDate, bool uniqueEmail, int passwordFormat, ref object userId)\r
  69                 {\r
  70                         string applicationId = (string) DerbyApplicationsHelper.Applications_CreateApplication (connection, applicationName);\r
  71                         string newUserId = (string) userId;\r
  72 \r
  73                         OleDbTransaction trans = (OleDbTransaction) connection.BeginTransaction ();\r
  74 \r
  75                         try {\r
  76                                 int returnValue = Users_CreateUser (connection, trans, applicationId, username, false, createDate, ref userId);\r
  77                                 if (returnValue == 1) {\r
  78                                         // the user exists in users table, this can occure when user\r
  79                                         // does not have membership information, but has other information\r
  80                                         // like roles, etc.\r
  81                                         if (userId != null && newUserId != null && newUserId != (string) userId) {\r
  82                                                 trans.Rollback ();\r
  83                                                 return 9; // wrong userid provided\r
  84                                         }\r
  85                                 }\r
  86                                 else if (returnValue == 2) {\r
  87                                         // another user with provided id already exists\r
  88                                         trans.Rollback ();\r
  89                                         return 10; // wrong userid provided\r
  90                                 }\r
  91                                 newUserId = (string) userId;\r
  92 \r
  93                                 string selectQueryMbrUserId = "SELECT UserId FROM aspnet_Membership WHERE UserId = ?";\r
  94                                 OleDbCommand selectCmdMbrUserId = new OleDbCommand (selectQueryMbrUserId, (OleDbConnection) connection);\r
  95                                 selectCmdMbrUserId.Transaction = trans;\r
  96                                 AddParameter (selectCmdMbrUserId, "UserId", newUserId);\r
  97                                 using (OleDbDataReader reader = selectCmdMbrUserId.ExecuteReader ()) {\r
  98                                         if (reader.Read ()) {\r
  99                                                 trans.Rollback ();\r
 100                                                 return 2; // user with such userId already exists\r
 101                                         }\r
 102                                 }\r
 103 \r
 104                                 if (uniqueEmail) {\r
 105                                         string queryMbrEmail = "SELECT * FROM  aspnet_Membership WHERE ApplicationId = ? AND LoweredEmail = ?";\r
 106                                         OleDbCommand cmdMbrEmail = new OleDbCommand (queryMbrEmail, (OleDbConnection) connection);\r
 107                                         cmdMbrEmail.Transaction = trans;\r
 108                                         AddParameter (cmdMbrEmail, "ApplicationId", applicationId);\r
 109                                         AddParameter (cmdMbrEmail, "LoweredEmail", email.ToLowerInvariant ());\r
 110                                         using (OleDbDataReader reader = cmdMbrEmail.ExecuteReader ()) {\r
 111                                                 if (reader.Read ()) {\r
 112                                                         trans.Rollback ();\r
 113                                                         return 3; // user with such email already exists\r
 114                                                 }\r
 115                                         }\r
 116                                 }\r
 117 \r
 118                                 if (returnValue == 1) {\r
 119                                         // if user was not created, but found existing and correct\r
 120                                         // update it's activity (membership create) time.\r
 121                                         string queryUpdActivity = "UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 122                                         OleDbCommand cmdUpdActivity = new OleDbCommand (queryUpdActivity, (OleDbConnection) connection);\r
 123                                         cmdUpdActivity.Transaction = trans;\r
 124                                         AddParameter (cmdUpdActivity, "LastActivityDate", createDate);\r
 125                                         AddParameter (cmdUpdActivity, "UserId", newUserId);\r
 126                                         cmdUpdActivity.ExecuteNonQuery ();\r
 127                                 }\r
 128 \r
 129                                 string queryInsertMbr = "INSERT INTO aspnet_Membership (ApplicationId, UserId, Password, PasswordFormat, PasswordSalt, Email, " +\r
 130                                         "LoweredEmail, PasswordQuestion, PasswordAnswer, IsApproved, IsLockedOut, CreateDate, LastLoginDate, " +\r
 131                                         "LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPwdAttemptWindowStart, " +\r
 132                                         "FailedPwdAnswerAttemptCount, FailedPwdAnswerAttWindowStart) " +\r
 133                                         "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";\r
 134                                 OleDbCommand cmdInsertMbr = new OleDbCommand (queryInsertMbr, (OleDbConnection) connection);\r
 135                                 cmdInsertMbr.Transaction = trans;\r
 136                                 AddParameter (cmdInsertMbr, "ApplicationId", applicationId);\r
 137                                 AddParameter (cmdInsertMbr, "UserId", newUserId);\r
 138                                 AddParameter (cmdInsertMbr, "Password", password);\r
 139                                 AddParameter (cmdInsertMbr, "PasswordFormat", passwordFormat);\r
 140                                 AddParameter (cmdInsertMbr, "PasswordSalt", passwordSalt);\r
 141                                 AddParameter (cmdInsertMbr, "Email", email);\r
 142                                 AddParameter (cmdInsertMbr, "LoweredEmail", email != null ? email.ToLowerInvariant () : null);\r
 143                                 AddParameter (cmdInsertMbr, "PasswordQuestion", pwdQuestion);\r
 144                                 AddParameter (cmdInsertMbr, "PasswordAnswer", pwdAnswer);\r
 145                                 AddParameter (cmdInsertMbr, "IsApproved", isApproved);\r
 146                                 AddParameter (cmdInsertMbr, "IsLockedOut", 0);\r
 147                                 AddParameter (cmdInsertMbr, "CreateDate", createDate);\r
 148                                 AddParameter (cmdInsertMbr, "LastLoginDate", DefaultDateTime);\r
 149                                 AddParameter (cmdInsertMbr, "LastPasswordChangedDate", createDate);\r
 150                                 AddParameter (cmdInsertMbr, "LastLockoutDate", DefaultDateTime);\r
 151                                 AddParameter (cmdInsertMbr, "FailedPasswordAttemptCount", 0);\r
 152                                 AddParameter (cmdInsertMbr, "FailedPwdAttemptWindowStart", DefaultDateTime);\r
 153                                 AddParameter (cmdInsertMbr, "FailedPwdAnswerAttemptCount", 0);\r
 154                                 AddParameter (cmdInsertMbr, "FailedPwdAnswerAttWindowStart", DefaultDateTime);\r
 155                                 cmdInsertMbr.ExecuteNonQuery ();\r
 156 \r
 157                                 trans.Commit ();\r
 158                         }\r
 159                         catch (Exception e) {\r
 160                                 trans.Rollback ();\r
 161                                 throw e;\r
 162                         }\r
 163 \r
 164                         return 0;\r
 165                 }\r
 166 \r
 167                 public static int Membership_FindUsersByEmail (DbConnection connection, string applicationName, string emailToMatch, int pageIndex, int pageSize, out DbDataReader reader)\r
 168                 {\r
 169                         string querySelect = "SELECT usr.UserName, mbr.UserId, mbr.Email, mbr.PasswordQuestion, mbr.Comment, mbr.IsApproved, " +\r
 170                                 "mbr.IsLockedOut, mbr.CreateDate, mbr.LastLoginDate, usr.LastActivityDate, mbr.LastPasswordChangedDate, mbr.LastLockoutDate " +\r
 171                                 "FROM aspnet_Membership mbr, aspnet_Users usr " +\r
 172                                 "WHERE usr.UserId = mbr.UserId AND mbr.LoweredEmail LIKE ? ORDER BY usr.LoweredUserName";\r
 173                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 174                         AddParameter (cmdSelect, "LoweredEmail", emailToMatch.ToLowerInvariant ());\r
 175                         reader = cmdSelect.ExecuteReader ();\r
 176                         return 0;\r
 177                 }\r
 178 \r
 179                 public static int Membership_FindUsersByName (DbConnection connection, string applicationName, string userNameToMatch, int pageIndex, int pageSize, out DbDataReader reader)\r
 180                 {\r
 181                         string querySelect = "SELECT usr.UserName, mbr.UserId, mbr.Email, mbr.PasswordQuestion, mbr.Comment, mbr.IsApproved, " +\r
 182                                 "mbr.IsLockedOut, mbr.CreateDate, mbr.LastLoginDate, usr.LastActivityDate, mbr.LastPasswordChangedDate, mbr.LastLockoutDate " +\r
 183                                 "FROM aspnet_Membership mbr, aspnet_Users usr " +\r
 184                                 "WHERE usr.UserId = mbr.UserId AND usr.LoweredUserName LIKE ? ORDER BY usr.LoweredUserName";\r
 185                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 186                         AddParameter (cmdSelect, "LoweredUserName", userNameToMatch.ToLowerInvariant ());\r
 187                         reader = cmdSelect.ExecuteReader ();\r
 188                         return 0;\r
 189                 }\r
 190 \r
 191                 public static int Membership_GetAllUsers (DbConnection connection, string applicationName, int pageIndex, int pageSize, out DbDataReader reader)\r
 192                 {\r
 193                         string querySelect = "SELECT usr.UserName, mbr.UserId, mbr.Email, mbr.PasswordQuestion, mbr.Comment, mbr.IsApproved, " +\r
 194                                 "mbr.IsLockedOut, mbr.CreateDate, mbr.LastLoginDate, usr.LastActivityDate, mbr.LastPasswordChangedDate, mbr.LastLockoutDate " + \r
 195                                 "FROM aspnet_Membership mbr, aspnet_Users usr " +\r
 196                                 "WHERE usr.UserId = mbr.UserId ORDER BY usr.LoweredUserName";\r
 197                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 198                         reader = cmdSelect.ExecuteReader ();\r
 199                         return 0;\r
 200                 }\r
 201 \r
 202                 public static int Membership_GetNumberOfUsersOnline (DbConnection connection, string applicationName, int minutesSinceLastInActive, DateTime currentTimeUtc)\r
 203                 {\r
 204                         string queryUsersActive = "SELECT COUNT(*) FROM aspnet_Users usr, aspnet_Applications app, aspnet_Membership mbr " +\r
 205                                 "WHERE usr.ApplicationId = app.ApplicationId AND usr.LastActivityDate > ? AND " +\r
 206                                 "app.LoweredApplicationName = ? AND usr.UserId = mbr.UserId";\r
 207 \r
 208 \r
 209                         OleDbCommand cmdUsersActive = new OleDbCommand (queryUsersActive, (OleDbConnection) connection);\r
 210                         AddParameter (cmdUsersActive, "LastActivityDate", currentTimeUtc.AddMinutes (-minutesSinceLastInActive));\r
 211                         AddParameter (cmdUsersActive, "LoweredApplicationName", applicationName.ToLowerInvariant ());\r
 212                         using (OleDbDataReader reader = cmdUsersActive.ExecuteReader ()) {\r
 213                                 if (reader.Read ())\r
 214                                         return reader.GetInt32 (0);\r
 215                         }\r
 216                         return 0;\r
 217                 }\r
 218 \r
 219                 public static int Membership_GetPassword (DbConnection connection, string applicationName, string username, string passwordAnswer, int maxInvalidPasswordAttempts, int passwordAttemptWindow, DateTime currentTimeUtc, out string password)\r
 220                 {\r
 221                         string querySelect = "SELECT usr.UserId, mbr.Password, mbr.PasswordAnswer, mbr.IsLockedOut, " +\r
 222                                 "mbr.LastLockoutDate, mbr.FailedPwdAnswerAttemptCount, mbr.FailedPwdAnswerAttWindowStart " +\r
 223                                 "FROM aspnet_Applications app, aspnet_Users usr, aspnet_Membership mbr " +\r
 224                                 "WHERE app.LoweredApplicationName = ? AND usr.ApplicationId = app.ApplicationId " +\r
 225                                 "AND usr.UserId = mbr.UserId AND usr.LoweredUserName = ?";\r
 226                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 227                         AddParameter (cmdSelect, "LoweredApplicationName", applicationName.ToLowerInvariant ());\r
 228                         AddParameter (cmdSelect, "LoweredUserName", username.ToLowerInvariant ());\r
 229 \r
 230                         password = null;\r
 231                         string dbUserId = null;\r
 232                         string dbPassword = null;\r
 233                         string dbPasswordAns = null;\r
 234                         bool dbLockedOut = false;\r
 235                         DateTime dbLastLockoutDate;\r
 236                         int dbFailedPasswordAnswerAttemptCount = 0;\r
 237                         DateTime dbFailedPasswordAnswerAttemptWindowStart;\r
 238 \r
 239                         using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 240                                 if (reader.Read ()) {\r
 241                                         dbUserId = reader.GetString (0);\r
 242                                         dbPassword = reader.GetString (1);\r
 243                                         dbPasswordAns = reader.GetString (2);\r
 244                                         dbLockedOut = reader.GetInt32 (3) > 0;\r
 245                                         dbLastLockoutDate = reader.GetDateTime (4);\r
 246                                         dbFailedPasswordAnswerAttemptCount = reader.GetInt32 (5);\r
 247                                         dbFailedPasswordAnswerAttemptWindowStart = reader.GetDateTime (6);\r
 248                                 }\r
 249                                 else {\r
 250                                         return 1; // user not found\r
 251                                 }\r
 252                         }\r
 253 \r
 254                         if (dbLockedOut)\r
 255                                 return 2; // locked out\r
 256 \r
 257                         if (dbPasswordAns != passwordAnswer) {\r
 258                                 if (currentTimeUtc > dbFailedPasswordAnswerAttemptWindowStart.AddMinutes (passwordAttemptWindow)) {\r
 259                                         dbFailedPasswordAnswerAttemptWindowStart = currentTimeUtc;\r
 260                                         dbFailedPasswordAnswerAttemptCount = 1;\r
 261                                 }\r
 262                                 else {\r
 263                                         dbFailedPasswordAnswerAttemptWindowStart = currentTimeUtc;\r
 264                                         dbFailedPasswordAnswerAttemptCount++;\r
 265                                 }\r
 266 \r
 267                                 if (dbFailedPasswordAnswerAttemptCount > maxInvalidPasswordAttempts) {\r
 268                                         dbLockedOut = true;\r
 269                                         dbLastLockoutDate = currentTimeUtc;\r
 270                                 }\r
 271                                 return 3; // wrong password answer\r
 272                         }\r
 273                         else {\r
 274                                 dbFailedPasswordAnswerAttemptCount = 0;\r
 275                                 dbFailedPasswordAnswerAttemptWindowStart = DefaultDateTime;\r
 276                                 password = dbPassword;\r
 277                         }\r
 278 \r
 279                         string queryUpdate = "UPDATE aspnet_Membership SET IsLockedOut = ?, LastLockoutDate = ?, " +\r
 280                                 "FailedPwdAnswerAttemptCount = ?, FailedPwdAnswerAttWindowStart = ? " +\r
 281                                 "WHERE UserId = ?";\r
 282                         OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 283                         AddParameter (cmdUpdate, "IsLockedOut", dbLockedOut);\r
 284                         AddParameter (cmdUpdate, "LastLockoutDate", dbLastLockoutDate);\r
 285                         AddParameter (cmdUpdate, "FailedPwdAnswerAttemptCount", dbFailedPasswordAnswerAttemptCount);\r
 286                         AddParameter (cmdUpdate, "FailedPwdAnswerAttWindowStart", dbFailedPasswordAnswerAttemptWindowStart);\r
 287                         AddParameter (cmdUpdate, "UserId", dbUserId);\r
 288                         cmdUpdate.ExecuteNonQuery ();\r
 289                         \r
 290                         return 0;\r
 291                 }\r
 292 \r
 293                 public static int Membership_GetPasswordWithFormat (DbConnection connection, string applicationName, string username, bool updateLastActivity, DateTime currentTimeUtc, out DbDataReader reader)\r
 294                 {\r
 295                         string querySelect = "SELECT usr.UserId, mbr.IsLockedOut, mbr.IsApproved, mbr.Password, mbr.PasswordFormat, mbr.PasswordSalt, " +\r
 296                                 "mbr.FailedPasswordAttemptCount, mbr.FailedPwdAnswerAttemptCount, mbr.LastLoginDate, usr.LastActivityDate " +\r
 297                                 "FROM aspnet_Applications app, aspnet_Users usr, aspnet_Membership mbr " +\r
 298                                 "WHERE app.LoweredApplicationName = ? AND usr.ApplicationId = app.ApplicationId " +\r
 299                                 "AND usr.UserId = mbr.UserId AND usr.LoweredUserName = ?";\r
 300                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 301                         AddParameter (cmdSelect, "LoweredApplicationName", applicationName.ToLowerInvariant ());\r
 302                         AddParameter (cmdSelect, "LoweredUserName", username.ToLowerInvariant ());\r
 303                         reader = cmdSelect.ExecuteReader ();\r
 304                         return 0;\r
 305                 }\r
 306 \r
 307                 public static int Membership_GetUserByEmail (DbConnection connection, string applicationName, string email, out string username)\r
 308                 {\r
 309                         string querySelect = "SELECT usr.UserName FROM aspnet_Applications app, aspnet_Users usr, aspnet_Membership mbr " +\r
 310                                 "WHERE app.LoweredApplicationName = ? AND usr.ApplicationId = app.ApplicationId AND " +\r
 311                                 "usr.UserId = mbr.UserId AND mbr.LoweredEmail " + (email == null ? "IS NULL" : "= ?");\r
 312 \r
 313                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 314                         AddParameter (cmdSelect, "LoweredApplicationName", applicationName.ToLowerInvariant ());\r
 315                         if (email != null)\r
 316                                 AddParameter (cmdSelect, "LoweredEmail", email.ToLowerInvariant ());\r
 317 \r
 318                         username = null;\r
 319                         using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 320                                 if (reader.Read ()) {\r
 321                                         username = reader.GetString (0);\r
 322                                         if (reader.Read ())\r
 323                                                 return 2; // more that one user found with this email\r
 324                                 }\r
 325                                 else\r
 326                                         return 1; // no users found\r
 327                                 return 0;\r
 328                         }\r
 329                 }\r
 330 \r
 331                 public static int Membership_GetUserByName (DbConnection connection, string applicationName, string username, bool updateLastActivity, DateTime currentTimeUtc, out DbDataReader reader)\r
 332                 {\r
 333                         reader = null;\r
 334                         object userId = GetUserId (connection, applicationName, username);\r
 335                         if (userId == null)\r
 336                                 return 1; // user not found\r
 337 \r
 338                         if (updateLastActivity) {\r
 339                                 string queryUpdate = "UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 340                                 OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 341                                 AddParameter (cmdUpdate, "LastActivityDate", currentTimeUtc);\r
 342                                 AddParameter (cmdUpdate, "UserId", userId);\r
 343                                 int records = cmdUpdate.ExecuteNonQuery ();\r
 344                                 if (records == 0)\r
 345                                         return -1; // unknown error\r
 346                         }\r
 347 \r
 348                         return Membership_GetUserByUserId (connection, userId, updateLastActivity, currentTimeUtc, out reader);\r
 349                 }\r
 350 \r
 351                 public static int Membership_GetUserByUserId (DbConnection connection, object userId, bool updateLastActivity, DateTime currentTimeUtc, out DbDataReader reader)\r
 352                 {\r
 353                         reader = null;\r
 354                         if (updateLastActivity) {\r
 355                                 string queryUpdate = "UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 356                                 OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 357                                 AddParameter (cmdUpdate, "LastActivityDate", currentTimeUtc);\r
 358                                 AddParameter (cmdUpdate, "UserId", userId);\r
 359                                 int recordsAffected = cmdUpdate.ExecuteNonQuery ();\r
 360                                 if (recordsAffected == 0)\r
 361                                         return 1; // user not found\r
 362                         }\r
 363 \r
 364                         string querySelect = "SELECT usr.UserName, mbr.UserId, mbr.Email, mbr.PasswordQuestion, mbr.Comment, mbr.IsApproved, " + \r
 365                                 "mbr.IsLockedOut, mbr.CreateDate, mbr.LastLoginDate, usr.LastActivityDate, mbr.LastPasswordChangedDate, mbr.LastLockoutDate " +\r
 366                                 "FROM aspnet_Users usr, aspnet_Membership mbr " +\r
 367                                 "WHERE usr.UserId = ? AND usr.UserId = mbr.UserId";\r
 368                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 369                         AddParameter (cmdSelect, "UserId", userId);\r
 370                         reader = cmdSelect.ExecuteReader ();\r
 371                         return 0;\r
 372                 }\r
 373 \r
 374                 public static int Membership_ResetPassword (DbConnection connection, string applicationName, string username, string newPassword, string passwordAnswer, int passwordFormat, string passwordSalt, int maxInvalidPasswordAttempts, int passwordAttemptWindow, DateTime currentTimeUtc)\r
 375                 {\r
 376                         string querySelect = "SELECT usr.UserId, mbr.Password, mbr.PasswordAnswer, mbr.IsLockedOut, " +\r
 377                                 "mbr.LastLockoutDate, mbr.FailedPwdAnswerAttemptCount, mbr.FailedPwdAnswerAttWindowStart " +\r
 378                                 "FROM aspnet_Applications app, aspnet_Users usr, aspnet_Membership mbr " +\r
 379                                 "WHERE app.LoweredApplicationName = ? AND usr.ApplicationId = app.ApplicationId " +\r
 380                                 "AND usr.UserId = mbr.UserId AND usr.LoweredUserName = ?";\r
 381                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 382                         AddParameter (cmdSelect, "LoweredApplicationName", applicationName.ToLowerInvariant ());\r
 383                         AddParameter (cmdSelect, "LoweredUserName", username.ToLowerInvariant ());\r
 384 \r
 385                         string dbUserId = null;\r
 386                         string dbPassword = null;\r
 387                         string dbPasswordAns = null;\r
 388                         bool dbLockedOut = false;\r
 389                         DateTime dbLastLockoutDate;\r
 390                         int dbFailedPasswordAnswerAttemptCount = 0;\r
 391                         DateTime dbFailedPasswordAnswerAttemptWindowStart;\r
 392 \r
 393                         using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 394                                 if (reader.Read ()) {\r
 395                                         dbUserId = reader.GetString (0);\r
 396                                         dbPassword = reader.GetString (1);\r
 397                                         dbPasswordAns = reader.GetString (2);\r
 398                                         dbLockedOut = reader.GetInt32 (3) > 0;\r
 399                                         dbLastLockoutDate = reader.GetDateTime (4);\r
 400                                         dbFailedPasswordAnswerAttemptCount = reader.GetInt32 (5);\r
 401                                         dbFailedPasswordAnswerAttemptWindowStart = reader.GetDateTime (6);\r
 402                                 }\r
 403                                 else {\r
 404                                         return 1; // user not found\r
 405                                 }\r
 406                         }\r
 407 \r
 408                         if (dbLockedOut)\r
 409                                 return 2; // locked out\r
 410 \r
 411                         if (dbPasswordAns != passwordAnswer) {\r
 412                                 if (currentTimeUtc > dbFailedPasswordAnswerAttemptWindowStart.AddMinutes (passwordAttemptWindow)) {\r
 413                                         dbFailedPasswordAnswerAttemptWindowStart = currentTimeUtc;\r
 414                                         dbFailedPasswordAnswerAttemptCount = 1;\r
 415                                 }\r
 416                                 else {\r
 417                                         dbFailedPasswordAnswerAttemptWindowStart = currentTimeUtc;\r
 418                                         dbFailedPasswordAnswerAttemptCount++;\r
 419                                 }\r
 420 \r
 421                                 if (dbFailedPasswordAnswerAttemptCount > maxInvalidPasswordAttempts) {\r
 422                                         dbLockedOut = true;\r
 423                                         dbLastLockoutDate = currentTimeUtc;\r
 424                                 }\r
 425                                 return 3; // passwrod answer is wrong\r
 426                         }\r
 427                         else {\r
 428                                 dbFailedPasswordAnswerAttemptCount = 0;\r
 429                                 dbFailedPasswordAnswerAttemptWindowStart = DefaultDateTime;\r
 430                         }\r
 431 \r
 432                         return Membership_SetPasswordUserId (connection, dbUserId, newPassword, passwordSalt, passwordFormat, currentTimeUtc);\r
 433                 }\r
 434 \r
 435                 public static int Membership_SetPassword (DbConnection connection, string applicationName, string username, string newPassword, int passwordFormat, string passwordSalt, DateTime currentTimeUtc)\r
 436                 {\r
 437                         string userId = GetUserId (connection, applicationName, username);\r
 438                         if (userId == null)\r
 439                                 return 1; // user not found\r
 440 \r
 441                         return Membership_SetPasswordUserId (connection, userId, newPassword, passwordSalt, passwordFormat, currentTimeUtc);\r
 442                 }\r
 443 \r
 444                 private static int Membership_SetPasswordUserId (DbConnection connection, string userId, string newPassword, string passwordSalt, int passwordFormat, DateTime currentTimeUtc)\r
 445                 {\r
 446                         string queryUpdate = "UPDATE aspnet_Membership SET Password = ?, PasswordFormat = ?, PasswordSalt = ?, " +\r
 447                                 "LastPasswordChangedDate = ? WHERE UserId = ?";\r
 448                         OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 449                         AddParameter (cmdUpdate, "Password", newPassword);\r
 450                         AddParameter (cmdUpdate, "PasswordFormat", passwordFormat);\r
 451                         AddParameter (cmdUpdate, "PasswordSalt", passwordSalt);\r
 452                         AddParameter (cmdUpdate, "LastPasswordChangedDate", currentTimeUtc);\r
 453                         AddParameter (cmdUpdate, "UserId", userId);\r
 454                         \r
 455                         cmdUpdate.ExecuteNonQuery ();\r
 456                         return 0;\r
 457                 }\r
 458 \r
 459                 public static int Membership_UnlockUser (DbConnection connection, string applicationName, string username)\r
 460                 {\r
 461                         string userId = GetUserId (connection, applicationName, username);\r
 462                         if (userId == null)\r
 463                                 return 1; // user not found\r
 464 \r
 465                         string queryUnlock = "UPDATE aspnet_Membership SET IsLockedOut = 0, " +\r
 466                                 "FailedPasswordAttemptCount = 0, FailedPwdAttemptWindowStart = ?, " +\r
 467                                 "FailedPwdAnswerAttemptCount = 0, FailedPwdAnswerAttWindowStart = ?, " +\r
 468                                 "LastLockoutDate = ? WHERE UserId = ?";\r
 469                         OleDbCommand cmdUnlock = new OleDbCommand (queryUnlock, (OleDbConnection) connection);\r
 470                         AddParameter (cmdUnlock, "FailedPwdAttemptWindowStart", DefaultDateTime);\r
 471                         AddParameter (cmdUnlock, "FailedPwdAnswerAttWindowStart", DefaultDateTime);\r
 472                         AddParameter (cmdUnlock, "LastLockoutDate", DefaultDateTime);\r
 473                         AddParameter (cmdUnlock, "UserId", userId);\r
 474 \r
 475                         cmdUnlock.ExecuteNonQuery ();\r
 476                         return 0;\r
 477                 }\r
 478 \r
 479                 public static int Membership_UpdateUser (DbConnection connection, string applicationName, string username, string email, string comment, bool isApproved, bool uniqueEmail, DateTime lastLoginDate, DateTime lastActivityDate, DateTime currentTimeUtc)\r
 480                 {\r
 481                         string userId = GetUserId (connection, applicationName, username);\r
 482                         if (userId == null)\r
 483                                 return 1; // user not found\r
 484 \r
 485                         if (uniqueEmail) {\r
 486                                 string queryUniqueMail = "SELECT * FROM aspnet_Membership WHERE ApplicationId = ? " +\r
 487                                         "AND UserId <> ? AND LoweredEmail = ?";\r
 488                                 OleDbCommand cmdUniqueMail = new OleDbCommand (queryUniqueMail, (OleDbConnection) connection);\r
 489                                 AddParameter (cmdUniqueMail, "ApplicationId", email);\r
 490                                 AddParameter (cmdUniqueMail, "UserId", userId);\r
 491                                 using (OleDbDataReader reader = cmdUniqueMail.ExecuteReader ()) {\r
 492                                         if (reader.Read ())\r
 493                                                 return 2; // duplicate email\r
 494                                 }\r
 495                         }\r
 496                         string queryUpdateUser = "UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 497                         OleDbCommand cmdUpdateUser = new OleDbCommand (queryUpdateUser, (OleDbConnection) connection);\r
 498                         AddParameter (cmdUpdateUser, "LastActivityDate", lastActivityDate);\r
 499                         AddParameter (cmdUpdateUser, "UserId", userId);\r
 500                         cmdUpdateUser.ExecuteNonQuery ();\r
 501 \r
 502                         string queryUpdateMember = "UPDATE aspnet_Membership SET Email = ?, LoweredEmail = ?, Comment = ?, " +\r
 503                                 "IsApproved = ?, LastLoginDate = ? WHERE UserId = ?";\r
 504                         OleDbCommand cmdUpdateMember = new OleDbCommand (queryUpdateMember, (OleDbConnection) connection);\r
 505                         AddParameter (cmdUpdateMember, "Email", email);\r
 506                         AddParameter (cmdUpdateMember, "LoweredEmail", email.ToLowerInvariant ());\r
 507                         AddParameter (cmdUpdateMember, "Comment", comment);\r
 508                         AddParameter (cmdUpdateMember, "IsApproved", isApproved);\r
 509                         AddParameter (cmdUpdateMember, "LastLoginDate", lastLoginDate);\r
 510                         AddParameter (cmdUpdateMember, "UserId", userId);\r
 511                         cmdUpdateMember.ExecuteNonQuery ();\r
 512 \r
 513                         return 0;\r
 514                 }\r
 515 \r
 516                 public static int Membership_UpdateUserInfo (DbConnection connection, string applicationName, string username, bool isPasswordCorrect, bool updateLastLoginActivityDate, int maxInvalidPasswordAttempts, int passwordAttemptWindow, DateTime currentTimeUtc, DateTime lastLoginDate, DateTime lastActivityDate)\r
 517                 {\r
 518                         string querySelect = "SELECT usr.UserId, mbr.IsApproved, mbr.IsLockedOut, mbr.LastLockoutDate, " +\r
 519                                                         "mbr.FailedPasswordAttemptCount, mbr.FailedPwdAttemptWindowStart " +\r
 520                                                         "FROM aspnet_Applications app, aspnet_Users usr, aspnet_Membership mbr " +\r
 521                                                         "WHERE app.LoweredApplicationName = ? AND usr.ApplicationId = app.ApplicationId " +\r
 522                                                         "AND usr.UserId = mbr.UserId AND usr.LoweredUserName = ?";\r
 523                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 524                         AddParameter (cmdSelect, "LoweredApplicationName", applicationName.ToLowerInvariant ());\r
 525                         AddParameter (cmdSelect, "LoweredUserName", username.ToLowerInvariant ());\r
 526 \r
 527                         string dbUserId = string.Empty;\r
 528                         bool dbIsApproved = false;\r
 529                         bool dbLockedOut = false;\r
 530                         DateTime dbLastLockoutDate;\r
 531                         int dbFailedPasswordAttemptCount = 0;\r
 532                         DateTime dbFailedPasswordAttemptWindowStart;\r
 533 \r
 534                         using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 535                                 if (reader.Read ()) {\r
 536                                         dbUserId = reader.GetString (0);\r
 537                                         dbIsApproved = reader.GetInt32 (1) > 0;\r
 538                                         dbLockedOut = reader.GetInt32 (2) > 0;\r
 539                                         dbLastLockoutDate = reader.GetDateTime (3);\r
 540                                         dbFailedPasswordAttemptCount = reader.GetInt32 (4);\r
 541                                         dbFailedPasswordAttemptWindowStart = reader.GetDateTime (5);\r
 542                                 }\r
 543                                 else {\r
 544                                         return 1; // user not found\r
 545                                 }\r
 546                         }\r
 547 \r
 548                         if (dbLockedOut)\r
 549                                 return 2; // locked out\r
 550 \r
 551                         if (!isPasswordCorrect) {\r
 552                                 if (currentTimeUtc > dbFailedPasswordAttemptWindowStart.AddMinutes (passwordAttemptWindow)) {\r
 553                                         dbFailedPasswordAttemptWindowStart = currentTimeUtc;\r
 554                                         dbFailedPasswordAttemptCount = 1;\r
 555                                 }\r
 556                                 else {\r
 557                                         dbFailedPasswordAttemptWindowStart = currentTimeUtc;\r
 558                                         dbFailedPasswordAttemptCount++;\r
 559                                 }\r
 560 \r
 561                                 if (dbFailedPasswordAttemptCount > maxInvalidPasswordAttempts) {\r
 562                                         dbLockedOut = true;\r
 563                                         dbLastLockoutDate = currentTimeUtc;\r
 564                                 }\r
 565                         }\r
 566                         else {\r
 567                                 dbFailedPasswordAttemptCount = 0;\r
 568                                 dbFailedPasswordAttemptWindowStart = DefaultDateTime;\r
 569                         }\r
 570 \r
 571                         if (updateLastLoginActivityDate) {\r
 572                                 string queryUpdUserActivity = "UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 573                                 OleDbCommand cmdUpdUserActivity = new OleDbCommand (queryUpdUserActivity, (OleDbConnection) connection);\r
 574                                 AddParameter (cmdUpdUserActivity, "LastActivityDate", currentTimeUtc);\r
 575                                 AddParameter (cmdUpdUserActivity, "UserId", dbUserId);\r
 576                                 cmdUpdUserActivity.ExecuteNonQuery ();\r
 577 \r
 578                                 string queryUpdMemberActivity = "UPDATE aspnet_Membership SET LastLoginDate = ? WHERE UserId = ?";\r
 579                                 OleDbCommand cmdUpdMemberActivity = new OleDbCommand (queryUpdMemberActivity, (OleDbConnection) connection);\r
 580                                 AddParameter (cmdUpdMemberActivity, "LastLoginDate", currentTimeUtc);\r
 581                                 AddParameter (cmdUpdMemberActivity, "UserId", dbUserId);\r
 582                                 cmdUpdMemberActivity.ExecuteNonQuery ();\r
 583                         }\r
 584 \r
 585                         string queryUpdate = "UPDATE aspnet_Membership SET IsLockedOut = ?, LastLockoutDate = ?, " +\r
 586                                 "FailedPasswordAttemptCount = ?, FailedPwdAttemptWindowStart = ? " +\r
 587                                 "WHERE UserId = ?";\r
 588                         OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 589                         AddParameter (cmdUpdate, "IsLockedOut", dbLockedOut);\r
 590                         AddParameter (cmdUpdate, "LastLockoutDate", dbLastLockoutDate);\r
 591                         AddParameter (cmdUpdate, "FailedPasswordAttemptCount", dbFailedPasswordAttemptCount);\r
 592                         AddParameter (cmdUpdate, "FailedPwdAttemptWindowStart", dbFailedPasswordAttemptWindowStart);\r
 593                         AddParameter (cmdUpdate, "UserId", dbUserId);\r
 594                         cmdUpdate.ExecuteNonQuery ();\r
 595                         return 0;\r
 596                 }\r
 597 \r
 598                 public static int Users_CreateUser (DbConnection connection, DbTransaction trans, string applicationId, string username, bool isAnonymous, DateTime lastActivityDate, ref object userId)\r
 599                 {\r
 600                         string selectQuery = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName = ? AND ApplicationId = ?";\r
 601                         OleDbCommand selectCmd = new OleDbCommand (selectQuery, (OleDbConnection) connection);\r
 602                         AddParameter (selectCmd, "LoweredUserName", username.ToLowerInvariant ());\r
 603                         AddParameter (selectCmd, "ApplicationId", applicationId);\r
 604                         if (trans != null)\r
 605                                 selectCmd.Transaction = (OleDbTransaction) trans;\r
 606 \r
 607                         string existingUserId = null;\r
 608                         using (OleDbDataReader reader = selectCmd.ExecuteReader ()) {\r
 609                                 if (reader.Read ())\r
 610                                         existingUserId = reader.GetString (0);\r
 611                         }\r
 612 \r
 613                         if (existingUserId != null && existingUserId.Length > 0) {\r
 614                                 userId = existingUserId;\r
 615                                 return 1; // user with such username and appid already exists\r
 616                         }\r
 617 \r
 618                         if (userId != null) {\r
 619                                 string querySelectUserId = "SELECT UserId FROM aspnet_Users WHERE UserId = ?";\r
 620                                 OleDbCommand cmdSelectUserId = new OleDbCommand (querySelectUserId, (OleDbConnection) connection);\r
 621                                 AddParameter (cmdSelectUserId, "UserId", userId);\r
 622                                 if (trans != null)\r
 623                                         cmdSelectUserId.Transaction = (OleDbTransaction) trans;\r
 624 \r
 625                                 using (OleDbDataReader reader = cmdSelectUserId.ExecuteReader ()) {\r
 626                                         if (reader.Read ())\r
 627                                                 return 2; // user with such userId already exists\r
 628                                 }\r
 629                         }\r
 630 \r
 631                         if (userId == null)\r
 632                                 userId = Guid.NewGuid ().ToString ();\r
 633 \r
 634                         string insertQuery = "INSERT INTO aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate) VALUES (?, ?, ?, ?, ?, ?)";\r
 635                         OleDbCommand insertCmd = new OleDbCommand (insertQuery, (OleDbConnection) connection);\r
 636                         AddParameter (insertCmd, "ApplicationId", applicationId);\r
 637                         AddParameter (insertCmd, "UserId", userId);\r
 638                         AddParameter (insertCmd, "UserName", username);\r
 639                         AddParameter (insertCmd, "LoweredUserName", username.ToLowerInvariant ());\r
 640                         AddParameter (insertCmd, "IsAnonymous", isAnonymous);\r
 641                         AddParameter (insertCmd, "LastActivityDate", lastActivityDate);\r
 642                         if (trans != null)\r
 643                                 insertCmd.Transaction = (OleDbTransaction) trans;\r
 644 \r
 645                         insertCmd.ExecuteNonQuery ();\r
 646                         return 0;\r
 647                 }\r
 648 \r
 649                 public static int Users_DeleteUser (DbConnection connection, string applicationName, string username, int tablesToDeleteFrom, ref int numTablesDeletedFrom)\r
 650                 {\r
 651                         string userId = GetUserId (connection, applicationName, username);\r
 652                         if (userId == null)\r
 653                                 return 1; // user not found\r
 654 \r
 655                         numTablesDeletedFrom = 0;\r
 656                         OleDbTransaction trans = (OleDbTransaction) connection.BeginTransaction ();\r
 657 \r
 658                         try {\r
 659                                 if ((tablesToDeleteFrom & 1) == 1) {\r
 660                                         string queryDelete = "DELETE FROM aspnet_Membership WHERE UserId = ?";\r
 661                                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
 662                                         AddParameter (cmdDelete, "UserId", userId);\r
 663                                         cmdDelete.Transaction = trans;\r
 664                                         cmdDelete.ExecuteNonQuery ();\r
 665 \r
 666                                         numTablesDeletedFrom++;\r
 667                                 }\r
 668 \r
 669                                 if ((tablesToDeleteFrom & 2) == 2) {\r
 670                                         string queryDelete = "DELETE FROM aspnet_UsersInRoles WHERE UserId = ?";\r
 671                                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
 672                                         AddParameter (cmdDelete, "UserId", userId);\r
 673                                         cmdDelete.Transaction = trans;\r
 674                                         cmdDelete.ExecuteNonQuery ();\r
 675 \r
 676                                         numTablesDeletedFrom++;\r
 677                                 }\r
 678 \r
 679                                 if ((tablesToDeleteFrom & 4) == 4) {\r
 680                                         string queryDelete = "DELETE FROM aspnet_Profile WHERE UserId = ?";\r
 681                                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
 682                                         AddParameter (cmdDelete, "UserId", userId);\r
 683                                         cmdDelete.Transaction = trans;\r
 684                                         cmdDelete.ExecuteNonQuery ();\r
 685 \r
 686                                         numTablesDeletedFrom++;\r
 687                                 }\r
 688 \r
 689                                 // this table was removed  from schema\r
 690                                 //if ((tablesToDeleteFrom & 8) == 8) {\r
 691                                 //    string queryDelete = "DELETE FROM aspnet_PersonalizationPerUser WHERE UserId = ?";\r
 692                                 //    OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
 693                                 //    AddParameter (cmdDelete, "UserId", userId);\r
 694                                 //    cmdDelete.Transaction = trans;\r
 695                                 //    cmdDelete.ExecuteNonQuery ();\r
 696 \r
 697                                 //    numTablesDeletedFrom++;\r
 698                                 //}\r
 699 \r
 700                                 if ((tablesToDeleteFrom & 15) == 15) {\r
 701                                         string queryDelete = "DELETE FROM aspnet_Users WHERE UserId = ?";\r
 702                                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
 703                                         AddParameter (cmdDelete, "UserId", userId);\r
 704                                         cmdDelete.Transaction = trans;\r
 705                                         cmdDelete.ExecuteNonQuery ();\r
 706 \r
 707                                         numTablesDeletedFrom++;\r
 708                                 }\r
 709 \r
 710                                 trans.Commit ();\r
 711                         }\r
 712                         catch (Exception e) {\r
 713                                 trans.Rollback ();\r
 714                                 throw e;\r
 715                         }\r
 716 \r
 717                         return 0;\r
 718                 }\r
 719 \r
 720                 private static string GetUserId (DbConnection connection, string applicationName, string username)\r
 721                 {\r
 722                         string selectQuery = "SELECT usr.UserId FROM aspnet_Membership mbr, aspnet_Users usr, aspnet_Applications app WHERE " +\r
 723                                 "usr.LoweredUserName = ? AND app.LoweredApplicationName = ? " +\r
 724                                 "AND usr.ApplicationId = app.ApplicationId " +\r
 725                                 "AND usr.UserId = mbr.UserId";\r
 726 \r
 727                         OleDbCommand selectCmd = new OleDbCommand (selectQuery, (OleDbConnection) connection);\r
 728                         AddParameter (selectCmd, "LoweredUserName", username.ToLowerInvariant ());\r
 729                         AddParameter (selectCmd, "PasswordAnswer", applicationName.ToLowerInvariant ());\r
 730 \r
 731                         using (OleDbDataReader reader = selectCmd.ExecuteReader ()) {\r
 732                                 if (reader.Read ())\r
 733                                         return reader.GetString (0);\r
 734                         }\r
 735 \r
 736                         return null; // user not found\r
 737                 }\r
 738         }\r
 739 }\r
 740 \r
 741 #endif