Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Solved BindValue() in QPSQL is not working?

    General and Desktop
    qsqlquery postgresql sqldriver sql
    5
    13
    2148
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • BartoszPaj
      BartoszPaj last edited by

      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

      JonB 1 Reply Last reply Reply Quote 0
      • JonB
        JonB @BartoszPaj last edited by JonB

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

        BartoszPaj 1 Reply Last reply Reply Quote 1
        • BartoszPaj
          BartoszPaj @JonB last edited by BartoszPaj

          @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);
          
          JonB 1 Reply Last reply Reply Quote 0
          • JonB
            JonB @BartoszPaj last edited by

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

            BartoszPaj 1 Reply Last reply Reply Quote 0
            • BartoszPaj
              BartoszPaj @JonB last edited by

              @JonB

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

              1 Reply Last reply Reply Quote 0
              • SGaist
                SGaist Lifetime Qt Champion last edited by

                Hi,

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

                See the PostgreSQL documentation prepare chapter.

                Interested in AI ? www.idiap.ch
                Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                1 Reply Last reply Reply Quote 1
                • BartoszPaj
                  BartoszPaj last edited by BartoszPaj

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

                  VRonin 1 Reply Last reply Reply Quote 0
                  • VRonin
                    VRonin @BartoszPaj last edited by VRonin

                    @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

                    "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                    ~Napoleon Bonaparte

                    On a crusade to banish setIndexWidget() from the holy land of Qt

                    1 Reply Last reply Reply Quote 2
                    • Christian Ehrlicher
                      Christian Ehrlicher Lifetime Qt Champion last edited by

                      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

                      Qt has to stay free or it will die.

                      1 Reply Last reply Reply Quote 2
                      • BartoszPaj
                        BartoszPaj last edited by

                        @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
                        
                        1 Reply Last reply Reply Quote 0
                        • Christian Ehrlicher
                          Christian Ehrlicher Lifetime Qt Champion last edited by

                          Please show some code.

                          Qt has to stay free or it will die.

                          1 Reply Last reply Reply Quote 0
                          • BartoszPaj
                            BartoszPaj last edited by

                            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.

                            1 Reply Last reply Reply Quote 0
                            • Christian Ehrlicher
                              Christian Ehrlicher Lifetime Qt Champion last edited by

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

                              Qt has to stay free or it will die.

                              1 Reply Last reply Reply Quote 2
                              • First post
                                Last post