generate hmac sha1 with unicode escaped payload, oracle plsql

localmonkey
January 10, 2019
237 views
2 votes
3 Answers

so i have an ORDS endpoint (Oracle Rest Data Services) that receives a JSON payload from Facebook API, and a variable, X-Hub-Signature, which comes in the header of the request.

i have to validate the request i receive, so i know it’s from Facebook.

i have to generate a hash that receives the payload (BLOB) and a key (string) that both facebook and i share (app_secret), and then i compare it to the value of X-Hub-Signature, so i can confirm it’s a valid request.

problem is, Facebook says:
“Please note that we generate the signature using an escaped unicode version of the payload, with lowercase hex digits. If you just calculate against the decoded bytes, you will end up with a different signature. For example, the string äöå should be escaped to u00e4u00f6u00e5.”

So far my hashes are a match to the payloads i receive, but i tried with those äöå characters and i can’t know for sure if it’s working, since all hmac online encoders don’t look that good and also i don’t know how to unicode escape them (on the online encoders).

so far i have this:

FUNCTION validate_payload (p_x_hub_signature      in     varchar2,
                           p_json_payload         in     blob)
RETURN varchar2
IS
    v_app_secret        varchar2(4000) := '2f2f2f2f2f2f2f';
    l_mac               raw(10000);
    v_x_hub_signature   varchar2(4000);

BEGIN

    l_mac := dbms_crypto.mac (src => p_json_payload,
                              typ => dbms_crypto.hmac_sh1,
                              key => UTL_I18N.STRING_TO_RAW (v_app_secret, 'AL32UTF8'));

    v_x_hub_signature := 'sha1='||lower(l_mac);

    return v_x_hub_signature;      

END;

can you give any feedback on this?
is this right?

thanks in advance, sorry bad english or explanation!

Answers

Chosen as BEST ANSWER

i just realized, i may have induced you into error. as Sentinel mentioned, v_app_secret doesn't need to be translated, only p_json_payload, which is a BLOB.

so far i've come up with this: do you think it's okay? i have no way to know for sure :/

FUNCTION validate_payload (p_x_hub_signature      in     varchar2,
                           p_json_payload         in     blob)
RETURN varchar2
IS
    v_app_secret        varchar2(4000) := '3f2f2f2f23f23f23';
    l_mac               raw(10000);
    v_x_hub_signature   varchar2(4000);
BEGIN
    l_mac := dbms_crypto.mac (src => UTL_I18N.STRING_TO_RAW (utl_raw.cast_to_varchar2 (p_json_payload),'AL32UTF8'),
                              typ => dbms_crypto.hmac_sh1,
                              key => UTL_I18N.STRING_TO_RAW (v_app_secret, 'AL32UTF8'));

    v_x_hub_signature := 'sha1='||lower(l_mac);

    dbms_output.put_line(v_x_hub_signature);

    return v_x_hub_signature;      

END;

(Edit)

Here is a PL/SQL solution. It requires less coding than Sentinel’s proposal and might be more clear.

declare
    v_app_secret varchar2(100) := 'äaöå abABC';
    escaped varchar2(100);
    item varchar2(6);
