public override void Close(MySqlDataReader reader)
{
base.Close(reader);
if (String.IsNullOrEmpty(outSelect)) return;
if ((reader.CommandBehavior & CommandBehavior.SchemaOnly) != 0) return;
MySqlCommand cmd = new MySqlCommand(outSelect, command.Connection);
using (MySqlDataReader rdr = cmd.ExecuteReader(reader.CommandBehavior))
{
ProcessOutputParameters(rdr);
}
}
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);
}
}
private MembershipUserCollection GetUsers(string username, string email,
int pageIndex, int pageSize, out int totalRecords)
{
MembershipUserCollection users = new MembershipUserCollection();
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
string sql = @"SELECT SQL_CALC_FOUND_ROWS u.name,m.* FROM my_aspnet_Users u
JOIN my_aspnet_Membership m ON m.userId=u.id
WHERE u.applicationId=@appId";
if (username != null)
{
sql += " AND u.name LIKE @name";
cmd.Parameters.AddWithValue("@name", username);
}
else if (email != null)
{
sql += " AND m.Email LIKE @email";
cmd.Parameters.AddWithValue("@email", email);
}
sql += " ORDER BY u.id ASC LIMIT {0},{1}";
cmd.CommandText = String.Format(sql, pageIndex * pageSize, pageSize);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
users.Add(GetUserFromReader(reader));
}
cmd.CommandText = "SELECT FOUND_ROWS()";
cmd.Parameters.Clear();
totalRecords = Convert.ToInt32(cmd.ExecuteScalar());
}
return users;
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetUsers");
throw new ProviderException(exceptionMessage);
}
}
internal static void InitCollections(MySqlConnection connection)
{
defaultCollations = new Dictionary<string, string>();
maxLengths = new Dictionary<string, int>();
MySqlCommand cmd = new MySqlCommand("SHOW CHARSET", connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
defaultCollations.Add(reader.GetString(0), reader.GetString(2));
maxLengths.Add(reader.GetString(0), Convert.ToInt32(reader.GetValue(3)));
}
}
}
private string[] GetRolesByUserName(MySqlConnection connection, string username)
{
List<string> roleList = new List<string>();
try
{
string sql = "SELECT r.name FROM my_aspnet_Roles r ";
if (username != null)
sql += "JOIN my_aspnet_UsersInRoles uir ON uir.roleId=r.id AND uir.userId=" +
GetUserId(connection, username);
sql += " WHERE r.applicationId=@appId";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
roleList.Add(reader.GetString(0));
}
return (string[])roleList.ToArray();
}
catch (Exception ex)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "GetRolesByUserName");
throw;
}
}
/// <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>
/// 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>
/// 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);
}
}
public virtual List<MySqlError> ReportWarnings(MySqlConnection connection)
{
List<MySqlError> warnings = new List<MySqlError>();
MySqlCommand cmd = new MySqlCommand("SHOW WARNINGS", connection);
cmd.InternallyCreated = true;
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
warnings.Add(new MySqlError(reader.GetString(0),
reader.GetInt32(1), reader.GetString(2)));
}
}
MySqlInfoMessageEventArgs args = new MySqlInfoMessageEventArgs();
args.errors = warnings.ToArray();
if (connection != null)
connection.OnInfoMessage(args);
return warnings;
}
/// <summary>
/// GetForeignKeysOnTable retrieves the foreign keys on the given table.
/// Since MySQL supports foreign keys on versions prior to 5.0, we can't use
/// information schema. MySQL also does not include any type of SHOW command
/// for foreign keys so we have to resort to use SHOW CREATE TABLE and parsing
/// the output.
/// </summary>
/// <param name="fkTable">The table to store the key info in.</param>
/// <param name="tableToParse">The table to get the foeign key info for.</param>
/// <param name="filterName">Only get foreign keys that match this name.</param>
/// <param name="includeColumns">Should column information be included in the table.</param>
private void GetForeignKeysOnTable(DataTable fkTable, DataRow tableToParse,
string filterName, bool includeColumns)
{
string sqlMode = GetSqlMode();
if (filterName != null)
filterName = filterName.ToLower(CultureInfo.InvariantCulture);
string sql = string.Format("SHOW CREATE TABLE `{0}`.`{1}`",
tableToParse["TABLE_SCHEMA"], tableToParse["TABLE_NAME"]);
string lowerBody = null, body = null;
MySqlCommand cmd = new MySqlCommand(sql, connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
body = reader.GetString(1);
lowerBody = body.ToLower(CultureInfo.InvariantCulture);
}
MySqlTokenizer tokenizer = new MySqlTokenizer(lowerBody);
tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1;
tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") != -1;
while (true)
{
string token = tokenizer.NextToken();
// look for a starting contraint
while (token != null && (token != "constraint" || tokenizer.Quoted))
token = tokenizer.NextToken();
if (token == null) break;
ParseConstraint(fkTable, tableToParse, tokenizer, includeColumns);
}
}
private void FindTables(DataTable schemaTable, string[] restrictions)
{
StringBuilder sql = new StringBuilder();
StringBuilder where = new StringBuilder();
sql.AppendFormat(CultureInfo.InvariantCulture,
"SHOW TABLE STATUS FROM `{0}`", restrictions[1]);
if (restrictions != null && restrictions.Length >= 3 &&
restrictions[2] != null)
where.AppendFormat(CultureInfo.InvariantCulture,
" LIKE '{0}'", restrictions[2]);
sql.Append(where.ToString());
string table_type = restrictions[1].ToLower() == "information_schema"
?
"SYSTEM VIEW"
: "BASE TABLE";
MySqlCommand cmd = new MySqlCommand(sql.ToString(), connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DataRow row = schemaTable.NewRow();
row["TABLE_CATALOG"] = null;
row["TABLE_SCHEMA"] = restrictions[1];
row["TABLE_NAME"] = reader.GetString(0);
row["TABLE_TYPE"] = table_type;
row["ENGINE"] = GetString(reader, 1);
row["VERSION"] = reader.GetValue(2);
row["ROW_FORMAT"] = GetString(reader, 3);
row["TABLE_ROWS"] = reader.GetValue(4);
row["AVG_ROW_LENGTH"] = reader.GetValue(5);
row["DATA_LENGTH"] = reader.GetValue(6);
row["MAX_DATA_LENGTH"] = reader.GetValue(7);
row["INDEX_LENGTH"] = reader.GetValue(8);
row["DATA_FREE"] = reader.GetValue(9);
row["AUTO_INCREMENT"] = reader.GetValue(10);
row["CREATE_TIME"] = reader.GetValue(11);
row["UPDATE_TIME"] = reader.GetValue(12);
row["CHECK_TIME"] = reader.GetValue(13);
row["TABLE_COLLATION"] = GetString(reader, 14);
row["CHECKSUM"] = reader.GetValue(15);
row["CREATE_OPTIONS"] = GetString(reader, 16);
row["TABLE_COMMENT"] = GetString(reader, 17);
schemaTable.Rows.Add(row);
}
}
}
public virtual DataTable GetUDF(string[] restrictions)
{
string sql = "SELECT name,ret,dl FROM mysql.func";
if (restrictions != null)
{
if (restrictions.Length >= 1 && !String.IsNullOrEmpty(restrictions[0]))
sql += String.Format(" WHERE name LIKE '{0}'", restrictions[0]);
}
DataTable dt = new DataTable("User-defined Functions");
dt.Columns.Add("NAME", typeof(string));
dt.Columns.Add("RETURN_TYPE", typeof(int));
dt.Columns.Add("LIBRARY_NAME", typeof(string));
MySqlCommand cmd = new MySqlCommand(sql, connection);
try
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DataRow row = dt.NewRow();
row[0] = reader.GetString(0);
row[1] = reader.GetInt32(1);
row[2] = reader.GetString(2);
dt.Rows.Add(row);
}
}
}
catch (MySqlException ex)
{
if (ex.Number != (int)MySqlErrorCode.TableAccessDenied)
throw;
throw new MySqlException(Resources.UnableToEnumerateUDF, ex);
}
return dt;
}
public virtual DataTable GetProcedures(string[] restrictions)
{
DataTable dt = new DataTable("Procedures");
dt.Columns.Add(new DataColumn("SPECIFIC_NAME", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_CATALOG", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_SCHEMA", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_NAME", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_TYPE", typeof(string)));
dt.Columns.Add(new DataColumn("DTD_IDENTIFIER", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_BODY", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_DEFINITION", typeof(string)));
dt.Columns.Add(new DataColumn("EXTERNAL_NAME", typeof(string)));
dt.Columns.Add(new DataColumn("EXTERNAL_LANGUAGE", typeof(string)));
dt.Columns.Add(new DataColumn("PARAMETER_STYLE", typeof(string)));
dt.Columns.Add(new DataColumn("IS_DETERMINISTIC", typeof(string)));
dt.Columns.Add(new DataColumn("SQL_DATA_ACCESS", typeof(string)));
dt.Columns.Add(new DataColumn("SQL_PATH", typeof(string)));
dt.Columns.Add(new DataColumn("SECURITY_TYPE", typeof(string)));
dt.Columns.Add(new DataColumn("CREATED", typeof(DateTime)));
dt.Columns.Add(new DataColumn("LAST_ALTERED", typeof(DateTime)));
dt.Columns.Add(new DataColumn("SQL_MODE", typeof(string)));
dt.Columns.Add(new DataColumn("ROUTINE_COMMENT", typeof(string)));
dt.Columns.Add(new DataColumn("DEFINER", typeof(string)));
StringBuilder sql = new StringBuilder("SELECT * FROM mysql.proc WHERE 1=1");
if (restrictions != null)
{
if (restrictions.Length >= 2 && restrictions[1] != null)
sql.AppendFormat(CultureInfo.InvariantCulture,
" AND db LIKE '{0}'", restrictions[1]);
if (restrictions.Length >= 3 && restrictions[2] != null)
sql.AppendFormat(CultureInfo.InvariantCulture,
" AND name LIKE '{0}'", restrictions[2]);
if (restrictions.Length >= 4 && restrictions[3] != null)
sql.AppendFormat(CultureInfo.InvariantCulture,
" AND type LIKE '{0}'", restrictions[3]);
}
MySqlCommand cmd = new MySqlCommand(sql.ToString(), connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DataRow row = dt.NewRow();
row["SPECIFIC_NAME"] = reader.GetString("specific_name");
row["ROUTINE_CATALOG"] = DBNull.Value;
row["ROUTINE_SCHEMA"] = reader.GetString("db");
row["ROUTINE_NAME"] = reader.GetString("name");
string routineType = reader.GetString("type");
row["ROUTINE_TYPE"] = routineType;
row["DTD_IDENTIFIER"] = routineType.ToLower(CultureInfo.InvariantCulture) == "function" ?
(object)reader.GetString("returns") : DBNull.Value;
row["ROUTINE_BODY"] = "SQL";
row["ROUTINE_DEFINITION"] = reader.GetString("body");
row["EXTERNAL_NAME"] = DBNull.Value;
row["EXTERNAL_LANGUAGE"] = DBNull.Value;
row["PARAMETER_STYLE"] = "SQL";
row["IS_DETERMINISTIC"] = reader.GetString("is_deterministic");
row["SQL_DATA_ACCESS"] = reader.GetString("sql_data_access");
row["SQL_PATH"] = DBNull.Value;
row["SECURITY_TYPE"] = reader.GetString("security_type");
row["CREATED"] = reader.GetDateTime("created");
row["LAST_ALTERED"] = reader.GetDateTime("modified");
row["SQL_MODE"] = reader.GetString("sql_mode");
row["ROUTINE_COMMENT"] = reader.GetString("comment");
row["DEFINER"] = reader.GetString("definer");
dt.Rows.Add(row);
}
}
return dt;
}
public virtual DataTable GetIndexColumns(string[] restrictions)
{
DataTable dt = new DataTable("IndexColumns");
dt.Columns.Add("INDEX_CATALOG", typeof(string));
dt.Columns.Add("INDEX_SCHEMA", typeof(string));
dt.Columns.Add("INDEX_NAME", typeof(string));
dt.Columns.Add("TABLE_NAME", typeof(string));
dt.Columns.Add("COLUMN_NAME", typeof(string));
dt.Columns.Add("ORDINAL_POSITION", typeof(int));
dt.Columns.Add("SORT_ORDER", typeof(string));
int max = restrictions == null ? 4 : restrictions.Length;
string[] tableRestrictions = new string[Math.Max(max, 4)];
if (restrictions != null)
restrictions.CopyTo(tableRestrictions, 0);
tableRestrictions[3] = "BASE TABLE";
DataTable tables = GetTables(tableRestrictions);
foreach (DataRow table in tables.Rows)
{
string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`",
table["TABLE_SCHEMA"], table["TABLE_NAME"]);
MySqlCommand cmd = new MySqlCommand(sql, connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string key_name = GetString(reader, reader.GetOrdinal("KEY_NAME"));
string col_name = GetString(reader, reader.GetOrdinal("COLUMN_NAME"));
if (restrictions != null)
{
if (restrictions.Length >= 4 && restrictions[3] != null &&
key_name != restrictions[3])
continue;
if (restrictions.Length >= 5 && restrictions[4] != null &&
col_name != restrictions[4])
continue;
}
DataRow row = dt.NewRow();
row["INDEX_CATALOG"] = null;
row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"];
row["INDEX_NAME"] = key_name;
row["TABLE_NAME"] = GetString(reader, reader.GetOrdinal("TABLE"));
row["COLUMN_NAME"] = col_name;
row["ORDINAL_POSITION"] = reader.GetValue(reader.GetOrdinal("SEQ_IN_INDEX"));
row["SORT_ORDER"] = reader.GetString("COLLATION");
dt.Rows.Add(row);
}
}
}
return dt;
}
private void LoadTableColumns(DataTable dt, string schema,
string tableName, string columnRestriction)
{
string sql = String.Format("SHOW FULL COLUMNS FROM `{0}`.`{1}`",
schema, tableName);
MySqlCommand cmd = new MySqlCommand(sql, connection);
int pos = 1;
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string colName = reader.GetString(0);
if (columnRestriction != null && colName != columnRestriction)
continue;
DataRow row = dt.NewRow();
row["TABLE_CATALOG"] = DBNull.Value;
row["TABLE_SCHEMA"] = schema;
row["TABLE_NAME"] = tableName;
row["COLUMN_NAME"] = colName;
row["ORDINAL_POSITION"] = pos++;
row["COLUMN_DEFAULT"] = reader.GetValue(5);
row["IS_NULLABLE"] = reader.GetString(3);
row["DATA_TYPE"] = reader.GetString(1);
row["CHARACTER_MAXIMUM_LENGTH"] = DBNull.Value;
row["CHARACTER_OCTET_LENGTH"] = DBNull.Value;
row["NUMERIC_PRECISION"] = DBNull.Value;
row["NUMERIC_SCALE"] = DBNull.Value;
row["CHARACTER_SET_NAME"] = reader.GetValue(2);
row["COLLATION_NAME"] = row["CHARACTER_SET_NAME"];
row["COLUMN_TYPE"] = reader.GetString(1);
row["COLUMN_KEY"] = reader.GetString(4);
row["EXTRA"] = reader.GetString(6);
row["PRIVILEGES"] = reader.GetString(7);
row["COLUMN_COMMENT"] = reader.GetString(8);
ParseColumnRow(row);
dt.Rows.Add(row);
}
}
}
/// <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 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>
/// Loads all the current character set names and ids for this server
/// into the charSets hashtable
/// </summary>
private void LoadCharacterSets(MySqlConnection connection)
{
MySqlCommand cmd = new MySqlCommand("SHOW COLLATION", connection);
// now we load all the currently active collations
try
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
charSets = new Hashtable();
while (reader.Read())
{
charSets[Convert.ToInt32(reader["id"], NumberFormatInfo.InvariantInfo)] =
reader.GetString(reader.GetOrdinal("charset"));
}
}
}
catch (Exception ex)
{
MySqlTrace.LogError(ThreadID, ex.Message);
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>
/// Loads the properties from the connected server into a hashtable
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
private Hashtable LoadServerProperties(MySqlConnection connection)
{
// load server properties
Hashtable hash = new Hashtable();
MySqlCommand cmd = new MySqlCommand("SHOW VARIABLES", connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
try
{
while (reader.Read())
{
string key = reader.GetString(0);
string value = reader.GetString(1);
hash[key] = value;
}
}
catch (Exception ex)
{
MySqlTrace.LogError(ThreadID, ex.Message);
throw;
}
}
if (hash.Contains("max_allowed_packet"))
maxPacketSize = Convert.ToInt64(hash["max_allowed_packet"]);
return hash;
}
private void ClearKillFlag()
{
// This query will silently crash because of the Kill call that happened before.
string dummyStatement = "SELECT * FROM bogus_table LIMIT 0"; /* dummy query used to clear kill flag */
MySqlCommand dummyCommand = new MySqlCommand(dummyStatement, connection);
dummyCommand.InternallyCreated = true;
try
{
var reader = dummyCommand.ExecuteReader(); // ExecuteReader catches the exception and returns null, which is expected.
}
catch (MySqlException ex)
{
if (ex.Number != (int)MySqlErrorCode.NoSuchTable) throw;
}
}
/// <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>
/// 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;
}
}
private void GetPasswordInfo(MySqlConnection connection, int userId,
out string passwordKey, out MembershipPasswordFormat passwordFormat)
{
MySqlCommand cmd = new MySqlCommand(
@"SELECT PasswordKey, PasswordFormat FROM my_aspnet_Membership WHERE
userId=@userId", connection);
cmd.Parameters.AddWithValue("@userId", userId);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
passwordKey = reader.GetString(reader.GetOrdinal("PasswordKey"));
passwordFormat = (MembershipPasswordFormat)reader.GetByte(
reader.GetOrdinal("PasswordFormat"));
}
}
private string GetProcedureParameterLine(DataRow isRow)
{
string sql = "SHOW CREATE {0} `{1}`.`{2}`";
sql = String.Format(sql, isRow["ROUTINE_TYPE"], isRow["ROUTINE_SCHEMA"],
isRow["ROUTINE_NAME"]);
MySqlCommand cmd = new MySqlCommand(sql, connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
// if we are not the owner of this proc or have permissions
// then we will get null for the body
if (reader.IsDBNull(2)) return null;
string sql_mode = reader.GetString(1);
string body = reader.GetString(2);
MySqlTokenizer tokenizer = new MySqlTokenizer(body);
tokenizer.AnsiQuotes = sql_mode.IndexOf("ANSI_QUOTES") != -1;
tokenizer.BackslashEscapes = sql_mode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
string token = tokenizer.NextToken();
while (token != "(")
token = tokenizer.NextToken();
int start = tokenizer.StartIndex + 1;
token = tokenizer.NextToken();
while (token != ")" || tokenizer.Quoted)
{
token = tokenizer.NextToken();
// if we see another ( and we are not quoted then we
// are in a size element and we need to look for the closing paren
if (token == "(" && !tokenizer.Quoted)
{
while (token != ")" || tokenizer.Quoted)
token = tokenizer.NextToken();
token = tokenizer.NextToken();
}
}
return body.Substring(start, tokenizer.StartIndex - start);
}
}
/// <summary>
/// Executes a single command against a MySQL database, possibly inside an existing transaction.
/// </summary>
/// <param name="connection"><see cref="MySqlConnection"/> object to use for the command</param>
/// <param name="transaction"><see cref="MySqlTransaction"/> object to use for the command</param>
/// <param name="commandText">Command text to use</param>
/// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command</param>
/// <param name="ExternalConn">True if the connection should be preserved, false if not</param>
/// <returns><see cref="MySqlDataReader"/> object ready to read the results of the command</returns>
private static MySqlDataReader ExecuteReader(MySqlConnection connection, MySqlTransaction transaction, string commandText, MySqlParameter[] commandParameters, bool ExternalConn)
{
//create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.Transaction = transaction;
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
if (commandParameters != null)
foreach (MySqlParameter p in commandParameters)
cmd.Parameters.Add(p);
//create a reader
MySqlDataReader dr;
// call ExecuteReader with the appropriate CommandBehavior
if (ExternalConn)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return dr;
}