public override void Teardown()
{
MySqlCommand cmd = new MySqlCommand("DROP DATABASE IF EXISTS `modeldb`", rootConn);
cmd.ExecuteNonQuery();
base.Teardown();
}
/// <summary>
/// Creates the or fetch application id.
/// </summary>
/// <param name="applicationName">Name of the application.</param>
/// <param name="applicationId">The application id.</param>
/// <param name="applicationDesc">The application desc.</param>
/// <param name="connection">The connection.</param>
public int EnsureId(MySqlConnection connection)
{
// first try and retrieve the existing id
if (FetchId(connection) <= 0)
{
MySqlCommand cmd = new MySqlCommand(
"INSERT INTO my_aspnet_Applications VALUES (NULL, @appName, @appDesc)", connection);
cmd.Parameters.AddWithValue("@appName", Name);
cmd.Parameters.AddWithValue("@appDesc", Description);
int recordsAffected = cmd.ExecuteNonQuery();
if (recordsAffected != 1)
throw new ProviderException(Resources.UnableToCreateApplication);
Id = Convert.ToInt32(cmd.LastInsertedId);
}
return Id;
}
public override void Setup()
{
base.Setup();
ResourceManager r = new ResourceManager("MariaDB.Data.Entity.Tests.Properties.Resources", typeof(BaseEdmTest).Assembly);
string schema = r.GetString("schema");
MySqlScript script = new MySqlScript(conn);
script.Query = schema;
script.Execute();
// now create our procs
schema = r.GetString("procs");
script = new MySqlScript(conn);
script.Delimiter = "$$";
script.Query = schema;
script.Execute();
MySqlCommand cmd = new MySqlCommand("DROP DATABASE IF EXISTS `modeldb`", rootConn);
cmd.ExecuteNonQuery();
}
/// <summary>
/// When overridden in a derived class, deletes all user-profile data
/// for profiles in which the last activity date occurred before the
/// specified date.
/// </summary>
/// <param name="authenticationOption">One of the
/// <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/>
/// values, specifying whether anonymous, authenticated, or both
/// types of profiles are deleted.</param>
/// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/>
/// that identifies which user profiles are considered inactive. If the
/// <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/>
/// value of a user profile occurs on or before this date and time, the
/// profile is considered inactive.</param>
/// <returns>
/// The number of profiles deleted from the data source.
/// </returns>
public override int DeleteInactiveProfiles(
ProfileAuthenticationOption authenticationOption,
DateTime userInactiveSinceDate)
{
using (MySqlConnection c = new MySqlConnection(connectionString))
{
c.Open();
MySqlCommand queryCmd = new MySqlCommand(
@"SELECT * FROM my_aspnet_Users
WHERE applicationId=@appId AND
lastActivityDate < @lastActivityDate",
c);
queryCmd.Parameters.AddWithValue("@appId", app.FetchId(c));
queryCmd.Parameters.AddWithValue("@lastActivityDate", userInactiveSinceDate);
if (authenticationOption == ProfileAuthenticationOption.Anonymous)
queryCmd.CommandText += " AND isAnonymous = 1";
else if (authenticationOption == ProfileAuthenticationOption.Authenticated)
queryCmd.CommandText += " AND isAnonymous = 0";
MySqlCommand deleteCmd = new MySqlCommand(
"DELETE FROM my_aspnet_Profiles WHERE userId = @userId", c);
deleteCmd.Parameters.Add("@userId", MySqlDbType.UInt64);
List<ulong> uidList = new List<ulong>();
using (MySqlDataReader reader = queryCmd.ExecuteReader())
{
while (reader.Read())
uidList.Add(reader.GetUInt64("userId"));
}
int count = 0;
foreach (ulong uid in uidList)
{
deleteCmd.Parameters[0].Value = uid;
count += deleteCmd.ExecuteNonQuery();
}
return count;
}
}
/// <summary>
/// Execute the load operation
/// </summary>
/// <returns>The number of rows inserted.</returns>
public int Load()
{
bool openedConnection = false;
if (Connection == null)
throw new InvalidOperationException(ResourceStrings.ConnectionNotSet);
// next we open up the connection if it is not already open
if (connection.State != ConnectionState.Open)
{
openedConnection = true;
connection.Open();
}
try
{
string sql = BuildSqlCommand();
MySqlCommand cmd = new MySqlCommand(sql, Connection);
cmd.CommandTimeout = Timeout;
return cmd.ExecuteNonQuery();
}
finally
{
if (openedConnection)
connection.Close();
}
}
protected override void DbDeleteDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
{
if (connection == null)
throw new ArgumentNullException("connection");
MySqlConnection conn = connection as MySqlConnection;
if (conn == null)
throw new ArgumentException(Resources.ConnectionMustBeOfTypeMySqlConnection, "connection");
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
builder.ConnectionString = conn.ConnectionString;
string dbName = builder.Database;
builder.Database = null;
using (MySqlConnection c = new MySqlConnection(builder.ConnectionString))
{
c.Open();
MySqlCommand cmd = new MySqlCommand(String.Format("DROP DATABASE IF EXISTS `{0}`", dbName), c);
if (commandTimeout.HasValue)
cmd.CommandTimeout = commandTimeout.Value;
cmd.ExecuteNonQuery();
}
}
/// <include file='docs/MySqlTransaction.xml' path='docs/Rollback/*'/>
public override void Rollback()
{
if (conn == null || (conn.State != ConnectionState.Open && !conn.SoftClosed))
throw new InvalidOperationException("Connection must be valid and open to rollback transaction");
if (!open)
throw new InvalidOperationException("Transaction has already been rolled back or is not pending");
MySqlCommand cmd = new MySqlCommand("ROLLBACK", conn);
cmd.ExecuteNonQuery();
open = false;
}
/// <summary>
/// Deletes the role.
/// </summary>
/// <param name="rolename">The rolename.</param>
/// <param name="throwOnPopulatedRole">if set to <c>true</c> [throw on populated role].</param>
/// <returns>true if the role was successfully deleted; otherwise, false. </returns>
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlTransaction txn = null;
try
{
if (!(RoleExists(rolename)))
throw new ProviderException(Resources.RoleNameNotFound);
if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)
throw new ProviderException(Resources.CannotDeleteAPopulatedRole);
connection.Open();
txn = connection.BeginTransaction();
// first delete all the user/role mappings with that roleid
MySqlCommand cmd = new MySqlCommand(
@"DELETE uir FROM my_aspnet_UsersInRoles uir JOIN
my_aspnet_Roles r ON uir.roleId=r.id
WHERE r.name LIKE @rolename AND r.applicationId=@appId", connection);
cmd.Parameters.AddWithValue("@rolename", rolename);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
cmd.ExecuteNonQuery();
// now delete the role itself
cmd.CommandText = @"DELETE FROM my_aspnet_Roles WHERE name=@rolename
AND applicationId=@appId";
cmd.ExecuteNonQuery();
txn.Commit();
}
catch (Exception ex)
{
if (txn != null)
txn.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "DeleteRole");
throw;
}
}
return true;
}
internal static void DeleteUserData(MySqlConnection connection, int userId)
{
MySqlCommand cmd = new MySqlCommand(
"DELETE FROM my_aspnet_UsersInRoles WHERE userId=@userId", connection);
cmd.Parameters.AddWithValue("@userId", userId);
cmd.ExecuteNonQuery();
}
/// <summary>
/// Adds a new membership user to the data source.
/// </summary>
/// <param name="username">The user name for the new user.</param>
/// <param name="password">The password for the new user.</param>
/// <param name="email">The e-mail address for the new user.</param>
/// <param name="passwordQuestion">The password question for the new user.</param>
/// <param name="passwordAnswer">The password answer for the new user</param>
/// <param name="isApproved">Whether or not the new user is approved to be validated.</param>
/// <param name="providerUserKey">The unique identifier from the membership data source for the user.</param>
/// <param name="status">A <see cref="T:System.Web.Security.MembershipCreateStatus"/> enumeration value indicating whether the user was created successfully.</param>
/// <returns>
/// A <see cref="T:System.Web.Security.MembershipUser"/> object populated with the information for the newly created user.
/// </returns>
public override 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;
return null;
}
if (RequiresUniqueEmail && !String.IsNullOrEmpty(GetUserNameByEmail(email)))
{
status = MembershipCreateStatus.DuplicateEmail;
return null;
}
ValidateQA(passwordQuestion, passwordAnswer);
// now try to validate the password
if (!ValidatePassword(password, "password", false))
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// now check to see if we already have a member by this name
MembershipUser u = GetUser(username, false);
if (u != null)
{
status = MembershipCreateStatus.DuplicateUserName;
return null;
}
string passwordKey = GetPasswordKey();
DateTime createDate = DateTime.Now;
MySqlTransaction transaction = null;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
connection.Open();
transaction = connection.BeginTransaction();
// either create a new user or fetch the existing user id
int userId = SchemaManager.CreateOrFetchUserId(connection, username,
app.EnsureId(connection), true);
MySqlCommand cmd = new MySqlCommand(
@"INSERT INTO my_aspnet_Membership
VALUES(@userId, @email, @comment, @password, @passwordKey,
@passwordFormat, @passwordQuestion, @passwordAnswer,
@isApproved, @lastActivityDate, @lastLoginDate,
@lastPasswordChangedDate, @creationDate,
@isLockedOut, @lastLockedOutDate, @failedPasswordAttemptCount,
@failedPasswordAttemptWindowStart, @failedPasswordAnswerAttemptCount,
@failedPasswordAnswerAttemptWindowStart)",
connection);
cmd.Parameters.AddWithValue("@userId", userId);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@comment", "");
cmd.Parameters.AddWithValue("@password",
EncodePassword(password, passwordKey, PasswordFormat));
cmd.Parameters.AddWithValue("@passwordKey", passwordKey);
cmd.Parameters.AddWithValue("@passwordFormat", PasswordFormat);
cmd.Parameters.AddWithValue("@passwordQuestion", passwordQuestion);
cmd.Parameters.AddWithValue("@passwordAnswer",
EncodePassword(passwordAnswer, passwordKey, PasswordFormat));
cmd.Parameters.AddWithValue("@isApproved", isApproved);
cmd.Parameters.AddWithValue("@lastActivityDate", createDate);
cmd.Parameters.AddWithValue("@lastLoginDate", createDate);
cmd.Parameters.AddWithValue("@lastPasswordChangedDate", createDate);
cmd.Parameters.AddWithValue("@creationDate", createDate);
cmd.Parameters.AddWithValue("@isLockedOut", false);
cmd.Parameters.AddWithValue("@lastLockedOutDate", createDate);
cmd.Parameters.AddWithValue("@failedPasswordAttemptCount", 0);
cmd.Parameters.AddWithValue("@failedPasswordAttemptWindowStart", createDate);
cmd.Parameters.AddWithValue("@failedPasswordAnswerAttemptCount", 0);
cmd.Parameters.AddWithValue("@failedPasswordAnswerAttemptWindowStart", createDate);
int recAdded = cmd.ExecuteNonQuery();
if (recAdded > 0)
status = MembershipCreateStatus.Success;
else
status = MembershipCreateStatus.UserRejected;
transaction.Commit();
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "CreateUser");
status = MembershipCreateStatus.ProviderError;
if (transaction != null)
transaction.Rollback();
return null;
}
}
return GetUser(username, false);
}
/// <summary>
/// Removes a user from the membership data source.
/// </summary>
/// <param name="username">The name of the user to delete.</param>
/// <param name="deleteAllRelatedData">true to delete data related to the user from the database; false to leave data related to the user in the database.</param>
/// <returns>
/// true if the user was successfully deleted; otherwise, false.
/// </returns>
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
try
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
int userId = GetUserId(conn, username);
if (-1 == userId) return false;
// if we are supposed to delete all related data, then delegate that to those providers
if (deleteAllRelatedData)
{
MySQLRoleProvider.DeleteUserData(conn, userId);
MySQLProfileProvider.DeleteUserData(conn, userId);
}
string sql = @"DELETE {0}m
FROM my_aspnet_Users u, my_aspnet_Membership m
WHERE u.id=m.userId AND u.id=@userId";
MySqlCommand cmd = new MySqlCommand(
String.Format(sql, deleteAllRelatedData ? "u," : ""), conn);
cmd.Parameters.AddWithValue("@appId", app.FetchId(conn));
cmd.Parameters.AddWithValue("@userId", userId);
return cmd.ExecuteNonQuery() > 0;
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "DeleteUser");
throw new ProviderException(exceptionMessage, e);
}
}
/// <summary>
/// Changes the password.
/// </summary>
/// <param name="username">The username.</param>
/// <param name="oldPassword">The old password.</param>
/// <param name="newPassword">The new password.</param>
/// <returns>true if the password was updated successfully, false if the supplied old password
/// is invalid, the user is locked out, or the user does not exist in the database.</returns>
public override bool ChangePassword(string username, string oldPassword, string newPassword)
{
// this will return false if the username doesn't exist
if (!(ValidateUser(username, oldPassword)))
return false;
ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
OnValidatingPassword(args);
if (args.Cancel)
{
if (!(args.FailureInformation == null))
throw args.FailureInformation;
else
throw new ProviderException(Resources.ChangePasswordCanceled);
}
// validate the password according to current guidelines
if (!ValidatePassword(newPassword, "newPassword", true))
return false;
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// retrieve the existing key and format for this user
string passwordKey;
MembershipPasswordFormat passwordFormat;
int userId = GetUserId(connection, username);
GetPasswordInfo(connection, userId, out passwordKey, out passwordFormat);
MySqlCommand cmd = new MySqlCommand(
@"UPDATE my_aspnet_Membership
SET Password = @pass, LastPasswordChangedDate = @lastPasswordChangedDate
WHERE userId=@userId", connection);
cmd.Parameters.AddWithValue("@pass",
EncodePassword(newPassword, passwordKey, passwordFormat));
cmd.Parameters.AddWithValue("@lastPasswordChangedDate", DateTime.Now);
cmd.Parameters.AddWithValue("@userId", userId);
return cmd.ExecuteNonQuery() > 0;
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "ChangePassword");
throw new ProviderException(exceptionMessage, e);
}
}
/// <summary>
/// Changes the password question and answer.
/// </summary>
/// <param name="username">The username.</param>
/// <param name="password">The password.</param>
/// <param name="newPwdQuestion">The new password question.</param>
/// <param name="newPwdAnswer">The new password answer.</param>
/// <returns>true if the update was successful; otherwise, false. A value of false is
/// also returned if the password is incorrect, the user is locked out, or the user
/// does not exist in the database.</returns>
public override bool ChangePasswordQuestionAndAnswer(string username,
string password, string newPwdQuestion, string newPwdAnswer)
{
// this handles the case where the username doesn't exist
if (!(ValidateUser(username, password)))
return false;
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string passwordKey;
MembershipPasswordFormat passwordFormat;
int userId = GetUserId(connection, username);
GetPasswordInfo(connection, userId, out passwordKey, out passwordFormat);
MySqlCommand cmd = new MySqlCommand(
@"UPDATE my_aspnet_Membership
SET PasswordQuestion = @passwordQuestion, PasswordAnswer = @passwordAnswer
WHERE userId=@userId", connection);
cmd.Parameters.AddWithValue("@passwordQuestion", newPwdQuestion);
cmd.Parameters.AddWithValue("@passwordAnswer",
EncodePassword(newPwdAnswer, passwordKey, passwordFormat));
cmd.Parameters.AddWithValue("@userId", userId);
return cmd.ExecuteNonQuery() > 0;
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "ChangePasswordQuestionAndAnswer");
throw new ProviderException(exceptionMessage, e);
}
}
private void UpdateFailureCount(int userId, string failureType, MySqlConnection connection)
{
MySqlCommand cmd = new MySqlCommand(
@"SELECT FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart FROM my_aspnet_Membership
WHERE userId=@userId", connection);
cmd.Parameters.AddWithValue("@userId", userId);
DateTime windowStart = new DateTime();
int failureCount = 0;
try
{
using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (!reader.HasRows)
throw new ProviderException(Resources.UnableToUpdateFailureCount);
reader.Read();
if (failureType == "Password")
{
failureCount = reader.GetInt32(0);
windowStart = reader.GetDateTime(1);
}
if (failureType == "PasswordAnswer")
{
failureCount = reader.GetInt32(2);
windowStart = reader.GetDateTime(3);
}
}
DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow);
if (failureCount == 0 || DateTime.Now > windowEnd)
{
if (failureType == "Password")
{
cmd.CommandText =
@"UPDATE my_aspnet_Membership
SET FailedPasswordAttemptCount = @count,
FailedPasswordAttemptWindowStart = @windowStart
WHERE userId=@userId";
}
if (failureType == "PasswordAnswer")
{
cmd.CommandText =
@"UPDATE my_aspnet_Membership
SET FailedPasswordAnswerAttemptCount = @count,
FailedPasswordAnswerAttemptWindowStart = @windowStart
WHERE userId = @userId";
}
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@count", 1);
cmd.Parameters.AddWithValue("@windowStart", DateTime.Now);
cmd.Parameters.AddWithValue("@userId", userId);
if (cmd.ExecuteNonQuery() < 0)
throw new ProviderException(Resources.UnableToUpdateFailureCount);
}
else
{
failureCount += 1;
if (failureCount >= MaxInvalidPasswordAttempts)
{
cmd.CommandText =
@"UPDATE my_aspnet_Membership SET IsLockedOut = @isLockedOut,
LastLockedOutDate = @lastLockedOutDate WHERE userId=@userId";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@isLockedOut", true);
cmd.Parameters.AddWithValue("@lastLockedOutDate", DateTime.Now);
cmd.Parameters.AddWithValue("@userId", userId);
if (cmd.ExecuteNonQuery() < 0)
throw new ProviderException(Resources.UnableToLockOutUser);
}
else
{
if (failureType == "Password")
{
cmd.CommandText =
@"UPDATE my_aspnet_Membership
SET FailedPasswordAttemptCount = @count WHERE userId=@userId";
}
if (failureType == "PasswordAnswer")
{
cmd.CommandText =
@"UPDATE my_aspnet_Membership
SET FailedPasswordAnswerAttemptCount = @count
WHERE userId=@userId";
}
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@count", failureCount);
cmd.Parameters.AddWithValue("@userId", userId);
if (cmd.ExecuteNonQuery() < 0)
throw new ProviderException("Unable to update failure count.");
}
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "UpdateFailureCount");
throw new ProviderException(exceptionMessage, e);
}
}
/// <summary>
/// Verifies that the specified user name and password exist in the data source.
/// </summary>
/// <param name="username">The name of the user to validate.</param>
/// <param name="password">The password for the specified user.</param>
/// <returns>
/// true if the specified username and password are valid; otherwise, false.
/// </returns>
public override bool ValidateUser(string username, string password)
{
bool isValid = false;
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// first get the user id. If that is -1, then the user doesn't exist
// so we just return false since we can't bump any counters
int userId = GetUserId(connection, username);
if (-1 == userId) return false;
string sql = @"SELECT Password, PasswordKey, PasswordFormat, IsApproved,
Islockedout FROM my_aspnet_Membership WHERE userId=@userId";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@userId", userId);
using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (!reader.HasRows) return false;
reader.Read();
if (reader.GetBoolean("IsLockedOut")) return false;
string pwd = reader.GetString(0);
string passwordKey = reader.GetString(1);
MembershipPasswordFormat format = (MembershipPasswordFormat)
reader.GetInt32(2);
bool isApproved = reader.GetBoolean(3);
reader.Close();
if (!CheckPassword(password, pwd, passwordKey, format))
UpdateFailureCount(userId, "Password", connection);
else if (isApproved)
{
isValid = true;
DateTime currentDate = DateTime.Now;
MySqlCommand updateCmd = new MySqlCommand(
@"UPDATE my_aspnet_Membership m, my_aspnet_Users u
SET m.LastLoginDate = @lastLoginDate, u.lastActivityDate = @date,
m.LastActivityDate=@date
WHERE m.userId=@userid AND u.id=@userid", connection);
updateCmd.Parameters.AddWithValue("@lastLoginDate", currentDate);
updateCmd.Parameters.AddWithValue("@date", currentDate);
updateCmd.Parameters.AddWithValue("@userid", userId);
updateCmd.ExecuteNonQuery();
}
}
return isValid;
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "ValidateUser");
throw new ProviderException(exceptionMessage, e);
}
}
/// <summary>
/// Updates information about a user in the data source.
/// </summary>
/// <param name="user">A <see cref="T:System.Web.Security.MembershipUser"/> object
/// that represents the user to update and the updated information for the user.</param>
public override void UpdateUser(MembershipUser user)
{
try
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
int userId = GetUserId(conn, user.UserName);
if (-1 == userId)
throw new ProviderException(Resources.UsernameNotFound);
string sql = @"UPDATE my_aspnet_Membership m, my_aspnet_Users u
SET m.Email=@email, m.Comment=@comment, m.IsApproved=@isApproved,
m.LastLoginDate=@lastLoginDate, u.lastActivityDate=@lastActivityDate,
m.LastActivityDate=@lastActivityDate
WHERE m.userId=u.id AND u.name LIKE @name AND u.applicationId=@appId";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@Email", user.Email);
cmd.Parameters.AddWithValue("@Comment", user.Comment);
cmd.Parameters.AddWithValue("@isApproved", user.IsApproved);
cmd.Parameters.AddWithValue("@lastLoginDate", user.LastLoginDate);
cmd.Parameters.AddWithValue("@lastActivityDate", user.LastActivityDate);
cmd.Parameters.AddWithValue("@name", user.UserName);
cmd.Parameters.AddWithValue("@appId", app.FetchId(conn));
cmd.ExecuteNonQuery();
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "UpdateUser");
throw new ProviderException(exceptionMessage);
}
}
/// <summary>
/// Creates the or fetch user id.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="username">The username.</param>
/// <param name="applicationId">The application id.</param>
/// <param name="authenticated">if set to <c>true</c> [authenticated].</param>
/// <returns></returns>
internal static int CreateOrFetchUserId(MySqlConnection connection, string username,
int applicationId, bool authenticated)
{
Debug.Assert(applicationId > 0);
// first attempt to fetch an existing user id
MySqlCommand cmd = new MySqlCommand(@"SELECT id FROM my_aspnet_Users
WHERE applicationId = @appId AND name = @name", connection);
cmd.Parameters.AddWithValue("@appId", applicationId);
cmd.Parameters.AddWithValue("@name", username);
object userId = cmd.ExecuteScalar();
if (userId != null) return (int)userId;
cmd.CommandText = @"INSERT INTO my_aspnet_Users VALUES
(NULL, @appId, @name, @isAnon, Now())";
cmd.Parameters.AddWithValue("@isAnon", !authenticated);
int recordsAffected = cmd.ExecuteNonQuery();
if (recordsAffected != 1)
throw new ProviderException(Resources.UnableToCreateUser);
cmd.CommandText = "SELECT LAST_INSERT_ID()";
return Convert.ToInt32(cmd.ExecuteScalar());
}
/// <summary>
/// Gets user information from the data source based on the unique identifier for the membership user. Provides an option to update the last-activity date/time stamp for the user.
/// </summary>
/// <param name="providerUserKey">The unique identifier for the membership user to get information for.</param>
/// <param name="userIsOnline">true to update the last-activity date/time stamp for the user; false to return user information without updating the last-activity date/time stamp for the user.</param>
/// <returns>
/// A <see cref="T:System.Web.Security.MembershipUser"/> object populated with the specified user's information from the data source.
/// </returns>
public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
{
MySqlTransaction txn = null;
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
txn = connection.BeginTransaction();
MySqlCommand cmd = new MySqlCommand("", connection);
cmd.Parameters.AddWithValue("@userId", providerUserKey);
if (userIsOnline)
{
cmd.CommandText =
@"UPDATE my_aspnet_Users SET lastActivityDate = @date WHERE id=@userId";
cmd.Parameters.AddWithValue("@date", DateTime.Now);
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE my_aspnet_Membership SET LastActivityDate=@date WHERE userId=@userId";
cmd.ExecuteNonQuery();
}
cmd.CommandText = @"SELECT m.*,u.name
FROM my_aspnet_Membership m JOIN my_aspnet_Users u ON m.userId=u.id
WHERE u.id=@userId";
MembershipUser user;
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (!reader.Read()) return null;
user = GetUserFromReader(reader);
}
txn.Commit();
return user;
}
}
catch (MySqlException e)
{
if (txn != null)
txn.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetUser(Object, Boolean)");
throw new ProviderException(exceptionMessage);
}
}
/// <summary>
/// Creates the role.
/// </summary>
/// <param name="rolename">The rolename.</param>
public override void CreateRole(string rolename)
{
if (rolename.IndexOf(',') != -1)
throw new ArgumentException(Resources.InvalidCharactersInUserName);
if (RoleExists(rolename))
throw new ProviderException(Resources.RoleNameAlreadyExists);
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
connection.Open();
MySqlCommand cmd = new MySqlCommand(
@"INSERT INTO my_aspnet_Roles Values(NULL, @appId, @name)", connection);
cmd.Parameters.AddWithValue("@appId", app.EnsureId(connection));
cmd.Parameters.AddWithValue("@name", rolename);
cmd.ExecuteNonQuery();
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "CreateRole");
throw;
}
}
}
/// <summary>
/// Resets a user's password to a new, automatically generated password.
/// </summary>
/// <param name="username">The user to reset the password for.</param>
/// <param name="answer">The password answer for the specified user.</param>
/// <returns>The new password for the specified user.</returns>
public override string ResetPassword(string username, string answer)
{
if (!(EnablePasswordReset))
throw new NotSupportedException(Resources.PasswordResetNotEnabled);
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// fetch the userid first
int userId = GetUserId(connection, username);
if (-1 == userId)
throw new ProviderException(Resources.UsernameNotFound);
if (answer == null && RequiresQuestionAndAnswer)
{
UpdateFailureCount(userId, "PasswordAnswer", connection);
throw new ProviderException(Resources.PasswordRequiredForReset);
}
string newPassword = Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters);
ValidatePasswordEventArgs Args = new ValidatePasswordEventArgs(username, newPassword, true);
OnValidatingPassword(Args);
if (Args.Cancel)
{
if (!(Args.FailureInformation == null))
throw Args.FailureInformation;
else
throw new MembershipPasswordException(Resources.PasswordResetCanceledNotValid);
}
MySqlCommand cmd = new MySqlCommand(@"SELECT PasswordAnswer,
PasswordKey, PasswordFormat, IsLockedOut
FROM my_aspnet_Membership WHERE userId=@userId", connection);
cmd.Parameters.AddWithValue("@userId", userId);
string passwordKey = String.Empty;
MembershipPasswordFormat format;
using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
reader.Read();
if (reader.GetBoolean("IsLockedOut"))
throw new MembershipPasswordException(Resources.UserIsLockedOut);
object passwordAnswer = reader.GetValue(reader.GetOrdinal("PasswordAnswer"));
passwordKey = reader.GetString("PasswordKey");
format = (MembershipPasswordFormat)reader.GetByte("PasswordFormat");
reader.Close();
if (RequiresQuestionAndAnswer)
{
if (!CheckPassword(answer, (string)passwordAnswer, passwordKey, format))
{
UpdateFailureCount(userId, "PasswordAnswer", connection);
throw new MembershipPasswordException(Resources.IncorrectPasswordAnswer);
}
}
}
cmd.CommandText = @"UPDATE my_aspnet_Membership
SET Password = @pass, LastPasswordChangedDate = @lastPassChange
WHERE userId=@userId";
cmd.Parameters.AddWithValue("@pass",
EncodePassword(newPassword, passwordKey, format));
cmd.Parameters.AddWithValue("@lastPassChange", DateTime.Now);
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected != 1)
throw new MembershipPasswordException(Resources.ErrorResettingPassword);
return newPassword;
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "ResetPassword");
throw new ProviderException(exceptionMessage, e);
}
}
/// <summary>
/// Removes the users from roles.
/// </summary>
/// <param name="usernames">The usernames.</param>
/// <param name="rolenames">The rolenames.</param>
public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames)
{
if (rolenames == null || rolenames.Length == 0) return;
if (usernames == null || usernames.Length == 0) return;
foreach (string rolename in rolenames)
{
if (!(RoleExists(rolename)))
throw new ProviderException(Resources.RoleNameNotFound);
}
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
if (!(IsUserInRole(username, rolename)))
throw new ProviderException(Resources.UserNotInRole);
}
}
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlTransaction txn = null;
try
{
connection.Open();
txn = connection.BeginTransaction();
string sql = @"DELETE uir FROM my_aspnet_UsersInRoles uir
JOIN my_aspnet_Users u ON uir.userId=u.id
JOIN my_aspnet_Roles r ON uir.roleId=r.id
WHERE u.name LIKE @username AND r.name LIKE @rolename
AND u.applicationId=@appId AND r.applicationId=@appId";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.Add("@username", MySqlDbType.VarChar, 255);
cmd.Parameters.Add("@rolename", MySqlDbType.VarChar, 255);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
cmd.Parameters[0].Value = username;
cmd.Parameters[1].Value = rolename;
cmd.ExecuteNonQuery();
}
}
txn.Commit();
}
catch (MySqlException e)
{
if (txn != null)
txn.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "RemoveUsersFromRoles");
throw;
}
}
}
/// <summary>
/// Unlocks the user.
/// </summary>
/// <param name="username">The username.</param>
/// <returns>true if the membership user was successfully unlocked;
/// otherwise, false. A value of false is also returned if the user
/// does not exist in the database. </returns>
public override bool UnlockUser(string username)
{
try
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
int userId = GetUserId(conn, username);
if (-1 == userId) return false;
string sql = @"UPDATE my_aspnet_Membership
SET IsLockedOut = false, LastLockedOutDate = @lastDate
WHERE userId=@userId";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@lastDate", DateTime.Now);
cmd.Parameters.AddWithValue("@userId", userId);
return cmd.ExecuteNonQuery() > 0;
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "UnlockUser");
throw new ProviderException(exceptionMessage, e);
}
}
/// <summary>
/// Adds the users to roles.
/// </summary>
/// <param name="usernames">The usernames.</param>
/// <param name="rolenames">The rolenames.</param>
public override void AddUsersToRoles(string[] usernames, string[] rolenames)
{
if (rolenames == null || rolenames.Length == 0) return;
if (usernames == null || usernames.Length == 0) return;
foreach (string rolename in rolenames)
{
if (String.IsNullOrEmpty(rolename))
throw new ArgumentException(Resources.IllegalRoleName, "rolenames");
if (!RoleExists(rolename))
throw new ProviderException(Resources.RoleNameNotFound);
}
foreach (string username in usernames)
{
if (String.IsNullOrEmpty(username))
throw new ArgumentException(Resources.IllegalUserName, "usernames");
if (username.IndexOf(',') != -1)
throw new ArgumentException(Resources.InvalidCharactersInUserName);
foreach (string rolename in rolenames)
{
if (IsUserInRole(username, rolename))
throw new ProviderException(Resources.UserIsAlreadyInRole);
}
}
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlTransaction txn = null;
try
{
connection.Open();
txn = connection.BeginTransaction();
MySqlCommand cmd = new MySqlCommand(
"INSERT INTO my_aspnet_UsersInRoles VALUES(@userId, @roleId)", connection);
cmd.Parameters.Add("@userId", MySqlDbType.Int32);
cmd.Parameters.Add("@roleId", MySqlDbType.Int32);
foreach (string username in usernames)
{
// either create a new user or fetch the existing user id
int userId = SchemaManager.CreateOrFetchUserId(connection,
username, app.FetchId(connection), true);
foreach (string rolename in rolenames)
{
int roleId = GetRoleId(connection, rolename);
cmd.Parameters[0].Value = userId;
cmd.Parameters[1].Value = roleId;
cmd.ExecuteNonQuery();
}
}
txn.Commit();
}
catch (Exception ex)
{
if (txn != null)
txn.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "AddUsersToRoles");
throw;
}
}
}
/// <summary>
/// Reset SQL_SELECT_LIMIT that could have been modified by CommandBehavior.
/// </summary>
internal void ResetSqlSelectLimit()
{
// if we are supposed to reset the sql select limit, do that here
if (resetSqlSelect)
{
resetSqlSelect = false;
MySqlCommand command = new MySqlCommand("SET SQL_SELECT_LIMIT=DEFAULT", connection);
command.internallyCreated = true;
command.ExecuteNonQuery();
}
}
/// <include file='docs/MySqlTransaction.xml' path='docs/Commit/*'/>
public override void Commit()
{
if (conn == null || (conn.State != ConnectionState.Open && !conn.SoftClosed))
throw new InvalidOperationException("Connection must be valid and open to commit transaction");
if (!open)
throw new InvalidOperationException("Transaction has already been committed or is not pending");
MySqlCommand cmd = new MySqlCommand("COMMIT", conn);
cmd.ExecuteNonQuery();
open = false;
}
public void CancelQuery(int timeout)
{
MySqlConnectionStringBuilder cb = new MySqlConnectionStringBuilder(
Settings.ConnectionString);
cb.Pooling = false;
cb.AutoEnlist = false;
cb.ConnectionTimeout = (uint)timeout;
using (MySqlConnection c = new MySqlConnection(cb.ConnectionString))
{
c.isKillQueryConnection = true;
c.Open();
string commandText = "KILL QUERY " + ServerThread;
MySqlCommand cmd = new MySqlCommand(commandText, c);
cmd.CommandTimeout = timeout;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Executes this instance.
/// </summary>
/// <returns>The number of statements executed as part of the script.</returns>
public int Execute()
{
bool openedConnection = false;
if (connection == null)
throw new InvalidOperationException(ResourceStrings.ConnectionNotSet);
if (query == null || query.Length == 0)
return 0;
// next we open up the connetion if it is not already open
if (connection.State != ConnectionState.Open)
{
openedConnection = true;
connection.Open();
}
// since we don't allow setting of parameters on a script we can
// therefore safely allow the use of user variables. no one should be using
// this connection while we are using it so we can temporarily tell it
// to allow the use of user variables
bool allowUserVars = connection.Settings.AllowUserVariables;
connection.Settings.AllowUserVariables = true;
try
{
string mode = connection.driver.Property("sql_mode");
mode = mode.ToUpper();
bool ansiQuotes = mode.IndexOf("ANSI_QUOTES") != -1;
bool noBackslashEscapes = mode.IndexOf("NO_BACKSLASH_ESCAPES") != -1;
// first we break the query up into smaller queries
List<ScriptStatement> statements = BreakIntoStatements(ansiQuotes, noBackslashEscapes);
int count = 0;
MySqlCommand cmd = new MySqlCommand(null, connection);
foreach (ScriptStatement statement in statements)
{
if (String.IsNullOrEmpty(statement.text)) continue;
cmd.CommandText = statement.text;
try
{
cmd.ExecuteNonQuery();
count++;
OnQueryExecuted(statement);
}
catch (Exception ex)
{
if (Error == null)
throw;
if (!OnScriptError(ex))
break;
}
}
OnScriptCompleted();
return count;
}
finally
{
connection.Settings.AllowUserVariables = allowUserVars;
if (openedConnection)
{
connection.Close();
}
}
}
/// <summary>
/// Sets the values of the specified group of property settings.
/// </summary>
/// <param name="context">A <see cref="T:System.Configuration.SettingsContext"/> describing the current application usage.</param>
/// <param name="collection">A <see cref="T:System.Configuration.SettingsPropertyValueCollection"/> representing the group of property settings to set.</param>
public override void SetPropertyValues(
SettingsContext context, SettingsPropertyValueCollection collection)
{
bool isAuthenticated = (bool)context["IsAuthenticated"];
string username = (string)context["UserName"];
if (String.IsNullOrEmpty(username)) return;
if (collection.Count < 1) return;
string index = String.Empty;
string stringData = String.Empty;
byte[] binaryData = null;
int count = EncodeProfileData(collection, isAuthenticated, ref index, ref stringData, ref binaryData);
if (count < 1) return;
MySqlTransaction txn = null;
// save the encoded profile data to the database
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
connection.Open();
txn = connection.BeginTransaction();
// either create a new user or fetch the existing user id
int userId = SchemaManager.CreateOrFetchUserId(connection, username,
app.EnsureId(connection), isAuthenticated);
MySqlCommand cmd = new MySqlCommand(
@"INSERT INTO my_aspnet_Profiles
VALUES (@userId, @index, @stringData, @binaryData, NULL) ON DUPLICATE KEY UPDATE
valueindex=VALUES(valueindex), stringdata=VALUES(stringdata),
binarydata=VALUES(binarydata)", connection);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@userId", userId);
cmd.Parameters.AddWithValue("@index", index);
cmd.Parameters.AddWithValue("@stringData", stringData);
cmd.Parameters.AddWithValue("@binaryData", binaryData);
count = cmd.ExecuteNonQuery();
if (count == 0)
throw new Exception(Resources.ProfileUpdateFailed);
txn.Commit();
}
catch (Exception ex)
{
if (txn != null)
txn.Rollback();
throw new ProviderException(Resources.ProfileUpdateFailed, ex);
}
}
}
/// <summary>
/// When overridden in a derived class, deletes profile properties
/// and information for profiles that match the supplied list of user names.
/// </summary>
/// <param name="usernames">A string array of user names for
/// profiles to be deleted.</param>
/// <returns>
/// The number of profiles deleted from the data source.
/// </returns>
public override int DeleteProfiles(string[] usernames)
{
using (MySqlConnection c = new MySqlConnection(connectionString))
{
c.Open();
MySqlCommand queryCmd = new MySqlCommand(
@"SELECT * FROM my_aspnet_Users
WHERE applicationId=@appId AND name = @name", c);
queryCmd.Parameters.AddWithValue("@appId", app.FetchId(c));
queryCmd.Parameters.Add("@name", MySqlDbType.VarChar);
MySqlCommand deleteCmd = new MySqlCommand(
"DELETE FROM my_aspnet_Profiles WHERE userId = @userId", c);
deleteCmd.Parameters.Add("@userId", MySqlDbType.UInt64);
int count = 0;
foreach (string name in usernames)
{
queryCmd.Parameters[1].Value = name;
ulong uid = (ulong)queryCmd.ExecuteScalar();
deleteCmd.Parameters[0].Value = uid;
count += deleteCmd.ExecuteNonQuery();
}
return count;
}
}
private string SetUserVariables(MySqlParameterCollection parms, bool preparing)
{
StringBuilder setSql = new StringBuilder();
if (serverProvidingOutputParameters) return setSql.ToString();
string delimiter = String.Empty;
foreach (MySqlParameter p in parms)
{
if (p.Direction != ParameterDirection.InputOutput) continue;
string pName = "@" + p.BaseName;
string uName = "@" + ParameterPrefix + p.BaseName;
string sql = String.Format("SET {0}={1}", uName, pName);
if (command.Connection.Settings.AllowBatch && !preparing)
{
setSql.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", delimiter, sql);
delimiter = "; ";
}
else
{
MySqlCommand cmd = new MySqlCommand(sql, command.Connection);
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
}
}
if (setSql.Length > 0)
setSql.Append("; ");
return setSql.ToString();
}