Android应用SQLite查询哈希邮箱BLOB无结果，求排查解决

核心原因 #

• 二进制转字符串的编码损坏：哈希生成的byte[]是纯二进制数据，无法保证所有字节都符合UTF-8编码规范。你用new String(emailHash, StandardCharsets.UTF_8)转换时，非法UTF-8字节会被替换为默认占位符（如�），导致传递给SQLite的字符串和存储的BLOB二进制数据完全不匹配。
• SQLite类型不匹配对比：当用字符串去匹配BLOB字段时，SQLite会自动将BLOB转换为字符串再比较，但这个转换的编码规则和你手动转换的不一致，进一步导致对比失败。

解决方案 #

方案1：直接绑定BLOB参数（推荐） #

使用SQLiteStatement手动绑定二进制数组，避免任何编码转换。修改后的查询代码如下：

@SuppressLint("Range")
public UserCredentials findUserCredentials(String email) throws DBFindException {
    SQLiteDatabase db = null;
    Cursor cursor = null;
    UserCredentials userCredentials = null;

    try {
        db = open();
        byte[] emailHash = SecurityService.hash(SerializationUtils.serialize(email));

        // 用rawQuery配合SQLiteStatement绑定BLOB参数
        String querySql = "SELECT * FROM " + TABLE_NAME + " WHERE " + EMAIL_HASH + " = ?";
        SQLiteStatement statement = db.compileStatement(querySql);
        statement.bindBlob(1, emailHash); // 直接传入二进制哈希值
        cursor = statement.executeQuery();

        if (cursor != null && cursor.moveToFirst()) {
            byte[] passwordBytes = cursor.getBlob(cursor.getColumnIndex(PASSWORD));
            byte[] salt = cursor.getBlob(cursor.getColumnIndex(PASSWORD_SALT));
            if (passwordBytes != null && salt != null) {
                HashData hashData = new HashData(passwordBytes, salt);
                userCredentials = new UserCredentials(cursor.getLong(cursor.getColumnIndex(ID)), hashData);
            }
        }
    } catch (SQLiteException | SerializationException | HashException exception) {
        throw new DBFindException("Failed to findUserCredentials from user with email (" + email + ")", exception);
    } finally {
        if (cursor != null)
            cursor.close();
        close(db);
    }

    return userCredentials;
}

方案2：转十六进制字符串存储查询 #

如果不想调整查询逻辑，可以将哈希值转为十六进制字符串，以TEXT类型存储和查询：

插入时修改：

byte[] emailHash = SecurityService.hash(SerializationUtils.serialize(user.getEmail()));
String hexHash = bytesToHex(emailHash);
contentValues.put(EMAIL_HASH, hexHash); // 存储为十六进制字符串

查询时修改：

byte[] emailHash = SecurityService.hash(SerializationUtils.serialize(email));
String hexHash = bytesToHex(emailHash);
String selection = EMAIL_HASH + "=?";
String[] selectionArgs = {hexHash};
cursor = db.query(TABLE_NAME, null, selection, selectionArgs, null, null, null);

补充字节转十六进制工具方法：

private static String bytesToHex(byte[] bytes) {
    StringBuilder hexBuilder = new StringBuilder();
    for (byte b : bytes) {
        hexBuilder.append(String.format("%02x", b));
    }
    return hexBuilder.toString();
}

内容的提问来源于stack exchange，提问作者Javier Jordán Luque