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 != (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 \r
  92                                 string selectQueryMbrUserId = "SELECT UserId FROM aspnet_Membership WHERE UserId = ?";\r
  93                                 OleDbCommand selectCmdMbrUserId = new OleDbCommand (selectQueryMbrUserId, (OleDbConnection) connection);\r
  94                                 selectCmdMbrUserId.Transaction = trans;\r
  95                                 AddParameter (selectCmdMbrUserId, "UserId", newUserId);\r
  96                                 using (OleDbDataReader reader = selectCmdMbrUserId.ExecuteReader ()) {\r
  97                                         if (reader.Read ()) {\r
  98                                                 trans.Rollback ();\r
  99                                                 return 2; // user with such userId already exists\r
 100                                         }\r
 101                                 }\r
 102 \r
 103                                 if (uniqueEmail) {\r
 104                                         string queryMbrEmail = "SELECT * FROM  aspnet_Membership WHERE ApplicationId = ? AND LoweredEmail = ?";\r
 105                                         OleDbCommand cmdMbrEmail = new OleDbCommand (queryMbrEmail, (OleDbConnection) connection);\r
 106                                         cmdMbrEmail.Transaction = trans;\r
 107                                         AddParameter (cmdMbrEmail, "ApplicationId", applicationId);\r
 108                                         AddParameter (cmdMbrEmail, "LoweredEmail", email.ToLower ());\r
 109                                         using (OleDbDataReader reader = cmdMbrEmail.ExecuteReader ()) {\r
 110                                                 if (reader.Read ()) {\r
 111                                                         trans.Rollback ();\r
 112                                                         return 3; // user with such email already exists\r
 113                                                 }\r
 114                                         }\r
 115                                 }\r
 116 \r
 117                                 if (returnValue == 1) {\r
 118                                         // if user was not created, but found existing and correct\r
 119                                         // update it's activity (membership create) time.\r
 120                                         string queryUpdActivity = "UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 121                                         OleDbCommand cmdUpdActivity = new OleDbCommand (queryUpdActivity, (OleDbConnection) connection);\r
 122                                         cmdUpdActivity.Transaction = trans;\r
 123                                         AddParameter (cmdUpdActivity, "LastActivityDate", createDate);\r
 124                                         AddParameter (cmdUpdActivity, "UserId", newUserId);\r
 125                                         cmdUpdActivity.ExecuteNonQuery ();\r
 126                                 }\r
 127 \r
 128                                 string queryInsertMbr = "INSERT INTO aspnet_Membership (ApplicationId, UserId, Password, PasswordFormat, PasswordSalt, Email, " +\r
 129                                         "LoweredEmail, PasswordQuestion, PasswordAnswer, IsApproved, IsLockedOut, CreateDate, LastLoginDate, " +\r
 130                                         "LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, " +\r
 131                                         "FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart) " +\r
 132                                         "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";\r
 133                                 OleDbCommand cmdInsertMbr = new OleDbCommand (queryInsertMbr, (OleDbConnection) connection);\r
 134                                 cmdInsertMbr.Transaction = trans;\r
 135                                 AddParameter (cmdInsertMbr, "ApplicationId", applicationId);\r
 136                                 AddParameter (cmdInsertMbr, "UserId", newUserId);\r
 137                                 AddParameter (cmdInsertMbr, "Password", password);\r
 138                                 AddParameter (cmdInsertMbr, "PasswordFormat", passwordFormat);\r
 139                                 AddParameter (cmdInsertMbr, "PasswordSalt", passwordSalt);\r
 140                                 AddParameter (cmdInsertMbr, "Email", email);\r
 141                                 AddParameter (cmdInsertMbr, "LoweredEmail", email.ToLower ());\r
 142                                 AddParameter (cmdInsertMbr, "PasswordQuestion", pwdQuestion);\r
 143                                 AddParameter (cmdInsertMbr, "PasswordAnswer", pwdAnswer);\r
 144                                 AddParameter (cmdInsertMbr, "IsApproved", isApproved);\r
 145                                 AddParameter (cmdInsertMbr, "IsLockedOut", 0);\r
 146                                 AddParameter (cmdInsertMbr, "CreateDate", createDate);\r
 147                                 AddParameter (cmdInsertMbr, "LastLoginDate", DefaultDateTime);\r
 148                                 AddParameter (cmdInsertMbr, "LastPasswordChangedDate", createDate);\r
 149                                 AddParameter (cmdInsertMbr, "LastLockoutDate", DefaultDateTime);\r
 150                                 AddParameter (cmdInsertMbr, "FailedPasswordAttemptCount", 0);\r
 151                                 AddParameter (cmdInsertMbr, "FailedPasswordAttemptWindowStart", DefaultDateTime);\r
 152                                 AddParameter (cmdInsertMbr, "FailedPasswordAnswerAttemptCount", 0);\r
 153                                 AddParameter (cmdInsertMbr, "FailedPasswordAnswerAttemptWindowStart", DefaultDateTime);\r
 154                                 cmdInsertMbr.ExecuteNonQuery ();\r
 155 \r
 156                                 trans.Commit ();\r
 157                         }\r
 158                         catch (Exception e) {\r
 159                                 trans.Rollback ();\r
 160                                 throw e;\r
 161                         }\r
 162 \r
 163                         return 0;\r
 164                 }\r
 165 \r
 166                 public static int Membership_FindUsersByEmail (DbConnection connection, string applicationName, string emailToMatch, int pageIndex, int pageSize, out DbDataReader reader)\r
 167                 {\r
 168                         string querySelect = "SELECT usr.UserName, mbr.UserId, mbr.Email, mbr.PasswordQuestion, mbr.Comment, mbr.IsApproved, " +\r
 169                                 "mbr.IsLockedOut, mbr.CreateDate, mbr.LastLoginDate, usr.LastActivityDate, mbr.LastPasswordChangedDate, mbr.LastLockoutDate " +\r
 170                                 "FROM aspnet_Membership mbr, aspnet_Users usr " +\r
 171                                 "WHERE usr.UserId = mbr.UserId AND mbr.LoweredEmail LIKE ? ORDER BY usr.LoweredUserName";\r
 172                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 173                         AddParameter (cmdSelect, "LoweredEmail", emailToMatch.ToLower ());\r
 174                         reader = cmdSelect.ExecuteReader ();\r
 175                         return 0;\r
 176                 }\r
 177 \r
 178                 public static int Membership_FindUsersByName (DbConnection connection, string applicationName, string userNameToMatch, int pageIndex, int pageSize, out DbDataReader reader)\r
 179                 {\r
 180                         string querySelect = "SELECT usr.UserName, mbr.UserId, mbr.Email, mbr.PasswordQuestion, mbr.Comment, mbr.IsApproved, " +\r
 181                                 "mbr.IsLockedOut, mbr.CreateDate, mbr.LastLoginDate, usr.LastActivityDate, mbr.LastPasswordChangedDate, mbr.LastLockoutDate " +\r
 182                                 "FROM aspnet_Membership mbr, aspnet_Users usr " +\r
 183                                 "WHERE usr.UserId = mbr.UserId AND usr.LoweredUserName LIKE ? ORDER BY usr.LoweredUserName";\r
 184                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 185                         AddParameter (cmdSelect, "LoweredUserName", userNameToMatch.ToLower ());\r
 186                         reader = cmdSelect.ExecuteReader ();\r
 187                         return 0;\r
 188                 }\r
 189 \r
 190                 public static int Membership_GetAllUsers (DbConnection connection, string applicationName, int pageIndex, int pageSize, out DbDataReader reader)\r
 191                 {\r
 192                         string querySelect = "SELECT usr.UserName, mbr.UserId, mbr.Email, mbr.PasswordQuestion, mbr.Comment, mbr.IsApproved, " +\r
 193                                 "mbr.IsLockedOut, mbr.CreateDate, mbr.LastLoginDate, usr.LastActivityDate, mbr.LastPasswordChangedDate, mbr.LastLockoutDate " + \r
 194                                 "FROM aspnet_Membership mbr, aspnet_Users usr " +\r
 195                                 "WHERE usr.UserId = mbr.UserId ORDER BY usr.LoweredUserName";\r
 196                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 197                         reader = cmdSelect.ExecuteReader ();\r
 198                         return 0;\r
 199                 }\r
 200 \r
 201                 public static int Membership_GetNumberOfUsersOnline (DbConnection connection, string applicationName, int minutesSinceLastInActive, DateTime currentTimeUtc)\r
 202                 {\r
 203                         string queryUsersActive = "SELECT COUNT(*) FROM aspnet_Users usr, aspnet_Applications app, aspnet_Membership mbr " +
 204                                 "WHERE usr.ApplicationId = app.ApplicationId AND usr.LastActivityDate > ? AND " +
 205                                 "app.LoweredApplicationName = ? AND usr.UserId = mbr.UserId";\r
 206 \r
 207 \r
 208                         OleDbCommand cmdUsersActive = new OleDbCommand (queryUsersActive, (OleDbConnection) connection);\r
 209                         AddParameter (cmdUsersActive, "LastActivityDate", currentTimeUtc.AddMinutes (-minutesSinceLastInActive));\r
 210                         AddParameter (cmdUsersActive, "LoweredApplicationName", applicationName.ToLower ());\r
 211                         using (OleDbDataReader reader = cmdUsersActive.ExecuteReader ()) {\r
 212                                 if (reader.Read ())\r
 213                                         return reader.GetInt32 (0);\r
 214                         }\r
 215                         return 0;\r
 216                 }\r
 217 \r
 218                 public static int Membership_GetPassword (DbConnection connection, string applicationName, string username, string passwordAnswer, int maxInvalidPasswordAttempts, int passwordAttemptWindow, DateTime currentTimeUtc, out string password)\r
 219                 {\r
 220                         string querySelect = "SELECT usr.UserId, mbr.Password, mbr.PasswordAnswer, mbr.IsLockedOut, " +\r
 221                                 "mbr.LastLockoutDate, mbr.FailedPasswordAnswerAttemptCount, mbr.FailedPasswordAnswerAttemptWindowStart " +\r
 222                                 "FROM aspnet_Applications app, aspnet_Users usr, aspnet_Membership mbr " +\r
 223                                 "WHERE app.LoweredApplicationName = ? AND usr.ApplicationId = app.ApplicationId " +\r
 224                                 "AND usr.UserId = mbr.UserId AND usr.LoweredUserName = ?";\r
 225                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 226                         AddParameter (cmdSelect, "LoweredApplicationName", applicationName.ToLower ());\r
 227                         AddParameter (cmdSelect, "LoweredUserName", username.ToLower ());\r
 228 \r
 229                         password = null;\r
 230                         string dbUserId = null;\r
 231                         string dbPassword = null;\r
 232                         string dbPasswordAns = null;\r
 233                         bool dbLockedOut = false;\r
 234                         DateTime dbLastLockoutDate;\r
 235                         int dbFailedPasswordAnswerAttemptCount = 0;\r
 236                         DateTime dbFailedPasswordAnswerAttemptWindowStart;\r
 237 \r
 238                         using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 239                                 if (reader.Read ()) {\r
 240                                         dbUserId = reader.GetString (0);\r
 241                                         dbPassword = reader.GetString (1);\r
 242                                         dbPasswordAns = reader.GetString (2);\r
 243                                         dbLockedOut = reader.GetInt32 (3) > 0;\r
 244                                         dbLastLockoutDate = reader.GetDateTime (4);\r
 245                                         dbFailedPasswordAnswerAttemptCount = reader.GetInt32 (5);\r
 246                                         dbFailedPasswordAnswerAttemptWindowStart = reader.GetDateTime (6);\r
 247                                 }\r
 248                                 else {\r
 249                                         return 1; // user not found\r
 250                                 }\r
 251                         }\r
 252 \r
 253                         if (dbLockedOut)\r
 254                                 return 2; // locked out\r
 255 \r
 256                         if (dbPasswordAns != passwordAnswer) {\r
 257                                 if (currentTimeUtc > dbFailedPasswordAnswerAttemptWindowStart.AddMinutes (passwordAttemptWindow)) {\r
 258                                         dbFailedPasswordAnswerAttemptWindowStart = currentTimeUtc;\r
 259                                         dbFailedPasswordAnswerAttemptCount = 1;\r
 260                                 }\r
 261                                 else {\r
 262                                         dbFailedPasswordAnswerAttemptWindowStart = currentTimeUtc;\r
 263                                         dbFailedPasswordAnswerAttemptCount++;\r
 264                                 }\r
 265 \r
 266                                 if (dbFailedPasswordAnswerAttemptCount > maxInvalidPasswordAttempts) {\r
 267                                         dbLockedOut = true;\r
 268                                         dbLastLockoutDate = currentTimeUtc;\r
 269                                 }\r
 270                                 return 3; // wrong password answer\r
 271                         }\r
 272                         else {\r
 273                                 dbFailedPasswordAnswerAttemptCount = 0;\r
 274                                 dbFailedPasswordAnswerAttemptWindowStart = DefaultDateTime;\r
 275                                 password = dbPassword;\r
 276                         }\r
 277 \r
 278                         string queryUpdate = "UPDATE aspnet_Membership SET IsLockedOut = ?, LastLockoutDate = ?, " +\r
 279                                 "FailedPasswordAnswerAttemptCount = ?, FailedPasswordAnswerAttemptWindowStart = ? " +\r
 280                                 "WHERE UserId = ?";\r
 281                         OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 282                         AddParameter (cmdUpdate, "IsLockedOut", dbLockedOut);\r
 283                         AddParameter (cmdUpdate, "LastLockoutDate", dbLastLockoutDate);\r
 284                         AddParameter (cmdUpdate, "FailedPasswordAnswerAttemptCount", dbFailedPasswordAnswerAttemptCount);\r
 285                         AddParameter (cmdUpdate, "FailedPasswordAnswerAttemptWindowStart", dbFailedPasswordAnswerAttemptWindowStart);\r
 286                         AddParameter (cmdUpdate, "UserId", dbUserId);\r
 287                         cmdUpdate.ExecuteNonQuery ();\r
 288                         \r
 289                         return 0;\r
 290                 }\r
 291 \r
 292                 public static int Membership_GetPasswordWithFormat (DbConnection connection, string applicationName, string username, bool updateLastActivity, DateTime currentTimeUtc, out DbDataReader reader)\r
 293                 {\r
 294                         string querySelect = "SELECT usr.UserId, mbr.IsLockedOut, mbr.IsApproved, mbr.Password, mbr.PasswordFormat, mbr.PasswordSalt, " +\r
 295                                 "mbr.FailedPasswordAttemptCount, mbr.FailedPasswordAnswerAttemptCount, mbr.LastLoginDate, usr.LastActivityDate " +\r
 296                                 "FROM aspnet_Applications app, aspnet_Users usr, aspnet_Membership mbr " +\r
 297                                 "WHERE app.LoweredApplicationName = ? AND usr.ApplicationId = app.ApplicationId " +\r
 298                                 "AND usr.UserId = mbr.UserId AND usr.LoweredUserName = ?";\r
 299                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 300                         AddParameter (cmdSelect, "LoweredApplicationName", applicationName.ToLower ());\r
 301                         AddParameter (cmdSelect, "LoweredUserName", username.ToLower ());\r
 302                         reader = cmdSelect.ExecuteReader ();\r
 303                         return 0;\r
 304                 }\r
 305 \r
 306                 public static int Membership_GetUserByEmail (DbConnection connection, string applicationName, string email, out string username)\r
 307                 {\r
 308                         string querySelect = "SELECT usr.UserName FROM aspnet_Applications app, aspnet_Users usr, aspnet_Membership mbr " +\r
 309                                 "WHERE app.LoweredApplicationName = ? AND usr.ApplicationId = app.ApplicationId AND " +\r
 310                                 "usr.UserId = mbr.UserId AND mbr.LoweredEmail " + (email == null ? "IS NULL" : "= ?");\r
 311 \r
 312                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 313                         AddParameter (cmdSelect, "LoweredApplicationName", applicationName.ToLower ());\r
 314                         if (email != null)\r
 315                                 AddParameter (cmdSelect, "LoweredEmail", email.ToLower ());\r
 316 \r
 317                         username = null;\r
 318                         using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 319                                 if (reader.Read ()) {\r
 320                                         username = reader.GetString (0);\r
 321                                         if (reader.Read ())\r
 322                                                 return 2; // more that one user found with this email\r
 323                                 }\r
 324                                 else\r
 325                                         return 1; // no users found\r
 326                                 return 0;\r
 327                         }\r
 328                 }\r
 329 \r
 330                 public static int Membership_GetUserByName (DbConnection connection, string applicationName, string username, bool updateLastActivity, DateTime currentTimeUtc, out DbDataReader reader)\r
 331                 {\r
 332                         reader = null;\r
 333                         object userId = GetUserId (connection, applicationName, username);\r
 334                         if (userId == null)\r
 335                                 return 1; // user not found\r
 336 \r
 337                         if (updateLastActivity) {\r
 338                                 string queryUpdate = "UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 339                                 OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 340                                 AddParameter (cmdUpdate, "LastActivityDate", currentTimeUtc);\r
 341                                 AddParameter (cmdUpdate, "UserId", userId);\r
 342                                 int records = cmdUpdate.ExecuteNonQuery ();\r
 343                                 if (records == 0)\r
 344                                         return -1; // unknown error\r
 345                         }\r
 346 \r
 347                         return Membership_GetUserByUserId (connection, userId, updateLastActivity, currentTimeUtc, out reader);\r
 348                 }\r
 349 \r
 350                 public static int Membership_GetUserByUserId (DbConnection connection, object userId, bool updateLastActivity, DateTime currentTimeUtc, out DbDataReader reader)\r
 351                 {\r
 352                         reader = null;\r
 353                         if (updateLastActivity) {\r
 354                                 string queryUpdate = "UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 355                                 OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 356                                 AddParameter (cmdUpdate, "LastActivityDate", currentTimeUtc);\r
 357                                 AddParameter (cmdUpdate, "UserId", userId);\r
 358                                 int recordsAffected = cmdUpdate.ExecuteNonQuery ();\r
 359                                 if (recordsAffected == 0)\r
 360                                         return 1; // user not found\r
 361                         }\r
 362 \r
 363                         string querySelect = "SELECT usr.UserName, mbr.UserId, mbr.Email, mbr.PasswordQuestion, mbr.Comment, mbr.IsApproved, " +
 364                                 "mbr.IsLockedOut, mbr.CreateDate, mbr.LastLoginDate, usr.LastActivityDate, mbr.LastPasswordChangedDate, mbr.LastLockoutDate " +
 365                                 "FROM aspnet_Users usr, aspnet_Membership mbr " +
 366                                 "WHERE usr.UserId = ? AND usr.UserId = mbr.UserId";\r
 367                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 368                         AddParameter (cmdSelect, "UserId", userId);\r
 369                         reader = cmdSelect.ExecuteReader ();\r
 370                         return 0;\r
 371                 }\r
 372 \r
 373                 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
 374                 {\r
 375                         string querySelect = "SELECT usr.UserId, mbr.Password, mbr.PasswordAnswer, mbr.IsLockedOut, " +\r
 376                                 "mbr.LastLockoutDate, mbr.FailedPasswordAnswerAttemptCount, mbr.FailedPasswordAnswerAttemptWindowStart " +\r
 377                                 "FROM aspnet_Applications app, aspnet_Users usr, aspnet_Membership mbr " +\r
 378                                 "WHERE app.LoweredApplicationName = ? AND usr.ApplicationId = app.ApplicationId " +\r
 379                                 "AND usr.UserId = mbr.UserId AND usr.LoweredUserName = ?";\r
 380                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 381                         AddParameter (cmdSelect, "LoweredApplicationName", applicationName.ToLower ());\r
 382                         AddParameter (cmdSelect, "LoweredUserName", username.ToLower ());\r
 383 \r
 384                         string dbUserId = null;\r
 385                         string dbPassword = null;\r
 386                         string dbPasswordAns = null;\r
 387                         bool dbLockedOut = false;\r
 388                         DateTime dbLastLockoutDate;\r
 389                         int dbFailedPasswordAnswerAttemptCount = 0;\r
 390                         DateTime dbFailedPasswordAnswerAttemptWindowStart;\r
 391 \r
 392                         using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 393                                 if (reader.Read ()) {\r
 394                                         dbUserId = reader.GetString (0);\r
 395                                         dbPassword = reader.GetString (1);\r
 396                                         dbPasswordAns = reader.GetString (2);\r
 397                                         dbLockedOut = reader.GetInt32 (3) > 0;\r
 398                                         dbLastLockoutDate = reader.GetDateTime (4);\r
 399                                         dbFailedPasswordAnswerAttemptCount = reader.GetInt32 (5);\r
 400                                         dbFailedPasswordAnswerAttemptWindowStart = reader.GetDateTime (6);\r
 401                                 }\r
 402                                 else {\r
 403                                         return 1; // user not found\r
 404                                 }\r
 405                         }\r
 406 \r
 407                         if (dbLockedOut)\r
 408                                 return 2; // locked out\r
 409 \r
 410                         if (dbPasswordAns != passwordAnswer) {\r
 411                                 if (currentTimeUtc > dbFailedPasswordAnswerAttemptWindowStart.AddMinutes (passwordAttemptWindow)) {\r
 412                                         dbFailedPasswordAnswerAttemptWindowStart = currentTimeUtc;\r
 413                                         dbFailedPasswordAnswerAttemptCount = 1;\r
 414                                 }\r
 415                                 else {\r
 416                                         dbFailedPasswordAnswerAttemptWindowStart = currentTimeUtc;\r
 417                                         dbFailedPasswordAnswerAttemptCount++;\r
 418                                 }\r
 419 \r
 420                                 if (dbFailedPasswordAnswerAttemptCount > maxInvalidPasswordAttempts) {\r
 421                                         dbLockedOut = true;\r
 422                                         dbLastLockoutDate = currentTimeUtc;\r
 423                                 }\r
 424                                 return 3; // passwrod answer is wrong\r
 425                         }\r
 426                         else {\r
 427                                 dbFailedPasswordAnswerAttemptCount = 0;\r
 428                                 dbFailedPasswordAnswerAttemptWindowStart = DefaultDateTime;\r
 429                         }\r
 430 \r
 431                         return Membership_SetPasswordUserId (connection, dbUserId, newPassword, passwordSalt, passwordFormat, currentTimeUtc);\r
 432                 }\r
 433 \r
 434                 public static int Membership_SetPassword (DbConnection connection, string applicationName, string username, string newPassword, int passwordFormat, string passwordSalt, DateTime currentTimeUtc)\r
 435                 {\r
 436                         string userId = GetUserId (connection, applicationName, username);\r
 437                         if (userId == null)\r
 438                                 return 1; // user not found\r
 439 \r
 440                         return Membership_SetPasswordUserId (connection, userId, newPassword, passwordSalt, passwordFormat, currentTimeUtc);\r
 441                 }\r
 442 \r
 443                 private static int Membership_SetPasswordUserId (DbConnection connection, string userId, string newPassword, string passwordSalt, int passwordFormat, DateTime currentTimeUtc)\r
 444                 {\r
 445                         string queryUpdate = "UPDATE aspnet_Membership SET Password = ?, PasswordFormat = ?, PasswordSalt = ?, " +\r
 446                                 "LastPasswordChangedDate = ? WHERE UserId = ?";\r
 447                         OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 448                         AddParameter (cmdUpdate, "Password", newPassword);\r
 449                         AddParameter (cmdUpdate, "PasswordFormat", passwordFormat);\r
 450                         AddParameter (cmdUpdate, "PasswordSalt", passwordSalt);\r
 451                         AddParameter (cmdUpdate, "LastPasswordChangedDate", currentTimeUtc);\r
 452                         AddParameter (cmdUpdate, "UserId", userId);\r
 453                         \r
 454                         cmdUpdate.ExecuteNonQuery ();\r
 455                         return 0;\r
 456                 }\r
 457 \r
 458                 public static int Membership_UnlockUser (DbConnection connection, string applicationName, string username)\r
 459                 {\r
 460                         string userId = GetUserId (connection, applicationName, username);\r
 461                         if (userId == null)\r
 462                                 return 1; // user not found\r
 463 \r
 464                         string queryUnlock = "UPDATE aspnet_Membership SET IsLockedOut = 0, " +\r
 465                                 "FailedPasswordAttemptCount = 0, FailedPasswordAttemptWindowStart = ?, " +\r
 466                                 "FailedPasswordAnswerAttemptCount = 0, FailedPasswordAnswerAttemptWindowStart = ?, " +\r
 467                                 "LastLockoutDate = ? WHERE UserId = ?";\r
 468                         OleDbCommand cmdUnlock = new OleDbCommand (queryUnlock, (OleDbConnection) connection);\r
 469                         AddParameter (cmdUnlock, "FailedPasswordAttemptWindowStart", DefaultDateTime);\r
 470                         AddParameter (cmdUnlock, "FailedPasswordAnswerAttemptWindowStart", DefaultDateTime);\r
 471                         AddParameter (cmdUnlock, "LastLockoutDate", DefaultDateTime);\r
 472                         AddParameter (cmdUnlock, "UserId", userId);\r
 473 \r
 474                         cmdUnlock.ExecuteNonQuery ();\r
 475                         return 0;\r
 476                 }\r
 477 \r
 478                 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
 479                 {\r
 480                         string userId = GetUserId (connection, applicationName, username);\r
 481                         if (userId == null)\r
 482                                 return 1; // user not found\r
 483 \r
 484                         if (uniqueEmail) {\r
 485                                 string queryUniqueMail = "SELECT * FROM aspnet_Membership WHERE ApplicationId = ? " +\r
 486                                         "AND UserId <> ? AND LoweredEmail = ?";\r
 487                                 OleDbCommand cmdUniqueMail = new OleDbCommand (queryUniqueMail, (OleDbConnection) connection);\r
 488                                 AddParameter (cmdUniqueMail, "ApplicationId", email);\r
 489                                 AddParameter (cmdUniqueMail, "UserId", userId);\r
 490                                 using (OleDbDataReader reader = cmdUniqueMail.ExecuteReader ()) {\r
 491                                         if (reader.Read ())\r
 492                                                 return 2; // duplicate email\r
 493                                 }\r
 494                         }\r
 495                         string queryUpdateUser = "UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 496                         OleDbCommand cmdUpdateUser = new OleDbCommand (queryUpdateUser, (OleDbConnection) connection);\r
 497                         AddParameter (cmdUpdateUser, "LastActivityDate", lastActivityDate);\r
 498                         AddParameter (cmdUpdateUser, "UserId", userId);\r
 499                         cmdUpdateUser.ExecuteNonQuery ();\r
 500 \r
 501                         string queryUpdateMember = "UPDATE aspnet_Membership SET Email = ?, LoweredEmail = ?, Comment = ?, " +\r
 502                                 "IsApproved = ?, LastLoginDate = ? WHERE UserId = ?";\r
 503                         OleDbCommand cmdUpdateMember = new OleDbCommand (queryUpdateMember, (OleDbConnection) connection);\r
 504                         AddParameter (cmdUpdateMember, "Email", email);\r
 505                         AddParameter (cmdUpdateMember, "LoweredEmail", email.ToLower ());\r
 506                         AddParameter (cmdUpdateMember, "Comment", comment);\r
 507                         AddParameter (cmdUpdateMember, "IsApproved", isApproved);\r
 508                         AddParameter (cmdUpdateMember, "LastLoginDate", lastLoginDate);\r
 509                         AddParameter (cmdUpdateMember, "UserId", userId);\r
 510                         cmdUpdateMember.ExecuteNonQuery ();\r
 511 \r
 512                         return 0;\r
 513                 }\r
 514 \r
 515                 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
 516                 {\r
 517                         string querySelect = "SELECT usr.UserId, mbr.IsApproved, mbr.IsLockedOut, mbr.LastLockoutDate, " +\r
 518                                                         "mbr.FailedPasswordAttemptCount, mbr.FailedPasswordAttemptWindowStart " +\r
 519                                                         "FROM aspnet_Applications app, aspnet_Users usr, aspnet_Membership mbr " +\r
 520                                                         "WHERE app.LoweredApplicationName = ? AND usr.ApplicationId = app.ApplicationId " +\r
 521                                                         "AND usr.UserId = mbr.UserId AND usr.LoweredUserName = ?";\r
 522                         OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);\r
 523                         AddParameter (cmdSelect, "LoweredApplicationName", applicationName.ToLower ());\r
 524                         AddParameter (cmdSelect, "LoweredUserName", username.ToLower ());\r
 525 \r
 526                         string dbUserId = string.Empty;\r
 527                         bool dbIsApproved = false;\r
 528                         bool dbLockedOut = false;\r
 529                         DateTime dbLastLockoutDate;\r
 530                         int dbFailedPasswordAttemptCount = 0;\r
 531                         DateTime dbFailedPasswordAttemptWindowStart;\r
 532 \r
 533                         using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {\r
 534                                 if (reader.Read ()) {\r
 535                                         dbUserId = reader.GetString (0);\r
 536                                         dbIsApproved = reader.GetInt32 (1) > 0;\r
 537                                         dbLockedOut = reader.GetInt32 (2) > 0;\r
 538                                         dbLastLockoutDate = reader.GetDateTime (3);\r
 539                                         dbFailedPasswordAttemptCount = reader.GetInt32 (4);\r
 540                                         dbFailedPasswordAttemptWindowStart = reader.GetDateTime (5);\r
 541                                 }\r
 542                                 else {\r
 543                                         return 1; // user not found\r
 544                                 }\r
 545                         }\r
 546 \r
 547                         if (dbLockedOut)\r
 548                                 return 2; // locked out\r
 549 \r
 550                         if (!isPasswordCorrect) {\r
 551                                 if (currentTimeUtc > dbFailedPasswordAttemptWindowStart.AddMinutes (passwordAttemptWindow)) {\r
 552                                         dbFailedPasswordAttemptWindowStart = currentTimeUtc;\r
 553                                         dbFailedPasswordAttemptCount = 1;\r
 554                                 }\r
 555                                 else {\r
 556                                         dbFailedPasswordAttemptWindowStart = currentTimeUtc;\r
 557                                         dbFailedPasswordAttemptCount++;\r
 558                                 }\r
 559 \r
 560                                 if (dbFailedPasswordAttemptCount > maxInvalidPasswordAttempts) {\r
 561                                         dbLockedOut = true;\r
 562                                         dbLastLockoutDate = currentTimeUtc;\r
 563                                 }\r
 564                         }\r
 565                         else {\r
 566                                 dbFailedPasswordAttemptCount = 0;\r
 567                                 dbFailedPasswordAttemptWindowStart = DefaultDateTime;\r
 568                         }\r
 569 \r
 570                         if (updateLastLoginActivityDate) {\r
 571                                 string queryUpdUserActivity = "UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";\r
 572                                 OleDbCommand cmdUpdUserActivity = new OleDbCommand (queryUpdUserActivity, (OleDbConnection) connection);\r
 573                                 AddParameter (cmdUpdUserActivity, "LastActivityDate", currentTimeUtc);\r
 574                                 AddParameter (cmdUpdUserActivity, "UserId", dbUserId);\r
 575                                 cmdUpdUserActivity.ExecuteNonQuery ();\r
 576 \r
 577                                 string queryUpdMemberActivity = "UPDATE aspnet_Membership SET LastLoginDate = ? WHERE UserId = ?";\r
 578                                 OleDbCommand cmdUpdMemberActivity = new OleDbCommand (queryUpdMemberActivity, (OleDbConnection) connection);\r
 579                                 AddParameter (cmdUpdMemberActivity, "LastLoginDate", currentTimeUtc);\r
 580                                 AddParameter (cmdUpdMemberActivity, "UserId", dbUserId);\r
 581                                 cmdUpdMemberActivity.ExecuteNonQuery ();\r
 582                         }\r
 583 \r
 584                         string queryUpdate = "UPDATE aspnet_Membership SET IsLockedOut = ?, LastLockoutDate = ?, " +\r
 585                                 "FailedPasswordAttemptCount = ?, FailedPasswordAttemptWindowStart = ? " +\r
 586                                 "WHERE UserId = ?";\r
 587                         OleDbCommand cmdUpdate = new OleDbCommand (queryUpdate, (OleDbConnection) connection);\r
 588                         AddParameter (cmdUpdate, "IsLockedOut", dbLockedOut);\r
 589                         AddParameter (cmdUpdate, "LastLockoutDate", dbLastLockoutDate);\r
 590                         AddParameter (cmdUpdate, "FailedPasswordAttemptCount", dbFailedPasswordAttemptCount);\r
 591                         AddParameter (cmdUpdate, "FailedPasswordAttemptWindowStart", dbFailedPasswordAttemptWindowStart);\r
 592                         AddParameter (cmdUpdate, "UserId", dbUserId);\r
 593                         cmdUpdate.ExecuteNonQuery ();\r
 594                         return 0;\r
 595                 }\r
 596 \r
 597                 public static int Users_CreateUser (DbConnection connection, DbTransaction trans, string applicationId, string username, bool isAnonymous, DateTime lastActivityDate, ref object userId)\r
 598                 {\r
 599                         string selectQuery = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName = ? AND ApplicationId = ?";\r
 600                         OleDbCommand selectCmd = new OleDbCommand (selectQuery, (OleDbConnection) connection);\r
 601                         AddParameter (selectCmd, "LoweredUserName", username.ToLower ());\r
 602                         AddParameter (selectCmd, "ApplicationId", applicationId);\r
 603                         if (trans != null)\r
 604                                 selectCmd.Transaction = (OleDbTransaction) trans;\r
 605 \r
 606                         string existingUserId = null;\r
 607                         using (OleDbDataReader reader = selectCmd.ExecuteReader ()) {\r
 608                                 if (reader.Read ())\r
 609                                         existingUserId = reader.GetString (0);\r
 610                         }\r
 611 \r
 612                         if (existingUserId != null && existingUserId.Length > 0) {\r
 613                                 userId = existingUserId;\r
 614                                 return 1; // user with such username and appid already exists\r
 615                         }\r
 616 \r
 617                         if (userId != null) {\r
 618                                 string querySelectUserId = "SELECT UserId FROM aspnet_Users WHERE UserId = ?";\r
 619                                 OleDbCommand cmdSelectUserId = new OleDbCommand (querySelectUserId, (OleDbConnection) connection);\r
 620                                 AddParameter (cmdSelectUserId, "UserId", userId);\r
 621                                 if (trans != null)\r
 622                                         cmdSelectUserId.Transaction = (OleDbTransaction) trans;\r
 623 \r
 624                                 using (OleDbDataReader reader = cmdSelectUserId.ExecuteReader ()) {\r
 625                                         if (reader.Read ())\r
 626                                                 return 2; // user with such userId already exists\r
 627                                 }\r
 628                         }\r
 629 \r
 630                         if (userId == null)\r
 631                                 userId = Guid.NewGuid ().ToString ();\r
 632 \r
 633                         string insertQuery = "INSERT INTO aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate) VALUES (?, ?, ?, ?, ?, ?)";\r
 634                         OleDbCommand insertCmd = new OleDbCommand (insertQuery, (OleDbConnection) connection);\r
 635                         AddParameter (insertCmd, "ApplicationId", applicationId);\r
 636                         AddParameter (insertCmd, "UserId", userId);\r
 637                         AddParameter (insertCmd, "UserName", username);\r
 638                         AddParameter (insertCmd, "LoweredUserName", username.ToLower ());\r
 639                         AddParameter (insertCmd, "IsAnonymous", isAnonymous);\r
 640                         AddParameter (insertCmd, "LastActivityDate", lastActivityDate);\r
 641                         if (trans != null)\r
 642                                 insertCmd.Transaction = (OleDbTransaction) trans;\r
 643 \r
 644                         insertCmd.ExecuteNonQuery ();\r
 645                         return 0;\r
 646                 }\r
 647 \r
 648                 public static int Users_DeleteUser (DbConnection connection, string applicationName, string username, int tablesToDeleteFrom, ref int numTablesDeletedFrom)\r
 649                 {\r
 650                         string userId = GetUserId (connection, applicationName, username);\r
 651                         if (userId == null)\r
 652                                 return 1; // user not found\r
 653 \r
 654                         numTablesDeletedFrom = 0;\r
 655                         OleDbTransaction trans = (OleDbTransaction) connection.BeginTransaction ();\r
 656 \r
 657                         try {\r
 658                                 if ((tablesToDeleteFrom & 1) == 1) {\r
 659                                         string queryDelete = "DELETE FROM aspnet_Membership WHERE UserId = ?";\r
 660                                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
 661                                         AddParameter (cmdDelete, "UserId", userId);\r
 662                                         cmdDelete.Transaction = trans;\r
 663                                         cmdDelete.ExecuteNonQuery ();\r
 664 \r
 665                                         numTablesDeletedFrom++;\r
 666                                 }\r
 667 \r
 668                                 if ((tablesToDeleteFrom & 2) == 2) {\r
 669                                         string queryDelete = "DELETE FROM aspnet_UsersInRoles WHERE UserId = ?";\r
 670                                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
 671                                         AddParameter (cmdDelete, "UserId", userId);\r
 672                                         cmdDelete.Transaction = trans;\r
 673                                         cmdDelete.ExecuteNonQuery ();\r
 674 \r
 675                                         numTablesDeletedFrom++;\r
 676                                 }\r
 677 \r
 678                                 if ((tablesToDeleteFrom & 4) == 4) {\r
 679                                         string queryDelete = "DELETE FROM aspnet_Profile WHERE UserId = ?";\r
 680                                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
 681                                         AddParameter (cmdDelete, "UserId", userId);\r
 682                                         cmdDelete.Transaction = trans;\r
 683                                         cmdDelete.ExecuteNonQuery ();\r
 684 \r
 685                                         numTablesDeletedFrom++;\r
 686                                 }\r
 687 \r
 688                                 if ((tablesToDeleteFrom & 8) == 8) {\r
 689                                         string queryDelete = "DELETE FROM aspnet_PersonalizationPerUser WHERE UserId = ?";\r
 690                                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
 691                                         AddParameter (cmdDelete, "UserId", userId);\r
 692                                         cmdDelete.Transaction = trans;\r
 693                                         cmdDelete.ExecuteNonQuery ();\r
 694 \r
 695                                         numTablesDeletedFrom++;\r
 696                                 }\r
 697 \r
 698                                 if ((tablesToDeleteFrom & 15) == 15) {\r
 699                                         string queryDelete = "DELETE FROM aspnet_Users WHERE UserId = ?";\r
 700                                         OleDbCommand cmdDelete = new OleDbCommand (queryDelete, (OleDbConnection) connection);\r
 701                                         AddParameter (cmdDelete, "UserId", userId);\r
 702                                         cmdDelete.Transaction = trans;\r
 703                                         cmdDelete.ExecuteNonQuery ();\r
 704 \r
 705                                         numTablesDeletedFrom++;\r
 706                                 }\r
 707 \r
 708                                 trans.Commit ();\r
 709                         }\r
 710                         catch (Exception e) {\r
 711                                 trans.Rollback ();\r
 712                                 throw e;\r
 713                         }\r
 714 \r
 715                         return 0;\r
 716                 }\r
 717 \r
 718                 private static string GetUserId (DbConnection connection, string applicationName, string username)\r
 719                 {\r
 720                         string selectQuery = "SELECT usr.UserId FROM aspnet_Membership mbr, aspnet_Users usr, aspnet_Applications app WHERE " +\r
 721                                 "usr.LoweredUserName = ? AND app.LoweredApplicationName = ? " +\r
 722                                 "AND app.ApplicationId = usr.app.ApplicationId " +\r
 723                                 "AND usr.UserId = mbr.UserId";\r
 724 \r
 725                         OleDbCommand selectCmd = new OleDbCommand (selectQuery, (OleDbConnection) connection);\r
 726                         AddParameter (selectCmd, "LoweredUserName", username.ToLower ());\r
 727                         AddParameter (selectCmd, "PasswordAnswer", applicationName.ToLower ());\r
 728 \r
 729                         using (OleDbDataReader reader = selectCmd.ExecuteReader ()) {\r
 730                                 if (reader.Read ())\r
 731                                         return reader.GetString (0);\r
 732                         }\r
 733 \r
 734                         return null; // user not found\r
 735                 }\r
 736         }\r
 737 }\r
 738 \r
 739 #endif