Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. BindValue() in QPSQL is not working?
Forum Updated to NodeBB v4.3 + New Features

BindValue() in QPSQL is not working?

Scheduled Pinned Locked Moved Solved General and Desktop
qsqlquerypostgresqlsqldriversql
13 Posts 5 Posters 3.3k Views 2 Watching
  • 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.
  • B Offline
    B Offline
    BartoszPaj
    wrote on 29 Jan 2019, 09:13 last edited by
    #1

    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

    J 1 Reply Last reply 29 Jan 2019, 09:36
    0
    • B BartoszPaj
      29 Jan 2019, 09:13

      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

      J Online
      J Online
      JonB
      wrote on 29 Jan 2019, 09:36 last edited by JonB
      #2

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

      B 1 Reply Last reply 29 Jan 2019, 09:42
      1
      • J JonB
        29 Jan 2019, 09:36

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

        B Offline
        B Offline
        BartoszPaj
        wrote on 29 Jan 2019, 09:42 last edited by BartoszPaj
        #3

        @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);
        
        J 1 Reply Last reply 29 Jan 2019, 09:53
        0
        • B BartoszPaj
          29 Jan 2019, 09:42

          @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);
          
          J Online
          J Online
          JonB
          wrote on 29 Jan 2019, 09:53 last edited by
          #4

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

          B 1 Reply Last reply 29 Jan 2019, 09:56
          0
          • J JonB
            29 Jan 2019, 09:53

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

            B Offline
            B Offline
            BartoszPaj
            wrote on 29 Jan 2019, 09:56 last edited by
            #5

            @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
            0
            • S Offline
              S Offline
              SGaist
              Lifetime Qt Champion
              wrote on 29 Jan 2019, 22:50 last edited by
              #6

              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
              1
              • B Offline
                B Offline
                BartoszPaj
                wrote on 4 Feb 2019, 08:50 last edited by BartoszPaj 2 Apr 2019, 08:52
                #7

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

                V 1 Reply Last reply 4 Feb 2019, 09:10
                0
                • B BartoszPaj
                  4 Feb 2019, 08:50

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

                  V Offline
                  V Offline
                  VRonin
                  wrote on 4 Feb 2019, 09:10 last edited by VRonin 2 Apr 2019, 09:13
                  #8

                  @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
                  2
                  • Christian EhrlicherC Offline
                    Christian EhrlicherC Offline
                    Christian Ehrlicher
                    Lifetime Qt Champion
                    wrote on 4 Feb 2019, 16:42 last edited by
                    #9

                    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 Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                    Visit the Qt Academy at https://academy.qt.io/catalog

                    1 Reply Last reply
                    2
                    • B Offline
                      B Offline
                      BartoszPaj
                      wrote on 2 Sept 2019, 10:39 last edited by
                      #10

                      @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
                      0
                      • Christian EhrlicherC Offline
                        Christian EhrlicherC Offline
                        Christian Ehrlicher
                        Lifetime Qt Champion
                        wrote on 2 Sept 2019, 16:26 last edited by
                        #11

                        Please show some code.

                        Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                        Visit the Qt Academy at https://academy.qt.io/catalog

                        1 Reply Last reply
                        0
                        • B Offline
                          B Offline
                          BartoszPaj
                          wrote on 2 Sept 2019, 16:52 last edited by
                          #12

                          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
                          0
                          • Christian EhrlicherC Offline
                            Christian EhrlicherC Offline
                            Christian Ehrlicher
                            Lifetime Qt Champion
                            wrote on 3 Sept 2019, 04:49 last edited by
                            #13

                            @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 Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                            Visit the Qt Academy at https://academy.qt.io/catalog

                            1 Reply Last reply
                            2

                            • Login

                            • Login or register to search.
                            • First post
                              Last post
                            0
                            • Categories
                            • Recent
                            • Tags
                            • Popular
                            • Users
                            • Groups
                            • Search
                            • Get Qt Extensions
                            • Unsolved