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(); }