CODEWITHSUNDEEP
oracle-databasehashplsqlbase64
hahaha
hahaha

Reputation: 1037

Input to base64binary in Oracle

So I have this stored procedure in Microsof SQL Server that takes 2 parameters (password and salt) and produces a base64binary. i would like to transfer this logic in Oracle

... set @source = hashbytes('sha2_256',@password + @Salt) set @encrypted=( select cast(N'' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')) ...

SAMPLE DATA:

@password=1234567890123456 and @salt=TESTTEST

@source will be 0xC1DB209FD3B6873B0F35C2FB103CCC5E9C585C26F79048D575318AA70E17F07F

and @encrypted will be wdsgn9O2hzsPNcL7EDzMXpxYXCb3kEjVdTGKpw4X8H8=

Trying to implement this i saw hash2 in oracle is a bit complicated so i thought ill try and at least do the base64binary part.

I tried this (and a few others) which i found in other questions here in SO

function to_base64(t in varchar2) return varchar2 is begin return utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t))); end to_base64;

but passing the @source value i get from the first step in MS SQL, I get different encoding string..

Is there something similar in Oracle? How should I do it?

Upvotes: 2

Views: 399

Answers (1)

Alex Poole
Alex Poole

Reputation: 191435

You can use dbms_crypto.hash() to hash the password+salt value; you need to convert that to raw first, and the result is also raw.

Then convert that raw hashed value to base64, which is still raw; and then finally convert that to varchar2.

declare
  l_password varchar2(30) := '1234567890123456';
  l_salt varchar2(30) := 'TESTTEST';
  l_raw raw(256);
  l_hash raw(256);
  l_base64_raw raw(256);
  l_base64 varchar2(256);
begin
  l_raw := utl_i18n.string_to_raw(data => l_password || l_salt, dst_charset => 'AL32UTF8');
  l_hash := dbms_crypto.hash(src => l_raw, typ => dbms_crypto.hash_sh256);
  dbms_output.put_line(l_hash);
  l_base64_raw := utl_encode.base64_encode(l_hash);
  dbms_output.put_line(l_base64_raw);
  l_base64 := utl_raw.cast_to_varchar2(l_base64_raw);
  dbms_output.put_line(l_base64);
end;
/

C1DB209FD3B6873B0F35C2FB103CCC5E9C585C26F79048D575318AA70E17F07F
776473676E394F32687A73504E634C3745447A4D58707859584362336B456A566454474B707734583848383D
wdsgn9O2hzsPNcL7EDzMXpxYXCb3kEjVdTGKpw4X8H8=


PL/SQL procedure successfully completed.

Or as a function, simplified slightly:

create or replace function hash_base64 (p_password varchar2, p_salt varchar2)
return varchar2
is
  l_raw raw(256);
  l_hash raw(256);
  l_base64 varchar2(256);
begin
  l_raw := utl_i18n.string_to_raw(data => p_password || p_salt, dst_charset => 'AL32UTF8');
  l_hash := dbms_crypto.hash(src => l_raw, typ => dbms_crypto.hash_sh256);
  l_base64 := utl_raw.cast_to_varchar2(r => utl_encode.base64_encode(l_hash));
  return l_base64;
end;
/    

select hash_base64 ('1234567890123456', 'TESTTEST') from dual;

HASH_BASE64('1234567890123456','TESTTEST')        
--------------------------------------------------
wdsgn9O2hzsPNcL7EDzMXpxYXCb3kEjVdTGKpw4X8H8=

I think the problem with what you were doing was that you were passing in the source value as a string, and then casting that string to raw; i.e. you were passing the string value 'C1DB2...' and casting that to raw, rather than passing that raw value.

So you'd effectively do something like:

declare
  l_base64 varchar2(256);
begin
  l_base64 := utl_raw.cast_to_varchar2(
                utl_encode.base64_encode(
                  utl_raw.cast_to_raw('C1DB209FD3B6873B0F35C2FB103CCC5E9C585C26F79048D575318AA70E17F07F')));
  dbms_output.put_line(l_base64);
end;
/

which gets

QzFEQjIwOUZEM0I2ODczQjBGMzVDMkZCMTAzQ0NDNUU5QzU4NUMyNkY3OTA0OEQ1NzUzMThBQTcwRTE3RjA3Rg==

or maybe you included the 0x prefix, which gets:

MHhDMURCMjA5RkQzQjY4NzNCMEYzNUMyRkIxMDNDQ0M1RTlDNTg1QzI2Rjc5MDQ4RDU3NTMxOEFBNzBFMTdGMDdG

Rather than casting that string to raw, you can just pass it as a raw literal:

declare
  l_base64 varchar2(256);
begin
  l_base64 := utl_raw.cast_to_varchar2(
                utl_encode.base64_encode('C1DB209FD3B6873B0F35C2FB103CCC5E9C585C26F79048D575318AA70E17F07F'));
  dbms_output.put_line(l_base64);
end;
/

wdsgn9O2hzsPNcL7EDzMXpxYXCb3kEjVdTGKpw4X8H8=

Upvotes: 3

Related Questions