Skip to content

change sp for delete other session when psw changed #127

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
kambizzandi merged 1 commit into from
May 8, 2022
Merged

change sp for delete other session when psw changed #127

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
122 changes: 96 additions & 26 deletions Modules/Account/functionalTest/testAccount.hpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -55,23 +55,35 @@ class testAccount : public clsBaseTest

private slots:
void initTestCase() {
cleanupAll();
initUnitTestData(false);
initUnitTestData(false, true);
}

void cleanupTestCase() {
gEncodedAdminJWT = "";
gEncodedJWT = "";
// cleanupUnitTestData();

// cleanupAll();
cleanupUnitTestData();
}

private:
/***************************************************************************************/
/* cleanup *****************************************************************************/
/***************************************************************************************/
void cleanupAll() {
/*
DELETE FROM dev_AAA.tblWalletBalances;
DELETE FROM dev_AAA.tblWalletsTransactions;
DELETE FROM dev_AAA.tblUserWallets;
DELETE FROM dev_AAA.tblOnlinePayments;

DELETE FROM dev_AAA.tblOfflinePayments;
DELETE FROM dev_AAA.tblOfflinePaymentClaims;
DELETE FROM dev_AAA.tblVoucher;
DELETE FROM dev_AAA.tblApprovalRequest;
DELETE FROM dev_AAA.tblActiveSessions;
DELETE FROM dev_AAA.tblUser WHERE usrID > 100;
DELETE FROM dev_AAA.tblRoles WHERE LOWER(rolName) LIKE '%test%';
DELETE FROM dev_Common.tblAlerts;
*/
void cleanupUnitTestData() {
QVariantMap Result;

clsDAC DAC;
Expand All @@ -91,7 +103,9 @@ private slots:
;)";
clsDACResult DACResult = DAC.execQuery("", QueryString);
Result.insert("tblWalletBalances", QVariantMap({{ "numRowsAffected", DACResult.numRowsAffected() }}));
} catch (...) { ; }
} catch (std::exception &_exp) {
qDebug() << "*** tblWalletBalances CLEANUP EXCEPTION ***" << _exp.what();
}

try {
QString QueryString = R"(
Expand All @@ -106,7 +120,9 @@ private slots:
;)";
clsDACResult DACResult = DAC.execQuery("", QueryString);
Result.insert("tblWalletsTransactions", QVariantMap({{ "numRowsAffected", DACResult.numRowsAffected() }}));
} catch (...) { ; }
} catch (std::exception &_exp) {
qDebug() << "*** tblWalletsTransactions CLEANUP EXCEPTION ***" << _exp.what();
}

try {
QString QueryString = R"(
Expand All @@ -119,37 +135,60 @@ private slots:
;)";
clsDACResult DACResult = DAC.execQuery("", QueryString);
Result.insert("tblUserWallets", QVariantMap({{ "numRowsAffected", DACResult.numRowsAffected() }}));
} catch (...) { ; }
} catch (std::exception &_exp) {
qDebug() << "*** tblUserWallets CLEANUP EXCEPTION ***" << _exp.what();
}

try {
QString QueryString = R"(
DELETE op
FROM tblOnlinePayments op
DELETE onp
FROM tblOnlinePayments onp
INNER JOIN tblVoucher vch
ON vch.vchID = op.onp_vchID
ON vch.vchID = onp.onp_vchID
INNER JOIN tblUser u
ON u.usrID = vch.vch_usrID
WHERE LOWER(u.usrEmail) LIKE 'unit_test%'
OR u.usrMobile LIKE '+98999888%'
;)";
clsDACResult DACResult = DAC.execQuery("", QueryString);
Result.insert("tblOnlinePayments", QVariantMap({{ "numRowsAffected", DACResult.numRowsAffected() }}));
} catch (...) { ; }
} catch (std::exception &_exp) {
qDebug() << "*** tblOnlinePayments CLEANUP EXCEPTION ***" << _exp.what();
}

try {
QString QueryString = R"(
DELETE fp
FROM tblOfflinePayments fp
DELETE ofp
FROM tblOfflinePayments ofp
INNER JOIN tblVoucher vch
ON vch.vchID = fp.ofp_vchID
ON vch.vchID = ofp.ofp_vchID
INNER JOIN tblUser u
ON u.usrID = vch.vch_usrID
WHERE LOWER(u.usrEmail) LIKE 'unit_test%'
OR u.usrMobile LIKE '+98999888%'
;)";
clsDACResult DACResult = DAC.execQuery("", QueryString);
Result.insert("tblOfflinePayments", QVariantMap({{ "numRowsAffected", DACResult.numRowsAffected() }}));
} catch (...) { ; }
} catch (std::exception &_exp) {
qDebug() << "*** tblOfflinePayments CLEANUP EXCEPTION ***" << _exp.what();
}

