加密 SQLite [英] Encrypting SQLite

问题描述

我将编写自己的加密，但想讨论一些内部原理.应在多个移动平台上使用 - iOS、Android、WP7，桌面或多或少用作测试平台.

I am going to write my own encryption, but would like to discuss some internals. Should be employed on several mobile platforms - iOS, Android, WP7 with desktop serving more or less as a test platform.

让我们先从现有解决方案的简要特征开始:

Let's start first with brief characteristics of existing solutions:

• SQLite 标准(商业)SEE 扩展 - 我不知道它在内部如何工作以及它如何与提到的移动平台合作.

• SQLite standard (commercial) SEE extension - I have no idea how it works internally and how it co-operates with mentioned mobile platforms.

System.data.sqlite(仅限 Windows):完整 DB、ECB 模式的 RC4 加密.他们还会加密 DB 标头，这偶尔(0.01% 的几率)会导致 DB 损坏.*) 附加优势:他们使用 SQLite 合并分发.

System.data.sqlite (Windows only): RC4 encyption of the complete DB, ECB mode. They encrypt also DB header, which occasionally (0.01% chance) leads to DB corruption.*) Additional advantage: They use SQLite amalgamation distribution.

SqlCipher(openssl，即多个平台):可选择的加密方案.他们加密整个数据库.CBC 模式(我认为)，随机 IV 向量.因此，他们必须修改页面参数(大小 + 用于存储 IV 的保留空间).他们意识到与未加密读取 DB 标头相关的问题，并尝试引入变通方法，但解决方案并不令人满意.额外的缺点:他们使用 SQLite3 源代码树.(另一方面，它支持附加功能，即使用特殊编译指示微调加密参数.)

SqlCipher (openssl, i.e. several platforms): Selectable encryption scheme. They encrypt whole DB. CBC mode (I think), random IV vector. Because of this, they must modify page parameters (size + reserved space to store IV). They realized the problems related to unencrypted reading of the DB header and tried to introduce workarounds, yet the solution is unsatisfactory. Additional disadvantage: They use SQLite3 source tree. (Which - on the other hand - enables additional features, i.e. fine tuning of the encryption parameters using special pragmas.)

根据我自己的分析，我认为以下可能是一个不错的解决方案，不会遇到上述问题:

Based on my own analysis I think the following could be a good solution that would not suffer above mentioned problems:

• 加密整个数据库，除了数据库头.
• ECB 模式:听起来有风险，但在简要查看 DB 格式后，我无法想象如何利用它进行攻击.
• AES128?
• 在 SQLite 合并之上实现(类似于 system.data.sqlite)

我想讨论这个加密方案可能存在的问题.

I'd like to discuss possible problems of this encryption scheme.

*) 由于 SQLite 在没有解密的情况下读取 DB 标头.由于 RC4(流密码)，这个问题只会在第一次使用时出现.AES 会更加危险，因为每个实时"数据库迟早都会面临这个问题.

*) Due to SQLite reading DB header without decryption. Due to RC4 (a stream cipher) this problem will manifest at the very first use only. AES would be a lot more dangerous as every "live" DB would sooner or later face this problem.

已编辑 - 基于 VFS 的加密案例

EDITED - case of VFS-based encryption

上述方法使用由 sqlite.org 认可的基于编解码器的方法.这是一组 3 个回调，最重要的是这个:

Above mentioned methods use codec-based methodology endorsed by sqlite.org. It is a set of 3 callbacks, the most important being this one:

void *(*xCodec)(void *iCtx, void *data, Pgno pgno, int mode)

此回调由 SQLite 自行决定用于加密/解密从磁盘读取/写入磁盘的数据.数据逐页交换.(页面是 512 By 的倍数.)

This callback is used at SQLite discretion for encrypting/decrypting data read from/written to the disk. The data is exchanged page by page. (Page is a multiple of 512 By.)

另一种选择是使用 VFS.VFS 是一组用于低级操作系统服务的回调.其中有几个与文件相关的服务，例如xOpen/xSeek/xRead/xWrite/xClose.特别是，这里是用于数据交换的方法