begin

    escaped := replace(regexp_replace(ASCIISTR(v_app_secret), '\([[:xdigit:]]{4})', 'u1'), 'u005C', '\');

    for i in 1..regexp_count(escaped, '\u[[:xdigit:]]{4}') loop
        item := regexp_substr(escaped, '\u[[:xdigit:]]{4}', 1, i);
        escaped := replace(escaped, item, lower(item));        
    end loop;
    dbms_output.put_line(escaped);

end;


u00e4au00f6u00e5 ab\ABC

Assuming needs to be translated to \

Starting with @Wernfried Domscheit’s method of using ASCIISTR and regexp_replace, it can be extended to a whole list of replacements to get the lower case versions:

with sample(str) as (
  select 'äöå abABC' from dual
), patterns(ord, pat, rep) as (
  select 1, '\A([[:xdigit:]]{3})', '\a1' from dual union all
  select 2, '\B([[:xdigit:]]{3})', '\b1' from dual union all
  select 3, '\C([[:xdigit:]]{3})', '\c1' from dual union all
  select 4, '\D([[:xdigit:]]{3})', '\d1' from dual union all
  select 5, '\E([[:xdigit:]]{3})', '\e1' from dual union all
  select 6, '\F([[:xdigit:]]{3})', '\f1' from dual union all
  select 7, '\([[:xdigit:]])A([[:xdigit:]]{2})', '\1a2' from dual union all
  select 8, '\([[:xdigit:]])B([[:xdigit:]]{2})', '\1b2' from dual union all
  select 9, '\([[:xdigit:]])C([[:xdigit:]]{2})', '\1c2' from dual union all
  select 10, '\([[:xdigit:]])D([[:xdigit:]]{2})', '\1d2' from dual union all
  select 11, '\([[:xdigit:]])E([[:xdigit:]]{2})', '\1e2' from dual union all
  select 12, '\([[:xdigit:]])F([[:xdigit:]]{2})', '\1f2' from dual union all
  select 13, '\([[:xdigit:]]{2})A([[:xdigit:]])', '\1a2' from dual union all
  select 14, '\([[:xdigit:]]{2})B([[:xdigit:]])', '\1b2' from dual union all
  select 15, '\([[:xdigit:]]{2})C([[:xdigit:]])', '\1c2' from dual union all
  select 16, '\([[:xdigit:]]{2})D([[:xdigit:]])', '\1d2' from dual union all
  select 17, '\([[:xdigit:]]{2})E([[:xdigit:]])', '\1e2' from dual union all
  select 18, '\([[:xdigit:]]{2})F([[:xdigit:]])', '\1f2' from dual union all
  select 19, '\([[:xdigit:]]{3})A', '\1a' from dual union all
  select 20, '\([[:xdigit:]]{3})B', '\1b' from dual union all
  select 21, '\([[:xdigit:]]{3})C', '\1c' from dual union all
  select 22, '\([[:xdigit:]]{3})D', '\1d' from dual union all
  select 23, '\([[:xdigit:]]{3})E', '\1e' from dual union all
  select 24, '\([[:xdigit:]]{3})F', '\1f' from dual union all
  select 25, '\([[:xdigit:]]{4})', 'u1' from dual union all
  select 26, '\u005c', '\\' from dual
), recur(ord, str, tr) as (
  select ord, str
       , REGEXP_REPLACE(asciistr(str), pat, rep)
    from sample
    join patterns
      on ord = 1
  union all
  select recur.ord+1, str
       , REGEXP_REPLACE(tr, pat, rep)
    from recur
    join patterns
      on patterns.ord = recur.ord + 1
)
select * from recur where ord = 26;

If you want a PL/SQL function to do it, the series of transformations can be nested up and put in a function. If you don’t want the final transformation of u005c to just remove the outer regexp_replace:

create or replace function Escape_Unicode(pCLOB clob) RETURN clob is
BEGIN
  return  regexp_replace(
            regexp_replace(
              regexp_replace(
                regexp_replace(
                  regexp_replace(
                    regexp_replace(
                      regexp_replace(
                        regexp_replace(
                          regexp_replace(
                            regexp_replace(
                              regexp_replace(
                                regexp_replace(
                                  regexp_replace(
                                    regexp_replace(
                                      regexp_replace(
                                        regexp_replace(
                                          regexp_replace(
                                            regexp_replace(
                                              regexp_replace(
                                                regexp_replace(
                                                  regexp_replace(
                                                    regexp_replace(
                                                      regexp_replace(
                                                        regexp_replace(
                                                          regexp_replace(
                                                            regexp_replace(
                                                              asciistr(pClOB)
                                                              , '\A([[:xdigit:]]{3})'
                                                              , '\a1'
                                                            )
                                                            , '\B([[:xdigit:]]{3})'
                                                            , '\b1'
                                                          )
                                                          , '\C([[:xdigit:]]{3})'
                                                          , '\c1'
                                                        )
                                                        , '\D([[:xdigit:]]{3})'
                                                        , '\d1'
                                                      )
                                                      , '\E([[:xdigit:]]{3})'
                                                      , '\e1'
                                                    )
                                                    , '\F([[:xdigit:]]{3})'
                                                    , '\f1'
                                                  )
                                                  , '\([[:xdigit:]])A([[:xdigit:]]{2})'
                                                  , '\1a2'
                                                )
                                                , '\([[:xdigit:]])B([[:xdigit:]]{2})'
                                                , '\1b2'
                                              )
                                              , '\([[:xdigit:]])C([[:xdigit:]]{2})'
                                              , '\1c2'
                                            )
                                            , '\([[:xdigit:]])D([[:xdigit:]]{2})'
                                            , '\1d2'
                                          )
                                          , '\([[:xdigit:]])E([[:xdigit:]]{2})'
                                          , '\1e2'
                                        )
                                        , '\([[:xdigit:]])F([[:xdigit:]]{2})'
                                        , '\1f2'
                                      )
                                      , '\([[:xdigit:]]{2})A([[:xdigit:]])'
                                      , '\1a2'
                                    )
                                    , '\([[:xdigit:]]{2})B([[:xdigit:]])'
                                    , '\1b2'
                                  )
                                  , '\([[:xdigit:]]{2})C([[:xdigit:]])'
                                  , '\1c2'
                                )
                                , '\([[:xdigit:]]{2})D([[:xdigit:]])'
                                , '\1d2'
                              )
                              , '\([[:xdigit:]]{2})E([[:xdigit:]])'
                              , '\1e2'
                            )
                            , '\([[:xdigit:]]{2})F([[:xdigit:]])'
                            , '\1f2'
                          )
                          , '\([[:xdigit:]]{3})A'
                          , '\1a'
                        )
                        , '\([[:xdigit:]]{3})B'
                        , '\1b'
                      )
                      , '\([[:xdigit:]]{3})C'
                      , '\1c'
                    )
                    , '\([[:xdigit:]]{3})D'
                    , '\1d'
                  )
                  , '\([[:xdigit:]]{3})E'
                  , '\1e'
                )
                , '\([[:xdigit:]]{3})F'
                , '\1f'
              )
              , '\([[:xdigit:]]{4})'
              , 'u1'
            )
            , '\u005c'
            , '\\'
          );
end;
/
select escape_unicode('äöå abABCd') from dual;

Please signup or login to give your own answer.

Click here to cancel reply.