/// <summary> /// Load method for user information class. If UserID = -1, the user failed to be loaded. Override this method to handle user logins. /// </summary> /// <param name="usernameOrEmail">The username or email to check. Which one to pass depends on the useEmailForLogin parameter.</param> /// <param name="password">The password to try.</param> /// <param name="useEmailForLogin">If true, the usernameOrEmail value will be assumed to be an email.</param> /// <param name="clientID">The client ID of the login attempt.</param> /// <param name="outputValue"> /// <para>The value output from the query. Use this to generate response messages. Values are:</para> /// <para>-1 - Unknown error. Something went wrong with the query.</para> /// <para>0 - Success</para> /// <para>1 - User is locked out.</para> /// <para>2 - Login failed.</para> /// <para>3 - No user or email was specified.</para> /// <para>4 - User doesn't exist.</para> /// </param> protected virtual void Load(string usernameOrEmail, string password, bool useEmailForLogin, int clientID, out int outputValue) { outputValue = -1; //Set up the sql request SQLDatabaseReporting sql = new SQLDatabaseReporting(); SQLParamList sqlParams = new SQLParamList(); sqlParams.Add(useEmailForLogin ? "@Email" : "@Username", usernameOrEmail); SqlParameter outParam; sqlParams.Add("@Password", password) .Add("@ClientID", clientID) .Add("@IP", RequestVars.GetRequestIPv4Address()) .AddOutputParam("@OutputValue", 4, out outParam); //Try and get the user's info DataTable dt = sql.ExecStoredProcedureDataTable("spCOM_WebReportingLogon", sqlParams); if (!sql.HasError) { outputValue = Conversion.StringToInt(outParam.Value.ToString(), -1); if (outputValue == 0 && dt.Rows.Count > 0) { //Success! SetUserDataFromDataRow(dt.Rows[0]); return; } } UserID = -1; }
/// <summary> /// Perform Password Update /// </summary> /// <param name="ui">Retrieved User</param> /// <param name="oldPass">User's current password</param> /// <param name="newPass">User's new password </param> /// <returns> /// /////Error Codes////// /// 1 - Success /// 2 - User not found /// 3 - Reset code expired /// 4 - Invalid password /// 5 - Database error /// 6 - No rows updated /// 7 - Provided Password is incorrect /// </returns> public static int UpdatePassword <T>(T ui, string oldPass, string newPass) where T : UserInformation, new() { //oldPass == null only when it's a GUID reset, so check if the reset date is within the limits if (oldPass == null && DateTime.Now > ui.PasswordResetDate.AddHours(12)) { return(3); //reset code expired } if (newPass.Length > 20 || newPass.Length < 6) { return(4); //invalid password length } SQLDatabaseReporting sql = new SQLDatabaseReporting(); SqlParameter outParam; SQLParamList sqlParams = new SQLParamList(); sqlParams.Add("@UserId", ui.UserID) .Add("@NewPassword", newPass) .AddOutputParam("@AffectedRows", 4, out outParam); if (oldPass != null) { sqlParams.Add("@OldPassword", oldPass); } sql.ExecStoredProcedureDataTable("spCOM_UpdatePassword", sqlParams); if (sql.HasError) { return(5); } int affectedRows = Conversion.StringToInt(outParam.Value.ToString(), -1); if (affectedRows > 1) { return(5); // Database Error } // Database Error if (oldPass != null && affectedRows == 0) { return(7); // assume if the old password was provided and no rows are returned that the password is incorrect } if (affectedRows == 0) { return(6); // information is valid but no rows updated } return(affectedRows == 1 ? 1 : 0); // if not successful by this point return unknown error (1 = success, 0, unknown) }
/// <summary> /// Loads a user based on their GUID string. /// </summary> /// <param name="guid">The GUID of the user to look up.</param> protected virtual void Load(string guid) { SQLDatabaseReporting sql = new SQLDatabaseReporting(); //Try and get the user's info DataTable dt = sql.ExecStoredProcedureDataTable("spCOM_LoadUserData", new SqlParameter("@GUID", guid)); if (!sql.HasError) { if (dt.Rows.Count > 0) { //Success! SetUserDataFromDataRow(dt.Rows[0]); return; } } UserID = -1; }
/// <summary> /// Assgn new reset GUID to user and update password reset date /// </summary> /// <param name="userId"></param> /// <param name="errorCode"> /// Action Response Code /// Error Codes: /// 0 -- Unknown Error /// 1 -- Success /// 2 -- UserId not found /// 3 -- Something went horribly wrong(Multiple records affected) /// 4 -- UserId not provided /// 5 -- Critical Error /// </param> /// <returns></returns> public static string ResetPassword(int userId, out int errorCode) { SQLDatabaseReporting sql = new SQLDatabaseReporting(); DataTable dt = sql.ExecStoredProcedureDataTable("spCOM_PasswordReset", new SqlParameter("@UserId", userId)); if (sql.HasError) { errorCode = 5; //database error return(null); } if (!sql.HasError && dt.Rows.Count > 0) { DataRow dr = dt.Rows[0]; errorCode = Conversion.StringToInt(dr["OutputCode"].ToString(), -1); return(dr["GUID"].ToString()); } // no sql error but no row something went really really wrong ಠ_ಠ errorCode = 5; // critical error return(string.Empty); }
/// <summary> /// Create New User Login in database /// </summary> /// <param name="username">requested username, must be unique to client id</param> /// <param name="password">Users's password - must be between 6-20 characters</param> /// <param name="clientId">Client Id client id for application</param> /// <param name="firstName">User's First Name</param> /// <param name="lastName">User's Last Name</param> /// <param name="email">User's Email - Must be globalally unique</param> /// <param name="error"> /// Error output - /// //////Codes////// /// 0- Unknown Error /// 1- Success /// 2- Username or Email Exists /// 3- Invalid Email address /// 4- Invalid Characters in first or last name /// 5- Password invalid /// 6- Database Error /// /// </param> /// <typeparam name="T"></typeparam> /// <returns></returns> public static T CreateNewUser <T>(string username, string password, int clientId, string firstName, string lastName, string email, out int error) where T : UserInformation, new() { SQLDatabaseReporting sql = new SQLDatabaseReporting(); error = 1; if (!Validation.RegExCheck(email, ValidationType.Email)) { error = 3; // Invalid email address } if (!Validation.RegExCheck(firstName, ValidationType.Name) || !Validation.RegExCheck(lastName, ValidationType.Name)) { error = 4; // Invalid characters in name } if (password.Length < 6 || password.Length > 20) { error = 5;// invalid password } if (error != 1) // if not successful up to this point return null to prevent unnecessary queries { return(null); } bool usernameExists = sql.NonQuery("SELECT * AS [UserCount] FROM [tblCOM_Users] WHERE [Username] = @Username AND [ClientId] = @ClientId", new SqlParameter("@Username", username), new SqlParameter("@ClientId", clientId)) > 0; bool emailExists = sql.NonQuery("SELECT * AS [UserCount] FROM [tblCOM_Users] WHERE [Email] = @Email AND [ClientId] = @ClientId", new SqlParameter("@Email", email)) > 0; if (usernameExists || emailExists) { error = 2; // user exists with same email or (username and clientId) } if (error != 1) { return(null); } //if you made it this far you should be good to create the user SqlParameter userId; var sqlParams = new SQLParamList() .Add("@Username", username) .Add("@Password", password) .Add("@Password", clientId) .Add("@Email", email) .Add("@FirstName", firstName) .Add("@LastName", lastName) .Add("@ClientId", clientId) .AddOutputParam("@UserID", 4, out userId); sql.ExecStoredProcedureDataTable("spCOM_CreateNewUser", sqlParams); if (sql.HasError) { error = 6; } //Database Error if (error == 1) { var cuser = GetUser <T>(Conversion.StringToInt(userId.Value.ToString(), -1)); if (cuser == null) { error = 6; return(null); } return(cuser); // return created user here } error = 0; return(null); }