QSqlQuery bindValue is not working properly

Script22

I'm trying to use QSqlQuery::bindValue with sqlite.

I have a valid connection to the database and I can execute select query with no problem.

My DB scheme as follows

CREATE TABLE "users" ( `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `username` TEXT NOT NULL, `pass` TEXT NOT NULL, `userGroup` INTEGER NOT NULL )

and here's my query

QString username = ui->usernameLineEdit->text();
QString password =  ui->passwordLineEdit->text();
dbManager->query.prepare("INSERT INTO users (username, pass, userGroup) VALUES (:name, :pass, :group)");

dbManager->query.bindValue(":name", username);
dbManager->query.bindValue(":pass", password);
dbManager->query.bindValue(":group", 0);
dbManager->query.exec();

qDebug() << dbManager->query.lastError() ;
qDebug() << dbManager->query.executedQuery();

And this is the output

QSqlError("", "Parameter count mismatch", "")
"INSERT INTO users (username, pass, userGroup) VALUES (?, ?, ?)"

What might be the issue here ?

SGaist

Hi,

Which version of Qt are you using ?

IIRC, named placeholder with SQLite will work properly with Qt 5.10.

Chrisw01

Hi,

There doesn't appear to be anything wrong with your SQL code. I wrote a little test program with 5.7.0 using MinGW and it works fine.

Here is the test code.

QSqlDatabase db;
    db=QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("TEST.DB");
    db.open();
    QSqlQuery sql;
    if(db.isValid()) {
        if(db.tables().isEmpty()) {
            sql.exec("CREATE TABLE \"users\" ( `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `username` TEXT NOT NULL, `pass` TEXT NOT NULL, `userGroup` INTEGER NOT NULL)");
            if(sql.numRowsAffected() == 1) {
                qDebug() << "Database Created!";
            }
        }
        sql.prepare("INSERT INTO users (username, pass, userGroup) VALUES (:name, :pass, :group)");
        sql.bindValue(":name", "Test Name");
        sql.bindValue(":pass", "Test Pass");
        sql.bindValue(":group", "0");
        sql.exec();
        if(sql.numRowsAffected() != 1) {
            qDebug() << sql.lastError();
            qDebug() << sql.lastQuery();
        }
        else qDebug() << "Num Rows Affected : " << sql.numRowsAffected();
    }
    else qDebug() << db.lastError();

Ran it over and over again always successful. I've been using QtSql since the 4.8 days always seems to work fine.. IIRC I did have database issues way back when with the Microsoft Compilers...

hope it helps.

VRonin

It might be the specific driver you are using. can you replace named binding with positional binding? see http://doc.qt.io/qt-5/qsqlquery.html#approaches-to-binding-values

A quick way to check why I suspect this is a problem is qDebug() << db.driver()->hasFeature(QSqlDriver::NamedPlaceholders);. if it returns false, Qt will try to do some magic to make it work anyway and sometimes it fails like in your case

Script22

@SGaist I'm using Qt 5.9 on manjaro Linux

Script22

@VRonin said in QSqlQuery bindValue is not working properly:

qDebug() << db.driver()->hasFeature(QSqlDriver::NamedPlaceholders);

It did return false, but no magic happened :)
Thanks ! I will try to figure out a work around it!

VRonin

@Script22 said in QSqlQuery bindValue is not working properly:

I will try to figure out a work around it!

The workaround is positional binding: useing (?,?,?) instead of (:name, :pass, :group)

Script22

@VRonin Tried that but it did not work either, I'm currently using an naive way via QString("query....").args()

SGaist

Did you got the same error ?

VRonin

@Script22 said in QSqlQuery bindValue is not working properly:

I'm currently using an naive way via QString("query....").args()

That is crazy dangerous for sql injection. make sure you use db.driver()->escapeIdentifier() to prevent it

Script22

@VRonin said in QSqlQuery bindValue is not working properly:

@Script22 said in QSqlQuery bindValue is not working properly:

I will try to figure out a work around it!

The workaround is positional binding: useing (?,?,?) instead of (:name, :pass, :group)

My Bad, the question mark method is working
I knew the danger, I'm just working on a demo for now
In the future I will be using MySql
Thanks everyone @VRonin @SGaist