public string GetUserPasswordByEmail(string email)
{
SqlConnection dbConnection = DBUtils.getDbConnection();
SqlTransaction dbTransaction = dbConnection.BeginTransaction();
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT us_password FROM tblUsers ");
sql.Append(" INNER JOIN tblUserEmails ON usem_us_id = us_id ");
sql.Append(" WHERE usem_email = @email; ");
SqlCommand dbCommand = new SqlCommand(sql.ToString(), dbConnection);
dbCommand.Transaction = dbTransaction;
dbCommand.Parameters.Add("email", SqlDbType.NVarChar).Value = email;
string result = null;
try
{
result = GeneralExtractor.ExtractString(dbCommand.ExecuteReader(), "us_password", null);
}
catch (SqlException) { }
dbConnection.Close();
return(result);
}
public int GetUserIdByEmail(string email)
{
SqlConnection dbConnection = DBUtils.getDbConnection();
SqlTransaction dbTransaction = dbConnection.BeginTransaction();
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT usem_us_id ");
sql.Append(" FROM tblUserEmails ");
sql.Append(" WHERE usem_email = @email ");
SqlCommand dbCommand = new SqlCommand(sql.ToString(), dbConnection);
dbCommand.Transaction = dbTransaction;
dbCommand.Parameters.Add("email", SqlDbType.NVarChar).Value = email;
int result = -1;
try
{
result = GeneralExtractor.ExtractInt(dbCommand.ExecuteReader(), "usem_us_id", -1);
}
catch (SqlException) { }
dbConnection.Close();
return(result);
}
public string GetEmailBySocialNetworkUserId(string socialNetworkName, string socialNetworkUserId)
{
SqlConnection dbConnection = DBUtils.getDbConnection();
SqlTransaction dbTransaction = dbConnection.BeginTransaction();
StringBuilder sql = new StringBuilder();
sql.Append(" SELECT ussm_usem_email ");
sql.Append(" FROM tblUserSocialMedia ");
sql.Append(" INNER JOIN prmSocialNetwork ON socntw_id = ussm_socntw_id ");
sql.Append(" WHERE socntw_name = @socialNetworkName AND ussm_socntw_user_id = @socialNetworkUserId ");
SqlCommand dbCommand = new SqlCommand(sql.ToString(), dbConnection);
dbCommand.Transaction = dbTransaction;
dbCommand.Parameters.Add("socialNetworkName", SqlDbType.NVarChar).Value = socialNetworkName;
dbCommand.Parameters.Add("socialNetworkUserId", SqlDbType.NVarChar).Value = socialNetworkUserId;
string result = null;
try
{
result = GeneralExtractor.ExtractString(dbCommand.ExecuteReader(), 0, null);
dbTransaction.Commit();
}
catch (SqlException)
{
dbTransaction.Rollback();
}
dbConnection.Close();
return(result);
}
/// <summary>Creates user via the usual Email registration process. Username, email and password are required.</summary>
/// <param name="User"> User object to store on the database</param>
public int CreateUserFromEmail(User user)
{
SqlConnection dbConnection = DBUtils.getDbConnection();
SqlTransaction dbTransaction = dbConnection.BeginTransaction();
StringBuilder sql = new StringBuilder();
sql.Append(" IF NOT EXISTS (SELECT usem_us_id FROM tblUserEmails WHERE usem_email = @email) ");
sql.Append(" BEGIN ");
sql.Append(" DECLARE @userId INT; ");
sql.Append(" INSERT INTO tblUsers (us_name, us_password, us_registration_date, us_profile_picture) VALUES (@userName, @password, GETDATE(), @defaultProfilePic); ");
sql.Append(" SET @userId = (SELECT CAST(SCOPE_IDENTITY() AS INT)); ");
sql.Append(" INSERT INTO tblUserEmails (usem_email, usem_us_id, usem_verified) VALUES (@email, @userId, 0); ");
sql.Append(" SELECT @userId; ");
sql.Append(" END ");
sql.Append(" ELSE ");
sql.Append(" BEGIN ");
sql.Append(" SELECT -1; ");
sql.Append(" END ");
SqlCommand dbCommand = new SqlCommand(sql.ToString(), dbConnection);
dbCommand.Transaction = dbTransaction;
dbCommand.Parameters.Add("userName", SqlDbType.NVarChar).Value = user.Username;
dbCommand.Parameters.Add("email", SqlDbType.NVarChar).Value = user.Emails[0];
dbCommand.Parameters.Add("password", SqlDbType.NVarChar).Value = user.Password;
dbCommand.Parameters.Add("defaultProfilePic", SqlDbType.NVarChar).Value = Utilities.WS_API_URL + "images/profile_pics/profile_default.jpg";
int result = -1;
try
{
result = GeneralExtractor.ExtractInt(dbCommand.ExecuteReader(), 0, -1);
dbTransaction.Commit();
}
catch (SqlException)
{
dbTransaction.Rollback();
}
dbConnection.Close();
return(result);
}
public string GenerateOrReturnPasswordResetCode(string email)
{
SqlConnection dbConnection = DBUtils.getDbConnection();
SqlTransaction dbTransaction = dbConnection.BeginTransaction();
StringBuilder sql = new StringBuilder();
sql.Append(" IF EXISTS ");
sql.Append(" ( ");
sql.Append(" SELECT pwr_code ");
sql.Append(" FROM tblPasswordReset ");
sql.Append(" WHERE pwr_email = @email AND pwr_attempts < 3 AND pwr_request_date > DATEADD(MINUTE, -15, GETDATE()) ");
sql.Append(" ) ");
sql.Append(" BEGIN ");
sql.Append(" SELECT pwr_code FROM tblPasswordReset WHERE pwr_email = @email; ");
sql.Append(" END ");
sql.Append(" ELSE ");
sql.Append(" BEGIN ");
sql.Append(" DELETE tblPasswordReset WHERE pwr_email = @email; ");
sql.Append(" DECLARE @codeVar VARCHAR(6) = '" + Utilities.generatePasswordResetCode() + "'; ");
sql.Append(" INSERT INTO tblPasswordReset (pwr_email, pwr_code, pwr_request_date) VALUES (@email, @codeVar, GETDATE()); ");
sql.Append(" SELECT @codeVar; ");
sql.Append(" END ");
SqlCommand dbCommand = new SqlCommand(sql.ToString(), dbConnection);
dbCommand.Transaction = dbTransaction;
dbCommand.Parameters.Add("email", SqlDbType.NVarChar).Value = email;
string result = null;
try
{
result = GeneralExtractor.ExtractString(dbCommand.ExecuteReader(), 0, null);
dbTransaction.Commit();
}
catch (SqlException)
{
dbTransaction.Rollback();
}
dbConnection.Close();
return(result);
}
public bool ResetUserPassword(string email, string code, string newPassword)
{
SqlConnection dbConnection = DBUtils.getDbConnection();
SqlTransaction dbTransaction = dbConnection.BeginTransaction();
StringBuilder sql = new StringBuilder();
sql.Append(" IF EXISTS ");
sql.Append(" ( ");
sql.Append(" SELECT pwr_code ");
sql.Append(" FROM tblPasswordReset ");
sql.Append(" WHERE pwr_email = @email AND pwr_attempts < 3 AND pwr_code = @code AND pwr_request_date > DATEADD(MINUTE, -20, GETDATE()) ");
sql.Append(" ) ");
sql.Append(" BEGIN ");
//Update password and remove code entry
sql.Append(" UPDATE tblUsers SET us_password = @password ");
sql.Append(" FROM tblUsers ");
sql.Append(" INNER JOIN tblUserEmails ON usem_us_id = us_id ");
sql.Append(" WHERE usem_email = @email; ");
sql.Append(" DELETE tblPasswordReset WHERE pwr_email = @email; ");
sql.Append(" SELECT 1; ");
sql.Append(" END ");
sql.Append(" ELSE ");
sql.Append(" BEGIN ");
sql.Append(" IF EXISTS ");
sql.Append(" ( ");
sql.Append(" SELECT pwr_code ");
sql.Append(" FROM tblPasswordReset ");
sql.Append(" WHERE pwr_email = @email AND pwr_attempts < 3 AND pwr_request_date > DATEADD(MINUTE, -20, GETDATE()) ");
sql.Append(" ) ");
sql.Append(" BEGIN ");
//Incorrect code - increase number of attempts
sql.Append(" UPDATE tblPasswordReset SET pwr_attempts = pwr_attempts + 1 FROM tblPasswordReset WHERE pwr_email = @email; ");
sql.Append(" END ");
sql.Append(" ELSE ");
sql.Append(" BEGIN ");
//3 attempts have been reached or code has expired or email does not exist in the request table - remove code entry for email
sql.Append(" DELETE tblPasswordReset WHERE pwr_email = @email; ");
sql.Append(" END ");
sql.Append(" SELECT 0; ");
sql.Append(" END ");
SqlCommand dbCommand = new SqlCommand(sql.ToString(), dbConnection);
dbCommand.Transaction = dbTransaction;
dbCommand.Parameters.Add("email", SqlDbType.NVarChar).Value = email;
dbCommand.Parameters.Add("code", SqlDbType.NVarChar).Value = code;
dbCommand.Parameters.Add("password", SqlDbType.NVarChar).Value = newPassword;
bool result = false;
try
{
result = GeneralExtractor.ExtractInt(dbCommand.ExecuteReader(), 0, 0) == 1;
dbTransaction.Commit();
}
catch (SqlException)
{
dbTransaction.Rollback();
}
dbConnection.Close();
return(result);
}
/// <summary>
/// Creates user via social network. If user already exists, update existing one with social network details
/// </summary>
/// <param name="user">User object</param>
/// <param name="socialNetworkName">Social network name. I.e. Google</param>
/// <returns>User id</returns>
public int CreateUserViaSocialNetwork(User user, string socialNetworkName)
{
SqlConnection dbConnection = DBUtils.getDbConnection();
SqlTransaction dbTransaction = dbConnection.BeginTransaction();
StringBuilder sql = new StringBuilder();
sql.Append(" DECLARE @userId INT; ");
sql.Append(" IF NOT EXISTS (SELECT usem_us_id FROM tblUserEmails WHERE usem_email = @email) ");
sql.Append(" BEGIN ");
// User does not exist - create a new one
sql.Append(" INSERT INTO tblUsers (us_name, us_password, us_registration_date, us_profile_picture) VALUES (@userName, @password, GETDATE(), @profilePic); ");
sql.Append(" SET @userId = (SELECT CAST(SCOPE_IDENTITY() AS INT)); ");
sql.Append(" INSERT INTO tblUserEmails (usem_email, usem_us_id, usem_verified, usem_verified_date) VALUES (@email, @userId, 0, GETDATE()); ");
sql.Append(" INSERT INTO tblUserSocialMedia (ussm_socntw_id, ussm_socntw_user_id, ussm_usem_email, ussm_us_id) ");
sql.Append(" SELECT socntw_id, @socialNetworkUserId, @email, @userId ");
sql.Append(" FROM prmSocialNetwork ");
sql.Append(" WHERE socntw_name = @socialNetworkName; ");
sql.Append(" SELECT @userId; ");
sql.Append(" END ");
sql.Append(" ELSE ");
sql.Append(" BEGIN ");
sql.Append(" IF NOT EXISTS (SELECT ussm_usem_email FROM tblUserSocialMedia INNER JOIN prmSocialNetwork ON socntw_id = ussm_socntw_id WHERE socntw_name = @socialNetworkName AND ussm_usem_email = @email) ");
sql.Append(" AND (SELECT TOP 1 usem_verified FROM tblUserEmails WHERE usem_email = @email) = 1 ");
sql.Append(" BEGIN");
// User exists - update it with social network details
sql.Append(" SET @userId = (SELECT usem_us_id FROM tblUserEmails WHERE usem_email = @email); ");
sql.Append(" UPDATE tblUsers ");
sql.Append(" SET us_profile_picture = CASE us_profile_picture WHEN @defaultProfilePic THEN @profilePic ELSE @defaultProfilePic END ");
sql.Append(" WHERE us_id = @userId; ");
sql.Append(" INSERT INTO tblUserSocialMedia (ussm_socntw_id, ussm_socntw_user_id, ussm_usem_email, ussm_us_id) ");
sql.Append(" SELECT socntw_id, @socialNetworkUserId, @email, @userId ");
sql.Append(" FROM prmSocialNetwork ");
sql.Append(" WHERE socntw_name = @socialNetworkName; ");
sql.Append(" SELECT @userId; ");
sql.Append(" END ");
sql.Append(" ELSE ");
sql.Append(" BEGIN");
// Such email already exists with this social network or email exists but user has not been activated - should never happen
sql.Append(" SELECT -1; ");
sql.Append(" END");
sql.Append(" END ");
SqlCommand dbCommand = new SqlCommand(sql.ToString(), dbConnection);
dbCommand.Transaction = dbTransaction;
dbCommand.Parameters.Add("Username", SqlDbType.NVarChar).Value = user.Username;
dbCommand.Parameters.Add("email", SqlDbType.NVarChar).Value = user.Emails[0];
dbCommand.Parameters.Add("password", SqlDbType.NVarChar).Value = user.Password;
dbCommand.Parameters.Add("socialNetworkUserId", SqlDbType.NVarChar).Value = user.SocialNetworkUserId;
dbCommand.Parameters.Add("socialNetworkName", SqlDbType.NVarChar).Value = socialNetworkName;
dbCommand.Parameters.Add("profilePic", SqlDbType.NVarChar).Value = user.ProfilePicURL;
dbCommand.Parameters.Add("defaultProfilePic", SqlDbType.NVarChar).Value = Utilities.WS_API_URL + "images/profile_pics/profile_default.jpg";
int result = -1;
try
{
result = GeneralExtractor.ExtractInt(dbCommand.ExecuteReader(), 0, -1);
dbTransaction.Commit();
}
catch (SqlException)
{
dbTransaction.Rollback();
}
dbConnection.Close();
return(result);
}
