QSqlQuery, wrong number of parameters for prepared statement error

Question

I am relative new to Qt/QSqlQuery, and in my current project I need to incorporate pgcrypto module, or pgp_sym_encrypt/decrypt function to be exact, into the regular SELECT/INSERT queries.

Here's the code that has been giving me problems:

bool BaseDAO::insertIntoDb(const DataObject &data) 
{
  QVariantMap dataMap = data.toQVariantMap();
  QString rows = getFieldsStringForSql(dataMap);
  QString placeholders = getPlaceholderStringForSql(dataMap);

  QSqlQuery query = DatabaseDriver::getInstance().prepareQuery(
      "INSERT INTO " + getTableName() + " " + rows + " VALUES " + placeholders + 
          R"( RETURNING ")" + getPKString() + R"(")" );
  
  bindValuesToQuery(query, dataMap);
  query.setForwardOnly(true);
  query.exec();   // <-- where error occurs
  ...
  ... 
}


QString BaseDAO::getPlaceholderStringForSql(const QVariantMap& data) const 
{

  QString fields = "(";
  
  int valueCount = data.count();
  const QList<QString> keys = data.keys();
  
  if(valueCount > 0)
    fields += ":" + keys[0];

  for(int i = 1; i < valueCount; ++i)
  {
    if(!QString::compare(getTableName(), "patient") && !QString::compare(keys[i], "name"))
      fields += ", pgp_sym_encrypt(:name, '" + m_pw + "')";   // need to encrypt name
    else
      fields += ", :" + keys[i];
  }
  fields += ")";

  return fields;
}


void BaseDAO::bindValuesToQuery(QSqlQuery& query, const QVariantMap& data) const
{
  const QVariantList valueList = data.values();
  const QList<QString> keys = data.keys();
  int valueCount = valueList.count();
  for (int i = 0; i < valueCount; i++)
  {
    const QVariant &val = valueList[i];
    
    switch(val.type()) {
    case QVariant::Date:
      query.bindValue(":" + keys[i], val.toDate().toString("yyyy-MM-dd"));
      break;
    case QVariant::Time:
      query.bindValue(":" + keys[i], val.toTime().toString("hh:mm:ss.zzz"));
      break;
    case QVariant::DateTime:
      query.bindValue(":" + keys[i], val.toDateTime().toString("yyyy-MM-ddThh:mm:ss.zzz"));
      break;
    default:
      query.bindValue(":" + keys[i], val);
      break;
    }
  }
}

I printed out the INSERT query before it got executed with query.lastQuery() and it looks like this:

INSERT INTO patient ("birthDate", "isMarked", "isProtected", "isTemporary", "modificationDate", "modificationTime", "name", "patientID", "permissionGroup", "sex")
VALUES 
 (:birthDate, :isMarked, :isProtected, :isTemporary, :modificationDate, :modificationTime, pgp_sym_encrypt(:name, 'password'), :patientID, :permissionGroup, :sex) 
RETURNING "idx"

and this is the error message I got:

Fatal Error: wrong number of parameters for prepared statement "qpsqlpstmt_4"
DETAIL: Expected 11 parameters but got 1.
QPSQL: Unable to create query, Query: <<***>>.

The error totally got me confused. There are 10 parameters, but it somehow expects 11 and only gets 1? Any help is greatly appreciated!

Answer 1

1. I would recommend to rewrite getPlaceholderStringForSql

void BaseDAO::bindValuesToQuery(QSqlQuery& query, const QVariantMap& data) const
{
  for(const QString & key : data.keys())
  {
    const QVariant &val = data.value(key);

    // According to Qt doc: Note that the placeholder mark (e.g :) must be included 
    // when specifying the placeholder name.
    const QString placeholder = QString(":%1").arg(key);

    switch (val.type()) {
    case QVariant::Date:
      query.bindValue(placeholder,val.toDate().toString( "yyyy-MM-dd"));
      break;
    case QVariant::Time:
      query.bindValue(placeholder,val.toTime().toString("hh:mm:ss.zzz"));
      break;
    case QVariant::DateTime:
      query.bindValue(placeholder,val.toDateTime().toString("yyyy-MM-ddThh:mm:ss.zzz"));
      break;
    default:
      query.bindValue(placeholder,val);
      break;
  }
}

2. Use raw litteral string for pgp_sym_encrypt(:name, '" + m_pw + "')"
   R"(pgp_sym_encrypt(:name, ')" + m_pw + R("'))"

3. The main problem of this code is that you suddenly add a database dependency in an API (QSqlDatabase and your base code using QSqlDatabase) which shall remain database independant. A much better solution would be to use c++ directly to encrypt ans decrypt the name. Or if you absolutely want to use SQL, you should probably create two stored procedure (encrypt_field and decrypt_field) encapsulating pgp_sym_encrypt and pgp_sym_decrypt. Then you should be able to make a query via a QSqlQuery calling this stored procedures. The result of encrypt_field can be add in the insert/update SQL query while the result of decrypt_field wil be used after a SELECT query.