authenticate user for valid password in sqlite in c++

Question

I am very new to sqlite , please help me out the username and password are passed as arguments and I have already stored the password to a corresponding username in the table name as CREDENTIALS. How to check if the password is valid? Thanks

        bool authenticateUser(string USER, string PASS)
        {
            sqlite3_stmt *pSelectStatement = NULL;
            int iResult = SQLITE_ERROR;
            bool ret = true;
            insertQuery<<"SELECT * FROM CREDENTIALS WHERE USERID LIKE '"<<USER<<"' AND PASSWORD LIKE '"<<PASS<<"';";
            iResult = sqlite3_prepare16_v2(db, insertQuery.str().c_str(), -1, &pSelectStatement, 0);
            if ((iResult == SQLITE_OK) && (pSelectStatement != NULL))
            {
                iResult = sqlite3_step(pSelectStatement);
                //was found?
                if (iResult == SQLITE_DONE) {
                    ret = false;
                    sqlite3_clear_bindings(pSelectStatement);
                    sqlite3_reset(pSelectStatement);
                 }
                iResult = sqlite3_finalize(pSelectStatement);
             }
             return ret;
         }

What type is `USERID` column? And you probably don't want to save the passwords as they are in the database. Read this http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database — Valentin, Nov 03 '16 at 16:02

score 0 · Answer 1 · answered Nov 03 '16 at 16:17

If I understand your requirement correctly, all you have to do is fire a select count query on the credentials table and then evaluate the result. If the count is 0, the password or the user id is wrong. If it is greater than 0 (ideally equal to 1), the credentials are valid.

See if the following works:

insertQuery<<"SELECT COUNT(1) FROM CREDENTIALS WHERE USERID LIKE '"<<USER<<"' AND PASSWORD LIKE '"<<PASS<<"';";

Then call sqlite3_prepare16_v2 and sqlite3_step as you are already doing. Then call sqlite3_column_int to get the value. The next call to sqlite3_step will then return SQLITE_DONE.

score 0 · Answer 2 · answered Nov 03 '16 at 17:48

I just figured out the problem in the function:

    iResult=sqlite3_prepare16_v2(db, insertQuery.str().c_str(), -1, &pSelectStatement, 0);

I just changed it to:

    iResult = sqlite3_prepare_v2(db, insertQuery.str().c_str(), -1, &pSelectStatement, 0);

and now the problem is solved. Thanks

authenticate user for valid password in sqlite in c++

2 Answers2