[SOLVED] MySQL - Check credentials?

[xyz247]

I've done this in PHP, but for QT i'm not finding much documentation. I compiled the QMYSQL driver (finally), I'm connected to my local DB and all I need to do now is to compare the user supplied username and password against the database. I have already hashed & salted the password.

This is what I currently have, which is not working. More specifically, it is now saying that anything I enter is valid. Please note, QSqlQuery.lastError() is returning a null string.

I echoed the credentials just before the query is executed, everything is proper. I can't think of what it could be.

query.prepare("SELECT UID, PWD FROM login WHERE UID=? AND PWD=?");
    query.addBindValue(username);
    query.addBindValue(password);
 
    if(!query.exec())
    {
        msg.setText(query.lastError().text());
        msg.exec();
    }
 
    QSqlRecord record = query.record();
    int cols = record.count();
 
    if(cols > 0)
    {
        msg.setText("Good");
return true;
    }
    else
    {
        msg.setText("Bad");
return false;
    }

query.prepare("SELECT UID, PWD FROM login WHERE UID=? AND PWD=?");
    query.addBindValue(username);
    query.addBindValue(password);

    if(!query.exec())
    {
        msg.setText(query.lastError().text());
        msg.exec();
    }

    QSqlRecord record = query.record();
    int cols = record.count();

    if(cols > 0)
    {
        msg.setText("Good");
return true;
    }
    else
    {
        msg.setText("Bad");
return false;
    }

To copy to clipboard, switch view to plain text mode 

[norobro]

it is now saying that anything I enter is valid.

From the QSqlQuery::record() docs:

An empty record is returned when there is no active query (isActive() returns false)

So you will always get the correct column count whether you have a valid user_id/password or not.

Try testing for query.size() > 0

[xyz247]

Okay, yeah I had added that soon after and got it working. Thank you.

Is it safe to compare locally, the results from the database against what the user entered? For example:

// username: The local username that the user entered
// remoteUsername: The username retrieved from the database
// Is it safe to compare these locally on the client's computer?
if(username != remoteUsername || password != remotePassword)
            {
                // Login failed
                invalidCredentialsMsg.exec();
            }

// username: The local username that the user entered
// remoteUsername: The username retrieved from the database
// Is it safe to compare these locally on the client's computer?
if(username != remoteUsername || password != remotePassword)
            {
                // Login failed
                invalidCredentialsMsg.exec();
            }

To copy to clipboard, switch view to plain text mode 

[ChrisW67]

I'd be inclined to do this:

query.prepare("SELECT 1 FROM login WHERE UID=? AND PWD=?");
...
query.addBindValue(username);
query.addBindValue(password);
if (query.exec() && query.next()) {
    //successful
}
else {
    // unsuccessful either no match or bad SQL
}

query.prepare("SELECT 1 FROM login WHERE UID=? AND PWD=?");
...
query.addBindValue(username);
query.addBindValue(password);
if (query.exec() && query.next()) {
    //successful
}
else {
    // unsuccessful either no match or bad SQL
}

To copy to clipboard, switch view to plain text mode 

and rely on the presence of a row as the indicator of success. I don't retrieve the uid and pwd because I don't need them.

[xyz247]

Chris, you're both a gentleman and a scholar (:

That is way more convenient! Thank you both, this is solved by both of you.

Does anyone know of any decent articles or books on developing a secure authentication client? For example, encrypting/decrypting SQL query strings, max password attempts, etc.? I can't afford for an exploit to be discovered.

[ChrisW67]

If you cannot afford an exploit then you have a lot of learning to do: doing this safely is a tricky proposition. There is no iron-clad guarantee.

If the SQL server is remote then you must use an SSL encrypted connection at the very least. If the traffic in this example is sniffed then they can login simply by replaying the correct hashed password. You could investigate ways to use public key crypto to verify identity without exposing passwords on the wire at all.