/// <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> /// 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; } } }
protected override bool DbDatabaseExists(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(); DataTable table = c.GetSchema("Databases", new string[] { dbName }); if (table != null && table.Rows.Count == 1) return true; return false; } }
/// <summary> /// Gets the users in role. /// </summary> /// <param name="rolename">The rolename.</param> /// <returns>A string array containing the names of all the users /// who are members of the specified role. </returns> public override string[] GetUsersInRole(string rolename) { List<string> users = new List<string>(); try { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); int roleId = GetRoleId(connection, rolename); string sql = @"SELECT u.name FROM my_aspnet_Users u JOIN my_aspnet_UsersInRoles uir ON uir.userId=u.id AND uir.roleId=@roleId WHERE u.applicationId=@appId"; MySqlCommand cmd = new MySqlCommand(sql, connection); cmd.Parameters.AddWithValue("@roleId", roleId); cmd.Parameters.AddWithValue("@appId", app.FetchId(connection)); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) users.Add(reader.GetString(0)); } } return users.ToArray(); } catch (Exception ex) { if (WriteExceptionsToEventLog) WriteToEventLog(ex, "GetUsersInRole"); throw; } }
/// <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> /// 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; }
/// <summary> /// Gets a list of all the roles for the configured applicationName. /// </summary> /// <returns> /// A string array containing the names of all the roles stored in the data source for the configured applicationName. /// </returns> public override string[] GetAllRoles() { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); return GetRolesByUserName(connection, null); } }
/// <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> /// 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> /// 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> /// Gets the user name associated with the specified e-mail address. /// </summary> /// <param name="email">The e-mail address to search for.</param> /// <returns> /// The user name associated with the specified e-mail address. If no match is found, return null. /// </returns> public override string GetUserNameByEmail(string email) { try { using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); string sql = @"SELECT u.name FROM my_aspnet_Users u JOIN my_aspnet_Membership m ON m.userid=u.id WHERE m.Email = @email AND u.applicationId=@appId"; MySqlCommand cmd = new MySqlCommand(sql, conn); cmd.Parameters.AddWithValue("@email", email); cmd.Parameters.AddWithValue("@appId", app.FetchId(conn)); return (string)cmd.ExecuteScalar(); } } catch (MySqlException e) { if (WriteExceptionsToEventLog) WriteToEventLog(e, "GetUserNameByEmail"); throw new ProviderException(exceptionMessage); } }
/// <summary> /// Gets information from the data source for a user. Provides an option to update the last-activity date/time stamp for the user. /// </summary> /// <param name="username">The name of the 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(string username, bool userIsOnline) { try { int userId = -1; using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); userId = GetUserId(connection, username); if (-1 == userId) return null; } return GetUser(userId, userIsOnline); } catch (MySqlException e) { if (WriteExceptionsToEventLog) WriteToEventLog(e, "GetUser(String, Boolean)"); throw new ProviderException(exceptionMessage, e); } }
/// <summary> /// Gets the password for the specified user name from the data source. /// </summary> /// <param name="username">The user to retrieve the password for.</param> /// <param name="answer">The password answer for the user.</param> /// <returns> /// The password for the specified user name. /// </returns> public override string GetPassword(string username, string answer) { if (!EnablePasswordRetrieval) throw new ProviderException(Resources.PasswordRetrievalNotEnabled); try { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); int userId = GetUserId(connection, username); if (-1 == userId) throw new ProviderException("Username not found."); string sql = @"SELECT Password, PasswordAnswer, PasswordKey, PasswordFormat, 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)) { reader.Read(); if (reader.GetBoolean("IsLockedOut")) throw new MembershipPasswordException(Resources.UserIsLockedOut); string password = reader.GetString("Password"); string passwordAnswer = reader.GetValue(reader.GetOrdinal("PasswordAnswer")).ToString(); string passwordKey = reader.GetString("PasswordKey"); MembershipPasswordFormat format = (MembershipPasswordFormat)reader.GetInt32(3); reader.Close(); if (RequiresQuestionAndAnswer && !(CheckPassword(answer, passwordAnswer, passwordKey, format))) { UpdateFailureCount(userId, "PasswordAnswer", connection); throw new MembershipPasswordException(Resources.IncorrectPasswordAnswer); } if (PasswordFormat == MembershipPasswordFormat.Encrypted) { password = UnEncodePassword(password, format); } return password; } } } catch (MySqlException e) { if (WriteExceptionsToEventLog) WriteToEventLog(e, "GetPassword"); throw new ProviderException(exceptionMessage, e); } }
/// <summary> /// Gets the number of users currently accessing the application. /// </summary> /// <returns> /// The number of users currently accessing the application. /// </returns> public override int GetNumberOfUsersOnline() { TimeSpan onlineSpan = new TimeSpan(0, Membership.UserIsOnlineTimeWindow, 0); DateTime compareTime = DateTime.Now.Subtract(onlineSpan); try { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); MySqlCommand cmd = new MySqlCommand( @"SELECT COUNT(*) FROM my_aspnet_Membership m JOIN my_aspnet_Users u ON m.userId=u.id WHERE m.LastActivityDate > @date AND u.applicationId=@appId", connection); cmd.Parameters.AddWithValue("@date", compareTime); cmd.Parameters.AddWithValue("@appId", app.FetchId(connection)); return Convert.ToInt32(cmd.ExecuteScalar()); } } catch (MySqlException e) { if (WriteExceptionsToEventLog) WriteToEventLog(e, "GetNumberOfUsersOnline"); throw new ProviderException(exceptionMessage, e); } }
private static void UpgradeToCurrent(string connectionString, int version) { ResourceManager r = new ResourceManager("MySql.Web.Properties.Resources", typeof(SchemaManager).Assembly); if (version == Version) return; using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); for (int ver = version + 1; ver <= Version; ver++) { string schema = r.GetString(String.Format("schema{0}", ver)); MySqlScript script = new MySqlScript(connection); script.Query = schema; script.Execute(); } } }
/// <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; } }
/// <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> /// When overridden in a derived class, returns the number of profiles /// in which the last activity date occurred on or 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 returned.</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"/> of /// a user profile occurs on or before this date and time, the profile /// is considered inactive.</param> /// <returns> /// The number of profiles in which the last activity date occurred on /// or before the specified date. /// </returns> public override int GetNumberOfInactiveProfiles( ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate) { using (MySqlConnection c = new MySqlConnection(connectionString)) { c.Open(); MySqlCommand queryCmd = new MySqlCommand( @"SELECT COUNT(*) 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"; return (int)queryCmd.ExecuteScalar(); } }
/// <summary> /// Finds the users in role. /// </summary> /// <param name="rolename">The rolename.</param> /// <param name="usernameToMatch">The username to match.</param> /// <returns>A string array containing the names of all the users where the /// user name matches usernameToMatch and the user is a member of the specified role. </returns> public override string[] FindUsersInRole(string rolename, string usernameToMatch) { if (!RoleExists(rolename)) throw new ProviderException(Resources.RoleNameNotFound); List<string> users = new List<string>(); try { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); string sql = @"SELECT u.name FROM my_aspnet_UsersInRole uir JOIN my_aspnet_Users u ON uir.userId=u.id JOIN my_aspnet_Roles r ON uir.roleId=r.id WHERE r.name LIKE @rolename AND u.name LIKE @username AND u.applicationId=@appId"; MySqlCommand cmd = new MySqlCommand(sql, connection); cmd.Parameters.AddWithValue("@username", usernameToMatch); cmd.Parameters.AddWithValue("@rolename", rolename); cmd.Parameters.AddWithValue("@appId", app.FetchId(connection)); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) users.Add(reader.GetString(0)); } } return users.ToArray(); } catch (MySqlException e) { if (WriteExceptionsToEventLog) WriteToEventLog(e, "FindUsersInRole"); throw; } }
/// <summary> /// Returns the collection of settings property values for the specified application instance and settings property group. /// </summary> /// <param name="context">A <see cref="T:System.Configuration.SettingsContext"/> describing the current application use.</param> /// <param name="collection">A <see cref="T:System.Configuration.SettingsPropertyCollection"/> containing the settings property group whose values are to be retrieved.</param> /// <returns> /// A <see cref="T:System.Configuration.SettingsPropertyValueCollection"/> containing the values for the specified settings property group. /// </returns> public override SettingsPropertyValueCollection GetPropertyValues( SettingsContext context, SettingsPropertyCollection collection) { SettingsPropertyValueCollection values = new SettingsPropertyValueCollection(); if (collection.Count < 1) return values; string username = (string)context["UserName"]; foreach (SettingsProperty property in collection) { if (property.PropertyType.IsPrimitive || property.PropertyType == typeof(string)) property.SerializeAs = SettingsSerializeAs.String; else property.SerializeAs = SettingsSerializeAs.Xml; values.Add(new SettingsPropertyValue(property)); } if (String.IsNullOrEmpty(username)) return values; // retrieve encoded profile data from the database try { using (MySqlConnection c = new MySqlConnection(connectionString)) { c.Open(); MySqlCommand cmd = new MySqlCommand( @"SELECT * FROM my_aspnet_Profiles p JOIN my_aspnet_Users u ON u.id = p.userId WHERE u.applicationId = @appId AND u.name = @name", c); cmd.Parameters.AddWithValue("@appId", app.FetchId(c)); cmd.Parameters.AddWithValue("@name", username); MySqlDataAdapter da = new MySqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count > 0) DecodeProfileData(dt.Rows[0], values); return values; } } catch (Exception ex) { throw new ProviderException(Resources.UnableToRetrieveProfileData, ex); } }
/// <summary> /// Gets a list of the roles that a specified user is in for the configured applicationName. /// </summary> /// <param name="username">The user to return a list of roles for.</param> /// <returns> /// A string array containing the names of all the roles that the specified user is in for the configured applicationName. /// </returns> public override string[] GetRolesForUser(string username) { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); return GetRolesByUserName(connection, username); } }
/// <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> /// Determines whether [is user in role] [the specified username]. /// </summary> /// <param name="username">The username.</param> /// <param name="rolename">The rolename.</param> /// <returns> /// <c>true</c> if [is user in role] [the specified username]; otherwise, <c>false</c>. /// </returns> public override bool IsUserInRole(string username, string rolename) { try { // this will refresh the app id if necessary if (!RoleExists(rolename)) return false; using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); string sql = @"SELECT COUNT(*) 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.applicationId=@appId AND u.name LIKE @userName AND r.name LIKE @roleName"; MySqlCommand cmd = new MySqlCommand(sql, connection); cmd.Parameters.AddWithValue("@appId", app.FetchId(connection)); cmd.Parameters.AddWithValue("@userName", username); cmd.Parameters.AddWithValue("@roleName", rolename); int count = Convert.ToInt32(cmd.ExecuteScalar()); return count > 0; } } catch (Exception ex) { if (WriteExceptionsToEventLog) WriteToEventLog(ex, "IsUserInRole"); throw; } }
private ProfileInfoCollection GetProfiles( ProfileAuthenticationOption authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords) { List<string> whereClauses = new List<string>(); using (MySqlConnection c = new MySqlConnection(connectionString)) { c.Open(); MySqlCommand cmd = new MySqlCommand( @"SELECT p.*, u.name, u.isAnonymous, u.lastActivityDate, LENGTH(p.stringdata) + LENGTH(p.binarydata) AS profilesize FROM my_aspnet_Profiles p JOIN my_aspnet_Users u ON u.id = p.userId WHERE u.applicationId = @appId", c); cmd.Parameters.AddWithValue("@appId", app.FetchId(c)); if (usernameToMatch != null) { cmd.CommandText += " AND u.name LIKE @userName"; cmd.Parameters.AddWithValue("@userName", usernameToMatch); } if (userInactiveSinceDate != DateTime.MinValue) { cmd.CommandText += " AND u.lastActivityDate < @lastActivityDate"; cmd.Parameters.AddWithValue("@lastActivityDate", userInactiveSinceDate); } if (authenticationOption == ProfileAuthenticationOption.Anonymous) cmd.CommandText += " AND u.isAnonymous = 1"; else if (authenticationOption == ProfileAuthenticationOption.Authenticated) cmd.CommandText += " AND u.isAnonymous = 0"; cmd.CommandText += String.Format(" LIMIT {0},{1}", pageIndex * pageSize, pageSize); ProfileInfoCollection pic = new ProfileInfoCollection(); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { ProfileInfo pi = new ProfileInfo( reader.GetString("name"), reader.GetBoolean("isAnonymous"), reader.GetDateTime("lastActivityDate"), reader.GetDateTime("lastUpdatedDate"), reader.GetInt32("profilesize")); pic.Add(pi); } } cmd.CommandText = "SELECT FOUND_ROWS()"; totalRecords = Convert.ToInt32(cmd.ExecuteScalar()); return pic; } }
/// <summary> /// Roles the exists. /// </summary> /// <param name="rolename">The rolename.</param> /// <returns>true if the role name already exists in the database; otherwise, false. </returns> public override bool RoleExists(string rolename) { try { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); MySqlCommand cmd = new MySqlCommand( @"SELECT COUNT(*) FROM my_aspnet_Roles WHERE applicationId=@appId AND name LIKE @name", connection); cmd.Parameters.AddWithValue("@appId", app.FetchId(connection)); cmd.Parameters.AddWithValue("@name", rolename); int count = Convert.ToInt32(cmd.ExecuteScalar()); return count != 0; } } catch (Exception ex) { if (WriteExceptionsToEventLog) WriteToEventLog(ex, "RoleExists"); throw; } }
/// <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; } }
protected override void DbCreateDatabase(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"); string query = DbCreateDatabaseScript(null, storeItemCollection); using (MySqlConnection c = new MySqlConnection()) { MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder(conn.ConnectionString); string dbName = sb.Database; sb.Database = null; c.ConnectionString = sb.ConnectionString; c.Open(); string fullQuery = String.Format("CREATE DATABASE `{0}`; USE `{0}`; {1}", dbName, query); MySqlScript s = new MySqlScript(c, fullQuery); s.Execute(); } }
private static int GetSchemaVersion(string connectionString) { // retrieve the current schema version using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); MySqlCommand cmd = new MySqlCommand("SELECT * FROM my_aspnet_SchemaVersion", conn); try { object ver = cmd.ExecuteScalar(); if (ver != null) return (int)ver; } catch (MySqlException ex) { if (ex.Number != (int)MySqlErrorCode.NoSuchTable) throw; string[] restrictions = new string[4]; restrictions[2] = "mysql_Membership"; DataTable dt = conn.GetSchema("Tables", restrictions); if (dt.Rows.Count == 1) return Convert.ToInt32(dt.Rows[0]["TABLE_COMMENT"]); } return 0; } }
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(); } }
/// <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); }