让 Oracle 的 MD5 与 PHP 的 MD5 匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1228685/
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
Getting Oracle's MD5 to match PHP's MD5
提问by Zenshai
I'm trying to compare an MD5 checksum generated by PHP to one generated by Oracle 10g. However it seems I'm comparing apples to oranges.
我正在尝试将 PHP 生成的 MD5 校验和与 Oracle 10g 生成的校验和进行比较。然而，我似乎在比较苹果和橙子。
Here's what I did to test the comparison:
这是我为测试比较所做的工作：
//md5 tests
//php md5
print md5('testingthemd5function');
print '<br/><br/>';
//oracle md5
$md5query = "select md5hash('testingthemd5function') from dual";
$stid = oci_parse($conn, $md5query);
if (!$stid) {
$e = oci_error($conn);
print htmlentities($e['message']);
exit;
}
$r = oci_execute($stid, OCI_DEFAULT);
if (!$r) {
$e = oci_error($stid);
echo htmlentities($e['message']);
exit;
}
$row = oci_fetch_row($stid);
print $row[0];
The md5 function (seen in the query above) in Oracle uses the 'dbms_obfuscation_toolkit.md5' package(?) and is defined like this:
Oracle 中的 md5 函数（在上面的查询中看到）使用 'dbms_obfuscation_toolkit.md5' 包（？）并定义如下：
CREATE OR REPLACE FUNCTION PORTAL.md5hash (v_input_string in varchar2) return varchar2
is
v_checksum varchar2(20);
begin
v_checksum := dbms_obfuscation_toolkit.md5 (input_string => v_input_string);
return v_checksum;
end;
What comes out on my PHP page is:
我的 PHP 页面上显示的是：
29dbb90ea99a397b946518c84f45e016
)?1??9{”eèOEà
Can anyone help me in getting the two to match?
谁能帮我让这两个匹配？
回答by OneOfOne
It returns raw bytes, you need to convert that into hex.
它返回原始字节，您需要将其转换为十六进制。
$x = unpack("H*", $row[0]);
echo $x[1];
回答by Meredith L. Patterson
It appears that what's being printed from the Oracle query is the raw bytestream of the md5 checksum, mangled because most of those octets won't be ascii characters. Try converting it to hexadecimal first.
从 Oracle 查询中打印出来的似乎是 md5 校验和的原始字节流，因为这些八位字节中的大多数不会是 ascii 字符而被损坏。首先尝试将其转换为十六进制。
回答by aswardly
Create a function like the following:
创建一个如下所示的函数：
create or replace
function md5( input varchar2 ) return sys.dbms_obfuscation_toolkit.varchar2_checksum as
begin
return lower(rawtohex(utl_raw.cast_to_raw(sys.dbms_obfuscation_toolkit.md5( input_string => input ))));
end;
and call it like this:
并这样称呼它：
select md5('foobar') from dual;
it seems that "dbms_obfuscation_toolkit.md5" does not really return in raw format, hence the need to call "utl_raw.cast_to_raw". I could be wrong though, there should be a better explanation for this.
似乎“dbms_obfuscation_toolkit.md5”并没有真正以原始格式返回，因此需要调用“utl_raw.cast_to_raw”。不过，我可能是错的，对此应该有更好的解释。
回答by Bazz
In case you would want to have the md5 in Oracle, you can use this method:
如果您想在 Oracle 中使用 md5，可以使用以下方法：
select lower(rawtohex(md5hash('foobar'))) from dual
回答by costmo
I got the same "numeric or value error" and found that two functions together work:
我得到了相同的“数字或值错误”，发现两个函数一起工作：
CREATE OR REPLACE FUNCTION MD5RAW( v_input_string in varchar2 )
RETURN varchar2 IS
v_checksum varchar2( 32 );
BEGIN
v_checksum := SYS.DBMS_OBFUSCATION_TOOLKIT.MD5( input_string => v_input_string );
return v_checksum;
END;
CREATE OR REPLACE FUNCTION MD5HEX( v_input_string in varchar2 )
RETURN varchar2 IS
v_hex_value varchar2( 32 );
BEGIN
SELECT LOWER( RAWTOHEX( MD5RAW( v_input_string ) ) )
INTO v_hex_value
FROM dual;
return v_hex_value;
END;
Then you can run this query to get your checksum:
然后你可以运行这个查询来获取你的校验和：
SELECT md5hex( 'my string smoked your hash' ) FROM dual;
That second function does the same thing as issuing the SELECT statement provided by Bazz on the function you provide, but I prefer to not have to do the rawToHex --> lower conversion inside of every query. That leaves too many things to potentially go wrong every time you use the query. I think it may be faster too since it is compiled at creation time instead of at runtime, but I may be wrong about that.
第二个函数与在您提供的函数上发出 Bazz 提供的 SELECT 语句的作用相同，但我更喜欢不必在每个查询中执行 rawToHex --> 较低的转换。每次使用查询时，都会有很多事情可能会出错。我认为它也可能更快，因为它是在创建时而不是在运行时编译的，但我可能错了。
