使用Qt向PostgreSQL插入/查询加密字符串异常求助

问题概述 #

需实现明文加密后存入PostgreSQL的bytea列，查询后解密还原原字符串（要求使用PostgreSQL推荐的bytea Hex格式存储）。当前出现以下问题：

• 调试日志无encrypted string:输出
• 解密后的studentName为空
• 单独测试BlowfishEncrypter的加密解密功能正常，怀疑字符串→ByteArray→hex转换环节出错

相关代码 #

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
  );
  bindValuesToQuery(query, dataMap);
  query.exec();
  
  // 调试代码
  QString sql = "SELECT name FROM student WHERE idx = ?";
  QSqlQuery q = DatabaseDriver::getInstance().prepareQuery(sql);
  q.addBindValue(m_idx.getValue());
  q.exec();
  QSqlRecord rec = q.record();
  QVariant cipher = rec.value("name");

  LOGS_INFO_PUB("encrypted string: " << StringUtil::fromQString(cipher.toString()));

  QByteArray byteArray = QByteArray::fromHex(cipher.toByteArray());
  std::string studentName = std::string(byteArray.constData(), byteArray.length());
  
  LOGS_INFO_PUB("student name is: " << BlowfishEncrypter.decrypt(studentName));
  ...
}


void BaseDAO::bindValuesToQuery(QSqlQuery& query, const QVariantMap& data) const
{
  const QVariantList valueList = data.values();
  QList<QString> keys = data.keys();
  int valueCount = valueList.count();
  
  for(int i = 0; i < valueCount; i++)
  {
    const QVariant &val = valueList[i];
    
    switch(val.type()) {
    ...
    ...
    default:
      if(getTableName() == "student" && keys[i] == "name") 
      {
        std::string cipher = 
              BlowfishEncrypter.encrypt(StringUtil::fromQString(val.toString()));
        QByteArray byteArray(cipher.c_str(), cipher.length());
        query.addBindValue(byteArray.toHex());
      } else {
        query.addBindValue(val);
      }
      break;
     }
  }
}

排查与修复方案 #

1. 查询结果未正确获取 #

执行q.exec()后，必须调用q.next()才能定位到查询结果行，否则QSqlRecord会是空记录，导致cipher无值，日志无输出。修改查询逻辑：

QString sql = "SELECT name FROM student WHERE idx = ?";
QSqlQuery q = DatabaseDriver::getInstance().prepareQuery(sql);
q.addBindValue(m_idx.getValue());
if(q.exec() && q.next()) { // 调用next()获取结果行
    QSqlRecord rec = q.record();
    QVariant cipher = rec.value("name");
    LOGS_INFO_PUB("encrypted string: " << StringUtil::fromQString(cipher.toString()));
    // 后续解密逻辑
} else {
    LOGS_INFO_PUB("查询无结果或执行失败");
}

2. bytea数据绑定方式错误 #

手动将加密后的字节转hex字符串绑定，会被驱动当作普通文本存储，而非bytea的二进制数据。直接绑定原始字节数组，让Qt驱动自动处理bytea格式：

std::string cipher = BlowfishEncrypter.encrypt(StringUtil::fromQString(val.toString()));
QByteArray byteArray(cipher.data(), cipher.size()); // 用data()避免空字符截断
query.addBindValue(byteArray, QSql::Binary); // 标记为二进制数据

3. 加密结果的空字符处理 #

Blowfish加密结果可能包含空字符\0，用c_str()转换会截断数据，必须用data()和实际长度构造QByteArray，避免丢失部分加密内容。

4. 解密时的类型转换修正 #

绑定原始字节数组后，读取时直接获取二进制数据，无需调用fromHex()：

QByteArray byteArray = cipher.toByteArray();
std::string studentName(byteArray.data(), byteArray.size());
std::string decryptedName = BlowfishEncrypter.decrypt(studentName);
LOGS_INFO_PUB("student name is: " << decryptedName);

内容的提问来源于stack exchange，提问作者testadizzy