public override bool IsUserInRole(string username, string roleName)
{
if (!Initialized)
{
return(_prevProvider.IsUserInRole(username, roleName));
}
string connString = ConnectionString;
if (string.IsNullOrEmpty(username))
{
MySqlSimpleMembershipProvider.NullArgumentException("username");
}
if (string.IsNullOrEmpty(roleName))
{
MySqlSimpleMembershipProvider.NullArgumentException("roleName");
}
int userid = MySqlSimpleMembershipProvider.GetUserId(username, connString, UserTableName, UserIdColumn, UserNameColumn);
int roleid = GetRoleId(roleName);
if (userid <= 0 || roleid <= 0)
{
return(false);
}
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(connString))
{
return((dbConn.ExecuteQuery(string.Format("select count(userid) from {0} where userid=? and roleid=?;", _userInRolesTable), userid, roleid)).Count() > 0);
}
}
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
if (!Initialized)
{
_prevProvider.AddUsersToRoles(usernames, roleNames);
}
else
{
if (usernames.Where(username => string.IsNullOrEmpty(username)).Count() > 0 || usernames.Where(username => string.IsNullOrEmpty(username)).Count() > 0)
{
throw new ArgumentException(Resources.InvalidArrayValue);
}
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
foreach (var userid in GetUsersId(usernames))
{
foreach (var roleid in GetRolesId(roleNames))
{
if (userid > 0 && roleid > 0)
{
dbConn.ExecuteNonQuery(string.Format("insert into {0} (userid, roleid) values(?,?);", _userInRolesTable), userid, roleid);
}
}
}
}
}
}
private bool UserHasRole(int userid, int roleid)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return(dbConn.ExecuteQuery(string.Format("select count(*) from {0} where userid=? and roleid=?;", _userInRolesTable), userid, roleid).Count() > 0);
}
}
private bool IsRoleInUse(int roleid)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(ConnectionString))
{
return(dbConn.ExecuteQuery(string.Format("select count(*) from {0} where roleid=?;", _userInRolesTable), roleid).Count() > 0);
}
}
public override void StoreOAuthRequestToken(string requestToken, string requestTokenSecret)
{
IsValidOperation(true);
string secret = GetOAuthTokenSecret(requestToken);
if (secret != null)
{
if (secret.Equals(requestTokenSecret, StringComparison.OrdinalIgnoreCase))
{
return;
}
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (dbConn.ExecuteNonQuery(string.Format("update {0} set secret=? where token=?;", _oauthTokenTable), requestTokenSecret, requestToken) <= 0)
{
throw new ProviderException(string.Format(Resources.UpdateTokenFailed, requestTokenSecret));
}
}
}
else
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (dbConn.ExecuteNonQuery(string.Format("insert into {0} (token, secret) values (?,?);", _oauthTokenTable), requestTokenSecret, requestToken) <= 0)
{
throw new ProviderException(string.Format(Resources.SaveTokenFailed, requestTokenSecret));
}
}
}
}
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
if (!Initialized)
{
_prevProvider.RemoveUsersFromRoles(usernames, roleNames);
}
else
{
if (usernames.Where(username => string.IsNullOrEmpty(username)).Count() > 0 || usernames.Where(username => string.IsNullOrEmpty(username)).Count() > 0)
{
throw new ArgumentException(Resources.InvalidArrayValue);
}
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(ConnectionString))
{
foreach (var userid in GetUsersId(usernames))
{
foreach (var roleid in GetRolesId(roleNames))
{
if (userid > 0 && roleid > 0)
{
dbConn.ExecuteNonQuery(string.Format("delete from {0} where userid=? and roleid=?;", _userInRolesTable), userid, roleid);
}
}
}
}
}
}
public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
if (!Initialized)
{
return(_prevProvider.DeleteRole(roleName, throwOnPopulatedRole));
}
if (string.IsNullOrEmpty(roleName))
{
MySqlSimpleMembershipProvider.NullArgumentException("roleName");
}
int roleid = GetRoleId(roleName);
if (roleid <= 0)
{
return(false);
}
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(ConnectionString))
{
if (throwOnPopulatedRole)
{
if (IsRoleInUse(roleid))
{
throw new InvalidOperationException(string.Format(Resources.RoleInUse, roleName));
}
}
else
{
dbConn.ExecuteNonQuery(string.Format("delete from {0} where roleid=?;", _userInRolesTable), roleid);
}
return(dbConn.ExecuteNonQuery(string.Format("delete from {0} where roleid=?;", _rolesTable), roleid) > 0);
}
}
public override string[] FindUsersInRole(string roleName, string usernameToMatch)
{
if (!Initialized)
{
return(_prevProvider.FindUsersInRole(roleName, usernameToMatch));
}
if (string.IsNullOrEmpty(roleName))
{
MySqlSimpleMembershipProvider.NullArgumentException("roleName");
}
if (string.IsNullOrEmpty(usernameToMatch))
{
return(GetUsersInRole(roleName));
}
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(ConnectionString))
{
var usersName = dbConn.ExecuteQuery(string.Format("select ut.{0} from {1} as rt join {2} as urt on rt.roleid=urt.roleid join {3} as ut on rt.userid=ut.{4} where rt.rolename=? and ut.name like '%?%'", UserNameColumn, _rolesTable, _userInRolesTable, UserTableName, UserIdColumn), roleName, usernameToMatch);
if (usersName.Count() > 0)
{
return(usersName.Select(username => username[0].ToString()).ToArray());
}
}
return(null);
}
public override string GeneratePasswordResetToken(string userName, int tokenExpirationInMinutesFromNow)
{
IsValidOperation(true);
if (string.IsNullOrEmpty(userName))
{
NullArgumentException("username");
}
int userid = GetUserId(userName);
if (userid <= 0)
{
InvalidUserException(userName);
}
if (UserConfirmed(userid))
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
string token = dbConn.ExecuteScalar(string.Format("select PasswordVerificationToken from {0} where userid=? and PasswordVerificationTokenExpirationDate > ?;", _membershipTable), userid, DateTime.Now) as string;
if (token != null)
{
token = GenerateToken();
if (dbConn.ExecuteNonQuery(string.Format("update {0} set PasswordVerificationToken=?, PasswordVerificationTokenExpirationDate=? where userid=?;", _membershipTable), token, DateTime.Now.AddMinutes(tokenExpirationInMinutesFromNow), userid) <= 0)
{
throw new ProviderException(Resources.GeneratePassVerificationTokenFailed);
}
}
return(token);
}
}
return(null);
}
public override string[] GetUsersInRole(string roleName)
{
if (!Initialized)
{
return(_prevProvider.GetUsersInRole(roleName));
}
if (string.IsNullOrEmpty(roleName))
{
MySqlSimpleMembershipProvider.NullArgumentException("roleName");
}
int roleid = GetRoleId(roleName);
if (roleid > 0)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(ConnectionString))
{
var users = dbConn.ExecuteQuery(string.Format("select ut.{0} from {1} as urt join {2} as ut on urt.userid = ut.{3} where urt.roleid=?;", UserNameColumn, _userInRolesTable, UserTableName, UserIdColumn), roleid);
if (users.Count() > 0)
{
return(users.Select(role => role[0].ToString()).ToArray());
}
}
}
return(null);
}
public override void CreateRole(string roleName)
{
if (!Initialized)
{
_prevProvider.CreateRole(roleName);
}
else
{
if (string.IsNullOrEmpty(roleName))
{
MySqlSimpleMembershipProvider.NullArgumentException("roleName");
}
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(ConnectionString))
{
int roleid = GetRoleId(roleName);
if (roleid != 0)
{
throw new InvalidOperationException(string.Format(Resources.RoleAlreadyExists, roleName));
}
if (dbConn.ExecuteNonQuery(string.Format("insert into {0} (rolename) values(?);", _rolesTable), roleName) <= 0)
{
throw new ProviderException(string.Format(Resources.CreateRoleFailed, roleName));
}
}
}
}
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
if (!Initialized)
{
return(_prevProvider.DeleteUser(username, deleteAllRelatedData));
}
int userid = GetUserId(username);
if (userid < 0)
{
return(false);
}
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (deleteAllRelatedData)
{
return(dbConn.ExecuteInTransaction(
new List <Tuple <string, object[]> >()
{
new Tuple <string, object[]>(string.Format("delete from {0} where {1}=?;", UserTableName, UserIdColumn), new object [] { userid }),
new Tuple <string, object[]>(string.Format("delete from {0} where userid=?;", _oauthMembershipTable), new object [] { userid }),
new Tuple <string, object[]>(string.Format("delete from {0} where userid=?;", _membershipTable), new object [] { userid }),
new Tuple <string, object[]>(string.Format("delete from {0} where userid=?;", _userInRolesTable), new object [] { userid })
}));
}
else
{
return(dbConn.ExecuteNonQuery(string.Format("delete from {0} where {1}=?;", UserTableName, UserIdColumn), userid) > 0);
}
}
}
public override bool ResetPasswordWithToken(string token, string newPassword)
{
IsValidOperation(true);
if (string.IsNullOrEmpty(token))
{
NullArgumentException("token");
}
if (string.IsNullOrEmpty(newPassword))
{
NullArgumentException("newPasword");
}
int userid = GetUserIdFromPasswordResetToken(token, true);
if (userid <= 0)
{
return(false);
}
bool passUpdated = UpdatePassword(userid, newPassword) > 0;
if (passUpdated)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (dbConn.ExecuteNonQuery(string.Format("update {0} set PasswordVerificationToken=null, PasswordVerificationTokenExpirationDate=null where userid=?;", _membershipTable), userid) <= 0)
{
throw new ProviderException(string.Format(Resources.ClearPassTokenFailed, userid, _membershipTable));
}
}
}
return(passUpdated);
}
public override void CreateOrUpdateOAuthAccount(string provider, string providerUserId, string userName)
{
IsValidOperation(true);
if (string.IsNullOrEmpty(userName))
{
NullArgumentException(userName);
}
var userid = GetUserId(userName);
if (userid <= 0)
{
throw new InvalidOperationException(string.Format(Resources.UserNotFound, UserTableName));
}
var oauthUserId = GetUserIdFromOAuth(provider, providerUserId);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (oauthUserId == 0)
{
if (dbConn.ExecuteNonQuery(string.Format("insert into {0} (provider, provideruserid, userid) values(?,?,?)", _oauthMembershipTable), provider, providerUserId, userid) <= 0)
{
throw new MembershipCreateUserException(MembershipCreateStatus.ProviderError);
}
}
else
{
if (dbConn.ExecuteNonQuery(string.Format("update {0} set userid=? where upper(provider) = ? and upper(provideruserid)=?;", _oauthMembershipTable), userid, provider.ToUpper(), providerUserId.ToUpper()) <= 0)
{
throw new MembershipCreateUserException(MembershipCreateStatus.ProviderError);
}
}
}
}
private bool UserConfirmed(int userId)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return(((long)dbConn.ExecuteScalar(string.Format("select count(*) from {0} where userid=? and isconfirmed=1;", _membershipTable), userId)) > 0);
}
}
public override string[] GetRolesForUser(string username)
{
if (!Initialized)
{
return(_prevProvider.GetRolesForUser(username));
}
if (string.IsNullOrEmpty(username))
{
MySqlSimpleMembershipProvider.NullArgumentException("username");
}
string connString = ConnectionString;
int userid = MySqlSimpleMembershipProvider.GetUserId(username, connString, UserTableName, UserIdColumn, UserNameColumn);
if (userid > 0)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(connString))
{
var roles = dbConn.ExecuteQuery(string.Format("select rt.rolename from {0} as urt join {1} as rt on urt.roleid = rt.roleid where urt.userid=?;", _userInRolesTable, _rolesTable), userid);
if (roles.Count() > 0)
{
return(roles.Select(role => role[0].ToString()).ToArray());
}
}
}
return(null);
}
public override string GetUserNameFromId(int userId)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return(dbConn.ExecuteScalar(string.Format("select {0} from {1} where {2}=?;", UserNameColumn, UserTableName, UserIdColumn), userId) as string);
}
}
public override string GetOAuthTokenSecret(string token)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return(dbConn.ExecuteScalar(string.Format("select secret from {0} where token=?;", _oauthTokenTable), token) as string);
}
}
public override bool HasLocalAccount(int userId)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return(dbConn.ExecuteQuery(string.Format("select userid from {0} where userid=?;", _membershipTable), userId).Count() > 0);
}
}
public override void DeleteOAuthToken(string token)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
dbConn.ExecuteNonQuery(string.Format("delete from {0} where token=?", _oauthTokenTable), token);
}
}
internal static bool VerifyIfTableExists(string connectionString, string tableName)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(connectionString))
{
var tables = dbConn.ExecuteQuery("show tables;");
return(tables.Where(record => record[0].ToString().Equals(tableName, StringComparison.OrdinalIgnoreCase)).Count() > 0);
}
}
public override void ReplaceOAuthRequestTokenWithAccessToken(string requestToken, string accessToken, string accessTokenSecret)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
dbConn.ExecuteNonQuery(string.Format("delete from {0} where token=?", _oauthTokenTable), requestToken);
}
StoreOAuthRequestToken(accessToken, accessTokenSecret);
}
public override void DeleteOAuthAccount(string provider, string providerUserId)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (dbConn.ExecuteNonQuery(string.Format("delete from {0} where upper(provider) = ? and upper(provideruserid)=?;", _oauthMembershipTable), provider.ToUpper(), providerUserId.ToUpper()) <= 0)
{
throw new Exception(string.Format(Resources.DeleteOAuthAccountFailed, provider, providerUserId));
}
}
}
internal void CreateTables()
{
var connString = ConnectionString;
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(connString))
{
//create schema
string schema = SchemaManager.GetSchema(11);
dbConn.ExecuteNonQuery(schema);
}
}
internal string GetHashedUserPassword(int userId)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var userPassword = dbConn.ExecuteQuerySingleRecord(string.Format("select password from {0} where userid=?;", _membershipTable), userId);
if (userPassword != null)
{
return(userPassword[0].ToString());
}
return(null);
}
}
internal void CreateTables()
{
var connString = GetConnectionString();
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(connString))
{
//create schema
ResourceManager r = new ResourceManager("MySql.Web.Properties.Resources", typeof(SchemaManager).Assembly);
string schema = r.GetString("schema11");
dbConn.ExecuteNonQuery(schema);
}
}
private int GetPasswordFailuresSinceLastSuccess(int userId)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var failures = dbConn.ExecuteScalar(string.Format("select PasswordFailuresSinceLastSuccess from {0} where userid=?;", _membershipTable), userId);
if (failures != null)
{
return((int)failures);
}
return(-1);
}
}
internal static int GetUserId(string userName, string connectionString, string userTableName, string userIdColumn, string userNameColumn)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(connectionString))
{
var user = dbConn.ExecuteQuerySingleRecord(string.Format("select {0} from {1} where {2} = ?;", userIdColumn, userTableName, userNameColumn), userName);
if (user != null)
{
return((int)user[userIdColumn]);
}
return(0);
}
}
private int GetUserIdFromPasswordResetToken(string token, bool checkExpirationDate = false)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var user = dbConn.ExecuteQuerySingleRecord(string.Format("select userid from {0} where PasswordVerificationToken = ? {1};", _membershipTable, (checkExpirationDate ? "and PasswordVerificationTokenExpirationDate > now()" : "")), token);
if (user != null)
{
return((int)user["userid"]);
}
return(0);
}
}
internal int GetRoleId(string role)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(ConnectionString))
{
var roleid = dbConn.ExecuteQuerySingleRecord(string.Format("select roleid from {0} where rolename=?;", _rolesTable), role);
if (roleid != null)
{
return((int)role[0]);
}
return(0);
}
}
private int UpdatePassword(int userId, string newPassword)
{
string hashedPass = HashPassword(newPassword);
if (hashedPass.Length > 128)
throw new ArgumentException(Resources.PasswordExceedsMaxLength, newPassword);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return dbConn.ExecuteNonQuery(string.Format("update {0} set password=?, PasswordChangedDate=now(), PasswordSalt='' where userid=?;", _membershipTable), hashedPass, userId);
}
}
internal void CreateTables()
{
string connString = GetConnectionString();
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(connString))
{
if (!VerifyIfTableExists(connString, UserTableName))
{
dbConn.ExecuteNonQuery(string.Format("create table {0} ({1} int not null primary key auto_increment, {2} varchar(250) not null unique);", UserTableName, UserIdColumn, UserNameColumn));
}
//create schema
ResourceManager r = new ResourceManager("MySql.Web.Properties.Resources", typeof(SchemaManager).Assembly);
string schema = r.GetString("schema11");
dbConn.ExecuteNonQuery(schema);
}
}
public override bool ResetPasswordWithToken(string token, string newPassword)
{
IsValidOperation(true);
if (string.IsNullOrEmpty(token))
{
NullArgumentException("token");
}
if (string.IsNullOrEmpty(newPassword))
{
NullArgumentException("newPasword");
}
int userid = GetUserIdFromPasswordResetToken(token, true);
if (userid <= 0)
{
return false;
}
bool passUpdated = UpdatePassword(userid, newPassword) > 0;
if (passUpdated)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (dbConn.ExecuteNonQuery(string.Format("update {0} set PasswordVerificationToken=null, PasswordVerificationTokenExpirationDate=null where userid=?;", _membershipTable), userid) <= 0)
{
throw new ProviderException(string.Format(Resources.ClearPassTokenFailed, userid, _membershipTable));
}
}
}
return passUpdated;
}
public override bool HasLocalAccount(int userId)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return (dbConn.ExecuteQuery(string.Format("select userid from {0} where userid=?;", _membershipTable), userId).Count() > 0);
}
}
private int GetUserIdFromPasswordResetToken(string token, bool checkExpirationDate = false)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var user = dbConn.ExecuteQuerySingleRecord(string.Format("select userid from {0} where PasswordVerificationToken = ? {1};", _membershipTable, (checkExpirationDate ? "and PasswordVerificationTokenExpirationDate > now()" : "")), token);
if (user != null)
return (int)user["userid"];
return 0;
}
}
public override int GetUserIdFromOAuth(string provider, string providerUserId)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var user = dbConn.ExecuteQuerySingleRecord(string.Format("select userid from {0} where upper(provider) = ? and upper(provideruserid)=?;", _oauthMembershipTable), provider.ToUpper(), providerUserId.ToUpper());
if (user != null)
return (int)user["userid"];
return 0;
}
}
private int GetPasswordFailuresSinceLastSuccess(int userId)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var failures = dbConn.ExecuteScalar(string.Format("select PasswordFailuresSinceLastSuccess from {0} where userid=?;", _membershipTable), userId);
if (failures != null)
return (int)failures;
return -1;
}
}
public override ICollection<OAuthAccountData> GetAccountsForUser(string userName)
{
IsValidOperation(true);
int userid = GetUserId(userName);
if (userid > 0)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var records = dbConn.ExecuteQuery(string.Format("select provider, provideruserid from {0} where userid=?", _oauthMembershipTable), userid);
if (records != null && records.Count() > 0)
{
var accounts = new List<OAuthAccountData>();
records.ToList().ForEach(record => accounts.Add(new OAuthAccountData(record["provider"].ToString(), record["provideruserid"].ToString())));
return accounts;
}
}
}
return new OAuthAccountData[0];
}
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
if (!Initialized)
return _prevProvider.DeleteUser(username, deleteAllRelatedData);
int userid = GetUserId(username);
if (userid < 0)
return false;
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (deleteAllRelatedData)
{
return dbConn.ExecuteInTransaction(
new List<Tuple<string, object[]>>()
{
new Tuple<string, object[]>(string.Format("delete from {0} where {1}=?;", UserTableName, UserIdColumn), new object [] { userid }),
new Tuple<string, object[]>(string.Format("delete from {0} where userid=?;", _oauthMembershipTable), new object [] { userid }),
new Tuple<string, object[]>(string.Format("delete from {0} where userid=?;", _membershipTable), new object [] { userid }),
new Tuple<string, object[]>(string.Format("delete from {0} where userid=?;", _userInRolesTable), new object [] { userid })
});
}
else
{
return (dbConn.ExecuteNonQuery(string.Format("delete from {0} where {1}=?;", UserTableName, UserIdColumn), userid) > 0);
}
}
}
private bool IsRoleInUse(int roleid)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return (dbConn.ExecuteQuery(string.Format("select count(*) from {0} where roleid=?;", _userInRolesTable), roleid).Count() > 0);
}
}
internal int GetRoleId(string role)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var roleid = dbConn.ExecuteQuerySingleRecord(string.Format("select roleid from {0} where rolename=?;", _rolesTable), role);
if (roleid != null)
return (int)role[0];
return 0;
}
}
internal string GetHashedUserPassword(int userId)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var userPassword = dbConn.ExecuteQuerySingleRecord(string.Format("select password from {0} where userid=?;", _membershipTable), userId);
if (userPassword != null)
return userPassword[0].ToString();
return null;
}
}
public override string GeneratePasswordResetToken(string userName, int tokenExpirationInMinutesFromNow)
{
IsValidOperation(true);
if (string.IsNullOrEmpty(userName))
NullArgumentException("username");
int userid = GetUserId(userName);
if (userid <= 0)
InvalidUserException(userName);
if (UserConfirmed(userid))
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
string token = dbConn.ExecuteScalar(string.Format("select PasswordVerificationToken from {0} where userid=? and PasswordVerificationTokenExpirationDate > ?;", _membershipTable), userid, DateTime.Now) as string;
if (token != null)
{
token = GenerateToken();
if (dbConn.ExecuteNonQuery(string.Format("update {0} set PasswordVerificationToken=?, PasswordVerificationTokenExpirationDate=? where userid=?;", _membershipTable), token, DateTime.Now.AddMinutes(tokenExpirationInMinutesFromNow), userid) <= 0)
{
throw new ProviderException(Resources.GeneratePassVerificationTokenFailed);
}
}
return token;
}
}
return null;
}
private void CreateUserInUserTable(string userName, IDictionary<string, object> values)
{
IsValidOperation(true);
var userid = GetUserId(userName);
if (userid > 0)
{
throw new MembershipCreateUserException(MembershipCreateStatus.DuplicateUserName);
}
StringBuilder columns = new StringBuilder();
columns.Append(UserNameColumn);
StringBuilder args = new StringBuilder();
args.Append("?");
var argsValues = new List<object>();
if (values != null)
{
foreach (var value in values)
{
if (string.Equals(UserNameColumn, value.Key, StringComparison.OrdinalIgnoreCase))
continue;
columns.Append(string.Format(",{0}", value.Value));
args.Append(",?");
argsValues.Add(value.Value != null ? value.Value : DBNull.Value);
}
}
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (dbConn.ExecuteNonQuery(string.Format("insert into {0} ({1}) values({2})", UserTableName, columns.ToString(), args.ToString()), userName, argsValues.ToArray()) < 1)
{
throw new MembershipCreateUserException(MembershipCreateStatus.ProviderError);
}
}
}
public override DateTime GetPasswordChangedDate(string userName)
{
int userid = GetUserId(userName);
if (userid < 0)
InvalidUserException(userName);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var changedDate = dbConn.ExecuteScalar(string.Format("select PasswordChangedDate from {0} where userid=?;", _membershipTable), userid);
if (changedDate != null)
return (DateTime)changedDate;
return DateTime.MinValue;
}
}
private bool UserConfirmed(int userId)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return ((long)dbConn.ExecuteScalar(string.Format("select count(*) from {0} where userid=? and isconfirmed=1;", _membershipTable), userId)) > 0;
}
}
internal static int GetUserId(string userName, string connectionString, string userTableName, string userIdColumn, string userNameColumn)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(connectionString))
{
var user = dbConn.ExecuteQuerySingleRecord(string.Format("select {0} from {1} where {2} = ?;", userIdColumn, userTableName, userNameColumn), userName);
if (user != null)
return (int)user[userIdColumn];
return 0;
}
}
public override bool ConfirmAccount(string userName, string accountConfirmationToken)
{
var userid = GetUserId(userName);
if (userid <= 0)
return false;
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var token = dbConn.ExecuteQuerySingleRecord(string.Format("select userid, confirmationtoken from {0} where confirmationtoken=? and userid=?", _membershipTable), accountConfirmationToken, userid);
if (token == null || (token != null && string.IsNullOrEmpty(token[1].ToString())))
{
return false;
}
return dbConn.ExecuteNonQuery(string.Format("update {0} set isconfirmed=1 where userid=?;", _membershipTable), userid) > 0;
}
}
public override string GetOAuthTokenSecret(string token)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return dbConn.ExecuteScalar(string.Format("select secret from {0} where token=?;", _oauthTokenTable), token) as string;
}
}
public override string CreateAccount(string userName, string password, bool requireConfirmationToken)
{
IsValidOperation(true);
if (string.IsNullOrEmpty(userName))
NullArgumentException(userName);
if (string.IsNullOrEmpty(password))
NullArgumentException(password);
var hashedPass = HashPassword(password);
if (hashedPass.Length > 128)
throw new ArgumentException(Resources.PasswordExceedsMaxLength, password);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
var userid = GetUserId(userName);
if (userid <= 0)
{
throw new InvalidOperationException(string.Format(Resources.UserNotFound, UserTableName));
}
if (dbConn.ExecuteQuerySingleRecord(string.Format("select userid from {0} where userid=?;", _membershipTable), userid) != null)
{
throw new InvalidOperationException(string.Format(Resources.UserAlreadyExists, userName));
}
object token = DBNull.Value;
if (requireConfirmationToken)
token = GenerateToken();
string insertQuery = string.Format("insert into {0} (userid, createdate, confirmationtoken, isconfirmed, password, passwordchangeddate, passwordsalt) values(?,now(),?,?,?,now(),?)", _membershipTable);
if (dbConn.ExecuteNonQuery(insertQuery, userid, token, !requireConfirmationToken, hashedPass, string.Empty) <= 0)
{
throw new MembershipCreateUserException(MembershipCreateStatus.ProviderError);
}
return token == DBNull.Value ? null : token.ToString();
}
}
public override string GetUserNameFromId(int userId)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return dbConn.ExecuteScalar(string.Format("select {0} from {1} where {2}=?;", UserNameColumn, UserTableName, UserIdColumn), userId) as string;
}
}
public override void CreateOrUpdateOAuthAccount(string provider, string providerUserId, string userName)
{
IsValidOperation(true);
if (string.IsNullOrEmpty(userName))
NullArgumentException(userName);
var userid = GetUserId(userName);
if (userid <= 0)
{
throw new InvalidOperationException(string.Format(Resources.UserNotFound, UserTableName));
}
var oauthUserId = GetUserIdFromOAuth(provider, providerUserId);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (oauthUserId == 0)
{
if (dbConn.ExecuteNonQuery(string.Format("insert into {0} (provider, provideruserid, userid) values(?,?,?)", _oauthMembershipTable), provider, providerUserId, userid) <= 0)
{
throw new MembershipCreateUserException(MembershipCreateStatus.ProviderError);
}
}
else
{
if (dbConn.ExecuteNonQuery(string.Format("update {0} set userid=? where upper(provider) = ? and upper(provideruserid)=?;", _oauthMembershipTable), userid, provider.ToUpper(), providerUserId.ToUpper()) <= 0)
{
throw new MembershipCreateUserException(MembershipCreateStatus.ProviderError);
}
}
}
}
public override void ReplaceOAuthRequestTokenWithAccessToken(string requestToken, string accessToken, string accessTokenSecret)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
dbConn.ExecuteNonQuery(string.Format("delete from {0} where token=?", _oauthTokenTable), requestToken);
}
StoreOAuthRequestToken(accessToken, accessTokenSecret);
}
public override bool DeleteAccount(string userName)
{
IsValidOperation(true);
int userid = GetUserId(userName);
if (userid < 0)
{
return false;
}
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
return (dbConn.ExecuteNonQuery(string.Format("delete from {0} where userid=?;", _membershipTable), userid) > 0);
}
}
public override void StoreOAuthRequestToken(string requestToken, string requestTokenSecret)
{
IsValidOperation(true);
string secret = GetOAuthTokenSecret(requestToken);
if (secret != null)
{
if (secret.Equals(requestTokenSecret, StringComparison.OrdinalIgnoreCase))
return;
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (dbConn.ExecuteNonQuery(string.Format("update {0} set secret=? where token=?;", _oauthTokenTable), requestTokenSecret, requestToken) <= 0)
throw new ProviderException(string.Format(Resources.UpdateTokenFailed, requestTokenSecret));
}
}
else
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (dbConn.ExecuteNonQuery(string.Format("insert into {0} (token, secret) values (?,?);", _oauthTokenTable), requestTokenSecret, requestToken) <= 0)
throw new ProviderException(string.Format(Resources.SaveTokenFailed, requestTokenSecret));
}
}
}
public override void DeleteOAuthAccount(string provider, string providerUserId)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (dbConn.ExecuteNonQuery(string.Format("delete from {0} where upper(provider) = ? and upper(provideruserid)=?;", _oauthMembershipTable), provider.ToUpper(), providerUserId.ToUpper()) <= 0)
{
throw new Exception(string.Format(Resources.DeleteOAuthAccountFailed, provider, providerUserId));
}
}
}
internal static bool VerifyIfTableExists(string connectionString, string tableName)
{
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(connectionString))
{
var tables = dbConn.ExecuteQuery("show tables;");
return tables.Where(record => record[0].ToString().Equals(tableName, StringComparison.OrdinalIgnoreCase)).Count() > 0;
}
}
public override void DeleteOAuthToken(string token)
{
IsValidOperation(true);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
dbConn.ExecuteNonQuery(string.Format("delete from {0} where token=?", _oauthTokenTable), token);
}
}
internal bool VerifyPassword(int userid, string password, string hashedPassword)
{
if (string.IsNullOrEmpty(password))
{
throw new ArgumentException(Resources.InvalidArgument, password);
}
if (string.IsNullOrEmpty(hashedPassword))
{
throw new ArgumentException(Resources.InvalidArgument, hashedPassword);
}
byte[] hashed = Convert.FromBase64String(hashedPassword);
if (hashed.Length != 48)
{
return false;
}
byte[] salt = new byte[16];
byte[] passBytes = new byte[32];
Buffer.BlockCopy(hashed, 0, salt, 0, 16);
Buffer.BlockCopy(hashed, 16, passBytes, 0, 32);
Rfc2898DeriveBytes rfc2898 = new Rfc2898DeriveBytes(password, salt, 1000);
bool validation = CompareBuffer(rfc2898.GetBytes(32), passBytes);
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(GetConnectionString()))
{
if (validation)
{
dbConn.ExecuteNonQuery(string.Format("update {0} set PasswordFailuresSinceLastSuccess=0 where userid=?;", _membershipTable), userid);
}
else
{
int failures = GetPasswordFailuresSinceLastSuccess(userid);
dbConn.ExecuteNonQuery(string.Format("update {0} set PasswordFailuresSinceLastSuccess=?, LastPasswordFailureDate=now() where userid=?;", _membershipTable), (failures == -1 ? 1 : failures + 1), userid);
}
}
return validation;
}
internal void CreateTables()
{
var connString = GetConnectionString();
using (MySqlDatabaseWrapper dbConn = new MySqlDatabaseWrapper(connString))
{
//create schema
ResourceManager r = new ResourceManager("MySql.Web.Properties.Resources", typeof(SchemaManager).Assembly);
string schema = r.GetString("schema11");
dbConn.ExecuteNonQuery(schema);
}
}
