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