|
I created a MySQL Profile Provider for myself and posted it on this site. Here is a link article
I didn't create it from scratch I used a MSAccess sample as a template and modified it to work with MySql. It seems to work pretty well. Maybe some other people will try it and let me know if there are any major issues.
-- modified at 16:12 Wednesday 28th June, 2006
|
|
|
|
|
I put the wrong link in the previous post. Here is the correct Link Profile Provider
|
|
|
|
|
Its telling me that I cannot access the database, but I know my user name is right
This happens whenever the "throw e" statement happens
If anyone can help it would be greatly apretiated
|
|
|
|
|
the "throw e" statements were there to on purpose, to halt the execution and show you that something went wrong with the mysql connection.
Note :
New version of this article here[^]
This is the last day I am replying to comments on this page.
|
|
|
|
|
Thanks for the great work! It helped me a lot.
What I was missing was an email validation and a check for the minimum password length.
What do you think about inserting these lines in method CreateUser?
if (password.Length < pMinRequiredPasswordLength)<br />
{<br />
status = MembershipCreateStatus.InvalidPassword;<br />
return null;<br />
}<br />
string strRegex = @"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}" +<br />
@"\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\" +<br />
@".)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";<br />
Regex re = new Regex(strRegex);<br />
if (!re.IsMatch(email))<br />
{<br />
status = MembershipCreateStatus.InvalidEmail;<br />
return null;<br />
}
-- Gunter Heiss.
|
|
|
|
|
I don't think that this is a good idea.
I think that it is not the provider's role to do checkings. Do your own checking with the asp.NET webforms validation controls.
Note :
New version of this article[^]
This is the last day I am replying to comments on this page.
|
|
|
|
|
Thank you. I was faced with the problem of having few MSSQl resources but a ast amount of mySQL resources. You article and code will help me make good use of the DBs that are available to me. This was a big help and greatly appreciated.
--Frank Jones
|
|
|
|
|
I realise this has been asked before, but is anyone aware of or working on a profile provider for .NET - MySql?
Have searched extensively on the internet and came up with the following-
Create my own (Document covers some of the areas of the creating a customised searching provider, but beyond my abilites)- http://www.theserverside.net/tt/articles/showarticle.tss?id=CreatingProfileProvider
Pay CoreLab a large amount for the priveledge (Not an option) - http://www.crlab.com/mysqlnet/[^]
Any help or direction would be greatly appreciated.
|
|
|
|
|
|
Have any idea how to use the server explorer in vs.net to manage mysql databases? Is there any way to show this up? Sorry for the offtopic
|
|
|
|
|
Im not expert, but from what I have seen there are currently two commonly used methods of connecting .NET to MySql:
Connector/net - Faster than the other but does not provide server explorer support.
Connector/ODBC - Slower but does provide server explorer support.
Either connectors can be found here:
http://www.mysql.com/products/connector/[^]
If you are looking to simply manage data, I would recommend SQLyog (Third party tool, but very good in my opinion.
Hope this helps.
|
|
|
|
|
First, thanks for that lovly code you have submitted. However I seem to have some problems. I'm wondering, is there some settings that has to be set if my mysql database is on a remote server? The error message longer down in the thread does not indicate that.
I have created the tables and I'm able to connect to this tables through normal code so to speak and use of the mysql driver.
Tips?
I also added the code to verify that things are setup correct.Testcode
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myConnection As MySqlConnection
Dim myDataAdapter As MySqlDataAdapter
Dim myDataSet As DataSet
Dim strSQL As String
Dim iRecordCount As Integer
myConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings("ConnString").ConnectionString)
strSQL = "SELECT * FROM users;"
myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
myDataSet = New Dataset()
myDataAdapter.Fill(myDataSet, "mytable")
GridView1.DataSource = myDataSet
GridView1.DataBind()
End Sub
Action: GetUserNameByEmail
Exception: MySql.Data.MySqlClient.MySqlException: Table 'wno36258.Users' doesn't exist
at MySql.Data.MySqlClient.PacketReader.CheckForError()
at MySql.Data.MySqlClient.PacketReader.ReadHeader()
at MySql.Data.MySqlClient.PacketReader.OpenPacket()
at MySql.Data.MySqlClient.NativeDriver.ReadResult(Int64& affectedRows, Int64& lastInsertId)
at MySql.Data.MySqlClient.CommandResult.ReadNextResult(Boolean isFirst)
at MySql.Data.MySqlClient.CommandResult..ctor(Driver d, Boolean isBinary)
at MySql.Data.MySqlClient.NativeDriver.SendQuery(Byte[] bytes, Int32 length, Boolean consume)
at MySql.Data.MySqlClient.MySqlCommand.GetNextResultSet(MySqlDataReader reader)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()
at Andri.Web.MySqlMembershipProvider.GetUserNameByEmail(String email)
Jone says:
There is no spone
|
|
|
|
|
strSQL = "SELECT * FROM wno36258.Users;"
If I added the wno36258.Users in the test code it got the same error. But why does it include the database name in front?
Jone says:
There is no spone
|
|
|
|
|
I had to create the database table with the database name before the table name. Did not think that was nesccary.
CREATE TABLE `wno36258`.`Users` (<br />
`PKID` VARCHAR( 36 ) NOT NULL ,<br />
`Username` VARCHAR( 255 ) NOT NULL ,<br />
`ApplicationName` VARCHAR( 100 ) NOT NULL ,<br />
`Email` VARCHAR( 100 ) NOT NULL ,<br />
`Comment` VARCHAR( 255 ) ,<br />
`Password` VARCHAR( 128 ) NOT NULL ,<br />
`PasswordQuestion` VARCHAR( 255 ) ,<br />
`PasswordAnswer` VARCHAR( 255 ) ,<br />
`IsApproved` TINYINT( 1 ) ,<br />
`LastActivityDate` DATETIME,<br />
`LastLoginDate` DATETIME,<br />
`LastPasswordChangedDate` DATETIME,<br />
`CreationDate` DATETIME,<br />
`IsOnLine` TINYINT( 1 ) ,<br />
`IsLockedOut` TINYINT( 1 ) ,<br />
`LastLockedOutDate` DATETIME,<br />
`FailedPasswordAttemptCount` INT( 11 ) ,<br />
`FailedPasswordAttemptWindowStart` DATETIME,<br />
`FailedPasswordAnswerAttemptCount` INT( 11 ) ,<br />
`FailedPasswordAnswerAttemptWindowStart` DATETIME,<br />
PRIMARY KEY ( `PKID` )
)
Jone says:
There is no spone
|
|
|
|
|
I am trying to get this running on my host's server (works fine on my desktop)... however, I am running into this problem...
Line 29:
|
|
|
|
|
Very impressed. Got this up and running in around half an hour with MYSQL version 5 and managed to deploy it to my ISP who are running 4.4.12 MYSQL
Works perfectly on both versions.
Do you have any plans to add in a profile provider class?
Thanks again.
-- modified at 8:37 Friday 12th May, 2006
|
|
|
|
|
I to am very impressed! I got the membership and role providers setup without any problems. Nice job!
I would love to see a profile provider class implemented as well. I did a fair amount of searching online and couldn't find any examples. The only thing I found was MySqlDirect from CoreLab, but that is a library that comes with a pretty high price tag. I am fine using the MySQL ADO.Net Data Provider, so I don't need the other functionality of the MySqlDirect solution. The only thing I would use this library for is the provider classes.
Thanks,
John
|
|
|
|
|
|
Thanks to Rakotomalala Andriniaina for all of his outstanding work on this.
Ive ran through all of the info here on the site and attempted to apply it to my ASP.Net 2.0 app (Using Visual Web Developer 2005 Express and MySql 5).
1. Found the SQL statements to have problems with MYSql 5.
I ended up creating the first two tables myself, but got the rest of the statements to execute okay. Using slightly modified code (Within SQLyog), listed below:
ALTER TABLE usersinroles
ADD INDEX ( Username, Rolename, ApplicationName) ;
ALTER TABLE roles
ADD INDEX ( Rolename , ApplicationName ) ;
CREATE TABLE users (
PKID varchar(36) NOT NULL default '',
Username varchar(255) NOT NULL default '',
ApplicationName varchar(100) NOT NULL default '',
Email varchar(100) NOT NULL default '',
Comment varchar(255) default NULL,
Password varchar(128) NOT NULL default '',
PasswordQuestion varchar(255) default NULL,
PasswordAnswer varchar(255) default NULL,
IsApproved tinyint(1) default NULL,
LastActivityDate datetime default NULL,
LastLoginDate datetime default NULL,
LastPasswordChangedDate datetime default NULL,
CreationDate datetime default NULL,
IsOnLine tinyint(1) default NULL,
IsLockedOut tinyint(1) default NULL,
LastLockedOutDate datetime default NULL,
FailedPasswordAttemptCount int(11) default NULL,
FailedPasswordAttemptWindowStart datetime default NULL,
FailedPasswordAnswerAttemptCount int(11) default NULL,
FailedPasswordAnswerAttemptWindowStart datetime default NULL,
PRIMARY KEY (PKID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
2. Recompiled the two cs files using the following syntax for the two files:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /r:C:\inetpub\wwwroot\bin\MySql.Data.dll /target:library /out:c:\Inetpub\wwwroot\bin\MySqlMembershipProvider.dll C:\Inetpub\wwwroot\App_Code\MySqlMembershipProvider.cs
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /r:C:\inetpub\wwwroot\bin\MySql.Data.dll /target:library /out:c:\Inetpub\wwwroot\bin\MySqlRoleProvider.dll C:\Inetpub\wwwroot\App_Code\MySqlRoleProvider.cs
3. Put the two dlls into the app_code folder as previously stated, but also had to create a reference within my application (Using the Reference GUI in Visual Web Developer) to the two dlls. It just would not work otherwise.
4. Modified my web.config to be this:
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appsettings>
<connectionstrings>
<add name="ConnString"
="" connectionstring="Database=db_serialnumering;Data Source=localhost;
User Id=YOURUSERNAME;Password=YOURPASS">
<system.web>
<compilation debug="true" strict="false" explicit="true">
<assemblies>
<add assembly="MySql.Data, Version=1.0.7.30072, Culture=neutral, PublicKeyToken=C5687FC88969C49E">
<pages>
<namespaces>
<clear>
<add namespace="System">
<add namespace="System.Collections">
<add namespace="System.Collections.Specialized">
<add namespace="System.Configuration">
<add namespace="System.Text">
<add namespace="System.Text.RegularExpressions">
<add namespace="System.Web">
<add namespace="System.Web.Caching">
<add namespace="System.Web.SessionState">
<add namespace="System.Web.Security">
<add namespace="System.Web.Profile">
<add namespace="System.Web.UI">
<add namespace="System.Web.UI.WebControls">
<add namespace="System.Web.UI.WebControls.WebParts">
<add namespace="System.Web.UI.HtmlControls">
<authentication mode="Windows">
<rolemanager defaultprovider="MySqlRoleProvider"
="" enabled="true" cacherolesincookie="true" cookiename=".ASPROLES" cookietimeout="30" cookiepath="/" cookierequiressl="false" cookieslidingexpiration="true" cookieprotection="All">
<providers>
<clear>
<add
name="MySqlRoleProvider"
="" type="Andri.Web.MySqlRoleProvider" connectionstringname="ConnString" applicationname="SerialNumbering" writeexceptionstoeventlog="true">
<membership defaultprovider="MySqlMembershipProvider"
="" userisonlinetimewindow="15">
<providers>
<clear>
<add
name="MySqlMembershipProvider"
="" type="Andri.Web.MySqlMembershipProvider" connectionstringname="ConnString" applicationname="SerialNumbering" enablepasswordretrieval="false" enablepasswordreset="true" requiresquestionandanswer="true" requiresuniqueemail="true" passwordformat="Hashed" writeexceptionstoeventlog="true">
5. I could then use the security tab.
Please refere to the previous To make these work on a VB website.... details.
These notes are simply what I had to do to make it work on a VB.net coded asp.net site.
Hope this helps.
|
|
|
|
|
Thanks for providing this code. It saves me having to work it out myself! I haven't tried out the actual code yet, but I did have some problems running the SQL script on MySQL 4.1 using phpMyAdmin and I thought I would share what I found out.
For anyone using MySQL 4.1 and having trouble, here are the changes I had to make:
- Replace single straight quotes with grave accents (`)
- Change the ApplicationName to a VARCHAR(255) in the CREATE TABLE `usersinroles` command (otherwise the corresponding ADD INDEX instruction fails)
I hope that helps!
|
|
|
|
|
Hi,
Thanks Andri for providing this! Works great once you get it configured.
I've successfully used the Login controls to allow new users to add themselves, but when I try to use the PasswordRecovery control, I keep getting the error: "Your answer could not be verified. Please try again." , even though I'm typing in the correct answer.
Could this have something to do with the encryption? I changed the encryption string to a different hex value in MySqlMembershipProvider.cs before I compiled it.
Any suggestions?
Thanks
Jay
|
|
|
|
|
|
Is there a VB Version of this? This is great, but the website I am working on is done completely in VB. I have tried compiling it seperately and then referencing the dll, but it doesn't work the way I need it to.
Any Help pointing me in the right direction would be greatly appreciated.
I don't know C# at all.
Thanks!
ahovious
|
|
|
|
|
I have a VB version in the works, here is the somewhat buggy code. I have the create user section working but still need to go through the rest of the fuctions. It is a revamped version of MS ODBC member service that was made for Access.
All that us really left is converting the "uniqueidentifer" type to "VARCHAR(50)" and changing "providerUserKey" to a string before doing anything to the "PKID" member.
Dave
TABLE INFO
---------------
DROP TABLE IF EXISTS `YOURDATABASENAME`.`Users`;
CREATE TABLE `YOURDATABASENAME`.`Users` (
`PKID` VARCHAR(50) NOT NULL,
`UserName` VARCHAR(255) NOT NULL,
`Password` VARCHAR(255) NOT NULL,
`ApplicationName` VARCHAR(255) NOT NULL,
`Email` VARCHAR(128) NOT NULL,
`Comment` VARCHAR(255) NULL,
`PasswordQuestion` VARCHAR(255) NULL,
`PasswordAnswer` VARCHAR(255) NULL,
`IsApproved` BOOLEAN NULL,
`LastActivityDate` DATETIME NULL,
`LastLoginDate` DATETIME NULL,
`LastPasswordChangedDate` DATETIME NULL,
`CreationDate` DATETIME NULL,
`IsOnLine` BOOLEAN NULL,
`IsLockedOut` BOOLEAN NULL,
`LastLockedOutDate` DATETIME NULL,
`FailedPasswordAttemptCount` INT NULL,
`FailedPasswordAttemptWindowStart` DATETIME NULL,
`FailedPasswordAnswerAttemptCount` INT NULL,
`FailedPasswordAnswerAttemptWindowStart` DATETIME NULL,
PRIMARY KEY (`PKID`)
)
ENGINE = InnoDB;
VB CODE
--------------------
Imports Microsoft.VisualBasic
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
Public NotInheritable Class OdbcMembershipProvider
Inherits MembershipProvider
'
' Global generated password length, generic exception message, event log info.
'
Private newPasswordLength As Integer = 8
Private eventSource As String = "OdbcMembershipProvider"
Private eventLog As String = "Application"
Private exceptionMessage As String = "An exception occurred. Please check the Event Log."
Private tableName As String = "Users"
Private connectionString As String
'
' 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 As Boolean
Public Property WriteExceptionsToEventLog() As Boolean
Get
Return pWriteExceptionsToEventLog
End Get
Set(ByVal value As Boolean)
pWriteExceptionsToEventLog = value
End Set
End Property
'
' System.Configuration.Provider.ProviderBase.Initialize Method
'
Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
'
' Initialize values from web.config.
'
If config Is Nothing Then _
Throw New ArgumentNullException("config")
If name Is Nothing OrElse name.Length = 0 Then _
name = "OdbcMembershipProvider"
If String.IsNullOrEmpty(config("description")) Then
config.Remove("description")
config.Add("description", "Sample ODBC Membership provider")
End If
' 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 As String = config("passwordFormat")
If temp_format Is Nothing Then
temp_format = "Hashed"
End If
Select Case temp_format
Case "Hashed"
pPasswordFormat = MembershipPasswordFormat.Hashed
Case "Encrypted"
pPasswordFormat = MembershipPasswordFormat.Encrypted
Case "Clear"
pPasswordFormat = MembershipPasswordFormat.Clear
Case Else
Throw New ProviderException("Password format not supported.")
End Select
'
' Initialize OdbcConnection.
'
Dim ConnectionStringSettings As ConnectionStringSettings = _
ConfigurationManager.ConnectionStrings(config("connectionStringName"))
If ConnectionStringSettings Is Nothing OrElse ConnectionStringSettings.ConnectionString.Trim() = "" Then
Throw New ProviderException("Connection string cannot be blank.")
End If
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 _
Throw New ProviderException("Hashed or Encrypted passwords " & _
"are not supported with auto-generated keys.")
End Sub
'
' A helper function to retrieve config values from the configuration file.
'
Private Function GetConfigValue(ByVal configValue As String, ByVal defaultValue As String) As String
If String.IsNullOrEmpty(configValue) Then _
Return defaultValue
Return configValue
End Function
'
' System.Web.Security.MembershipProvider properties.
'
Private pApplicationName As String
Private pEnablePasswordReset As Boolean
Private pEnablePasswordRetrieval As Boolean
Private pRequiresQuestionAndAnswer As Boolean
Private pRequiresUniqueEmail As Boolean
Private pMaxInvalidPasswordAttempts As Integer
Private pPasswordAttemptWindow As Integer
Private pPasswordFormat As MembershipPasswordFormat
Public Overrides Property ApplicationName() As String
Get
Return pApplicationName
End Get
Set(ByVal value As String)
pApplicationName = value
End Set
End Property
Public Overrides ReadOnly Property EnablePasswordReset() As Boolean
Get
Return pEnablePasswordReset
End Get
End Property
Public Overrides ReadOnly Property EnablePasswordRetrieval() As Boolean
Get
Return pEnablePasswordRetrieval
End Get
End Property
Public Overrides ReadOnly Property RequiresQuestionAndAnswer() As Boolean
Get
Return pRequiresQuestionAndAnswer
End Get
End Property
Public Overrides ReadOnly Property RequiresUniqueEmail() As Boolean
Get
Return pRequiresUniqueEmail
End Get
End Property
Public Overrides ReadOnly Property MaxInvalidPasswordAttempts() As Integer
Get
Return pMaxInvalidPasswordAttempts
End Get
End Property
Public Overrides ReadOnly Property PasswordAttemptWindow() As Integer
Get
Return pPasswordAttemptWindow
End Get
End Property
Public Overrides ReadOnly Property PasswordFormat() As MembershipPasswordFormat
Get
Return pPasswordFormat
End Get
End Property
Private pMinRequiredNonAlphanumericCharacters As Integer
Public Overrides ReadOnly Property MinRequiredNonAlphanumericCharacters() As Integer
Get
Return pMinRequiredNonAlphanumericCharacters
End Get
End Property
Private pMinRequiredPasswordLength As Integer
Public Overrides ReadOnly Property MinRequiredPasswordLength() As Integer
Get
Return pMinRequiredPasswordLength
End Get
End Property
Private pPasswordStrengthRegularExpression As String
Public Overrides ReadOnly Property PasswordStrengthRegularExpression() As String
Get
Return pPasswordStrengthRegularExpression
End Get
End Property
'
' System.Web.Security.MembershipProvider methods.
'
'
' MembershipProvider.ChangePassword
'
Public Overrides Function ChangePassword(ByVal username As String, _
ByVal oldPwd As String, _
ByVal newPwd As String) As Boolean
If Not ValidateUser(username, oldPwd) Then _
Return False
Dim args As ValidatePasswordEventArgs = _
New ValidatePasswordEventArgs(username, newPwd, True)
OnValidatingPassword(args)
If args.Cancel Then
If Not args.FailureInformation Is Nothing Then
Throw args.FailureInformation
Else
Throw New ProviderException("Change password canceled due to New password validation failure.")
End If
End If
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("UPDATE " & tableName & _
" 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 As Integer = 0
Try
conn.Open()
rowsAffected = cmd.ExecuteNonQuery()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "ChangePassword")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
conn.Close()
End Try
If rowsAffected > 0 Then
Return True
End If
Return False
End Function
'
' MembershipProvider.ChangePasswordQuestionAndAnswer
'
Public Overrides Function ChangePasswordQuestionAndAnswer(ByVal username As String, _
ByVal password As String, _
ByVal newPwdQuestion As String, _
ByVal newPwdAnswer As String) As Boolean
If Not ValidateUser(username, password) Then _
Return False
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("UPDATE " & tableName & _
" 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 As Integer = 0
Try
conn.Open()
rowsAffected = cmd.ExecuteNonQuery()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "ChangePasswordQuestionAndAnswer")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
conn.Close()
End Try
If rowsAffected > 0 Then
Return True
End If
Return False
End Function
'
' MembershipProvider.CreateUser
'
Public Overrides Function CreateUser(ByVal username As String, _
ByVal password As String, _
ByVal email As String, _
ByVal passwordQuestion As String, _
ByVal passwordAnswer As String, _
ByVal isApproved As Boolean, _
ByVal providerUserKey As Object, _
ByRef status As MembershipCreateStatus) As MembershipUser
Dim Args As ValidatePasswordEventArgs = _
New ValidatePasswordEventArgs(username, password, True)
OnValidatingPassword(args)
If args.Cancel Then
status = MembershipCreateStatus.InvalidPassword
Return Nothing
End If
If RequiresUniqueEmail AndAlso GetUserNameByEmail(email) <> "" Then
status = MembershipCreateStatus.DuplicateEmail
Return Nothing
End If
Dim u As MembershipUser = GetUser(username, False)
If u Is Nothing Then
Dim createDate As DateTime = DateTime.Now
If providerUserKey Is Nothing Then
providerUserKey = Guid.NewGuid()
Else
If Not TypeOf providerUserKey Is Guid Then
status = MembershipCreateStatus.InvalidProviderUserKey
Return Nothing
End If
End If
Dim PK As String = providerUserKey.ToString
Dim KL As Int16 = PK.Length
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("INSERT INTO " & tableName & _
" (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.VarChar, 50).Value = PK
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 As Integer = cmd.ExecuteNonQuery()
If recAdded > 0 Then
status = MembershipCreateStatus.Success
Else
status = MembershipCreateStatus.UserRejected
End If
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "CreateUser")
End If
status = MembershipCreateStatus.ProviderError
Finally
conn.Close()
End Try
Return GetUser(username, False)
Else
status = MembershipCreateStatus.DuplicateUserName
End If
Return Nothing
End Function
'
' MembershipProvider.DeleteUser
'
Public Overrides Function DeleteUser(ByVal username As String, _
ByVal deleteAllRelatedData As Boolean) As Boolean
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("DELETE FROM " & tableName & _
" 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 As Integer = 0
Try
conn.Open()
rowsAffected = cmd.ExecuteNonQuery()
If deleteAllRelatedData Then
' Process commands to delete all data for the user in the database.
End If
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "DeleteUser")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
conn.Close()
End Try
If rowsAffected > 0 Then _
Return True
Return False
End Function
'
' MembershipProvider.GetAllUsers
'
Public Overrides Function GetAllUsers(ByVal pageIndex As Integer, _
ByVal pageSize As Integer, _
ByRef totalRecords As Integer) _
As MembershipUserCollection
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Count(*) FROM " & tableName & _
"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 Then Return users
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," & _
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " & _
" FROM " & tableName & " " & _
" WHERE ApplicationName = ? " & _
" ORDER BY Username Asc"
reader = cmd.ExecuteReader()
Dim counter As Integer = 0
Dim startIndex As Integer = pageSize * pageIndex
Dim endIndex As Integer = startIndex + pageSize - 1
Do While reader.Read()
If counter >= startIndex Then
Dim u As MembershipUser = GetUserFromReader(reader)
users.Add(u)
End If
If counter >= endIndex Then cmd.Cancel()
counter += 1
Loop
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetAllUsers")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
If Not reader Is Nothing Then reader.Close()
conn.Close()
End Try
Return users
End Function
'
' MembershipProvider.GetNumberOfUsersOnline
'
Public Overrides Function GetNumberOfUsersOnline() As Integer
Dim 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 " & tableName & _
" WHERE LastActivityDate > ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@CompareDate", OdbcType.DateTime).Value = compareTime
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim numOnline As Integer = 0
Try
conn.Open()
numOnline = CInt(cmd.ExecuteScalar())
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetNumberOfUsersOnline")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
conn.Close()
End Try
Return numOnline
End Function
'
' MembershipProvider.GetPassword
'
Public Overrides Function GetPassword(ByVal username As String, ByVal answer As String) As String
If Not EnablePasswordRetrieval Then
Throw New ProviderException("Password Retrieval Not Enabled.")
End If
If PasswordFormat = MembershipPasswordFormat.Hashed Then
Throw New ProviderException("Cannot retrieve Hashed passwords.")
End If
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Password, PasswordAnswer, IsLockedOut FROM " & tableName & _
" 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 As String = ""
Dim passwordAnswer As String = ""
Dim reader As OdbcDataReader = Nothing
Try
conn.Open()
reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
If reader.HasRows Then
reader.Read()
If reader.GetBoolean(2) Then _
Throw New MembershipPasswordException("The supplied user is locked out.")
password = reader.GetString(0)
passwordAnswer = reader.GetString(1)
Else
Throw New MembershipPasswordException("The supplied user name is not found.")
End If
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetPassword")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
If Not reader Is Nothing Then reader.Close()
conn.Close()
End Try
If RequiresQuestionAndAnswer AndAlso Not CheckPassword(answer, passwordAnswer) Then
UpdateFailureCount(username, "passwordAnswer")
Throw New MembershipPasswordException("Incorrect password answer.")
End If
If PasswordFormat = MembershipPasswordFormat.Encrypted Then
password = UnEncodePassword(password)
End If
Return password
End Function
'
' MembershipProvider.GetUser(String, Boolean)
'
Public Overrides Function GetUser(ByVal username As String, _
ByVal userIsOnline As Boolean) 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 " & tableName & " 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 = Nothing
Dim reader As OdbcDataReader = Nothing
Try
conn.Open()
reader = cmd.ExecuteReader()
If reader.HasRows Then
reader.Read()
u = GetUserFromReader(reader)
If userIsOnline Then
Dim updateCmd As OdbcCommand = New OdbcCommand("UPDATE " & tableName & " " & _
"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()
End If
End If
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetUser(String, Boolean)")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
If Not reader Is Nothing Then reader.Close()
conn.Close()
End Try
Return u
End Function
'
' MembershipProvider.GetUser(Object, Boolean)
'
Public Overrides Function GetUser(ByVal providerUserKey As Object, _
ByVal userIsOnline As Boolean) 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 " & tableName & " WHERE PKID = ?", conn)
cmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey
Dim u As MembershipUser = Nothing
Dim reader As OdbcDataReader = Nothing
Try
conn.Open()
reader = cmd.ExecuteReader()
If reader.HasRows Then
reader.Read()
u = GetUserFromReader(reader)
If userIsOnline Then
Dim updateCmd As OdbcCommand = New OdbcCommand("UPDATE " & tableName & " " & _
"SET LastActivityDate = ? " & _
"WHERE PKID = ?", conn)
updateCmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now
updateCmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey
updateCmd.ExecuteNonQuery()
End If
End If
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetUser(Object, Boolean)")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
If Not reader Is Nothing Then reader.Close()
conn.Close()
End Try
Return u
End Function
'
' 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 Function GetUserFromReader(ByVal reader As OdbcDataReader) As MembershipUser
Dim providerUserKey As Object = reader.GetValue(0)
Dim username As String = reader.GetString(1)
Dim email As String = reader.GetString(2)
Dim passwordQuestion As String = ""
If Not reader.GetValue(3) Is DBNull.Value Then _
passwordQuestion = reader.GetString(3)
Dim comment As String = ""
If Not reader.GetValue(4) Is DBNull.Value Then _
comment = reader.GetString(4)
Dim isApproved As Boolean = reader.GetBoolean(5)
Dim isLockedOut As Boolean = reader.GetBoolean(6)
Dim creationDate As DateTime = reader.GetDateTime(7)
Dim lastLoginDate As DateTime = New DateTime()
If Not 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()
If Not 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
End Function
'
' MembershipProvider.UnlockUser
'
Public Overrides Function UnlockUser(ByVal username As String) As Boolean
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("UPDATE " & tableName & _
" 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 As Integer = 0
Try
conn.Open()
rowsAffected = cmd.ExecuteNonQuery()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "UnlockUser")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
conn.Close()
End Try
If rowsAffected > 0 Then _
Return True
Return False
End Function
'
' MembershipProvider.GetUserNameByEmail
'
Public Overrides Function GetUserNameByEmail(ByVal email As String) As String
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Username" & _
" FROM " & tableName & " 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 As String = ""
Try
conn.Open()
'username = cmd.ExecuteScalar().ToString()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetUserNameByEmail")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
conn.Close()
End Try
If username Is Nothing Then _
username = ""
Return username
End Function
'
' MembershipProvider.ResetPassword
'
Public Overrides Function ResetPassword(ByVal username As String, ByVal answer As String) As String
If Not EnablePasswordReset Then
Throw New NotSupportedException("Password Reset is not enabled.")
End If
If answer Is Nothing AndAlso RequiresQuestionAndAnswer Then
UpdateFailureCount(username, "passwordAnswer")
Throw New ProviderException("Password answer required for password Reset.")
End If
Dim newPassword As String = _
System.Web.Security.Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters)
Dim Args As ValidatePasswordEventArgs = _
New ValidatePasswordEventArgs(username, newPassword, True)
OnValidatingPassword(args)
If args.Cancel Then
If Not args.FailureInformation Is Nothing Then
Throw args.FailureInformation
Else
Throw New MembershipPasswordException("Reset password canceled due to password validation failure.")
End If
End If
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT PasswordAnswer, IsLockedOut FROM " & tableName & _
" 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 As Integer = 0
Dim passwordAnswer As String = ""
Dim reader As OdbcDataReader = Nothing
Try
conn.Open()
reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
If reader.HasRows Then
reader.Read()
If reader.GetBoolean(1) Then _
Throw New MembershipPasswordException("The supplied user is locked out.")
passwordAnswer = reader.GetString(0)
Else
Throw New MembershipPasswordException("The supplied user name is not found.")
End If
If RequiresQuestionAndAnswer AndAlso Not CheckPassword(answer, passwordAnswer) Then
UpdateFailureCount(username, "passwordAnswer")
Throw New MembershipPasswordException("Incorrect password answer.")
End If
Dim updateCmd As OdbcCommand = New OdbcCommand("UPDATE " & tableName & _
" 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")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
If Not reader Is Nothing Then reader.Close()
conn.Close()
End Try
If rowsAffected > 0 Then
Return newPassword
Else
Throw New MembershipPasswordException("User not found, or user is locked out. Password not Reset.")
End If
End Function
'
' MembershipProvider.UpdateUser
'
Public Overrides Sub UpdateUser(ByVal user As MembershipUser)
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("UPDATE " & tableName & _
" 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")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
conn.Close()
End Try
End Sub
'
' MembershipProvider.ValidateUser
'
Public Overrides Function ValidateUser(ByVal username As String, ByVal password As String) As Boolean
Dim isValid As Boolean = False
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Password, IsApproved FROM " & tableName & _
" 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 = Nothing
Dim isApproved As Boolean = False
Dim pwd As String = ""
Try
conn.Open()
reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
If reader.HasRows Then
reader.Read()
pwd = reader.GetString(0)
isApproved = reader.GetBoolean(1)
Else
Return False
End If
reader.Close()
If CheckPassword(password, pwd) Then
If isApproved Then
isValid = True
Dim updateCmd As OdbcCommand = New OdbcCommand("UPDATE " & tableName & " 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()
End If
Else
conn.Close()
UpdateFailureCount(username, "password")
End If
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "ValidateUser")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
If Not reader Is Nothing Then reader.Close()
conn.Close()
End Try
Return isValid
End Function
'
' UpdateFailureCount
' A helper method that performs the checks and updates associated with
' password failure tracking.
'
Private Sub UpdateFailureCount(ByVal username As String, ByVal failureType As String)
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT FailedPasswordAttemptCount, " & _
" FailedPasswordAttemptWindowStart, " & _
" FailedPasswordAnswerAttemptCount, " & _
" FailedPasswordAnswerAttemptWindowStart " & _
" FROM " & tableName & " " & _
" 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 = Nothing
Dim windowStart As DateTime = New DateTime()
Dim failureCount As Integer = 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)
End If
If failureType = "passwordAnswer" Then
failureCount = reader.GetInt32(2)
windowStart = reader.GetDateTime(3)
End If
End If
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 " & tableName & " " & _
" SET FailedPasswordAttemptCount = ?, " & _
" FailedPasswordAttemptWindowStart = ? " & _
" WHERE Username = ? AND ApplicationName = ?"
If failureType = "passwordAnswer" Then _
cmd.CommandText = "UPDATE " & tableName & " " & _
" 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 _
Throw New 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 " & tableName & " " & _
" 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 _
Throw New 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 " & tableName & " " & _
" SET FailedPasswordAttemptCount = ?" & _
" WHERE Username = ? AND ApplicationName = ?"
If failureType = "passwordAnswer" Then _
cmd.CommandText = "UPDATE " & tableName & " " & _
" 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 _
Throw New ProviderException("Unable to update failure count.")
End If
End If
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "UpdateFailureCount")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
If Not reader Is Nothing Then reader.Close()
conn.Close()
End Try
End Sub
'
' CheckPassword
' Compares password values based on the MembershipPasswordFormat.
'
Private Function CheckPassword(ByVal password As String, ByVal dbpassword As String) As Boolean
Dim pass1 As String = password
Dim pass2 As String = dbpassword
Select Case PasswordFormat
Case MembershipPasswordFormat.Encrypted
pass2 = UnEncodePassword(dbpassword)
Case MembershipPasswordFormat.Hashed
pass1 = EncodePassword(password)
Case Else
End Select
If pass1 = pass2 Then
Return True
End If
Return False
End Function
'
' EncodePassword
' Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.
'
Private Function EncodePassword(ByVal password As String) As String
Dim encodedPassword As String = password
Select Case 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)))
Case Else
Throw New ProviderException("Unsupported password format.")
End Select
Return encodedPassword
End Function
'
' UnEncodePassword
' Decrypts or leaves the password clear based on the PasswordFormat.
'
Private Function UnEncodePassword(ByVal encodedPassword As String) As String
Dim password As String = encodedPassword
Select Case PasswordFormat
Case MembershipPasswordFormat.Clear
Case MembershipPasswordFormat.Encrypted
password = _
Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)))
Case MembershipPasswordFormat.Hashed
Throw New ProviderException("Cannot unencode a hashed password.")
Case Else
Throw New ProviderException("Unsupported password format.")
End Select
Return password
End Function
'
' HexToByte
' Converts a hexadecimal string to a byte array. Used to convert encryption
' key values from the configuration.
'
Private Function HexToByte(ByVal hexString As String) As Byte()
Dim ReturnBytes((hexString.Length \ 2) - 1) As Byte
For i As Integer = 0 To ReturnBytes.Length - 1
ReturnBytes(i) = Convert.ToByte(hexString.Substring(i * 2, 2), 16)
Next
Return ReturnBytes
End Function
'
' MembershipProvider.FindUsersByName
'
Public Overrides Function FindUsersByName(ByVal usernameToMatch As String, _
ByVal pageIndex As Integer, _
ByVal pageSize As Integer, _
ByRef totalRecords As Integer) _
As MembershipUserCollection
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Count(*) FROM " & tableName & " " & _
"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 = Nothing
Try
conn.Open()
totalRecords = CInt(cmd.ExecuteScalar())
If totalRecords <= 0 Then Return users
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," & _
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " & _
" FROM " & tableName & " " & _
" WHERE Username LIKE ? AND ApplicationName = ? " & _
" ORDER BY Username Asc"
reader = cmd.ExecuteReader()
Dim counter As Integer = 0
Dim startIndex As Integer = pageSize * pageIndex
Dim endIndex As Integer = startIndex + pageSize - 1
Do While reader.Read()
If counter >= startIndex Then
Dim u As MembershipUser = GetUserFromReader(reader)
users.Add(u)
End If
If counter >= endIndex Then cmd.Cancel()
counter += 1
Loop
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "FindUsersByName")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
If Not reader Is Nothing Then reader.Close()
conn.Close()
End Try
Return users
End Function
'
' MembershipProvider.FindUsersByEmail
'
Public Overrides Function FindUsersByEmail(ByVal emailToMatch As String, _
ByVal pageIndex As Integer, _
ByVal pageSize As Integer, _
ByRef totalRecords As Integer) _
As MembershipUserCollection
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Count(*) FROM " & tableName & " " & _
"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 Then Return users
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," & _
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " & _
" FROM " & tableName & " " & _
" WHERE Email LIKE ? AND ApplicationName = ? " & _
" ORDER BY Username Asc"
reader = cmd.ExecuteReader()
Dim counter As Integer = 0
Dim startIndex As Integer = pageSize * pageIndex
Dim endIndex As Integer = startIndex + pageSize - 1
Do While reader.Read()
If counter >= startIndex Then
Dim u As MembershipUser = GetUserFromReader(reader)
users.Add(u)
End If
If counter >= endIndex Then cmd.Cancel()
counter += 1
Loop
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "FindUsersByEmail")
Throw New ProviderException(exceptionMessage)
Else
Throw e
End If
Finally
If Not reader Is Nothing Then reader.Close()
conn.Close()
End Try
Return users
End Function
'
' 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.
'
Private Sub WriteToEventLog(ByVal e As Exception, ByVal action As String)
Dim log As EventLog = New EventLog()
log.Source = eventSource
log.Log = eventLog
Dim message As String = "An exception occurred communicating with the data source." & vbCrLf & vbCrLf
message &= "Action: " & action & vbCrLf & vbCrLf
message &= "Exception: " & e.ToString()
log.WriteEnTry(message)
End Sub
End Class
End Namespace
WEB FILE
--------------
<configuration>
<location path="CreateUser.aspx">
<system.web>
<authorization>
<allow users="?">
<location path="RetrievePassword.aspx">
<system.web>
<authorization>
<allow users="?">
<connectionstrings>
<add name="OdbcServices" connectionstring="DSN=LocalSQL;">
<system.web>
<authentication mode="Forms">
<forms loginurl="login.aspx" name=".ASPXFORMSAUTH">
<authorization>
<deny users="?">
<machinekey validationkey="C50B3C89CB21F4F1422FF158A5B42D0E8DB8CB5CDA1742572A487D9401E3400267682B202B746511891C1BAF47F8D25C07F6C39A104696DB51F17C529AD3CABE" decryptionkey="8A9BE8FD67AF6979E7D20198CFEA50DD3D3799C77AF2B72F" validation="SHA1">
<membership defaultprovider="OdbcProvider" userisonlinetimewindow="15">
<providers>
<add name="OdbcProvider" type="Samples.AspNet.Membership.OdbcMembershipProvider" connectionstringname="OdbcServices" enablepasswordretrieval="true" enablepasswordreset="true" requiresquestionandanswer="true" writeexceptionstoeventlog="true">
<compilation debug="true">
|
|
|
|
|
do you have a a code to use webparts with mysql?
|
|
|
|
|