I have old system with database that has table with column of type char(20) and the customer decided to encrypt that column for some business needs however for some system integration needs the length of the output column to be similar to input which is 20 and I have read about Format-Preserving encryption

My question is, Is format-preserving encryption is strong like AES methods and Are there any SQL Server implementations yet that can be used with large number of records millions?

2 Answers 2


I think your main problem would be to implement the Format-Preserving encryption in the database.

You could simply use AES on the field. See for example the encryption functions supported by MySQL. For a 20-byte input, aes_encrypt() output would be 32 binary characters (AES works on 16-byte blocks). You can get closer (25) with the less-secure (and deprecated) des_encrypt(). If you don't need strong encryption, MariaDB features an encode() function that may do the work, that would keep it the same length.

The main problem is that these encryption method would output binary bytes (the server would have no problem with it) but that may be undesirable for the clients (or simply for providing an output). Encoding it in base64 (33% overhead) is probably acceptable in such case.


Maybe you could do something like this, assuming you don't want to or cannot change your application:

  • rename table to something else, e.g., Customers becomes Customers_0
  • increase the column sizes to accommodate cipher text and encryption flag
  • create a view Customers based on Customers_0 that decrypts
  • create instead of triggers on view to encrypt column data when updated or inserted.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .