Format-preserving encryption (FPE) is a type of encryption that ensures the ciphertext retains the same format as the plaintext.

Unlike traditional encryption methods that often produce binary or alphanumeric outputs, FPE maintains the original data’s structure, length, and character set. This makes FPE particularly useful in database applications where data type consistency is crucial.

Consider a scenario where a database table must temporarily be copied to a different schema, with columns containing sensitive data encrypted. Let us assume that one of those columns contains credit card numbers and its data type has been defined as CHAR(16).

Using traditional encryption, a row with the following credit card number: 1234 5678 9012 3456 may be transformed into a string of characters such as 7f8d9e2a1b3c4d5e6f7a8b9c0d1e2f3g. The resulting string is unfortunately too long to be stored in the column.

With format-preserving encryption (FPE) though, the encrypted credit card number retains the original format, resulting in something like: 4576 8392 1234 6789.

Implementing FPE in PL/SQL

Oracle Enterprise Edition provides the Oracle Data Masking and Subsetting pack , which provides a format preserving encryption option: “Encryption encrypts the sensitive data using a cryptographic key while preserving the format of the data. It’s a reversible masking option as you can decrypt your data using the same key. It’s useful when masked data sent to a third party has to be merged back along with further updates.”

In this blogpost we describe a native PL/SQL implementation providing the same functionality.

It is an implementation of the FF1 method described in United States of America National Institude of Standards and Technology (NIST) Special Publication 800-38G Revision 1" - “Recommendation for Block Cipher Modes of Operation - Methods for Format-Preserving Encryption” .

In the following we highlight the core ideas and describe the corresponding PL/SQL implementation.

The FF1 algorithm

An initialization step defines a number of parameters like

• the radix, i.e. base of the numeral system used during the process.
• the maximum message length.
• the secret key.
• a ’tweak’, i.e. an optional parameter to provide additional variability.

Once initialization completes the algorithm employs a Feistel structure. It is a symmetric structure that transforms plaintext into ciphertext through a series of iterations, called ‘rounds’. The input plaintext block is split into two halves and a number of ‘rounds’ is executed.

Each iteration (round) applies a round function FF to one half of the data and then combines the result with the other half using a bitwise operation (typically XOR). In each round, the left half becomes the right half for the next round. After the last round, the halves are concatenated to produce the final ciphertext.

The decryption process is the reverse of encryption. The final ciphertext is split in two halves, then the same steps are applied in reverse order using the same round functions.

A key advantage of the Feistel structure is that it allows the same algorithm to be used for both encryption and decryption.

PL/SQL implementation

Our implementation is based on two Oracle packages: DBMS_CRYPTO for encryption and decryption, and UTL_RAW for raw data manipulation.

The Oracle PL/SQL UTL_RAW package is a utility package that provides functions and procedures for manipulating raw data.

While UTL_RAW itself doesn’t provide cryptographic functions directly, it works well in conjunction with Oracle’s DBMS_CRYPTO package.

It is designed specifically for operations on raw data types, which are essential when dealing with encrypted binary data.

It includes functions for converting between different data types (e.g., raw to hex, hex to raw, raw to varchar2, etc.).

The ability to concatenate raw data and extract substrings simplifies the splitting and merging of data blocks, essential steps in the Feistel structure of the FF1 algorithm.

It also provides bitwise logical operations (AND, OR, XOR), which are fundamental in the implementation of Feistel networks and the FF1 algorithm.

These operations allow manipulation of individual bits in raw data, a common requirement in encryption algorithms.

A limitation of our implementation is that we limit the precision of some arithmetic due to insufficient built-in support for large integer operations in Oracle.

The limitation is not a blocker for our use-case, it can however be lifted by implementing the corresponding operations in PL/SQL as well.

The corresponding package specification follows:

CREATE OR REPLACE PACKAGE ff1 AS
/*
FF1 implementation as described in NIST Special Publication 800-38G Revision 1.
*/

-- 2 <= minlen <= maxlen < 2^32
TYPE NUM_STR IS VARRAY(1000) OF PLS_INTEGER;

--  Returns the remainder of m divided by n
FUNCTION modulo(m PLS_INTEGER, n PLS_INTEGER)
RETURN PLS_INTEGER;

-- Returns the number that the numeral string str represents
-- in base radix when the numerals are valued in decreasing
-- order of significance.
FUNCTION to_numberr(radix in INTEGER, str in NUM_STR)
RETURN NUMBER;

-- NUM_STR to varchar2 conversion
FUNCTION to_vchar2(str in NUM_STR)
RETURN VARCHAR2;

-- Given a nonnegative integer x less than radix^m ,
-- the representation of x as a string of m numerals
-- in base radix, in decreasing order of significance.
FUNCTION to_num_str(radix in INTEGER, m in INTEGER, val in INTEGER)
RETURN NUM_STR;

-- Given a numeral string, X, the numeral string that consists
-- of the numerals of X in reverse order.
FUNCTION reverse(str in NUM_STR)
RETURN NUM_STR;

-- Given a byte string, X, the byte string that consists of the
-- bytes of X in reverse order.
FUNCTION reverseb(bytestr RAW)
RETURN RAW;

-- Slice a num_str and return the slice
FUNCTION slice(str in NUM_STR,
start_idx PLS_INTEGER,
end_idx PLS_INTEGER)
RETURN NUM_STR;

-- Concatenate two num_str varrays
FUNCTION concat(a in NUM_STR, b in NUM_STR)
RETURN NUM_STR;

-- Pseudorandom function.
FUNCTION prf(bytestr in RAW, key IN RAW) RETURN RAW;

-- encrypt
FUNCTION encrypt(
xstr IN NUM_STR,
key IN RAW,
radix IN PLS_INTEGER,    
rounds IN PLS_INTEGER
) RETURN NUM_STR;

-- decrypt
FUNCTION decrypt(
xstr IN NUM_STR,
key IN RAW,
radix IN PLS_INTEGER,    
rounds IN PLS_INTEGER
) RETURN NUM_STR;
END;

If you interested in learning more about this implementation, or how Neurocom can help you address software development challenges in a cost-effective way, Contact us today to schedule a meeting.