XTEA (crypt 64 bits)

Library Snippets

xtea (encrypt 64 bit values)

Works with PostgreSQL

Any version

Written in

PL/pgSQL

Depends on

Nothing

xtea encrypts or decrypts a single int8 (64 bits) value with a 16 bytes (128 bits) key of bytea type, or an equivalent array of 4 x int4 values.

It may be used to generate series of unique large values that look random, or to obfuscate a BIGSERIAL primary key without loosing its unicity property.

The XTEA algorithm is a block cipher used in cryptography, see https://en.wikipedia.org/wiki/XTEA

A C implementation is also available through the cryptint extension on PGXN. It runs much faster than the plpgsql version proposed here, but needs compilation and installation by a superuser.

/*
   Encrypts a bigint/int8 (8 bytes) with the XTEA block cipher.
   Arguments:  
      - int8 (bigint) value to encrypt/decrypt
      - bytea encryption key, 16 bytes long
        OR array of four integers: int4[4]
      - direction: true to encrypt, false to decrypt
   
   - Encrypt usage first option (with encryption key as bytea):
     select xtea(1234, bytea '\x1234567890ABC0ffeeFaceC0ffeeFeed', true);
   - Corresponding decrypt usage:
     select xtea(-7937660076067879872, bytea '\x1234567890ABC0ffeeFaceC0ffeeFeed', false);

   - Encrypt usage second option (with encryption key as int4[4]):
     select xtea(1234,
                 array[305419896,-1867792129,-285552960,-1114387]::int[],
                 true);
   - Corresponding decrypt usage:
     select xtea(-7937660076067879872, 
                 array[305419896,-1867792129,-285552960,-1114387]::int[],
		 false);
   
   As each value encrypts into another unique value (given an encryption
   key), this may be used to obfuscate an int8 primary key without loosing
   the unicity property.

   The binary encryption key is equivalent to the big-endian representation
   of 4 consecutive signed integers in the int4[] array.

   plpgsql implementation by Daniel Vérité.
   Based on C code from David Wheeler and Roger Needham.
   source:  https://en.wikipedia.org/wiki/XTEA

   The plpgsql code is more complex than its C counterpart because it emulates
   unsigned 32 bits integers and modulo 32-bit arithmetic with the bigint type.
*/
create or replace function xtea(val bigint, cr_key bytea, encrypt boolean)
returns bigint as $$
declare
  bk int[4];
  b bigint; -- unsigned 32 bits
begin
  if octet_length(cr_key)<>16 then
     raise exception 'XTEA crypt key must be 16 bytes long.';
  end if;
  for i in 1..4 loop
    b:=0;
    for j in 0..3 loop
      -- interpret cr_key as 4 big-endian signed 32 bits numbers
      b:= (b<<8) | get_byte(cr_key, (i-1)*4+j);
    end loop;
    bk[i] := case when b>2147483647 then b-4294967296 else b end;
  end loop;
  return xtea(val, bk, encrypt);
end
$$ immutable language plpgsql;

create or replace function xtea(val bigint, key128 int4[4], encrypt boolean)
returns bigint as $$
declare
  -- we use bigint (int8) to implement unsigned 32 bits with modulo 32 arithmetic
  -- (in C, uint32_t is used but pg's int4 is signed and would overflow).
  -- the most significant halves of v0,v1,_sum must always be zero
  -- they're AND'ed with 0xffffffff after every operation
  v0 bigint;
  v1 bigint;
  _sum bigint:=0;
  cr_key bigint[4]:=array[
     case when key128[1]<0 then key128[1]+4294967296 else key128[1] end,
     case when key128[2]<0 then key128[2]+4294967296 else key128[2] end,
     case when key128[3]<0 then key128[3]+4294967296 else key128[3] end,
     case when key128[4]<0 then key128[4]+4294967296 else key128[4] end
   ];
begin
  v0 := (val>>32)&4294967295;
  v1 := val&4294967295;
  IF encrypt THEN
    FOR i in 0..63 LOOP
      v0 := (v0 + ((
	     ((v1<<4)&4294967295 # (v1>>5))
	       + v1)&4294967295
		   #
		   (_sum + cr_key[1+(_sum&3)::int])&4294967295
		   ))&4294967295;
      _sum := (_sum + 2654435769) & 4294967295;
      v1 := (v1 + ((
             ((v0<<4)&4294967295 # (v0>>5))
	       + v0)&4294967295
		  # 
		  (_sum + cr_key[1+((_sum>>11)&3)::int])&4294967295
		  ))&4294967295;
    END LOOP;
  ELSE
    _sum := (2654435769 * 64)&4294967295;
    FOR i in 0..63 LOOP
      v1 := (v1 - ((
	      ((v0<<4)&4294967295 # (v0>>5))
		  + v0)&4294967295
		  # 
		  (_sum + cr_key[1+((_sum>>11)&3)::int])&4294967295
		  ))&4294967295;

      _sum := (_sum - 2654435769)& 4294967295;

      v0 := (v0 - ((
	     ((v1<<4)&4294967295 # (v1>>5))
	       + v1)&4294967295
		   #
		   (_sum + cr_key[1+(_sum&3)::int])&4294967295
		   ))&4294967295;

    END LOOP;
  END IF;
  return (v0<<32)|v1;
end
$$ immutable strict language plpgsql;

Sample output:

SELECT
  x,
  encx AS encrypted,
  xtea(encx, 'nooneknowsthekey'::bytea,false) AS decrypted
FROM (SELECT x, xtea(x, 'nooneknowsthekey'::bytea, true) AS encx
      FROM generate_series(-10,10) AS x
   ) AS s;

  x  |      encrypted       | decrypted 
-----+----------------------+-----------
 -10 |  4385243210905785209 |       -10
  -9 |  8069258762620289669 |        -9
  -8 |  3926559087555398168 |        -8
  -7 | -8988258197004549588 |        -7
  -6 |  3551076798823338680 |        -6
  -5 |  7365416518795732112 |        -5
  -4 |   136212175735208317 |        -4
  -3 |  3098188211073624918 |        -3
  -2 |  5824967969120338177 |        -2
  -1 |  -463468193554373329 |        -1
   0 | -7485772404085155809 |         0
   1 | -1311071933951566764 |         1
   2 | -4708675461424073238 |         2
   3 | -6865005668390999818 |         3
   4 |  5578000650960353108 |         4
   5 | -3219674686933841021 |         5
   6 | -6469229889308771589 |         6
   7 |  -606871692563545028 |         7
   8 | -8199987422425699249 |         8
   9 |  -463287495999648233 |         9
  10 |  7675955260644241951 |        10
(21 rows)