From 1b93b9bc9f661df64e2431216f21ec8fb7e7b1cc Mon Sep 17 00:00:00 2001 From: kambizzandi Date: Wed, 12 Jan 2022 16:46:40 +0300 Subject: [PATCH] forgotpsw checked and fixture for it created --- .../Account/functionalTest/testAccount.hpp | 5 +- Modules/Account/moduleSrc/Account.cpp | 141 +++++++++++------ Modules/Account/moduleSrc/Account.h | 31 ++-- .../moduleSrc/ORM/ForgotPassRequest.cpp | 16 +- .../Account/moduleSrc/ORM/ForgotPassRequest.h | 17 +- Modules/Account/moduleSrc/ORM/Schema.my.sql | 148 ++++++++++-------- 6 files changed, 218 insertions(+), 140 deletions(-) diff --git a/Modules/Account/functionalTest/testAccount.hpp b/Modules/Account/functionalTest/testAccount.hpp index 2b8e4ced..9cbd13fc 100644 --- a/Modules/Account/functionalTest/testAccount.hpp +++ b/Modules/Account/functionalTest/testAccount.hpp @@ -338,8 +338,8 @@ private slots: void CreateForgotPasswordLink(){ QVERIFY(callAPI(RESTClientHelper::POST, "Account/createForgotPasswordLink",{},{ - {"login", UT_UserEmail}, - {"via", "Web"}, + {"emailOrMobile", UT_UserEmail}, +// {"via", "Web"}, }).toBool()); } @@ -354,6 +354,7 @@ private slots: //827ccb0eea8a706c4c34a16891f84e7b # 12345 QVERIFY(callAPI(RESTClientHelper::POST, "Account/changePassByUUID", {},{ + { "emailOrMobile", UT_UserEmail }, { "uuid", Code }, { "newPass", "827ccb0eea8a706c4c34a16891f84e7b" } }).toBool()); diff --git a/Modules/Account/moduleSrc/Account.cpp b/Modules/Account/moduleSrc/Account.cpp index 1a68f46e..f1846ad2 100644 --- a/Modules/Account/moduleSrc/Account.cpp +++ b/Modules/Account/moduleSrc/Account.cpp @@ -128,6 +128,26 @@ tmplConfigurable Account::InvalidPasswordsFile ( enuConfigSource::Arg | enuConfigSource::File ); +QString ValidateAndNormalizeEmailOrPhoneNumber(QString &_emailOrMobile) +{ + if (QFV.email().isValid(_emailOrMobile)) + { + if (QFV.emailNotFake().isValid(_emailOrMobile) == false) + throw exHTTPBadRequest("Email domain is suspicious. Please use a real email."); + + _emailOrMobile = _emailOrMobile.toLower(); + return "E"; + } + + if (QFV.mobile().isValid(_emailOrMobile)) + { + _emailOrMobile = PhoneHelper::NormalizePhoneNumber(_emailOrMobile); + return "M"; + } + + throw exHTTPBadRequest("emailOrMobile must be a valid email or mobile"); +} + /*****************************************************************/ /*****************************************************************/ /*****************************************************************/ @@ -216,21 +236,7 @@ QVariantMap Account::apiPUTsignup( { Authorization::validateIPAddress(_REMOTE_IP); - QString Type; - - if (QFV.email().isValid(_emailOrMobile)) { - if (QFV.emailNotFake().isValid(_emailOrMobile)) - Type = 'E'; - else - throw exHTTPBadRequest("Email domain is suspicious. Please use a real email."); - } - else if (QFV.mobile().isValid(_emailOrMobile)) - { - Type = 'M'; - _emailOrMobile = PhoneHelper::NormalizePhoneNumber(_emailOrMobile); - } - else - throw exHTTPBadRequest("emailOrMobile must be a valid email or mobile"); + QString Type = ValidateAndNormalizeEmailOrPhoneNumber(_emailOrMobile); QFV/*.asciiAlNum()*/.maxLenght(50).validate(_role); @@ -412,11 +418,12 @@ Targoman::API::AccountModule::stuMultiJWT Account::apilogin( { Authorization::validateIPAddress(_REMOTE_IP); - QFV.oneOf({QFV.emailNotFake(), QFV.mobile()}).validate(_emailOrMobile, "login"); - QFV.asciiAlNum().maxLenght(20).validate(_salt, "salt"); +// QFV.oneOf({QFV.emailNotFake(), QFV.mobile()}).validate(_emailOrMobile, "login"); +// if (QFV.mobile().isValid(_emailOrMobile)) +// _emailOrMobile = PhoneHelper::NormalizePhoneNumber(_emailOrMobile); + ValidateAndNormalizeEmailOrPhoneNumber(_emailOrMobile); - if (QFV.mobile().isValid(_emailOrMobile)) - _emailOrMobile = PhoneHelper::NormalizePhoneNumber(_emailOrMobile); + QFV.asciiAlNum().maxLenght(20).validate(_salt, "salt"); auto LoginInfo = Authentication::login(_REMOTE_IP, _emailOrMobile, @@ -471,22 +478,7 @@ bool Account::apiresendApprovalCode( { Authorization::validateIPAddress(_REMOTE_IP); - QString Type; - - if (QFV.email().isValid(_emailOrMobile)) - { - if (QFV.emailNotFake().isValid(_emailOrMobile)) - Type = 'E'; - else - throw exHTTPBadRequest("Email domain is suspicious. Please use a real email."); - } - else if (QFV.mobile().isValid(_emailOrMobile)) - { - Type = 'M'; - _emailOrMobile = PhoneHelper::NormalizePhoneNumber(_emailOrMobile); - } - else - throw exHTTPBadRequest("emailOrMobile must be a valid email or mobile"); + QString Type = ValidateAndNormalizeEmailOrPhoneNumber(_emailOrMobile); // this->callSP("AAA.sp_CREATE_approvalRequestAgain", { // { "iBy", Type }, @@ -657,44 +649,76 @@ bool Account::apilogout(TAPI::JWT_t _JWT) QString Account::apicreateForgotPasswordLink( TAPI::RemoteIP_t _REMOTE_IP, - QString _login + QString _emailOrMobile ) { Authorization::validateIPAddress(_REMOTE_IP); - QFV.oneOf({QFV.emailNotFake(), QFV.mobile()}).validate(_login, "login"); + QString Type = ValidateAndNormalizeEmailOrPhoneNumber(_emailOrMobile); this->callSP("AAA.sp_CREATE_forgotPassRequest", { - { "iLogin", _login }, - { "iVia", QString(_login.contains('@') ? 'E' : 'M') }, + { "iLogin", _emailOrMobile }, + { "iVia", Type }, }); - return _login.contains('@') ? "email" : "mobile"; + return (Type == "E" ? "email" : "mobile"); } -bool Account::apichangePass(TAPI::JWT_t _JWT, TAPI::MD5_t _oldPass, QString _oldPassSalt, TAPI::MD5_t _newPass) +#ifdef QT_DEBUG +QString Account::apiPOSTfixtureGetLastForgotPasswordUUIDAndMakeAsSent( + TAPI::RemoteIP_t _REMOTE_IP, + QString _emailOrMobile + ) { - QFV.asciiAlNum().maxLenght(20).validate(_oldPassSalt, "salt"); + Q_UNUSED(_REMOTE_IP); - this->callSP("AAA.sp_UPDATE_changePass", { - { "iUserID", clsJWT(_JWT).usrID() }, - { "iOldPass", _oldPass }, - { "iOldPassSalt", _oldPassSalt }, - { "iNewPass", _newPass }, - }); + QString Type = ValidateAndNormalizeEmailOrPhoneNumber(_emailOrMobile); - return true; + QVariantMap Data = SelectQuery(ForgotPassRequest::instance()) + .addCol(tblForgotPassRequest::fprUUID) + .addCol(tblForgotPassRequest::fprStatus) + .innerJoinWith(tblForgotPassRequest::Relation::User) + .where({ Type == "E" ? tblUser::usrEmail : tblUser::usrMobile, enuConditionOperator::Equal, _emailOrMobile }) + .andWhere({ tblForgotPassRequest::fprRequestedVia, enuConditionOperator::Equal, Type.at(0) }) + .orderBy(tblForgotPassRequest::fprRequestDate, enuOrderDir::Descending) + .one() + ; + + QString UUID = Data.value(tblForgotPassRequest::fprUUID).toString(); + + if (UUID.isEmpty()) + throw exHTTPNotFound("No UUID could be found"); + + QString fprStatus = Data.value(tblForgotPassRequest::fprStatus).toString(); + if (fprStatus != "Sent") + { + quint64 RowsCount = UpdateQuery(ForgotPassRequest::instance()) + .set(tblForgotPassRequest::fprStatus, enuFPRStatus::Sent) + .where({ tblForgotPassRequest::fprUUID, enuConditionOperator::Equal, UUID }) + .execute(1) + ; + if (RowsCount == 0) + throw exHTTPNotFound("error in set as sent"); + } + + return UUID; } +#endif bool Account::apichangePassByUUID( TAPI::RemoteIP_t _REMOTE_IP, + QString _emailOrMobile, TAPI::MD5_t _uuid, TAPI::MD5_t _newPass ) { Authorization::validateIPAddress(_REMOTE_IP); + QString Type = ValidateAndNormalizeEmailOrPhoneNumber(_emailOrMobile); + this->callSP("AAA.sp_UPDATE_changePassByUUID", { + { "iVia", Type }, + { "iLogin", _emailOrMobile }, { "iUUID", _uuid }, { "iNewPass", _newPass }, }); @@ -702,6 +726,25 @@ bool Account::apichangePassByUUID( return true; } +bool Account::apichangePass( + TAPI::JWT_t _JWT, + TAPI::MD5_t _oldPass, + QString _oldPassSalt, + TAPI::MD5_t _newPass + ) +{ + QFV.asciiAlNum().maxLenght(20).validate(_oldPassSalt, "salt"); + + this->callSP("AAA.sp_UPDATE_changePass", { + { "iUserID", clsJWT(_JWT).usrID() }, + { "iOldPass", _oldPass }, + { "iOldPassSalt", _oldPassSalt }, + { "iNewPass", _newPass }, + }); + + return true; +} + /*****************************************************************\ |* Voucher & Payments ********************************************| \*****************************************************************/ diff --git a/Modules/Account/moduleSrc/Account.h b/Modules/Account/moduleSrc/Account.h index 6ad809e5..77326042 100644 --- a/Modules/Account/moduleSrc/Account.h +++ b/Modules/Account/moduleSrc/Account.h @@ -248,30 +248,42 @@ private slots: createForgotPasswordLink, ( TAPI::RemoteIP_t _REMOTE_IP, - QString _login + QString _emailOrMobile ), "Create a forgot password request returning a UUID for the requiest" ) - bool REST_GET_OR_POST( - changePass, +#ifdef QT_DEBUG + QString REST_POST( + fixtureGetLastForgotPasswordUUIDAndMakeAsSent, ( - TAPI::JWT_t _JWT, - TAPI::MD5_t _oldPass, - QString _oldPassSalt, - TAPI::MD5_t _newPass + TAPI::RemoteIP_t _REMOTE_IP, + QString _emailOrMobile ), - "Changes password of the logged-in user" + "fixture: Get Last Forgot Password UUID And Make As Sent" ) +#endif bool REST_GET_OR_POST( changePassByUUID, ( TAPI::RemoteIP_t _REMOTE_IP, + QString _emailOrMobile, TAPI::MD5_t _uuid, TAPI::MD5_t _newPass ), - "Changes password based on a UUID provided by " + "Changes password based on a UUID provided by createForgotPasswordLink" + ) + + bool REST_GET_OR_POST( + changePass, + ( + TAPI::JWT_t _JWT, + TAPI::MD5_t _oldPass, + QString _oldPassSalt, + TAPI::MD5_t _newPass + ), + "Changes password of the logged-in user" ) /*****************************************************************\ @@ -356,7 +368,6 @@ private slots: ) #ifdef QT_DEBUG -protected slots: QVariant REST_POST( fixtureSetup, ( diff --git a/Modules/Account/moduleSrc/ORM/ForgotPassRequest.cpp b/Modules/Account/moduleSrc/ORM/ForgotPassRequest.cpp index 680ceeaf..915ce0e9 100644 --- a/Modules/Account/moduleSrc/ORM/ForgotPassRequest.cpp +++ b/Modules/Account/moduleSrc/ORM/ForgotPassRequest.cpp @@ -35,16 +35,16 @@ ForgotPassRequest::ForgotPassRequest() : intfSQLBasedModule( AAASchema, tblForgotPassRequest::Name, - {///< ColName Type Validation Default UpBy Sort Filter Self Virt PK - { tblForgotPassRequest::fprUUID, S(TAPI::MD5_t), QFV, ORM_PRIMARY_KEY }, - { tblForgotPassRequest::fpr_usrID, S(quint64), QFV.integer().minValue(1), QRequired, UPNone }, - { tblForgotPassRequest::fprRequestedVia, S(Targoman::API::AccountModule::enuForgotPassLinkVia::Type), QFV, Targoman::API::AccountModule::enuForgotPassLinkVia::Email, UPNone }, - { tblForgotPassRequest::fprRequestDate, ORM_CREATED_ON }, - { tblForgotPassRequest::fprApplyDate, S(TAPI::DateTime_t), QFV, QNull, UPNone }, - { tblForgotPassRequest::fprStatus, ORM_STATUS_FIELD(Targoman::API::AccountModule::enuFPRStatus, Targoman::API::AccountModule::enuFPRStatus::New) }, + {///< ColName Type Validation Default UpBy Sort Filter Self Virt PK + { tblForgotPassRequest::fprUUID, S(TAPI::MD5_t), QFV, ORM_PRIMARY_KEY }, + { tblForgotPassRequest::fpr_usrID, S(quint64), QFV.integer().minValue(1), QRequired, UPNone }, + { tblForgotPassRequest::fprRequestedVia, S(Targoman::API::AccountModule::enuForgotPassLinkVia::Type), QFV, Targoman::API::AccountModule::enuForgotPassLinkVia::Email, UPNone }, + { tblForgotPassRequest::fprRequestDate, ORM_CREATED_ON }, + { tblForgotPassRequest::fprApplyDate, S(TAPI::DateTime_t), QFV, QNull, UPAdmin }, + { tblForgotPassRequest::fprStatus, ORM_STATUS_FIELD(Targoman::API::AccountModule::enuFPRStatus, Targoman::API::AccountModule::enuFPRStatus::New) }, }, {///< Col Reference Table ForeignCol - { tblForgotPassRequest::fpr_usrID, R(AAASchema,tblUser::Name), tblUser::usrID }, + { tblForgotPassRequest::Relation::User, { tblForgotPassRequest::fpr_usrID, R(AAASchema,tblUser::Name), tblUser::usrID } }, } ) {} diff --git a/Modules/Account/moduleSrc/ORM/ForgotPassRequest.h b/Modules/Account/moduleSrc/ORM/ForgotPassRequest.h index 963c02ef..0da75b36 100644 --- a/Modules/Account/moduleSrc/ORM/ForgotPassRequest.h +++ b/Modules/Account/moduleSrc/ORM/ForgotPassRequest.h @@ -47,13 +47,16 @@ namespace ORM { #pragma GCC diagnostic push #pragma GCC diagnostic ignored "-Wunused-variable" namespace tblForgotPassRequest { -constexpr char Name[] = "tblForgotPassRequest"; -TARGOMAN_CREATE_CONSTEXPR(fprUUID); -TARGOMAN_CREATE_CONSTEXPR(fpr_usrID); -TARGOMAN_CREATE_CONSTEXPR(fprRequestedVia); -TARGOMAN_CREATE_CONSTEXPR(fprRequestDate); -TARGOMAN_CREATE_CONSTEXPR(fprApplyDate); -TARGOMAN_CREATE_CONSTEXPR(fprStatus); + constexpr char Name[] = "tblForgotPassRequest"; + namespace Relation { + constexpr char User[] = "user"; + } + TARGOMAN_CREATE_CONSTEXPR(fprUUID); + TARGOMAN_CREATE_CONSTEXPR(fpr_usrID); + TARGOMAN_CREATE_CONSTEXPR(fprRequestedVia); + TARGOMAN_CREATE_CONSTEXPR(fprRequestDate); + TARGOMAN_CREATE_CONSTEXPR(fprApplyDate); + TARGOMAN_CREATE_CONSTEXPR(fprStatus); } #pragma GCC diagnostic pop diff --git a/Modules/Account/moduleSrc/ORM/Schema.my.sql b/Modules/Account/moduleSrc/ORM/Schema.my.sql index ca947b75..94b72c5d 100644 --- a/Modules/Account/moduleSrc/ORM/Schema.my.sql +++ b/Modules/Account/moduleSrc/ORM/Schema.my.sql @@ -1309,53 +1309,61 @@ DELIMITER ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO' */ ; +/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `sp_CREATE_forgotPassRequest`( IN `iLogin` VARCHAR(50), IN `iVia` CHAR(1) - ) BEGIN - DECLARE UserID INT UNSIGNED; - DECLARE UserName VARCHAR(50); - DECLARE UserFamily VARCHAR(50); - DECLARE LinkUUID CHAR(32); - - SELECT tblUser.usrID, - tblUser.usrName, - tblUser.usrFamily - INTO UserID, - UserName, - UserFamily - FROM tblUser - LEFT JOIN tblForgotPassRequest + DECLARE vUserID INT UNSIGNED; + DECLARE vUserName VARCHAR(50); + DECLARE vUserFamily VARCHAR(50); + DECLARE vLinkUUID CHAR(32); + + SELECT tblUser.usrID + , tblUser.usrName + , tblUser.usrFamily + INTO vUserID + , vUserName + , vUserFamily + FROM tblUser + LEFT JOIN tblForgotPassRequest ON tblForgotPassRequest.fpr_usrID = tblUser.usrID - WHERE (tblUser.usrEmail = iLogin OR tblUser.usrMobile = iLogin) - AND (ISNULL(tblForgotPassRequest.fprStatus) - OR tblForgotPassRequest.fprStatus != 'N' - OR TIME_TO_SEC(TIMEDIFF(NOW(), tblForgotPassRequest.fprRequestDate)) > 60 - ) - LIMIT 1; - - IF (UserID IS NOT NULL) THEN - SET LinkUUID = Common.fnCreateRandomMD5(); - - INSERT INTO tblForgotPassRequest - SET tblForgotPassRequest.fpr_usrID = UserID, - tblForgotPassRequest.fprRequestedVia = iVia, - tblForgotPassRequest.fprUUID = LinkUUID; - - INSERT INTO Common.tblAlerts - SET Common.tblAlerts.alr_usrID = UserID, - Common.tblAlerts.alr_altCode = 'passReset', - Common.tblAlerts.alrReplacements = JSON_OBJECT( - 'usrName',UserName, - 'usrFamily',UserFamily, - 'via', iVia, - 'UUID',LinkUUID - ); - END IF; + WHERE ( + tblUser.usrEmail = iLogin + OR tblUser.usrMobile = iLogin + ) + AND ( + ISNULL(tblForgotPassRequest.fprStatus) + OR tblForgotPassRequest.fprStatus != 'N' + OR TIME_TO_SEC(TIMEDIFF(NOW(), tblForgotPassRequest.fprRequestDate)) > 60 + ) + LIMIT 1 + ; + + IF (vUserID IS NOT NULL) THEN + SET vLinkUUID = Common.fnCreateRandomMD5(); + + INSERT + INTO tblForgotPassRequest + SET tblForgotPassRequest.fpr_usrID = vUserID + , tblForgotPassRequest.fprRequestedVia = iVia + , tblForgotPassRequest.fprUUID = vLinkUUID + ; + + INSERT + INTO Common.tblAlerts + SET Common.tblAlerts.alr_usrID = vUserID + , Common.tblAlerts.alr_altCode = 'passReset' + , Common.tblAlerts.alrReplacements = JSON_OBJECT( + 'usrName', vUserName, + 'usrFamily', vUserFamily, + 'via', iVia, + 'UUID', vLinkUUID + ) + ; + END IF; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; @@ -2478,36 +2486,48 @@ DELIMITER ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO' */ ; +/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `sp_UPDATE_changePassByUUID`( + IN `iVia` VARCHAR(1), + IN `iLogin` VARCHAR(50), IN `iUUID` VARCHAR(50), IN `iNewPass` VARCHAR(50) ) BEGIN - DECLARE UserID BIGINT UNSIGNED; - DECLARE IsExpired BOOL; - - SELECT tblForgotPassRequest.fpr_usrID, - TIMEDIFF(NOW(), tblForgotPassRequest.fprRequestDate) > "00:30:00" - INTO UserID, IsExpired - FROM tblForgotPassRequest - WHERE tblForgotPassRequest.fprUUID = iUUID - AND tblForgotPassRequest.fprStatus = 'S'; - - IF ISNULL (IsExpired) OR IsExpired THEN - SIGNAL SQLSTATE '45000' - SET MESSAGE_TEXT = '401:Invalid or Expired link'; - END IF; - - UPDATE tblForgotPassRequest - SET tblForgotPassRequest.fprStatus = IF(tblForgotPassRequest.fprUUID = iUUID, 'A', 'E') - WHERE tblForgotPassRequest.fpr_usrID = UserID - AND tblForgotPassRequest.fprStatus IN ('S', 'N'); - - UPDATE tblUser - SET tblUser.usrPass = iNewPass - WHERE tblUser.usrID = UserID; + DECLARE vUserID BIGINT UNSIGNED; + DECLARE vIsExpired BOOL; + + SELECT tblForgotPassRequest.fpr_usrID + , TIMEDIFF(NOW(), tblForgotPassRequest.fprRequestDate) > "00:30:00" + INTO vUserID + , vIsExpired + FROM tblForgotPassRequest +INNER JOIN tblUser + ON tblUser.usrID = tblForgotPassRequest.fpr_usrID + WHERE ( + tblUser.usrEmail = iLogin + OR tblUser.usrMobile = iLogin + ) + AND tblForgotPassRequest.fprUUID = iUUID + AND tblForgotPassRequest.fprStatus = 'S' + ; + + IF ISNULL (vIsExpired) OR vIsExpired THEN + SIGNAL SQLSTATE '45000' + SET MESSAGE_TEXT = '401:Invalid or Expired link'; + END IF; + + UPDATE tblForgotPassRequest + SET tblForgotPassRequest.fprStatus = IF(tblForgotPassRequest.fprUUID = iUUID, 'A', 'E') + WHERE tblForgotPassRequest.fpr_usrID = vUserID + AND tblForgotPassRequest.fprStatus IN ('S', 'N') + ; + + UPDATE tblUser + SET tblUser.usrPass = iNewPass + WHERE tblUser.usrID = vUserID + ; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ;