BindValue() in QPSQL is not working?



  • Hi,
    when I try use bindValue in query I get error:

    QPSQL: Unable to prepare statement", "LINE 1: LIMIT :limitValue...\n" <42703>
    

    Example query:

    QString query_text = "SELECT * FROM schema.invoice WHERE invoice.status = 1 ORDER BY invoice.date LIMIT :limitValue FOR UPDATE"
    query.prepare(query_text);
    query.bindValue(":limitValue",limit);
    query.exec();
    

    I ruled out bad query, because if I use QString("query").arg(limit) it's works.
    So, how to correct use bindValue with QPSQL?

    Qt version 5.9.5, DataBase version: PostgreSQL 10.5



  • @BartoszPaj
    I presume ('coz it works like this MS SQL Server): LIMIT (MS SQL uses TOP) cannot be followed by a variable, only by an in-line constant number? That's why arg() works but not prepare/bindValue. Or am I barking up the wrong tree?



  • @JonB
    Now I checked for syntax:

    "... WHERE invoice_id = :invoiceID "
    

    and it's still not working.
    I found more info here: https://forum.qt.io/topic/81978/qsqlquery-bindvalue-is-not-working-properly/6
    and now I would like to try db.driver()->isIdentifierEscaped(), but i don't know what to insert as identifier.

    UPDATE:
    If you want check, how to replace bindValue into query example:

    query.bindValue(":invoiceID",id_faktury);
    qDebug() << db.driver()->escapeIdentifier(":invoiceID",QSqlDriver::FieldName);
    


  • @BartoszPaj
    Sorry, have you verified what qDebug() << db.driver()->hasFeature(QSqlDriver::NamedPlaceholders); returns? Or are you saying your code now works with escapeIdentifier()?



  • @JonB

    Yup, i verified what bindValue returns by qDebug() << db.driver()->hasFeature(QSqlDriver::NamedPlaceholders);
    Now I'm testing other binding approaches.


  • Lifetime Qt Champion

    Hi,

    I don't think that bind variables can be used for limit.

    See the PostgreSQL documentation prepare chapter.



  • @SGaist
    it's looks like you're right. So I have to use QString.args(...).

    But could you explain, how to use escapeIdentifier() in practise?


  • Qt Champions 2018

    @BartoszPaj said in BindValue() in QPSQL is not working?:

    could you explain, how to use escapeIdentifier() in practise?

    escapeIdentifier returns a string that is an SQL-injection safe version of the argument.
    Take this example:

    const QString userName = QInputDialog::getText(nullptr, tr("Username"), tr("User name:"), QLineEdit::Normal);
    const QString password = QInputDialog::getText(nullptr, tr("Password"), tr("Password:"), QLineEdit::Normal);
    QSqlQuery authQuery;
    authQuery.prepare(QStringLiteral("select username from users where password='%1' and username='%2'").arg(password , userName));
    if(authQuery.exec() && authQuery.next()){
    emit authenticated()
    }
    

    This is unsafe (on many levels but we'll focus on SQL injection here) as inserting as input in both dialogues ' or ''=' will always authenticate the user as the resulting query is select username from users where password='' or ''='' and username='' or ''=''.
    escapeIdentifier fixes this problem:

    const QString userName = QInputDialog::getText(nullptr, tr("Username"), tr("User name:"), QLineEdit::Normal);
    const QString password = QInputDialog::getText(nullptr, tr("Password"), tr("Password:"), QLineEdit::Normal);
    QSqlQuery authQuery;
    const QString escapedUserName = authQuery.driver()->escapeIdentifier(userName ,QSqlDriver::FieldName);
    const QString escapedPassword  = authQuery.driver()->escapeIdentifier(password ,QSqlDriver::FieldName);
    authQuery.prepare(QStringLiteral("select username from users where password='%1' and username='%2'").arg(escapedPassword  , escapedUserName ));
    if(authQuery.exec() && authQuery.next()){
    emit authenticated()
    }
    

    The resulting query will be select username from users where password=''' or ''''=''' and username=''' or ''''='''

    Source: https://www.w3schools.com/sql/sql_injection.asp


  • Qt Champions 2018

    I tested a similar query (slightly modified tst_qsqlquery autotest) and it works fine for me:

    QVERIFY( q.prepare( "select * from " + qtest_prepare + " where id = :id order by id LIMIT :limit FOR UPDATE" ) );
    q.bindValue( ":id", i );
    q.bindValue( ":limit", 1 );
    QVERIFY_SQL( q, exec() );
    QVERIFY_SQL( q, next() );
    

    q.next() returns false if I pass 0 for :limit in bindValue().
    Postgres 11 with Qt 5.13/dev



  • @Christian-Ehrlicher , may it be.
    But I'm using Qt 5.9.x & 5.12.x with Postgres 9.3 :/

    This time i have somthing like this when I use bindValue:

    QString(INSERT INTO schema.table ( id_klienta ) VALUES ( :id_klienta ) returning id_pomiar)
    
    INSERT INTO schema.table ( id_klienta ) VALUES ( ? ) returning id_pomiar
    

    after bindValue etc. i get

    "42601", "QPSQL: Unable to create query" "ERROR:  syntax error at or near \"ng\"\nLINE 1: ...pso_online.pomiary_all ( id_klienta ) VALUES ( 643ng id_pomi...\n                                                             ^\n(42601)"
    

    When I check query into Postegres in pg_stat_activity

    query: INSERT INTO  schema.table ( id_klienta ) VALUES ( 643ng id_pomiar
    

  • Qt Champions 2018

    Please show some code.



  • I can't show all code (contract restrictions):

    QString ContentSynchronization::insertMeasurement(params)
    {
     //... some code if other table name then...
    return QString("INSERT INTO %1.%2 "
                           "("
                           " id_klienta, "
                           " model,"
                           " tbw"
                           " ) "
                           " VALUES "
                           " ( "
                           " :id_klienta, "
                           " :model,"
                           " :tbw )"
                           " ) "
                           " RETURNING "
                           " id INTO "
                           " id_pomiar ")
                    .arg(schema).arg(table_name);
    }
    
    void ContentSynchronization::insertMeasurement_bindValues(QSqlQuery *query_ptr, unsigned int clientID, const DataBaseSynchThread::TypesOfMeasurementStatus status, Measurement measurement, measurement_table table)
    {
    query_ptr->bindValue(":id_klienta",clientID);
    query_ptr->bindValue(":model",measurement.model);
    }
    

    Block where all executed

    QString queryText = insertMeasurement(data.connection->schema(),cashe);
        data.connection->setQueryText(queryText);
        insertMeasurement_bindValues(data.connection->query(),clientID,status,measumerent,cashe);
        if(data.connection->execute())
    

    But, I solved problem, by position placeholders.

    driver()->hasFeature(QSqlDriver::NamedPlaceholders) returns false.


  • Qt Champions 2018

    @bartoszpaj said in BindValue() in QPSQL is not working?:

    driver()->hasFeature(QSqlDriver::NamedPlaceholders) returns false.

    Good catch. I think it's because support for PostgreSQL 10 was added in a later version. If the version is not known to the driver it will fall back to the oldest.