Skip to Main Content
  • Questions
  • dbms_crypto.sign (sign_sha256_rsa) base64 result is different than same sign with nodejs crypto

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andri.

Asked: September 26, 2024 - 8:51 am UTC

Last updated: September 30, 2024 - 4:56 am UTC

Version: 21.3 XE, 23ai

Viewed 100+ times

You Asked

I try to sign simple text using dbms_crypto.sign package but when I do it with a nodejs script then I get different results.
Main purpose of this is to sign and get DKIM signature, when I do it with a nodejs then its ok and will pass example Gmail DKIM
check, but when I do it with a plsql then I get different signature result and there is something wrong with... but what ???

# plslq script with test private key is here: https://livesql.oracle.com/ords/livesql/s/ccwff37zhsw1nncjxddtjslao

declare
l_privateKey varchar2(4000) := replace(replace(replace(replace(
'-----BEGIN RSA PRIVATE KEY-----
MIIEpAIBAAKCAQEAw+ImPl9v2JVqN0y5PNZIgSpk6hJnWhZfgkgE9ZPfoWCJkbOi
EXPUtAhUN6xjv2OhnjUQ4/EDMiQmvUzYu7cacUxD/6mHDSqVmhy3PvayI2aeipwN
gP5YWjlWzdWixnbx8HzuD1MssBo0k4R9CygwHKzpGRhwW59m+J6iZ0mGiIb3X3BP
fD0RF8a9uuUfGpgqyUHubQrdTHBExn+Fte49xldkrHQK2JdNtf4NTyRFg60GGz3j
w83wQhN6zbEkdQ4TZ8ceWwZ8UQnbq5YEie29uvn8ErNTl3UJR+ggBCszTrvFUAe1
6E4CRLWY0GRn4K9NN9f9mrrhFwBI3yIDk3HZIQIDAQABAoIBAQCfb9CPDiE8WYll
z3YqtYO4Vqhi+W3UHsfIhBQ9SwpIEQqIZHzy1aP4kZry4ccivdaLrke8MKzYQCKk
DAuQ5t2IAHqMexEov1ObZ+ojPqsKM3mx+ojnSJ7jeS5VRaFDBwewJg+iu6FxCTNo
KvuYfDKE9JyaR5EUkm8+x+gKSAdP0ZakkBEUFX/pU75dLCNEdDXqjA5b/pc6y+eQ
TJs1n+1+3v6PGllezjKbMOOpxu6y5hagP8cOWQlbR44o+aChqAkH43fJK5PRP56g
vdtP+HcPWdIvSseIguMQF5Gf2RY7Edk27bfMBiGsJU79UQJMQusH+AETG4ZAX/9H
pQQiHsJtAoGBAOR2h0BrJ5eQwOqRLnUj49Z0PnJNSTJeo8PjhvArdV9J9cf+YuJW
3gcBC1cjE9JF4Mj9vaARCBvfLPOLWh2I9ALOSouez2UQOAX4BOGX1lcWcb5JRbFv
uYt6Zz8DbjOU4tfqTAObROR64CWE7h0an3xhMRKACIebD1XKpscLSKAjAoGBANt+
V6CzTl0Wr7Xzd3AlTnTd1kIgVmMJPB13g0zxf8xnWWJeu6jSAXLGfwKUp1Ugrri4
UubPgTVSi6UmfxUcp/2zcVXOpGGo5XRHAOTCZhKsV0hpXARDLm/7OfzOmmgLPYBX
0Oe4pZfsyTPMncRLw7bts7taNxAezaTcVvmsrtPrAoGAY6QKUx8o8mKwsN1G/NH9
YZ/rSGDKxswd8qZ+SKs7kqaoKmSZbgvi4/0hnAxxbakKY76/AFvNRJKugT3KvMzp
V3vvaTuHSpWrSXclcdqOIgdo7kv4uG146AsnkoUFgDu0P+Uw5/XFMz0gG10BmPHu
kwm0pFPw5GvNYWZ0HKWgNC8CgYBC4RdQNvJcuOnnNf0hpaxnPaToVzQbTxgLHnxa
eIdpP+BjcdwaT4rzbvoKTkNCVAgI7tkLzRRgq/sy4iz8wNOLIR8adzFtNGpd6Lij
+3vgmrSpsri/77x5kIIVvtobc7lUnu1ffcqvJQ4Vg3Za9n73ASyvnIiEl+4vGphB
0VUf6wKBgQCtQPEUUIsLfwBw6sFzrFTejTLji7689VStgAQyfvB0HXmtkTdcZ2uw
fjqGAMC1V4m+aGHUE48gDXucqJ48m3OICw1y24Jv9D2GJtb36KFaqxD1vGQ76mji
V72V60xnap7WsTmIUrOIY425tvFw5RZYnnuaR+KFx+Hq3knL2KHpBw==
-----END RSA PRIVATE KEY-----'
,unistr('\000d'),null),unistr('\000a'),null),
'-----BEGIN RSA PRIVATE KEY-----',null),
'-----END RSA PRIVATE KEY-----',null); 
l_messageTxt varchar2(4000) := 
    'This is a test message!'||unistr('\000d\000a')||
    'We want to know if a node:crypto created siganture'||unistr('\000d\000a')||
    'is the same as we create signature with dbms_crypto.sign';
