Qt Sql not able to bind variables to QSqlQuery prepare Statement

Question

I am trying to fetch some records from MySQL database by using a prepared statement by using QSqlQuery as:

 QString username=ui->textEdit_password->toPlainText();
 QString password=ui->textEdit_password->toPlainText();
 QSqlQuery query;
 query.prepare("SELECT * FROM login_access WHERE username=? AND password=?");
 query.addBindValue(username);
 query.addBindValue(password);
 query.exec();`

When i run : std::string q_str1=query.executedQuery().toUtf8().constData(); std::cout<<"Query : "<<q_str1<<"\n"; It outputs : Query : SELECT * FROM login_access WHERE username=? AND password=? where the "?" has not been replaced and the query returns nothing since the "?" character is compared to the database records.

On running the query: SELECT * FROM login_access, the query returns all the database records in the login_access table.

I have also tried replacing the "?" with placeholders ":uname",":pass" and changed query.addBindValue(username); to query.bindValue(":uname",username);, and done same with password field.

I am running QtCreator 4.4.1 Thanks.

If you use named placeholders, your binding should use the same names. So you have to write query.bindValue(":uname", username);. Compare: ":user" vs ":uname". — vahancho
– vahancho, Commented Jan 25, 2018 at 10:24
Sorry, that was supposed to be query.bindValue(":uname",username); — Rhnbmpl
– Rhnbmpl, Commented Jan 25, 2018 at 10:26
You fill username and password from the same source, ui->textEdit_password. Could that be the issue? However, please make sure to provide a tested minimal reproducible example. — Murphy
– Murphy, Commented Jan 25, 2018 at 11:52
Additionally, storing cleartext passwords in databases is professional blunder. Get rid of that, use a cryptographic hash now! — Murphy
– Murphy, Commented Jan 25, 2018 at 11:55

AndyJ · Accepted Answer · 2018-01-25 11:53:57Z

1

Use query.bindValue( ...) because this sets the placeholder value.

I tested executedQuery() on one of my SQL statements with placeholders and it returned a string with just the placeholders, not the values. The documentation does say that in most cases it the same string as lastQuery(). http://doc.qt.io/qt-5/qsqlquery.html#executedQuery

You have confirmed that your SQL statement without the where clause works so the next stage is to check you are binding what you think you are binding. To do this use boundValue(const QString placeholder) to find out if the placehold value is being bound.

It might also be useful to check the query has run OK.

So, after your query.exec you should put the following (assuming these are your placeholders) just to check these things:

qDebug() << query.lasterError();
qDebug() << query.boundValue(":uname");
qDebug() << query.boundValue(":pass");

answered Jan 25, 2018 at 11:53

AndyJ

362 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rhnbmpl Over a year ago

Thanks, It solved my problem. After using query.boundValue() , I was finally able to see the variables I was entering in the prepared statement, instead of seeing "?". I realised I was taking both the username and password variable from the password field.And all my queries were returning nothing because both fields were password variables.

ymoreau Over a year ago

According to QSqlQuery doc Binding values using positional placeholders (version 2) - OP code should work too though.

AndyJ Over a year ago

Yes, the OP code should have worked but he confirmed he was passing incorrect data into the SQL statement. For debugging, the documentation states that in most cases executedQuery() returns the same string as lastQuery() so in some cases it will return the placeholders not the placeholder values. I think it may be database dependent. My test on a SQLITE database returned the placeholders. Using boundValue() will return the data you are binding to the value so it will at least clarify what you are binding. It's useful if executedQuery() doesn't work.

Collectives™ on Stack Overflow

Qt Sql not able to bind variables to QSqlQuery prepare Statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest