3

I am working on an web application that will allow users to write messages and store them on a database to be accessed by some other users who are meant to. I have a user authentication system ready and also the encryption system works fine but I am confused about the datatype to use for storing the messages.

I am using a MySQL database with PHP to store the values encrypted using openssl AES-256. The cipher texts produced are binary, and I need to store them on the database.

Now I can try using base64to encode the binary strings a, but it increases the size of the data from 33 to 100%.

The messages could be long and I want my database no get screwed up with performance or storage capabilities. I also have to stick with MySQL for some reasons.

So should I use BLOB or TEXT? Which one will be more efficient in terms of performance? And also, what should be the type-like TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT or the the corresponding types for BLOBs?

I want to make sure both faster performance and storage capabilities are fulfilled.

Thanks for the help!

  • This maybe useful. http://stackoverflow.com/questions/11624807/what-is-the-differences-between-blob-and-text-in-mysql-data-types – Hasan Peeal Mar 16 '16 at 08:55
  • If you have binary data you **must** store it as a `BLOB`!! If you want to store it as `TEXT` you will need to Base64 encode it, otherwise you **will** get data corruption - Base64 encoding will increase the file size by [1/3](http://security.stackexchange.com/a/117075/59196), so this is probably a bad choice. – Boris the Spider Mar 16 '16 at 08:57
  • Yes, I did mention Base64 encoding and its issues. @BoristheSpider –  Mar 16 '16 at 09:00
  • Dissenting opinion: **DON'T** store it in the database. Instead, store it on the file system and record the filename in the database. – Scott Arciszewski Mar 16 '16 at 16:34
  • So I will have about a 1000 new files on my filesystem everyday? @ScottArciszewski –  Mar 16 '16 at 20:14
  • Yeah, that's about 1 new file every 86.4 seconds. As long as your files aren't so enormous that they require 86 seconds to transcribe at full CPU/RAM/disk utilization, you should be fine. – Scott Arciszewski Mar 16 '16 at 20:33
  • So that would mean, if I have an average of 1000 new files everyday, by the end of the year, I will find 365,000 files? That kinda looks like a lot, but it could help if you would tell me what format to store it as? –  Mar 16 '16 at 20:36
  • I would use `file_storage_dir/HH/HH/HHHHHHHHHHHHHHHHHHH.dat` where H is a placeholder for a random hex character (determined via CSPRNG). You can safely store binary in a file without worrying about the data getting chewed. – Scott Arciszewski Mar 16 '16 at 23:32
  • Lazy way: Use 24 bytes from `random_bytes()`, convert them to hex, use the first two for the directory, and the last 22 for the file name. Collision chance: Negligible. – Scott Arciszewski Mar 17 '16 at 17:22

1 Answers1

0

Yes, it will be better if you can store the values in the "TEXT" data type. For more details, please read this http://dev.mysql.com/doc/refman/5.5/en/blob.html.

Regarding knowledge of storage requirements, you can read thiss one http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html.

Hope it helps.

HilAy Patel
  • 1
  • 1
  • 3