如何使用pl/sql在HMAC-SHA256中编码字符串? [英] how can I encode string in HMAC-SHA256 using pl/sql?

问题描述

我正在使用Oracle数据库11g Express Edition在PL/SQL中进行REST API授权.对于每个过程，我都有api_key，nonce和signature作为IN变量.

I'm working on REST API authorization in PL/SQL using Oracle database 11g Express Edition. I have api_key, nonce and signature as IN variables for every procedure.

签名是HMAC-SHA256编码的字符串，包含存储在我的数据库中的api_secret.我想检查签名是否与数据库中的api_secret匹配.

Signature is a HMAC-SHA256 encoded string containing api_secret stored in my database. I want check if signature matching my api_secret in database.

我的问题是如何使用pl/sql在HMAC-SHA256中编码字符串?

My question is how can I encode string in HMAC-SHA256 using pl/sql?

推荐答案

CruiserX for Oracle 10g，11g的sha256.encrypt对我在oracle XE中的HMAC-SHA256 API授权是巨大的帮助.

sha256.encrypt for Oracle 10g,11g by CruiserX was huge help for my HMAC-SHA256 API authorization in oracle XE.

在oracle 11g XE中具有hmac-sha256 API授权的我的版本的完整源代码，您可以在我的中找到plsql_hmac-sha256 git项目.但是，要执行HMAC键控，我们基本上需要存储一些基本信息，例如上次用户请求的随机数.当然，在我的情况下，还有一些用于散列的函数(oracle 11gXE) sha256.encrypt .但是在较新的版本中，oracle确实为dbms_crypto.hash函数提供了更好的内置选项.

Complete source code for my version with hmac-sha256 API authorization in oracle 11g XE you can find in my plsql_hmac-sha256 git project. But to perform HMAC keying we basically need store some basic information like nonce of last user request. And of course some function for hashing in my case (oracle 11gXE) sha256.encrypt . but in newer version oracle did provide better buil-in options for that like dbms_crypto.hash function.

   PROCEDURE HMAC_AUTHORIZATION (i_api_key  IN   VARCHAR2, i_api_nonce IN NUMBER, i_api_sign IN VARCHAR, R OUT NUMBER)
   IS
    p_auth_id                   NUMBER(10);
    p_api_sign_msg              VARCHAR2(500);
    p_api_nonce                 NUMBER(35);
    p_api_sign                  VARCHAR2(500);
    system_sign                 VARCHAR2(500);

    BEGIN
    SELECT AUTH_ID, API_NONCE INTO p_auth_id, p_api_nonce FROM USER_AUTH WHERE API_KEY = i_api_key AND AUTH_STATUS = 1 AND API_NONCE < i_api_nonce;

    /* User signature */
    p_api_sign_msg := p_auth_id || i_api_key || i_api_nonce;
    p_api_sign := sha256.encrypt(p_api_sign_msg);

    /* system signature */
    system_sign := sha256.encrypt(p_auth_id || i_api_key || i_api_nonce);

    IF p_api_sign = system_sign THEN
        UPDATE USER_AUTH SET REQUESTS_COUNT = REQUESTS_COUNT+1, API_NONCE = i_api_nonce, LAST_REQUEST = SYSDATE WHERE API_KEY = i_api_key AND AUTH_STATUS = 1 AND AUTH_ID = p_auth_id;
        commit;
        R := 1;
    ELSE
        R :=  0;
    END IF;

    EXCEPTION WHEN NO_DATA_FOUND THEN
        R :=  0;
    WHEN OTHERS THEN
       R :=  0;
    END HMAC_AUTHORIZATION;

感谢您的帮助.也许有人会觉得这很有用:)

Thanks for help. Maybe someone will find this useful :)

这篇关于如何使用pl/sql在HMAC-SHA256中编码字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！