Qt SQL Server - Hashed password

Arash Mitooie

Hello everyone
I've got this line of SQL query to check the validity of a username password :

if exists (select * from Acc where username = :un and userpassword = HASHBYTES('SHA2_256', :pw)) select 1 else select 0

It works correctly when executed directly in SQL Server Management studio but when I bind values and query from Qt it won't work.
What am I doing wrong? I'd appreciate any help. I'll paste the complete code section in Qt below :

    q->prepare("if exists (select * from Acc where username = :un and userpassword = HASHBYTES('SHA2_256', :pw)) select 1 else select 0");
    q->bindValue(":un", un);
    q->bindValue(":pw", pw);
    if (q->exec())
    {
        q->next();
        if (q->value(0).toInt() == 0)
        {
            QMessageBox::critical(this, "", "User not found", QMessageBox::Ok);
        }
        else
        {
            //execute form . . . 
        }

hskoglund

If pw is a QString it should work, could you try a
qDebug() << q->executedQuery();
to see what the bind() calls resolved to.

Arash Mitooie

@hskoglund
Thanks hskoglund. It is a QString in Qt. The query executes but yields wrong result. It returns 0 when executed from Qt, while when directly done in SSMS, selects 1.
I checked a thousand times whether I'm binding the exact strings I use in SSMS. I get them from line edits in Qt:

    QString un = ui->lineEditUsername->text();
    QString pw = ui->lineEditPassword->text();

hskoglund

I also have some apps for SQL Server but I don't use bind() calls, instead I compose the query string manually, like this:

QString s = QString("if exists (select * from Acc where username = '%1' and userpassword = HASHBYTES('SHA2_256', '%2')) select 1 else select 0").arg(un).arg(pw);

just to be able to do a qDebug() or trace the SQL call...

Arash Mitooie

@hskoglund
For heaven sake that worked!
I am now very very thankfull, hskoglund :-)
I (being a noobie) also learned a new Technic :-)
cool . . .

JonB

@Arash-Mitooie
Start by splitting the statement into two, let's confirm it can find the username but not the userpassword.

JonB

@Arash-Mitooie
I hate to be mean to my friend @hskoglund , but I don't think you should do it that way. As shown it will error on some passwords, and is open to injection. Binding variables is a much safer technique than putting stings in-line.

Arash Mitooie

@JonB
Thanks JonB. I tried the username alone, and it works but the password alone doesn't work via bindValue. I had read in another post that the safe way to do is to use bindValue, that's why I initially tried that. But I really don't know why it doesn't work . . .