postgresql 加密/解密
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12598382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql encrypt / decrypt
提问by KIM
I'm working on field encrypt / decrypt.
我正在研究现场加密/解密。
What I choose is
我选择的是
select encrypt('123456789012345','1234','aes');
encrypt
------------------------------------
\x34591627f9c8eae417fc7cbbf458592c
(1 row)
I got my data encrypted though, the other string is there after decrypt like below...
虽然我加密了我的数据，但解密后另一个字符串在那里，如下所示......
postgres=# select decrypt('\x34591627f9c8eae417fc7cbbf458592c','1234','aes');
decrypt
----------------------------------
\x313233343536373839303132333435
(1 row)
Have I made wrong way? (I know this kind of asking could be stupid... )
我做错了吗？（我知道这种询问可能很愚蠢......）
What I have to do is just getting a most simple way and encrypted data has small size....
我所要做的只是获得一种最简单的方法，并且加密数据的大小很小....
Thanks in advance...
提前致谢...
回答by Gary
The decrypt function is returning a byte string, not a character string, so its being shown in hex notation. The actual values are the same \x31 = 1, \x32 = 2 etc.
解密函数返回一个字节字符串，而不是字符串，因此它以十六进制表示法显示。实际值相同 \x31 = 1, \x32 = 2 等。
You need to cast the return value back to text.
您需要将返回值转换回文本。
eg:
例如：
select convert_from(decrypt('\x34591627f9c8eae417fc7cbbf458592c','1234','aes'),'SQL_ASCII');
convert_from
-----------------
123456789012345
(1 row)
回答by jkilgrow
Thank you, Gary!
谢谢你，加里！
To add on to that, if you are using decrypt in a table query, you will have to specifically cast the column to a bytea type. For example, if you have the following:
除此之外，如果您在表查询中使用解密，则必须专门将该列转换为 bytea 类型。例如，如果您有以下内容：
CREATE TABLE public.test_crypto
(
id bigint NOT NULL DEFAULT nextval('test_crypto_id_seq'::regclass),
plain_text text COLLATE pg_catalog."default",
crypted_text text COLLATE pg_catalog."default",
CONSTRAINT test_crypto_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
You can encrypt data like this:
您可以像这样加密数据：
insert into public.test_crypto (plain_text, crypted_text)
values ('plaintext', encrypt('plaintext', 'salty', 'aes'))
And decrypt it like this:
并像这样解密：
select id, plain_text,
convert_from(decrypt(crypted_text::bytea, 'salty', 'aes'), 'SQL_ASCII')
from test_crypto
If you don't use crypted_text::bytea, the SQL parser will yell at you for not being able to find the function you are talking about 'decrypt'.
如果您不使用 crypted_text::bytea，SQL 解析器会因为找不到您正在谈论的“解密”函数而对您大喊大叫。
