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