1

My development environment: C/C++,CentsOS,Openssl,sqlite3.

I am encrypting data using openssl EVP interface(AES 128 bit). The encrypted data may contain null. How can i properly insert it into sqlite3 DB and retrieve back from DB to decrypt?

Thanks.

abhay kanade
  • 33
  • 5

1 Answers1

1

How can i properly insert it into sqlite3 DB and retrieve back from DB to decrypt?

I think you have at least two options. First, you can encode your cipher text. This can be done in OpenSSL or with the database. If you use OpenSSL, then check out BIO_f_base64 in the man pages.

I'm not sure what you would use to encode binary data using database functions. For that, see SQL As Understood By SQLite | Core Functions.

Second, you can forgo the encoding and simply store the binary blob. The blob is a data type in SQLite, see Datatypes In SQLite Version 3. Also see questions like SQLite Blob insertion c++ and SQLite not storing blobs containing \0 bytes.

Avoiding the encoding and using binary data directly is probably the most efficient method since it avoids the extra work. But it will make searching the data more difficult in some instances.

There could be a third option, but it depends on your threat model. You can encrypt the entire database, and then insert plain text into it. For this option, see SQLite with encryption/password protection.

This option could be less secure than encrypting the data before inserting due to key management. If the database needs to be online, then the key which encrypts the entire database has to be stored somewhere, and that means the attacker can probably recover it from the filesystem.

Community
  • 1
  • 1
jww
  • 97,681
  • 90
  • 411
  • 885
  • Thanks for your help. As of now there is no requirement to encrypt whole DB or password protect feature. I will go with Base64 encoding approach. I tried with blob, but how can i retrieve it from DB? I inserted a string containing null using blob, but when i retrieve it using "sqlite3_column_blob", the resulted string would not contain full data. I meant to say that, if insert a string "text\0abc", i will get back only "text" Am i doing something wrong? – abhay kanade Feb 13 '16 at 14:53
  • I know C strings are '\0' terminated. What am i supposed to do? – abhay kanade Feb 13 '16 at 14:55
  • @abhaykanade - it sounds like you are hitting the issue discussed in [SQLite not storing blobs containing \0 bytes](http://stackoverflow.com/q/13825164). Have you reviewed the question and its answers? – jww Feb 13 '16 at 14:58
  • Yep! I will go with base64 encoding:) – abhay kanade Feb 13 '16 at 16:38