How to: Sample Membership Provider Implementation
Illustrates a complete ODBC-based membership provider.
The following code illustrates a complete ODBC-based membership provider.
Example
Imports System.Web.Security
Imports System.Configuration.Provider
Imports System.Collections.Specialized
Imports System
Imports System.Data
Imports System.Data.Odbc
Imports System.Configuration
Imports System.Diagnostics
Imports System.Web
Imports System.Globalization
Imports System.Security.Cryptography
Imports System.Text
Imports System.Web.Configuration
'' This provider works with the following schema for the table of user data.' ' CREATE TABLE Users' (' PKID Guid NOT NULL PRIMARY KEY,' Username Text (255) NOT NULL,' ApplicationName Text (255) NOT NULL,' Email Text (128) NOT NULL,' Comment Text (255),' Password Text (128) NOT NULL,' PasswordQuestion Text (255),' PasswordAnswer Text (255),' IsApproved YesNo, ' LastActivityDate DateTime,' LastLoginDate DateTime,' LastPasswordChangedDate DateTime,' CreationDate DateTime, ' IsOnLine YesNo,' IsLockedOut YesNo,' LastLockedOutDate DateTime,' FailedPasswordAttemptCount Integer,' FailedPasswordAttemptWindowStart DateTime,' FailedPasswordAnswerAttemptCount Integer,' FailedPasswordAnswerAttemptWindowStart DateTime' )' Namespace Samples.AspNet.Membership
PublicNotInheritableClass OdbcMembershipProvider
Inherits MembershipProvider
' ' Global generated password length, generic exception message, event log info. 'Private newPasswordLength AsInteger = 8
Private eventSource AsString = "OdbcMembershipProvider"Private eventLog AsString = "Application"Private exceptionMessage AsString = "An exception occurred. Please check the Event Log."Private connectionString AsString
' ' Used when determining encryption key values. 'Private machineKey As MachineKeySection
' ' If False, exceptions are thrown to the caller. If True, ' exceptions are written to the event log. 'Private pWriteExceptionsToEventLog AsBooleanPublicProperty WriteExceptionsToEventLog AsBooleanGetReturn pWriteExceptionsToEventLog
EndGetSet
pWriteExceptionsToEventLog = value
EndSetEndProperty
' ' System.Configuration.Provider.ProviderBase.Initialize Method 'PublicOverridesSub Initialize(name AsString, config As NameValueCollection)
' ' Initialize values from web.config. 'If config IsNothingThen _
ThrowNew ArgumentNullException("config")
If name IsNothingOrElse name.Length = 0 Then _
name = "OdbcMembershipProvider"IfString.IsNullOrEmpty(config("description")) Then
config.Remove("description")
config.Add("description", "Sample ODBC Membership provider")
EndIf
' Initialize the abstract base class.MyBase.Initialize(name, config)
pApplicationName = GetConfigValue(config("applicationName"), _
System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath)
pMaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config("maxInvalidPasswordAttempts"), "5"))
pPasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config("passwordAttemptWindow"), "10"))
pMinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config("minRequiredAlphaNumericCharacters"), "1"))
pMinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config("minRequiredPasswordLength"), "7"))
pPasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config("passwordStrengthRegularExpression"), ""))
pEnablePasswordReset = Convert.ToBoolean(GetConfigValue(config("enablePasswordReset"), "True"))
pEnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config("enablePasswordRetrieval"), "True"))
pRequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config("requiresQuestionAndAnswer"), "False"))
pRequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config("requiresUniqueEmail"), "True"))
pWriteExceptionsToEventLog = Convert.ToBoolean(GetConfigValue(config("writeExceptionsToEventLog"), "True"))
Dim temp_format AsString = config("passwordFormat")
If temp_format IsNothingThen
temp_format = "Hashed"EndIfSelectCase temp_format
Case"Hashed"
pPasswordFormat = MembershipPasswordFormat.Hashed
Case"Encrypted"
pPasswordFormat = MembershipPasswordFormat.Encrypted
Case"Clear"
pPasswordFormat = MembershipPasswordFormat.Clear
CaseElseThrowNew ProviderException("Password format not supported.")
EndSelect
' ' Initialize OdbcConnection. 'Dim ConnectionStringSettings As ConnectionStringSettings = _
ConfigurationManager.ConnectionStrings(config("connectionStringName"))
If ConnectionStringSettings IsNothingOrElse ConnectionStringSettings.ConnectionString.Trim() = ""ThenThrowNew ProviderException("Connection string cannot be blank.")
EndIf
connectionString = COnnectionStringSettings.ConnectionString
' Get encryption and decryption key information from the configuration.Dim cfg As System.Configuration.Configuration = _
WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath)
machineKey = CType(cfg.GetSection("system.web/machineKey"), MachineKeySection)
If machineKey.ValidationKey.Contains("AutoGenerate") Then _
If PasswordFormat <> MembershipPasswordFormat.Clear Then _
ThrowNew ProviderException("Hashed or Encrypted passwords " & _
"are not supported with auto-generated keys.")
EndSub
' ' A helper function to retrieve config values from the configuration file. 'PrivateFunction GetConfigValue(configValue AsString, defaultValue AsString) AsStringIfString.IsNullOrEmpty(configValue) Then _
Return defaultValue
Return configValue
EndFunction
' ' System.Web.Security.MembershipProvider properties. 'Private pApplicationName AsStringPrivate pEnablePasswordReset AsBooleanPrivate pEnablePasswordRetrieval AsBooleanPrivate pRequiresQuestionAndAnswer AsBooleanPrivate pRequiresUniqueEmail AsBooleanPrivate pMaxInvalidPasswordAttempts AsIntegerPrivate pPasswordAttemptWindow AsIntegerPrivate pPasswordFormat As MembershipPasswordFormat
PublicOverridesProperty ApplicationName AsStringGetReturn pApplicationName
EndGetSet
pApplicationName = value
EndSetEndPropertyPublicOverridesReadOnlyProperty EnablePasswordReset AsBooleanGetReturn pEnablePasswordReset
EndGetEndPropertyPublicOverridesReadOnlyProperty EnablePasswordRetrieval AsBooleanGetReturn pEnablePasswordRetrieval
EndGetEndPropertyPublicOverridesReadOnlyProperty RequiresQuestionAndAnswer AsBooleanGetReturn pRequiresQuestionAndAnswer
EndGetEndPropertyPublicOverridesReadOnlyProperty RequiresUniqueEmail AsBooleanGetReturn pRequiresUniqueEmail
EndGetEndPropertyPublicOverridesReadOnlyProperty MaxInvalidPasswordAttempts AsIntegerGetReturn pMaxInvalidPasswordAttempts
EndGetEndPropertyPublicOverridesReadOnlyProperty PasswordAttemptWindow AsIntegerGetReturn pPasswordAttemptWindow
EndGetEndPropertyPublicOverridesReadOnlyProperty PasswordFormat As MembershipPasswordFormat
GetReturn pPasswordFormat
EndGetEndPropertyPrivate pMinRequiredNonAlphanumericCharacters AsIntegerPublicOverridesReadOnlyProperty MinRequiredNonAlphanumericCharacters() AsIntegerGetReturn pMinRequiredNonAlphanumericCharacters
EndGetEndPropertyPrivate pMinRequiredPasswordLength AsIntegerPublicOverridesReadOnlyProperty MinRequiredPasswordLength() AsIntegerGetReturn pMinRequiredPasswordLength
EndGetEndPropertyPrivate pPasswordStrengthRegularExpression AsStringPublicOverridesReadOnlyProperty PasswordStrengthRegularExpression() AsStringGetReturn pPasswordStrengthRegularExpression
EndGetEndProperty
' ' System.Web.Security.MembershipProvider methods. '
' ' MembershipProvider.ChangePassword 'PublicOverridesFunction ChangePassword(username AsString, _
oldPwd AsString, _
newPwd AsString) AsBooleanIfNot ValidateUser(username, oldPwd) Then _
ReturnFalseDim args As ValidatePasswordEventArgs = _
New ValidatePasswordEventArgs(username, newPwd, True)
OnValidatingPassword(args)
If args.Cancel ThenIfNot args.FailureInformation IsNothingThenThrow args.FailureInformation
ElseThrowNew ProviderException("Change password canceled due to New password validation failure.")
EndIfEndIfDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("UPDATE Users " & _
" SET Password = ?, LastPasswordChangedDate = ? " & _
" WHERE Username = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(newPwd)
cmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = DateTime.Now
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim rowsAffected AsInteger = 0
Try
conn.Open()
rowsAffected = cmd.ExecuteNonQuery()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "ChangePassword")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryIf rowsAffected > 0 ThenReturnTrueEndIfReturnFalseEndFunction
' ' MembershipProvider.ChangePasswordQuestionAndAnswer 'PublicOverridesFunction ChangePasswordQuestionAndAnswer(username AsString, _
password AsString, _
newPwdQuestion AsString, _
newPwdAnswer AsString) AsBooleanIfNot ValidateUser(username, password) Then _
ReturnFalseDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("UPDATE Users " & _
" SET PasswordQuestion = ?, PasswordAnswer = ?" & _
" WHERE Username = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Question", OdbcType.VarChar, 255).Value = newPwdQuestion
cmd.Parameters.Add("@Answer", OdbcType.VarChar, 255).Value = EncodePassword(newPwdAnswer)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim rowsAffected AsInteger = 0
Try
conn.Open()
rowsAffected = cmd.ExecuteNonQuery()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "ChangePasswordQuestionAndAnswer")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryIf rowsAffected > 0 ThenReturnTrueEndIfReturnFalseEndFunction
' ' MembershipProvider.CreateUser 'PublicOverridesFunction CreateUser(ByVal username AsString, _
ByVal password AsString, _
ByVal email AsString, _
ByVal passwordQuestion AsString, _
ByVal passwordAnswer AsString, _
ByVal isApproved AsBoolean, _
ByVal providerUserKey AsObject, _
ByRef status As MembershipCreateStatus) As MembershipUser
Dim Args As ValidatePasswordEventArgs = _
New ValidatePasswordEventArgs(username, password, True)
OnValidatingPassword(args)
If args.Cancel Then
status = MembershipCreateStatus.InvalidPassword
ReturnNothingEndIfIf RequiresUniqueEmail AndAlso GetUserNameByEmail(email) <> ""Then
status = MembershipCreateStatus.DuplicateEmail
ReturnNothingEndIfDim u As MembershipUser = GetUser(username, False)
If u IsNothingThenDim createDate As DateTime = DateTime.Now
If providerUserKey IsNothingThen
providerUserKey = Guid.NewGuid()
ElseIfNotTypeOf providerUserKey Is Guid Then
status = MembershipCreateStatus.InvalidProviderUserKey
ReturnNothingEndIfEndIfDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("INSERT INTO Users " & _
" (PKID, Username, Password, Email, PasswordQuestion, " & _
" PasswordAnswer, IsApproved," & _
" Comment, CreationDate, LastPasswordChangedDate, LastActivityDate," & _
" ApplicationName, IsLockedOut, LastLockedOutDate," & _
" FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, " & _
" FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart)" & _
" Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", conn)
cmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(password)
cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = email
cmd.Parameters.Add("@PasswordQuestion", OdbcType.VarChar, 255).Value = passwordQuestion
cmd.Parameters.Add("@PasswordAnswer", OdbcType.VarChar, 255).Value = EncodePassword(passwordAnswer)
cmd.Parameters.Add("@IsApproved", OdbcType.Bit).Value = isApproved
cmd.Parameters.Add("@Comment", OdbcType.VarChar, 255).Value = ""
cmd.Parameters.Add("@CreationDate", OdbcType.DateTime).Value = createDate
cmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = createDate
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = createDate
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
cmd.Parameters.Add("@IsLockedOut", OdbcType.Bit).Value = False
cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = createDate
cmd.Parameters.Add("@FailedPasswordAttemptCount", OdbcType.Int).Value = 0
cmd.Parameters.Add("@FailedPasswordAttemptWindowStart", OdbcType.DateTime).Value = createDate
cmd.Parameters.Add("@FailedPasswordAnswerAttemptCount", OdbcType.Int).Value = 0
cmd.Parameters.Add("@FailedPasswordAnswerAttemptWindowStart", OdbcType.DateTime).Value = createDate
Try
conn.Open()
Dim recAdded AsInteger = cmd.ExecuteNonQuery()
If recAdded > 0 Then
status = MembershipCreateStatus.Success
Else
status = MembershipCreateStatus.UserRejected
EndIfCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "CreateUser")
EndIf
status = MembershipCreateStatus.ProviderError
Finally
conn.Close()
EndTryReturn GetUser(username, False)
Else
status = MembershipCreateStatus.DuplicateUserName
EndIfReturnNothingEndFunction
' ' MembershipProvider.DeleteUser 'PublicOverridesFunction DeleteUser(username AsString, _
deleteAllRelatedData AsBoolean) AsBooleanDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("DELETE FROM Users " & _
" WHERE Username = ? AND Applicationname = ?", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim rowsAffected AsInteger = 0
Try
conn.Open()
rowsAffected = cmd.ExecuteNonQuery()
If deleteAllRelatedData Then ' Process commands to delete all data for the user in the database.EndIfCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "DeleteUser")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryIf rowsAffected > 0 Then _
ReturnTrueReturnFalseEndFunction
' ' MembershipProvider.GetAllUsers 'PublicOverridesFunction GetAllUsers(ByVal pageIndex AsInteger, _
ByVal pageSize AsInteger, _
ByRef totalRecords AsInteger) _
As MembershipUserCollection
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Count(*) FROM Users " & _
"WHERE ApplicationName = ?", conn)
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Dim users As MembershipUserCollection = New MembershipUserCollection()
Dim reader As OdbcDataReader = Nothing
totalRecords = 0
Try
conn.Open()
totalRecords = CInt(cmd.ExecuteScalar())
If totalRecords <= 0 ThenReturn users
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," & _
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " & _
" FROM Users " & _
" WHERE ApplicationName = ? " & _
" ORDER BY Username Asc"
reader = cmd.ExecuteReader()
Dim counter AsInteger = 0
Dim startIndex AsInteger = pageSize * pageIndex
Dim endIndex AsInteger = startIndex + pageSize - 1
DoWhile reader.Read()
If counter >= startIndex ThenDim u As MembershipUser = GetUserFromReader(reader)
users.Add(u)
EndIfIf counter >= endIndex Then cmd.Cancel()
counter += 1
LoopCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetAllUsers")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryReturn users
EndFunction
' ' MembershipProvider.GetNumberOfUsersOnline 'PublicOverridesFunction GetNumberOfUsersOnline() AsIntegerDim onlineSpan As TimeSpan = New TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0)
Dim compareTime As DateTime = DateTime.Now.Subtract(onlineSpan)
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Count(*) FROM Users " & _
" WHERE LastActivityDate > ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@CompareDate", OdbcType.DateTime).Value = compareTime
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim numOnline AsInteger = 0
Try
conn.Open()
numOnline = CInt(cmd.ExecuteScalar())
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetNumberOfUsersOnline")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryReturn numOnline
EndFunction
' ' MembershipProvider.GetPassword 'PublicOverridesFunction GetPassword(username AsString, answer AsString) AsStringIfNot EnablePasswordRetrieval ThenThrowNew ProviderException("Password Retrieval Not Enabled.")
EndIfIf PasswordFormat = MembershipPasswordFormat.Hashed ThenThrowNew ProviderException("Cannot retrieve Hashed passwords.")
EndIfDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Password, PasswordAnswer, IsLockedOut FROM Users " & _
" WHERE Username = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim password AsString = ""Dim passwordAnswer AsString = ""Dim reader As OdbcDataReader = NothingTry
conn.Open()
reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
If reader.HasRows Then
reader.Read()
If reader.GetBoolean(2) Then _
ThrowNew MembershipPasswordException("The supplied user is locked out.")
password = reader.GetString(0)
passwordAnswer = reader.GetString(1)
ElseThrowNew MembershipPasswordException("The supplied user name is not found.")
EndIfCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetPassword")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryIf RequiresQuestionAndAnswer AndAlsoNot CheckPassword(answer, passwordAnswer) Then
UpdateFailureCount(username, "passwordAnswer")
ThrowNew MembershipPasswordException("Incorrect password answer.")
EndIfIf PasswordFormat = MembershipPasswordFormat.Encrypted Then
password = UnEncodePassword(password)
EndIfReturn password
EndFunction
' ' MembershipProvider.GetUser(String, Boolean) 'PublicOverridesFunction GetUser(ByVal username AsString, _
ByVal userIsOnline AsBoolean) As MembershipUser
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT PKID, Username, Email, PasswordQuestion," & _
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate" & _
" FROM Users WHERE Username = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim u As MembershipUser = NothingDim reader As OdbcDataReader = NothingTry
conn.Open()
reader = cmd.ExecuteReader()
If reader.HasRows Then
reader.Read()
u = GetUserFromReader(reader)
If userIsOnline ThenDim updateCmd As OdbcCommand = New OdbcCommand("UPDATE Users " & _
"SET LastActivityDate = ? " & _
"WHERE Username = ? AND Applicationname = ?", conn)
updateCmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now
updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
updateCmd.ExecuteNonQuery()
EndIfEndIfCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetUser(String, Boolean)")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryReturn u
EndFunction
' ' MembershipProvider.GetUser(Object, Boolean) 'PublicOverridesFunction GetUser(ByVal providerUserKey AsObject, _
ByVal userIsOnline AsBoolean) As MembershipUser
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT PKID, Username, Email, PasswordQuestion," & _
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate" & _
" FROM Users WHERE PKID = ?", conn)
cmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey
Dim u As MembershipUser = NothingDim reader As OdbcDataReader = NothingTry
conn.Open()
reader = cmd.ExecuteReader()
If reader.HasRows Then
reader.Read()
u = GetUserFromReader(reader)
If userIsOnline ThenDim updateCmd As OdbcCommand = New OdbcCommand("UPDATE Users " & _
"SET LastActivityDate = ? " & _
"WHERE PKID = ?", conn)
updateCmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now
updateCmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey
updateCmd.ExecuteNonQuery()
EndIfEndIfCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetUser(Object, Boolean)")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryReturn u
EndFunction
' ' GetUserFromReader ' A helper function that takes the current row from the OdbcDataReader ' and hydrates a MembershiUser from the values. Called by the ' MembershipUser.GetUser implementation. 'PrivateFunction GetUserFromReader(ByVal reader As OdbcDataReader) As MembershipUser
Dim providerUserKey AsObject = reader.GetValue(0)
Dim username AsString = reader.GetString(1)
Dim email AsString = reader.GetString(2)
Dim passwordQuestion AsString = ""IfNot reader.GetValue(3) Is DBNull.Value Then _
passwordQuestion = reader.GetString(3)
Dim comment AsString = ""IfNot reader.GetValue(4) Is DBNull.Value Then _
comment = reader.GetString(4)
Dim isApproved AsBoolean = reader.GetBoolean(5)
Dim isLockedOut AsBoolean = reader.GetBoolean(6)
Dim creationDate As DateTime = reader.GetDateTime(7)
Dim lastLoginDate As DateTime = New DateTime()
IfNot reader.GetValue(8) Is DBNull.Value Then _
lastLoginDate = reader.GetDateTime(8)
Dim lastActivityDate As DateTime = reader.GetDateTime(9)
Dim lastPasswordChangedDate As DateTime = reader.GetDateTime(10)
Dim lastLockedOutDate As DateTime = New DateTime()
IfNot reader.GetValue(11) Is DBNull.Value Then _
lastLockedOutDate = reader.GetDateTime(11)
Dim u As MembershipUser = New MembershipUser(Me.Name, _
username, _
providerUserKey, _
email, _
passwordQuestion, _
comment, _
isApproved, _
isLockedOut, _
creationDate, _
lastLoginDate, _
lastActivityDate, _
lastPasswordChangedDate, _
lastLockedOutDate)
Return u
EndFunction
' ' MembershipProvider.UnlockUser 'PublicOverridesFunction UnlockUser(ByVal username AsString) AsBooleanDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("UPDATE Users " & _
" SET IsLockedOut = False, LastLockedOutDate = ? " & _
" WHERE Username = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = DateTime.Now
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim rowsAffected AsInteger = 0
Try
conn.Open()
rowsAffected = cmd.ExecuteNonQuery()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "UnlockUser")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryIf rowsAffected > 0 Then _
ReturnTrueReturnFalseEndFunction
' ' MembershipProvider.GetUserNameByEmail 'PublicOverridesFunction GetUserNameByEmail(email AsString) AsStringDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Username" & _
" FROM Users WHERE Email = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = email
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim username AsString = ""Try
conn.Open()
username = cmd.ExecuteScalar().ToString()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetUserNameByEmail")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryIf username IsNothingThen _
username = ""Return username
EndFunction
' ' MembershipProvider.ResetPassword 'PublicOverridesFunction ResetPassword(ByVal username AsString, ByVal answer AsString) AsStringIfNot EnablePasswordReset ThenThrowNew NotSupportedException("Password Reset is not enabled.")
EndIfIf answer IsNothingAndAlso RequiresQuestionAndAnswer Then
UpdateFailureCount(username, "passwordAnswer")
ThrowNew ProviderException("Password answer required for password Reset.")
EndIfDim newPassword AsString = _
System.Web.Security.Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters)
Dim Args As ValidatePasswordEventArgs = _
New ValidatePasswordEventArgs(username, newPassword, True)
OnValidatingPassword(args)
If args.Cancel ThenIfNot args.FailureInformation IsNothingThenThrow args.FailureInformation
ElseThrowNew MembershipPasswordException("Reset password canceled due to password validation failure.")
EndIfEndIfDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT PasswordAnswer, IsLockedOut FROM Users " & _
" WHERE Username = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim rowsAffected AsInteger = 0
Dim passwordAnswer AsString = ""Dim reader As OdbcDataReader = NothingTry
conn.Open()
reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
If reader.HasRows Then
reader.Read()
If reader.GetBoolean(1) Then _
ThrowNew MembershipPasswordException("The supplied user is locked out.")
passwordAnswer = reader.GetString(0)
ElseThrowNew MembershipPasswordException("The supplied user name is not found.")
EndIfIf RequiresQuestionAndAnswer AndAlsoNot CheckPassword(answer, passwordAnswer) Then
UpdateFailureCount(username, "passwordAnswer")
ThrowNew MembershipPasswordException("Incorrect password answer.")
EndIfDim updateCmd As OdbcCommand = New OdbcCommand("UPDATE Users " & _
" SET Password = ?, LastPasswordChangedDate = ?" & _
" WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False", conn)
updateCmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(newPassword)
updateCmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = DateTime.Now
updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
rowsAffected = updateCmd.ExecuteNonQuery()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "ResetPassword")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryIf rowsAffected > 0 ThenReturn newPassword
ElseThrowNew MembershipPasswordException("User not found, or user is locked out. Password not Reset.")
EndIfEndFunction
' ' MembershipProvider.UpdateUser 'PublicOverridesSub UpdateUser(ByVal user As MembershipUser)
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("UPDATE Users " & _
" SET Email = ?, Comment = ?," & _
" IsApproved = ?" & _
" WHERE Username = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = user.Email
cmd.Parameters.Add("@Comment", OdbcType.VarChar, 255).Value = user.Comment
cmd.Parameters.Add("@IsApproved", OdbcType.Bit).Value = user.IsApproved
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = user.UserName
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Try
conn.Open()
cmd.ExecuteNonQuery()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "UpdateUser")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryEndSub
' ' MembershipProvider.ValidateUser 'PublicOverridesFunction ValidateUser(username AsString, password AsString) AsBooleanDim isValid AsBoolean = FalseDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Password, IsApproved FROM Users " & _
" WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim reader As OdbcDataReader = NothingDim isApproved AsBoolean = FalseDim pwd AsString = ""Try
conn.Open()
reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
If reader.HasRows Then
reader.Read()
pwd = reader.GetString(0)
isApproved = reader.GetBoolean(1)
ElseReturnFalseEndIf
reader.Close()
If CheckPassword(password, pwd) ThenIf isApproved Then
isValid = TrueDim updateCmd As OdbcCommand = New OdbcCommand("UPDATE Users SET LastLoginDate = ?" & _
" WHERE Username = ? AND ApplicationName = ?", conn)
updateCmd.Parameters.Add("@LastLoginDate", OdbcType.DateTime).Value = DateTime.Now
updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
updateCmd.ExecuteNonQuery()
EndIfElse
conn.Close()
UpdateFailureCount(username, "password")
EndIfCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "ValidateUser")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryReturn isValid
EndFunction
' ' UpdateFailureCount ' A helper method that performs the checks and updates associated with ' password failure tracking. 'PrivateSub UpdateFailureCount(username AsString, failureType AsString)
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT FailedPasswordAttemptCount, " & _
" FailedPasswordAttemptWindowStart, " & _
" FailedPasswordAnswerAttemptCount, " & _
" FailedPasswordAnswerAttemptWindowStart " & _
" FROM Users " & _
" WHERE Username = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim reader As OdbcDataReader = NothingDim windowStart As DateTime = New DateTime()
Dim failureCount AsInteger = 0
Try
conn.Open()
reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
If reader.HasRows Then
reader.Read()
If failureType = "password"Then
failureCount = reader.GetInt32(0)
windowStart = reader.GetDateTime(1)
EndIfIf failureType = "passwordAnswer"Then
failureCount = reader.GetInt32(2)
windowStart = reader.GetDateTime(3)
EndIfEndIf
reader.Close()
Dim windowEnd As DateTime = windowStart.AddMinutes(PasswordAttemptWindow)
If failureCount = 0 OrElse DateTime.Now > windowEnd Then ' First password failure or outside of PasswordAttemptWindow. ' Start a New password failure count from 1 and a New window starting now.If failureType = "password"Then _
cmd.CommandText = "UPDATE Users " & _
" SET FailedPasswordAttemptCount = ?, " & _
" FailedPasswordAttemptWindowStart = ? " & _
" WHERE Username = ? AND ApplicationName = ?"If failureType = "passwordAnswer"Then _
cmd.CommandText = "UPDATE Users " & _
" SET FailedPasswordAnswerAttemptCount = ?, " & _
" FailedPasswordAnswerAttemptWindowStart = ? " & _
" WHERE Username = ? AND ApplicationName = ?"
cmd.Parameters.Clear()
cmd.Parameters.Add("@Count", OdbcType.Int).Value = 1
cmd.Parameters.Add("@WindowStart", OdbcType.DateTime).Value = DateTime.Now
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
If cmd.ExecuteNonQuery() < 0 Then _
ThrowNew ProviderException("Unable to update failure count and window start.")
Else
failureCount += 1
If failureCount >= MaxInvalidPasswordAttempts Then ' Password attempts have exceeded the failure threshold. Lock out ' the user.
cmd.CommandText = "UPDATE Users " & _
" SET IsLockedOut = ?, LastLockedOutDate = ? " & _
" WHERE Username = ? AND ApplicationName = ?"
cmd.Parameters.Clear()
cmd.Parameters.Add("@IsLockedOut", OdbcType.Bit).Value = True
cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = DateTime.Now
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
If cmd.ExecuteNonQuery() < 0 Then _
ThrowNew ProviderException("Unable to lock out user.")
Else ' Password attempts have not exceeded the failure threshold. Update ' the failure counts. Leave the window the same.If failureType = "password"Then _
cmd.CommandText = "UPDATE Users " & _
" SET FailedPasswordAttemptCount = ?" & _
" WHERE Username = ? AND ApplicationName = ?"If failureType = "passwordAnswer"Then _
cmd.CommandText = "UPDATE Users " & _
" SET FailedPasswordAnswerAttemptCount = ?" & _
" WHERE Username = ? AND ApplicationName = ?"
cmd.Parameters.Clear()
cmd.Parameters.Add("@Count", OdbcType.Int).Value = failureCount
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
If cmd.ExecuteNonQuery() < 0 Then _
ThrowNew ProviderException("Unable to update failure count.")
EndIfEndIfCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "UpdateFailureCount")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryEndSub
' ' CheckPassword ' Compares password values based on the MembershipPasswordFormat. 'PrivateFunction CheckPassword(password AsString, dbpassword AsString) AsBooleanDim pass1 AsString = password
Dim pass2 AsString = dbpassword
SelectCase PasswordFormat
Case MembershipPasswordFormat.Encrypted
pass2 = UnEncodePassword(dbpassword)
Case MembershipPasswordFormat.Hashed
pass1 = EncodePassword(password)
CaseElseEndSelectIf pass1 = pass2 ThenReturnTrueEndIfReturnFalseEndFunction
' ' EncodePassword ' Encrypts, Hashes, or leaves the password clear based on the PasswordFormat. 'PrivateFunction EncodePassword(password AsString) AsStringDim encodedPassword AsString = password
SelectCase PasswordFormat
Case MembershipPasswordFormat.Clear
Case MembershipPasswordFormat.Encrypted
encodedPassword = _
Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)))
Case MembershipPasswordFormat.Hashed
Dim hash As HMACSHA1 = New HMACSHA1()
hash.Key = HexToByte(machineKey.ValidationKey)
encodedPassword = _
Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)))
CaseElseThrowNew ProviderException("Unsupported password format.")
EndSelectReturn encodedPassword
EndFunction
' ' UnEncodePassword ' Decrypts or leaves the password clear based on the PasswordFormat. 'PrivateFunction UnEncodePassword(encodedPassword AsString) AsStringDim password AsString = encodedPassword
SelectCase PasswordFormat
Case MembershipPasswordFormat.Clear
Case MembershipPasswordFormat.Encrypted
password = _
Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)))
Case MembershipPasswordFormat.Hashed
ThrowNew ProviderException("Cannot unencode a hashed password.")
CaseElse
throw new ProviderException("Unsupported password format.")
EndSelectReturn password
EndFunction
' ' HexToByte ' Converts a hexadecimal string to a byte array. Used to convert encryption ' key values from the configuration. 'PrivateFunction HexToByte(hexString AsString) AsByte()
Dim ReturnBytes((hexString.Length \ 2) - 1) AsByteFor i AsInteger = 0 To ReturnBytes.Length - 1
ReturnBytes(i) = Convert.ToByte(hexString.Substring(i*2, 2), 16)
NextReturn ReturnBytes
EndFunction
' ' MembershipProvider.FindUsersByName 'PublicOverridesFunction FindUsersByName(usernameToMatch AsString, _
pageIndex AsInteger, _
pageSize AsInteger, _
ByRef totalRecords AsInteger) _
As MembershipUserCollection
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Count(*) FROM Users " & _
"WHERE Username LIKE ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@UsernameSearch", OdbcType.VarChar, 255).Value = usernameToMatch
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim users As MembershipUserCollection = New MembershipUserCollection()
Dim reader As OdbcDataReader = NothingTry
conn.Open()
totalRecords = CInt(cmd.ExecuteScalar())
If totalRecords <= 0 ThenReturn users
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," & _
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " & _
" FROM Users " & _
" WHERE Username LIKE ? AND ApplicationName = ? " & _
" ORDER BY Username Asc"
reader = cmd.ExecuteReader()
Dim counter AsInteger = 0
Dim startIndex AsInteger = pageSize * pageIndex
Dim endIndex AsInteger = startIndex + pageSize - 1
DoWhile reader.Read()
If counter >= startIndex ThenDim u As MembershipUser = GetUserFromReader(reader)
users.Add(u)
EndIfIf counter >= endIndex Then cmd.Cancel()
counter += 1
LoopCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "FindUsersByName")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryReturn users
EndFunction
' ' MembershipProvider.FindUsersByEmail 'PublicOverridesFunction FindUsersByEmail(emailToMatch AsString, _
pageIndex AsInteger, _
pageSize AsInteger, _
ByRef totalRecords AsInteger) _
As MembershipUserCollection
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Count(*) FROM Users " & _
"WHERE Email LIKE ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@EmailSearch", OdbcType.VarChar, 255).Value = emailToMatch
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Dim users As MembershipUserCollection = New MembershipUserCollection()
Dim reader As OdbcDataReader = Nothing
totalRecords = 0
Try
conn.Open()
totalRecords = CInt(cmd.ExecuteScalar())
If totalRecords <= 0 ThenReturn users
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," & _
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " & _
" FROM Users " & _
" WHERE Email LIKE ? AND ApplicationName = ? " & _
" ORDER BY Username Asc"
reader = cmd.ExecuteReader()
Dim counter AsInteger = 0
Dim startIndex AsInteger = pageSize * pageIndex
Dim endIndex AsInteger = startIndex + pageSize - 1
DoWhile reader.Read()
If counter >= startIndex ThenDim u As MembershipUser = GetUserFromReader(reader)
users.Add(u)
EndIfIf counter >= endIndex Then cmd.Cancel()
counter += 1
LoopCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "FindUsersByEmail")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryReturn users
EndFunction
' ' WriteToEventLog ' A helper function that writes exception detail to the event log. Exceptions ' are written to the event log as a security measure to aSub Private database ' details from being Returned to the browser. If a method does not Return a status ' or boolean indicating the action succeeded or failed, a generic exception is also ' Thrown by the caller. 'PrivateSub WriteToEventLog(e As Exception, action AsString)
Dim log As EventLog = New EventLog()
log.Source = eventSource
log.Log = eventLog
Dim message AsString = "An exception occurred communicating with the data source." & vbCrLf & vbCrLf
message &= "Action: " & action & vbCrLf & vbCrLf
message &= "Exception: " & e.ToString()
log.WriteEnTry(message)
EndSubEndClassEndNamespace
using System.Web.Security;
using System.Configuration.Provider;
using System.Collections.Specialized;
using System;
using System.Data;
using System.Data.Odbc;
using System.Configuration;
using System.Diagnostics;
using System.Web;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using System.Web.Configuration;
/*
This provider works with the following schema for the table of user data.
CREATE TABLE Users
(
PKID Guid NOT NULL PRIMARY KEY,
Username Text (255) NOT NULL,
ApplicationName Text (255) NOT NULL,
Email Text (128) NOT NULL,
Comment Text (255),
Password Text (128) NOT NULL,
PasswordQuestion Text (255),
PasswordAnswer Text (255),
IsApproved YesNo,
LastActivityDate DateTime,
LastLoginDate DateTime,
LastPasswordChangedDate DateTime,
CreationDate DateTime,
IsOnLine YesNo,
IsLockedOut YesNo,
LastLockedOutDate DateTime,
FailedPasswordAttemptCount Integer,
FailedPasswordAttemptWindowStart DateTime,
FailedPasswordAnswerAttemptCount Integer,
FailedPasswordAnswerAttemptWindowStart DateTime
)
*/namespace Samples.AspNet.Membership
{
publicsealedclass OdbcMembershipProvider: MembershipProvider
{
//// Global connection string, generated password length, generic exception message, event log info.//privateint newPasswordLength = 8;
privatestring eventSource = "OdbcMembershipProvider";
privatestring eventLog = "Application";
privatestring exceptionMessage = "An exception occurred. Please check the Event Log.";
privatestring connectionString;
//// Used when determining encryption key values.//private MachineKeySection machineKey;
//// If false, exceptions are thrown to the caller. If true,// exceptions are written to the event log.//privatebool pWriteExceptionsToEventLog;
publicbool WriteExceptionsToEventLog
{
get { return pWriteExceptionsToEventLog; }
set { pWriteExceptionsToEventLog = value; }
}
//// System.Configuration.Provider.ProviderBase.Initialize Method//publicoverridevoid Initialize(string name, NameValueCollection config)
{
//// Initialize values from web.config.//if (config == null)
thrownew ArgumentNullException("config");
if (name == null || name.Length == 0)
name = "OdbcMembershipProvider";
if (String.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "Sample ODBC Membership provider");
}
// Initialize the abstract base class.base.Initialize(name, config);
pApplicationName = GetConfigValue(config["applicationName"],
System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
pMaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
pPasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
pMinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"));
pMinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
pPasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""));
pEnablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
pEnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
pRequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
pRequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
pWriteExceptionsToEventLog = Convert.ToBoolean(GetConfigValue(config["writeExceptionsToEventLog"], "true"));
string temp_format = config["passwordFormat"];
if (temp_format == null)
{
temp_format = "Hashed";
}
switch (temp_format)
{
case"Hashed":
pPasswordFormat = MembershipPasswordFormat.Hashed;
break;
case"Encrypted":
pPasswordFormat = MembershipPasswordFormat.Encrypted;
break;
case"Clear":
pPasswordFormat = MembershipPasswordFormat.Clear;
break;
default:
thrownew ProviderException("Password format not supported.");
}
//// Initialize OdbcConnection.//
ConnectionStringSettings ConnectionStringSettings =
ConfigurationManager.ConnectionStrings[config["connectionStringName"]];
if (ConnectionStringSettings == null || ConnectionStringSettings.ConnectionString.Trim() == "")
{
thrownew ProviderException("Connection string cannot be blank.");
}
connectionString = ConnectionStringSettings.ConnectionString;
// Get encryption and decryption key information from the configuration.
Configuration cfg =
WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
machineKey = (MachineKeySection)cfg.GetSection("system.web/machineKey");
if (machineKey.ValidationKey.Contains("AutoGenerate"))
if (PasswordFormat != MembershipPasswordFormat.Clear)
thrownew ProviderException("Hashed or Encrypted passwords " +
"are not supported with auto-generated keys.");
}
//// A helper function to retrieve config values from the configuration file.//privatestring GetConfigValue(string configValue, string defaultValue)
{
if (String.IsNullOrEmpty(configValue))
return defaultValue;
return configValue;
}
//// System.Web.Security.MembershipProvider properties.//privatestring pApplicationName;
privatebool pEnablePasswordReset;
privatebool pEnablePasswordRetrieval;
privatebool pRequiresQuestionAndAnswer;
privatebool pRequiresUniqueEmail;
privateint pMaxInvalidPasswordAttempts;
privateint pPasswordAttemptWindow;
private MembershipPasswordFormat pPasswordFormat;
publicoverridestring ApplicationName
{
get { return pApplicationName; }
set { pApplicationName = value; }
}
publicoverridebool EnablePasswordReset
{
get { return pEnablePasswordReset; }
}
publicoverridebool EnablePasswordRetrieval
{
get { return pEnablePasswordRetrieval; }
}
publicoverridebool RequiresQuestionAndAnswer
{
get { return pRequiresQuestionAndAnswer; }
}
publicoverridebool RequiresUniqueEmail
{
get { return pRequiresUniqueEmail; }
}
publicoverrideint MaxInvalidPasswordAttempts
{
get { return pMaxInvalidPasswordAttempts; }
}
publicoverrideint PasswordAttemptWindow
{
get { return pPasswordAttemptWindow; }
}
publicoverride MembershipPasswordFormat PasswordFormat
{
get { return pPasswordFormat; }
}
privateint pMinRequiredNonAlphanumericCharacters;
publicoverrideint MinRequiredNonAlphanumericCharacters
{
get { return pMinRequiredNonAlphanumericCharacters; }
}
privateint pMinRequiredPasswordLength;
publicoverrideint MinRequiredPasswordLength
{
get { return pMinRequiredPasswordLength; }
}
privatestring pPasswordStrengthRegularExpression;
publicoverridestring PasswordStrengthRegularExpression
{
get { return pPasswordStrengthRegularExpression; }
}
//// System.Web.Security.MembershipProvider methods.////// MembershipProvider.ChangePassword//publicoverridebool ChangePassword(string username, string oldPwd, string newPwd)
{
if (!ValidateUser(username, oldPwd))
returnfalse;
ValidatePasswordEventArgs args =
new ValidatePasswordEventArgs(username, newPwd, true);
OnValidatingPassword(args);
if (args.Cancel)
if (args.FailureInformation != null)
throw args.FailureInformation;
elsethrownew MembershipPasswordException("Change password canceled due to new password validation failure.");
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("UPDATE Users " +
" SET Password = ?, LastPasswordChangedDate = ? " +
" WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(newPwd);
cmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
int rowsAffected = 0;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "ChangePassword");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
if (rowsAffected > 0)
{
returntrue;
}
returnfalse;
}
//// MembershipProvider.ChangePasswordQuestionAndAnswer//publicoverridebool ChangePasswordQuestionAndAnswer(string username,
string password,
string newPwdQuestion,
string newPwdAnswer)
{
if (!ValidateUser(username, password))
returnfalse;
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("UPDATE Users " +
" SET PasswordQuestion = ?, PasswordAnswer = ?" +
" WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Question", OdbcType.VarChar, 255).Value = newPwdQuestion;
cmd.Parameters.Add("@Answer", OdbcType.VarChar, 255).Value = EncodePassword(newPwdAnswer);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
int rowsAffected = 0;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "ChangePasswordQuestionAndAnswer");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
if (rowsAffected > 0)
{
returntrue;
}
returnfalse;
}
//// MembershipProvider.CreateUser//publicoverride MembershipUser CreateUser(string username,
string password,
string email,
string passwordQuestion,
string passwordAnswer,
bool isApproved,
object providerUserKey,
out MembershipCreateStatus status)
{
ValidatePasswordEventArgs args =
new ValidatePasswordEventArgs(username, password, true);
OnValidatingPassword(args);
if (args.Cancel)
{
status = MembershipCreateStatus.InvalidPassword;
returnnull;
}
if (RequiresUniqueEmail && GetUserNameByEmail(email) != "")
{
status = MembershipCreateStatus.DuplicateEmail;
returnnull;
}
MembershipUser u = GetUser(username, false);
if (u == null)
{
DateTime createDate = DateTime.Now;
if (providerUserKey == null)
{
providerUserKey = Guid.NewGuid();
}
else
{
if ( !(providerUserKey is Guid) )
{
status = MembershipCreateStatus.InvalidProviderUserKey;
returnnull;
}
}
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("INSERT INTO Users " +
" (PKID, Username, Password, Email, PasswordQuestion, " +
" PasswordAnswer, IsApproved," +
" Comment, CreationDate, LastPasswordChangedDate, LastActivityDate," +
" ApplicationName, IsLockedOut, LastLockedOutDate," +
" FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, " +
" FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart)" +
" Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", conn);
cmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(password);
cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = email;
cmd.Parameters.Add("@PasswordQuestion", OdbcType.VarChar, 255).Value = passwordQuestion;
cmd.Parameters.Add("@PasswordAnswer", OdbcType.VarChar, 255).Value = EncodePassword(passwordAnswer);
cmd.Parameters.Add("@IsApproved", OdbcType.Bit).Value = isApproved;
cmd.Parameters.Add("@Comment", OdbcType.VarChar, 255).Value = "";
cmd.Parameters.Add("@CreationDate", OdbcType.DateTime).Value = createDate;
cmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = createDate;
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = createDate;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
cmd.Parameters.Add("@IsLockedOut", OdbcType.Bit).Value = false;
cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = createDate;
cmd.Parameters.Add("@FailedPasswordAttemptCount", OdbcType.Int).Value = 0;
cmd.Parameters.Add("@FailedPasswordAttemptWindowStart", OdbcType.DateTime).Value = createDate;
cmd.Parameters.Add("@FailedPasswordAnswerAttemptCount", OdbcType.Int).Value = 0;
cmd.Parameters.Add("@FailedPasswordAnswerAttemptWindowStart", OdbcType.DateTime).Value = createDate;
try
{
conn.Open();
int recAdded = cmd.ExecuteNonQuery();
if (recAdded > 0)
{
status = MembershipCreateStatus.Success;
}
else
{
status = MembershipCreateStatus.UserRejected;
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "CreateUser");
}
status = MembershipCreateStatus.ProviderError;
}
finally
{
conn.Close();
}
return GetUser(username, false);
}
else
{
status = MembershipCreateStatus.DuplicateUserName;
}
returnnull;
}
//// MembershipProvider.DeleteUser//publicoverridebool DeleteUser(string username, bool deleteAllRelatedData)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("DELETE FROM Users " +
" WHERE Username = ? AND Applicationname = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
int rowsAffected = 0;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (deleteAllRelatedData)
{
// Process commands to delete all data for the user in the database.
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteUser");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
if (rowsAffected > 0)
returntrue;
returnfalse;
}
//// MembershipProvider.GetAllUsers//publicoverride MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, outint totalRecords)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " +
"WHERE ApplicationName = ?", conn);
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
MembershipUserCollection users = new MembershipUserCollection();
OdbcDataReader reader = null;
totalRecords = 0;
try
{
conn.Open();
totalRecords = (int)cmd.ExecuteScalar();
if (totalRecords <= 0) { return users; }
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
" FROM Users " +
" WHERE ApplicationName = ? " +
" ORDER BY Username Asc";
reader = cmd.ExecuteReader();
int counter = 0;
int startIndex = pageSize * pageIndex;
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
MembershipUser u = GetUserFromReader(reader);
users.Add(u);
}
if (counter >= endIndex) { cmd.Cancel(); }
counter++;
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetAllUsers ");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return users;
}
//// MembershipProvider.GetNumberOfUsersOnline//publicoverrideint GetNumberOfUsersOnline()
{
TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
DateTime compareTime = DateTime.Now.Subtract(onlineSpan);
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " +
" WHERE LastActivityDate > ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@CompareDate", OdbcType.DateTime).Value = compareTime;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
int numOnline = 0;
try
{
conn.Open();
numOnline = (int)cmd.ExecuteScalar();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetNumberOfUsersOnline");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
return numOnline;
}
//// MembershipProvider.GetPassword//publicoverridestring GetPassword(string username, string answer)
{
if (!EnablePasswordRetrieval)
{
thrownew ProviderException("Password Retrieval Not Enabled.");
}
if (PasswordFormat == MembershipPasswordFormat.Hashed)
{
thrownew ProviderException("Cannot retrieve Hashed passwords.");
}
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Password, PasswordAnswer, IsLockedOut FROM Users " +
" WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
string password = "";
string passwordAnswer = "";
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.HasRows)
{
reader.Read();
if (reader.GetBoolean(2))
thrownew MembershipPasswordException("The supplied user is locked out.");
password = reader.GetString(0);
passwordAnswer = reader.GetString(1);
}
else
{
thrownew MembershipPasswordException("The supplied user name is not found.");
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetPassword");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
{
UpdateFailureCount(username, "passwordAnswer");
thrownew MembershipPasswordException("Incorrect password answer.");
}
if (PasswordFormat == MembershipPasswordFormat.Encrypted)
{
password = UnEncodePassword(password);
}
return password;
}
//// MembershipProvider.GetUser(string, bool)//publicoverride MembershipUser GetUser(string username, bool userIsOnline)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT PKID, Username, Email, PasswordQuestion," +
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate" +
" FROM Users WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
MembershipUser u = null;
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
u = GetUserFromReader(reader);
if (userIsOnline)
{
OdbcCommand updateCmd = new OdbcCommand("UPDATE Users " +
"SET LastActivityDate = ? " +
"WHERE Username = ? AND Applicationname = ?", conn);
updateCmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now;
updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
updateCmd.ExecuteNonQuery();
}
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetUser(String, Boolean)");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return u;
}
//// MembershipProvider.GetUser(object, bool)//publicoverride MembershipUser GetUser(object providerUserKey, bool userIsOnline)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT PKID, Username, Email, PasswordQuestion," +
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate" +
" FROM Users WHERE PKID = ?", conn);
cmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey;
MembershipUser u = null;
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
u = GetUserFromReader(reader);
if (userIsOnline)
{
OdbcCommand updateCmd = new OdbcCommand("UPDATE Users " +
"SET LastActivityDate = ? " +
"WHERE PKID = ?", conn);
updateCmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now;
updateCmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey;
updateCmd.ExecuteNonQuery();
}
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetUser(Object, Boolean)");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return u;
}
//// GetUserFromReader// A helper function that takes the current row from the OdbcDataReader// and hydrates a MembershiUser from the values. Called by the // MembershipUser.GetUser implementation.//private MembershipUser GetUserFromReader(OdbcDataReader reader)
{
object providerUserKey = reader.GetValue(0);
string username = reader.GetString(1);
string email = reader.GetString(2);
string passwordQuestion = "";
if (reader.GetValue(3) != DBNull.Value)
passwordQuestion = reader.GetString(3);
string comment = "";
if (reader.GetValue(4) != DBNull.Value)
comment = reader.GetString(4);
bool isApproved = reader.GetBoolean(5);
bool isLockedOut = reader.GetBoolean(6);
DateTime creationDate = reader.GetDateTime(7);
DateTime lastLoginDate = new DateTime();
if (reader.GetValue(8) != DBNull.Value)
lastLoginDate = reader.GetDateTime(8);
DateTime lastActivityDate = reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.GetDateTime(10);
DateTime lastLockedOutDate = new DateTime();
if (reader.GetValue(11) != DBNull.Value)
lastLockedOutDate = reader.GetDateTime(11);
MembershipUser u = new MembershipUser(this.Name,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate);
return u;
}
//// MembershipProvider.UnlockUser//publicoverridebool UnlockUser(string username)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("UPDATE Users " +
" SET IsLockedOut = False, LastLockedOutDate = ? " +
" WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
int rowsAffected = 0;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "UnlockUser");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
if (rowsAffected > 0)
returntrue;
returnfalse;
}
//// MembershipProvider.GetUserNameByEmail//publicoverridestring GetUserNameByEmail(string email)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Username" +
" FROM Users WHERE Email = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = email;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
string username = "";
try
{
conn.Open();
username = (string)cmd.ExecuteScalar();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetUserNameByEmail");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
if (username == null)
username = "";
return username;
}
//// MembershipProvider.ResetPassword//publicoverridestring ResetPassword(string username, string answer)
{
if (!EnablePasswordReset)
{
thrownew NotSupportedException("Password reset is not enabled.");
}
if (answer == null && RequiresQuestionAndAnswer)
{
UpdateFailureCount(username, "passwordAnswer");
thrownew ProviderException("Password answer required for password reset.");
}
string newPassword =
System.Web.Security.Membership.GeneratePassword(newPasswordLength,MinRequiredNonAlphanumericCharacters);
ValidatePasswordEventArgs args =
new ValidatePasswordEventArgs(username, newPassword, true);
OnValidatingPassword(args);
if (args.Cancel)
if (args.FailureInformation != null)
throw args.FailureInformation;
elsethrownew MembershipPasswordException("Reset password canceled due to password validation failure.");
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT PasswordAnswer, IsLockedOut FROM Users " +
" WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
int rowsAffected = 0;
string passwordAnswer = "";
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.HasRows)
{
reader.Read();
if (reader.GetBoolean(1))
thrownew MembershipPasswordException("The supplied user is locked out.");
passwordAnswer = reader.GetString(0);
}
else
{
thrownew MembershipPasswordException("The supplied user name is not found.");
}
if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
{
UpdateFailureCount(username, "passwordAnswer");
thrownew MembershipPasswordException("Incorrect password answer.");
}
OdbcCommand updateCmd = new OdbcCommand("UPDATE Users " +
" SET Password = ?, LastPasswordChangedDate = ?" +
" WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False", conn);
updateCmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(newPassword);
updateCmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = DateTime.Now;
updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
rowsAffected = updateCmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "ResetPassword");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
if (rowsAffected > 0)
{
return newPassword;
}
else
{
thrownew MembershipPasswordException("User not found, or user is locked out. Password not Reset.");
}
}
//// MembershipProvider.UpdateUser//publicoverridevoid UpdateUser(MembershipUser user)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("UPDATE Users " +
" SET Email = ?, Comment = ?," +
" IsApproved = ?" +
" WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = user.Email;
cmd.Parameters.Add("@Comment", OdbcType.VarChar, 255).Value = user.Comment;
cmd.Parameters.Add("@IsApproved", OdbcType.Bit).Value = user.IsApproved;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = user.UserName;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "UpdateUser");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
//// MembershipProvider.ValidateUser//publicoverridebool ValidateUser(string username, string password)
{
bool isValid = false;
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Password, IsApproved FROM Users " +
" WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
OdbcDataReader reader = null;
bool isApproved = false;
string pwd = "";
try
{
conn.Open();
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.HasRows)
{
reader.Read();
pwd = reader.GetString(0);
isApproved = reader.GetBoolean(1);
}
else
{
returnfalse;
}
reader.Close();
if (CheckPassword(password, pwd))
{
if (isApproved)
{
isValid = true;
OdbcCommand updateCmd = new OdbcCommand("UPDATE Users SET LastLoginDate = ?" +
" WHERE Username = ? AND ApplicationName = ?", conn);
updateCmd.Parameters.Add("@LastLoginDate", OdbcType.DateTime).Value = DateTime.Now;
updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
updateCmd.ExecuteNonQuery();
}
}
else
{
conn.Close();
UpdateFailureCount(username, "password");
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "ValidateUser");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return isValid;
}
//// UpdateFailureCount// A helper method that performs the checks and updates associated with// password failure tracking.//privatevoid UpdateFailureCount(string username, string failureType)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT FailedPasswordAttemptCount, " +
" FailedPasswordAttemptWindowStart, " +
" FailedPasswordAnswerAttemptCount, " +
" FailedPasswordAnswerAttemptWindowStart " +
" FROM Users " +
" WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
OdbcDataReader reader = null;
DateTime windowStart = new DateTime();
int failureCount = 0;
try
{
conn.Open();
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.HasRows)
{
reader.Read();
if (failureType == "password")
{
failureCount = reader.GetInt32(0);
windowStart = reader.GetDateTime(1);
}
if (failureType == "passwordAnswer")
{
failureCount = reader.GetInt32(2);
windowStart = reader.GetDateTime(3);
}
}
reader.Close();
DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow);
if (failureCount == 0 || DateTime.Now > windowEnd)
{
// First password failure or outside of PasswordAttemptWindow. // Start a new password failure count from 1 and a new window starting now.if (failureType == "password")
cmd.CommandText = "UPDATE Users " +
" SET FailedPasswordAttemptCount = ?, " +
" FailedPasswordAttemptWindowStart = ? " +
" WHERE Username = ? AND ApplicationName = ?";
if (failureType == "passwordAnswer")
cmd.CommandText = "UPDATE Users " +
" SET FailedPasswordAnswerAttemptCount = ?, " +
" FailedPasswordAnswerAttemptWindowStart = ? " +
" WHERE Username = ? AND ApplicationName = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("@Count", OdbcType.Int).Value = 1;
cmd.Parameters.Add("@WindowStart", OdbcType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
if (cmd.ExecuteNonQuery() < 0)
thrownew ProviderException("Unable to update failure count and window start.");
}
else
{
if (failureCount++ >= MaxInvalidPasswordAttempts)
{
// Password attempts have exceeded the failure threshold. Lock out// the user.
cmd.CommandText = "UPDATE Users " +
" SET IsLockedOut = ?, LastLockedOutDate = ? " +
" WHERE Username = ? AND ApplicationName = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("@IsLockedOut", OdbcType.Bit).Value = true;
cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
if (cmd.ExecuteNonQuery() < 0)
thrownew ProviderException("Unable to lock out user.");
}
else
{
// Password attempts have not exceeded the failure threshold. Update// the failure counts. Leave the window the same.if (failureType == "password")
cmd.CommandText = "UPDATE Users " +
" SET FailedPasswordAttemptCount = ?" +
" WHERE Username = ? AND ApplicationName = ?";
if (failureType == "passwordAnswer")
cmd.CommandText = "UPDATE Users " +
" SET FailedPasswordAnswerAttemptCount = ?" +
" WHERE Username = ? AND ApplicationName = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("@Count", OdbcType.Int).Value = failureCount;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
if (cmd.ExecuteNonQuery() < 0)
thrownew ProviderException("Unable to update failure count.");
}
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "UpdateFailureCount");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
}
//// CheckPassword// Compares password values based on the MembershipPasswordFormat.//privatebool CheckPassword(string password, string dbpassword)
{
string pass1 = password;
string pass2 = dbpassword;
switch (PasswordFormat)
{
case MembershipPasswordFormat.Encrypted:
pass2 = UnEncodePassword(dbpassword);
break;
case MembershipPasswordFormat.Hashed:
pass1 = EncodePassword(password);
break;
default:
break;
}
if (pass1 == pass2)
{
returntrue;
}
returnfalse;
}
//// EncodePassword// Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.//privatestring EncodePassword(string password)
{
string encodedPassword = password;
switch (PasswordFormat)
{
case MembershipPasswordFormat.Clear:
break;
case MembershipPasswordFormat.Encrypted:
encodedPassword =
Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
break;
case MembershipPasswordFormat.Hashed:
HMACSHA1 hash = new HMACSHA1();
hash.Key = HexToByte(machineKey.ValidationKey);
encodedPassword =
Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
break;
default:
thrownew ProviderException("Unsupported password format.");
}
return encodedPassword;
}
//// UnEncodePassword// Decrypts or leaves the password clear based on the PasswordFormat.//privatestring UnEncodePassword(string encodedPassword)
{
string password = encodedPassword;
switch (PasswordFormat)
{
case MembershipPasswordFormat.Clear:
break;
case MembershipPasswordFormat.Encrypted:
password =
Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
break;
case MembershipPasswordFormat.Hashed:
thrownew ProviderException("Cannot unencode a hashed password.");
default:
thrownew ProviderException("Unsupported password format.");
}
return password;
}
//// HexToByte// Converts a hexadecimal string to a byte array. Used to convert encryption// key values from the configuration.//privatebyte[] HexToByte(string hexString)
{
byte[] returnBytes = newbyte[hexString.Length / 2];
for (int i = 0; i < returnBytes.Length; i+)
returnBytes[i] = Convert.ToByte(hexString.Substring(i*2, 2), 16);
return returnBytes;
}
//// MembershipProvider.FindUsersByName//publicoverride MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, outint totalRecords)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " +
"WHERE Username LIKE ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@UsernameSearch", OdbcType.VarChar, 255).Value = usernameToMatch;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
MembershipUserCollection users = new MembershipUserCollection();
OdbcDataReader reader = null;
try
{
conn.Open();
totalRecords = (int)cmd.ExecuteScalar();
if (totalRecords <= 0) { return users; }
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
" FROM Users " +
" WHERE Username LIKE ? AND ApplicationName = ? " +
" ORDER BY Username Asc";
reader = cmd.ExecuteReader();
int counter = 0;
int startIndex = pageSize * pageIndex;
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
MembershipUser u = GetUserFromReader(reader);
users.Add(u);
}
if (counter >= endIndex) { cmd.Cancel(); }
counter++;
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "FindUsersByName");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return users;
}
//// MembershipProvider.FindUsersByEmail//publicoverride MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, outint totalRecords)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " +
"WHERE Email LIKE ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@EmailSearch", OdbcType.VarChar, 255).Value = emailToMatch;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
MembershipUserCollection users = new MembershipUserCollection();
OdbcDataReader reader = null;
totalRecords = 0;
try
{
conn.Open();
totalRecords = (int)cmd.ExecuteScalar();
if (totalRecords <= 0) { return users; }
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
" FROM Users " +
" WHERE Email LIKE ? AND ApplicationName = ? " +
" ORDER BY Username Asc";
reader = cmd.ExecuteReader();
int counter = 0;
int startIndex = pageSize * pageIndex;
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
MembershipUser u = GetUserFromReader(reader);
users.Add(u);
}
if (counter >= endIndex) { cmd.Cancel(); }
counter++;
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "FindUsersByEmail");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return users;
}
//// WriteToEventLog// A helper function that writes exception detail to the event log. Exceptions// are written to the event log as a security measure to avoid private database// details from being returned to the browser. If a method does not return a status// or boolean indicating the action succeeded or failed, a generic exception is also // thrown by the caller.//privatevoid WriteToEventLog(Exception e, string action)
{
EventLog log = new EventLog();
log.Source = eventSource;
log.Log = eventLog;
string message = "An exception occurred communicating with the data source.\n\n";
message += "Action: " + action + "\n\n";
message += "Exception: " + e.ToString();
log.WriteEntry(message);
}
}
}
See Also
Concepts
Sample Membership Provider Implementation