try {
QString QueryString = R"(
DELETE ofpc
FROM tblOfflinePaymentClaims ofpc
INNER JOIN tblVoucher vch
ON vch.vchID = ofpc.ofpc_vchID
INNER JOIN tblUser u
ON u.usrID = vch.vch_usrID
WHERE LOWER(u.usrEmail) LIKE 'unit_test%'
OR u.usrMobile LIKE '+98999888%'
;)";
clsDACResult DACResult = DAC.execQuery("", QueryString);
Result.insert("tblOfflinePaymentClaims", QVariantMap({{ "numRowsAffected", DACResult.numRowsAffected() }}));
} catch (std::exception &_exp) {
qDebug() << "*** tblOfflinePaymentClaims CLEANUP EXCEPTION ***" << _exp.what();
}

try {
QString QueryString = R"(
Expand All @@ -162,7 +201,9 @@ private slots:
;)";
clsDACResult DACResult = DAC.execQuery("", QueryString);
Result.insert("tblVoucher", QVariantMap({{ "numRowsAffected", DACResult.numRowsAffected() }}));
} catch (...) { ; }
} catch (std::exception &_exp) {
qDebug() << "*** tblVoucher CLEANUP EXCEPTION ***" << _exp.what();
}

try {
QString QueryString = R"(
Expand All @@ -175,7 +216,9 @@ private slots:
;)";
clsDACResult DACResult = DAC.execQuery("", QueryString);
Result.insert("tblApprovalRequest", QVariantMap({{ "numRowsAffected", DACResult.numRowsAffected() }}));
} catch (...) { ; }
} catch (std::exception &_exp) {
qDebug() << "*** tblApprovalRequest CLEANUP EXCEPTION ***" << _exp.what();
}

try {
QString QueryString = R"(
Expand All @@ -188,7 +231,9 @@ private slots:
;)";
clsDACResult DACResult = DAC.execQuery("", QueryString);
Result.insert("tblActiveSessions", QVariantMap({{ "numRowsAffected", DACResult.numRowsAffected() }}));
} catch (...) { ; }
} catch (std::exception &_exp) {
qDebug() << "*** tblActiveSessions CLEANUP EXCEPTION ***" << _exp.what();
}

try {
QString QueryString = R"(
Expand All @@ -199,7 +244,9 @@ private slots:
;)";
clsDACResult DACResult = DAC.execQuery("", QueryString);
Result.insert("tblUser", QVariantMap({{ "numRowsAffected", DACResult.numRowsAffected() }}));
} catch (...) { ; }
} catch (std::exception &_exp) {
qDebug() << "*** tblUser CLEANUP EXCEPTION ***" << _exp.what();
}

try {
QString QueryString = R"(
Expand All @@ -209,7 +256,9 @@ private slots:
;)";
clsDACResult DACResult = DAC.execQuery("", QueryString);
Result.insert("tblRoles", QVariantMap({{ "numRowsAffected", DACResult.numRowsAffected() }}));
} catch (...) { ; }
} catch (std::exception &_exp) {
qDebug() << "*** tblRoles CLEANUP EXCEPTION ***" << _exp.what();
}

qDebug() << Result;
}
Expand Down Expand Up @@ -529,8 +578,8 @@ private slots:

void CreateForgotPasswordLink() {
QVERIFY(callUserAPI(RESTClientHelper::POST,
"Account/createForgotPasswordLink",{}, {
{"emailOrMobile", UT_UserEmail},
"Account/createForgotPasswordLink", {}, {
{ "emailOrMobile", UT_UserEmail },
}).toBool());
}

Expand Down Expand Up @@ -1093,7 +1142,10 @@ private slots:
QVERIFY(this->WithdrawalID > 0);

} QT_CATCH (const std::exception &exp) {
QTest::qFail(exp.what(), __FILE__, __LINE__);
if (QString(exp.what()) == "User not found")
QVERIFY(true);
else
QTest::qFail(exp.what(), __FILE__, __LINE__);
}
}
void requestWithdrawalFor_admin_invalid_iban() {
Expand All @@ -1114,11 +1166,29 @@ private slots:
QVERIFY(this->WithdrawalID > 0);

} QT_CATCH (const std::exception &exp) {
QTest::qFail(exp.what(), __FILE__, __LINE__);
if (QString(exp.what()) == "IBAN not defined")
QVERIFY(true);
else
QTest::qFail(exp.what(), __FILE__, __LINE__);
}
}
// "Account/UserWallets/acceptWithdrawal"

};

