QSqlQuery bindValue is not working properly



  • 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 ?


  • Lifetime Qt Champion

    Hi,

    Which version of Qt are you using ?

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



  • 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.



  • 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



  • @SGaist I'm using Qt 5.9 on manjaro Linux



  • @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!



  • @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)



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


  • Lifetime Qt Champion

    Did you got the same error ?



  • @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



  • @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


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.