l_signature varchar2(4000);
begin
l_signature := utl_raw.cast_to_varchar2( 
                utl_encode.base64_encode( 
                  dbms_crypto.sign(
                    src        => dbms_crypto.hash( utl_raw.cast_to_raw( l_messageTxt ) , dbms_crypto.hash_sh256 ),
                    prv_key    => utl_raw.cast_to_raw( l_privateKey ),
                    pubkey_alg => dbms_crypto.key_type_rsa,
                    sign_alg   => dbms_crypto.sign_sha256_rsa                                            
                  ) 
                ) 
              );
dbms_output.put_line( replace(replace(l_signature,unistr('\000d'),null),unistr('\000a'),null) );                       
end; 


# plsql base64 result :

QpTrDuV52p0phBvqxlljX9Cyr93eVXFArKpYsUURovH1Vx4loCqRY8GIv+tKZ8miELoFjIrqCtIhot7hnJhWb53IUrWt2WOGaCtpcsjMyjKKGBbbADud3CVuFRl1L/mX2BZ07sxNe/mezgADqHWnmuDH0pLC8adyr+iXRMW/s9wYAycqO0EXEmUbx/e3ahdTKpzl50bHu6Yy/Zl8zMZG7RFpfEoK6ZPiK6rjJcMc2+D3iPJPCJ7HRXtIkg62glJPZ82eT134WccaAeVBIEngPb2dhnycUxEjOwZajwecf1otYDEwMkHmlmaMItYNUKNDxwW6AQJY8bFImlfgUlDuDw==

# nodejs script is:

var crypto = require('node:crypto');
var privateKey = require("fs").readFileSync("./test-private.key");
var messageTxt = 'This is a test message!'+'\r\n'+
                 'We want to know if a node:crypto created siganture'+'\r\n'+
                 'is the same as we create signature with dbms_crypto.sign';
signer = crypto.createSign( 'RSA-SHA256' );
signer.update( messageTxt );
signature = signer.sign( privateKey, 'base64' );  
console.log( signature );


# nodejs base64 result:

K7NYGlrZg/tI3jYT9KkXAG0houTALo0tzn0bvYardbpijuNeT0t2G1dPdeGRBI1kk349WiYvFVVxPDNAx15qeNnJhj1F6Qfoxx55kHaxGKZm0u1ZuipFEDVXz450SouEU3jieDRIUekEActvXYr4UL3d09nHVZnehrtM6o53tF6ueNeiaZvWYT5hiel7pGaf8IhRRUCFV3CYmo9rRD7NrEeNbC4MkQB5KfHSu/nKPnr5s3oxI/WcbU6atnBHVd7dlQf54QV1met9ApQ2OxrK7XXvZbptEtG4lPymudqfS6fZejnlhJfGK//anoFEVoEKWF5mZOwpWa5SLsboxHMejA==


and Connor said...

Here's some sample code:

SQL> set serverout on
SQL> declare
  2    l_privateKey varchar2(4000) := 'MIICXAIBAAKBgQCw3/lfgpMFt4TN8F...';
  3    l_messageTxt varchar2(4000) :=
  4      'This is a test message!';
  5
  6        l_signature   raw (2000);
  7  begin
  8       l_signature := dbms_crypto.sign
  9       (
 10        src        => utl_i18n.string_to_raw(l_messagetxt,'al32utf8'),
 11        prv_key    => utl_i18n.string_to_raw( l_privatekey, 'al32utf8'),
 12        pubkey_alg => dbms_crypto.key_type_rsa,
 13        sign_alg   => dbms_crypto.sign_sha256_rsa
 14       );
 15      dbms_output.put_line(utl_raw.cast_to_varchar2(utl_encode.base64_encode(l_signature)));
 16  end;
 17  /
EtFqIvp/7MNkFN8EtwAdntP9r58BkTCf7TmXu+N4RIWMe58DllbynaXXLgSstUAM
4B17h3utfBIrwZ2eXfSSWNLkzWGNwwIJHqIM9/7PbdBp73g8WhFuP22OYJyEx4A6
eL29AyyPYA9RYOdPRnts/lg+q7ekOKXYjSFUXlxlm8U=

PL/SQL procedure successfully completed.


I then went to this site for a verify and got the same result.

https://8gwifi.org/rsasignverifyfunctions.jsp

SIGN_SHA256

So fold your code into the demo above and see how you go

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library