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?
QtWS25 Last Chance

BindValue() in QPSQL is not working?

Scheduled Pinned Locked Moved Solved General and Desktop
qsqlquerypostgresqlsqldriversql
13 Posts 5 Posters 3.3k Views
  • 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 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 Offline
    J Offline
    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
            • C Offline
              C 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
                • C Offline
                  C 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
                    • C Offline
                      C 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