skip to Main Content

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!

3

Answers


  1. 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;
    

  2. 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 \

    Login or Signup to reply.
  3. 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;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search