/*
DELETE FROM dev_AAA.tblWalletsTransactions;
DELETE FROM dev_AAA.tblWalletBalances;
DELETE FROM dev_AAA.tblUserWallets;
DELETE FROM dev_AAA.tblOnlinePayments;
DELETE FROM dev_AAA.tblOfflinePayments;
DELETE FROM dev_AAA.tblOfflinePaymentClaims;
DELETE FROM dev_AAA.tblVoucher;
DELETE FROM dev_AAA.tblApprovalRequest;
DELETE FROM dev_AAA.tblActiveSessions;
DELETE FROM dev_AAA.tblUser WHERE usrID > 100;
DELETE FROM dev_AAA.tblRoles WHERE LOWER(rolName) LIKE '%test%';
DELETE FROM dev_Common.tblAlerts;
*/

#endif // TEST_ACCOUNT_HPP
95 changes: 95 additions & 0 deletions Modules/Account/migrations/db/m20220508_171120_AAA_delete_other_sessions_if_psw_changed.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,95 @@
/* Migration File: m20220508_171120_AAA_delete_other_sessions_if_psw_changed.sql */
/* CAUTION: don't forget to use {{dbprefix}} for schemas */

DROP PROCEDURE IF EXISTS `spPassword_Change`;
DELIMITER ;;
CREATE PROCEDURE `spPassword_Change`(
IN `iUserID` BIGINT UNSIGNED,
IN `iSessionGUID` CHAR(32),
IN `iOldPass` CHAR(32),
IN `iOldPassSalt` VARCHAR(50),
IN `iNewPass` CHAR(32)
)
BEGIN
DECLARE vWasOK BOOL DEFAULT FALSE;

SELECT 1
INTO vWasOK
FROM tblUser
WHERE tblUser.usrID = iUserID
AND fnPasswordsAreEqual(iOldPass, iOldPassSalt, tblUser.usrPass)
;

IF NOT vWasOK THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '401:Invalid User or Password';
END IF;

UPDATE tblUser
SET tblUser.usrPass = iNewPass
WHERE tblUser.usrID = iUserID
;

-- forcibly logout other user's sessions
UPDATE tblActiveSessions
SET tblActiveSessions.ssnStatus = 'F'
WHERE tblActiveSessions.ssn_usrID = iUserID
AND tblActiveSessions.ssnKey <> iSessionGUID
AND tblActiveSessions.ssnStatus = 'A'
;

END;;
DELIMITER ;

DROP PROCEDURE IF EXISTS `spPassword_ChangeByCode`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `spPassword_ChangeByCode`(
IN `iBy` VARCHAR(1),
IN `iLogin` VARCHAR(50),
IN `iCode` VARCHAR(50),
IN `iNewPass` VARCHAR(50)
)
BEGIN
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.fprCode = iCode
AND tblForgotPassRequest.fprStatus = 'S'
;

IF ISNULL(vIsExpired) OR vIsExpired THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '401:Code is invalid or expired';
END IF;

UPDATE tblForgotPassRequest
SET tblForgotPassRequest.fprStatus = IF(tblForgotPassRequest.fprCode = iCode, 'A', 'E')
WHERE tblForgotPassRequest.fpr_usrID = vUserID
AND tblForgotPassRequest.fprStatus IN ('S', 'N')
;

UPDATE tblUser
SET tblUser.usrPass = iNewPass
WHERE tblUser.usrID = vUserID
;

-- forcibly logout all user's sessions
UPDATE tblActiveSessions
SET tblActiveSessions.ssnStatus = 'F'
WHERE tblActiveSessions.ssn_usrID = vUserID
AND tblActiveSessions.ssnStatus = 'A'
;

END;;
DELIMITER ;
7 changes: 5 additions & 2 deletions Modules/Account/moduleSrc/Account.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -168,7 +168,7 @@ Account::Account() :
if (InputFile.open(QIODevice::ReadOnly)) {
QTextStream Stream(&InputFile);
while (!Stream.atEnd())
InvalidPasswords.insert(Stream.readLine().replace(QRegularExpression("#.*"), ""));
InvalidPasswords.insert(Stream.readLine().replace(QRegularExpression("#.*"), "").trimmed());
InputFile.close();
}
}
Expand Down Expand Up @@ -685,8 +685,11 @@ bool Account::apichangePass(
) {
QFV.asciiAlNum().maxLenght(20).validate(_oldPassSalt, "salt");

clsJWT JWT(_APICALLBOOM.getJWT());

this->callSP("spPassword_Change", {
{ "iUserID", _APICALLBOOM.getUserID() },
{ "iUserID", JWT.usrID() },
{ "iSessionGUID", JWT.session() },
{ "iOldPass", _oldPass },
{ "iOldPassSalt", _oldPassSalt },
{ "iNewPass", _newPass },
Expand Down