Alternative option is to use VFS. VFS is a set of callbacks used for low-level OS-services. Among them there are several file-related services, e.g. xOpen/xSeek/xRead/xWrite/xClose. In particular, here are the methods used for data exchange

int (*xRead)(sqlite3_file*, void*, int iAmt, sqlite3_int64 iOfst);
int (*xWrite)(sqlite3_file*, const void*, int iAmt, sqlite3_int64 iOfst);

这些调用中的数据大小范围从 4 By(常见情况)到 DB 页大小.如果您想使用块密码(还可以使用什么?)，那么您需要组织底层块缓存.我无法想象有一种实现会像 SQLite 内置事务一样安全高效.

Data size in these calls ranges from 4 By (frequent case) to the DB page size. If you want to use a block cipher (what else to use?), then you need to organize underlying block cache. I cannot imagine an implementation that would be as safe and as efficient as SQLite built-in transactions.

第二个问题:VFS 实现依赖于平台.Android/iOS/WP7/桌面都使用不同的来源，即必须逐个平台实施基于 VFS 的加密.

Second problem: VFS implementation is platform-dependent. Android/iOS/WP7/desktop all use different sources, i.e. VFS-based encryption would have to be implemented platform-by-platform.

下一个问题更微妙:平台可能会使用 VFS 调用来实现文件锁定.这些用途不得加密.此外，共享锁不能被缓冲.换句话说，VFS 级别的加密可能会损害锁定功能.

Next problem is a more subtle: Platform may use VFS calls to realize file locks. These uses must not be encrypted. More over, shared locks must not be buffered. In other words, encryption at the VFS level might compromise locking functionality.

已编辑 - 对基于 VFS 的加密进行明文攻击

EDITED - plaintext attack on VFS-based encryption

我后来意识到这一点:DB 标头以固定字符串SQLite 格式 3"开头，标头包含许多其他固定字节值.这为已知的明文攻击 (KPA) 敞开了大门.

I realized this later: DB header starts with fixed string "SQLite format 3" and the header contains a lot of other fixed byte values. This opens the door for known plaintext attacks (KPA).

这主要是基于 VFS 的加密的问题，因为它没有数据库头被加密的信息.

This is mainly the problem of VFS-based encryption as it does not have the info that the DB header is being encrypted.

System.data.sqlite 也有这个问题，因为它也加密 (RC4) 数据库头.

System.data.sqlite has also this problem as it encrypts (RC4) also the DB header.

SqlCipher 使用用于将密码转换为密钥的盐覆盖 hdr 字符串.此外，它默认使用AES，因此KPA攻击没有危险.

SqlCipher overwrites hdr string with salt used to convert password to the key. Moreover, it uses by default AES, hence KPA attack presents no danger.

推荐答案

您不需要破解 db 格式或 sqlite 源代码.SQLite 公开了虚拟文件系统 (vfs) API，该 API 可用于将文件系统(或其他 vfs)与加密层包装在一起，该加密层可动态加密/解密页面.当我这样做时，结果证明这是一项非常简单的任务，只有一百行左右的代码.这样整个数据库都将被加密，包括日志文件，并且对任何客户端代码都是完全透明的.典型的页面大小为 1024，几乎可以使用任何已知的分组密码.根据我从他们的文档中得出的结论，这正是 SQLCipher 所做的.

You don't need to hack db format or sqlite source code. SQLite exposes virtual file-system (vfs) API, which can be used to wrap file system (or another vfs) with encryption layer which encrypts/decrypts pages on the fly. When I did that it turned out to be very simple task, just hundred lines of code or so. This way whole DB will be encrypted, including journal file, and it is completely transparent to any client code. With typical page size of 1024, almost any known block cipher can be used. From what I can conclude from their docs, this is exactly what SQLCipher does.

关于您看到的问题":

• 您不需要重新实现文件系统支持，您可以环绕默认 VFS.所以没有锁定或平台依赖性的问题.
• SQLite 的默认操作系统后端也是 VFS，除了您添加之外，使用 VFS 没有任何开销.
• 您不需要块缓存.当然，当它只要求 4 个字节时，您将不得不读取整个块，但不要缓存它，它将永远不会被再次读取.SQLite 有自己的缓存来防止这种情况(Pager 模块).

这篇关于加密 SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！