BindValue() in QPSQL is not working?

[BartoszPaj]

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]

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

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

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

[BartoszPaj]

@JonB

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

[SGaist]

Hi,

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

See the PostgreSQL documentation prepare chapter.

[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]

@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

[Christian Ehrlicher]

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

[BartoszPaj]

@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

[Christian Ehrlicher]

Please show some code.

[BartoszPaj]

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.

[Christian Ehrlicher]

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