XTEA（64位加密）
来自 PostgreSQL 维基
跳转到导航跳转到搜索
xtea 使用 16 字节（128 位）bytea 类型的密钥或等效的 4 x int4 数组，对单个 int8（64 位）值进行加密或解密。
它可用于生成看起来随机的一系列独特的较大的值，或在不丢失其唯一性属性的情况下对 BIGSERIAL 主键进行模糊处理。
XTEA 算法是密码学中使用的分组密码，请参阅 https://en.wikipedia.org/wiki/XTEA
也可以通过 PGXN 上的 cryptint 扩展 使用 C 实现。它比这里提出的 plpgsql 版本运行得快得多，但需要由超级用户进行编译和安装。
/*
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;
示例输出
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)
