如何XOR md5哈希值,并将其转换为十六进制在postgresql [英] How to XOR md5 hash values and cast them to HEX in postgresql
问题描述
到目前为止,我尝试过的是
SELECT md5(text)会返回文本(十六进制字符串)。
之后我们需要他们
SELECT x'hex_string'#x'hex_string';
但上面的结果是二进制值。
如何再次将它们转换为十六进制字符串?
在postgresql中是否还有xor md5值,并将其转换为十六进制值?
这些二进制值实际上是类型位变化
,与 bytea
。
位变化
自带对XOR等内置支持,但PostgreSQL不提供从位变化
到 bytea
的转换。
你可以写一个函数来执行转换,但它不是微不足道,可能不是更有效的方式在你的情况。
这将更有意义的XOR md5直接解析。 PostgreSQL不为 bytea
提供XOR运算符,但它可以很容易地写在函数中,特别是假设操作数具有相等的长度(16字节在md5摘要的情况):
CREATE FUNCTION xor_digests(_in1 bytea,_in2 bytea)RETURNS bytea
AS $$
DECLARE
o int; - offset
BEGIN
FOR o IN 0..octet_length(_in1)-1 LOOP
_in1:= set_byte(_in1,o,get_byte(_in1,o)#get_byte(_in2,o ));
END LOOP;
RETURN _in1;
END;
$$ language plpgsql;
现在内置的postgresql md5
函数生成十六进制字符串不是最适合后处理,。 pgcrypto
模块提供此功能:
digest(数据文本,类型文本)返回bytea
使用此函数并获取最终结果为十六进制字符串:
select encode(
xor_digest(digest('first string','md5'),
digest('second string','md5')),
'hex ');
产生结果:
c1bd61a3c411bc0127c6d7ab1238c4bd
code> text 。
如果无法安装
pgcrypto
只有内置的md5
函数可用,您仍然可以组合encode
和decode
来实现这样的结果:select
encode(
xor_digest
decode(md5('first string'),'hex'),
decode(md5('second string'),'hex')
),
'
);
结果:
c1bd61a3c411bc0127c6d7ab1238c4bd
What I have tried so far
SELECT md5(text) will return text (hex strings) .
After that We need to xor them
SELECT x'hex_string' # x'hex_string';
But the above results in binary values. How do I again convert them into hex string?
Is there anyway to xor md5 values in postgresql and convert this into hexadecimal values again ?
解决方案Those binary values are in fact of type
bit varying
, which differs significantly frombytea
.
bit varying
comes with built-in support for XOR and such, but PostgreSQL doesn't provide a cast frombit varying
tobytea
.You could write a function that does the cast, but it's not trivial and probably not the more efficient way in your case.
It would make more sense to XOR the md5 digests directly. PostgreSQL doesn't provide the XOR operator for
bytea
either, but it can be easily written in a function, especially when assumed that the operands have an equal length (16 bytes in the case of md5 digests):CREATE FUNCTION xor_digests(_in1 bytea, _in2 bytea) RETURNS bytea AS $$ DECLARE o int; -- offset BEGIN FOR o IN 0..octet_length(_in1)-1 LOOP _in1 := set_byte(_in1, o, get_byte(_in1, o) # get_byte(_in2, o)); END LOOP; RETURN _in1; END; $$ language plpgsql;
Now the built-in postgresql
md5
function that produces an hex string is not the best fit for post-processing, either. Thepgcrypto
module provides this function instead:digest(data text, type text) returns bytea
Using this function and getting the final result as an hex string:
select encode( xor_digest ( digest('first string', 'md5') , digest('second string', 'md5')), 'hex');
produces the result:
c1bd61a3c411bc0127c6d7ab1238c4bd
with typetext
.If
pgcrypto
can't be installed and only the built-inmd5
function is available, you could still combineencode
anddecode
to achieve the result like this:select encode( xor_digest( decode(md5('first string'), 'hex'), decode(md5('second string'), 'hex') ), 'hex' );
Result:
c1bd61a3c411bc0127c6d7ab1238c4bd
这篇关于如何XOR md5哈希值,并将其转换为十六进制在postgresql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!