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.4k 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 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