Oracle SQL Insert Trigger to Hash Password 不起作用(CHAR 问题) [英] Oracle SQL Insert Trigger to Hash Password is not working (Issue with CHAR)

问题描述

我正在尝试构建一个插入触发器来将字符串散列到 SH-256 密码中:

DROP TABLE 客户端；创建表客户端(生成的 ID 号始终作为身份主键，密码 CHAR(64) NOT NULL);-- 添加插入触发器创建或替换触发器 client_hash_trigger插入前客户端每行宣布密码哈希字符(64)；开始SELECT STANDARD_HASH(:NEW.password, 'SHA256') INTO password_hash FROM dual;:NEW.password := password_hash;结尾;插入客户端值(默认，'BruteForce')；

当我运行 SQL 时，我会在我的表中得到一个错误的哈希值.当我将展位字符更改为 varchar 时，它正在工作.当我将 STANDARD_HASH 直接放入插入行时，它也能正常工作.问题出在哪里?

解决方案

[TL;DR] 使用 STANDARD_HASH( RTRIM( :new.PASSWORD ), 'SHA256' ) 作为您想要生成的'BruteForce' 上的密码而不是 'BruteForce '(等等)用空格填充到 64 个字符的长度(这是使用 CHAR(64) 会给你)

---

如果您不想使用盐(您应该在这个时代使用)，那么只需正确修剪密码以去除 CHAR 数据类型填充了字符串:

CREATE OR REPLACE TRIGGER client_hash_trigger在插入客户端之前每行开始SELECT STANDARD_HASH( RTRIM( :new.PASSWORD ), 'SHA256' )进入 :new.PASSWORD从双重;结尾;/

那么:

CREATE TABLE 客户端 (编号 (10,0)始终作为身份生成约束客户端__id__pk 主键，密码字符(64)非空);INSERT INTO client (id, password) VALUES (DEFAULT, 'BruteForce');

将输出:

SELECT id, password from client;

<块引用><前>身份证 |密码-: |:---------------------------------------------------------------1 |6BAFE79E0E7360094A490C0B84F2D5BA1C5A6E5C6FE1E45C2E7850966698AF29

---

散列和加盐

改编自这个答案

如果你也想遵循最佳实践，那么你应该在散列之前对密码加盐:

CREATE TABLE 客户端 (编号 (10,0)始终作为身份生成约束客户端__id__pk 主键，密码字符(64)非空，密码盐 VARCHAR2(61)非空);

那么你的触发器是:

CREATE TRIGGER client_hash_trigger在客户端插入或更新之前每行开始如果 :new.PASSWORD = :old.PASSWORD 那么-- 假设事情没有改变(哈希冲突的可能性很小).-- 如果密码哈希没有改变，请确保旧的盐没有被替换.:new.PASSWORD_SALT := :old.PASSWORD_SALT;别的-- 重新生成一个新的盐并散列密码.:new.PASSWORD_SALT := DBMS_RANDOM.STRING('P', FLOOR(DBMS_RANDOM.VALUE(40, 61)));SELECT STANDARD_HASH (:new.PASSWORD_SALT || RTRIM(:new.PASSWORD), 'SHA256')进入 :new.PASSWORD从双重;万一;结尾;/

然后:

INSERT INTO client (id, password) VALUES (DEFAULT, 'BruteForce');

可能输出:

SELECT * FROM client;

<块引用><前>身份证 |密码 |密码_盐-: |:--------------------------------------------------------------- |:------------------------------------------1 |7465541FDF9379B2112D4E92150F594732139E94B79D0EB0247593B4E8CEB3E4 |dhj4GOC8E(xA&8b9f)j@"Y-o$G!UECR\go.SrFaZ<&

db<>fiddle 这里

I'm trying to build a insert trigger to hash a string into a SH-256 password:

DROP TABLE client;

CREATE TABLE client (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    password CHAR(64) NOT NULL
);


-- Add Insert Trigger
CREATE OR REPLACE TRIGGER client_hash_trigger
    BEFORE INSERT
    ON client
    FOR EACH ROW
DECLARE 
    password_hash CHAR(64);
BEGIN
    SELECT STANDARD_HASH(:NEW.password, 'SHA256') INTO password_hash FROM dual;
    :NEW.password := password_hash;
END;


INSERT INTO client VALUES (DEFAULT, 'BruteForce');

When I run the SQL I'll get a wrong hash in my table. When I change booth char to varchar it's working. When I put the STANDARD_HASH directly into the insert line it working as well. Where is the issue?

解决方案

[TL;DR] Use STANDARD_HASH( RTRIM( :new.PASSWORD ), 'SHA256' ) as you want to generate the password on 'BruteForce' and not 'BruteForce ' (etc.) padded with white-space to a length of 64 characters (which is what using CHAR(64) would give you).

---

If you don't want to use a salt (which you should be using in this day-and-age) then just right-trim the password to get rid of the trailing white-space that the CHAR data type have padded the string with:

CREATE OR REPLACE TRIGGER client_hash_trigger
BEFORE INSERT ON client
FOR EACH ROW
BEGIN
  SELECT STANDARD_HASH( RTRIM( :new.PASSWORD ), 'SHA256' )
  INTO   :new.PASSWORD
  FROM   DUAL;
END;
/

Then:

CREATE TABLE client (
    id            NUMBER(10,0)
                  GENERATED ALWAYS AS IDENTITY
                  CONSTRAINT client__id__pk PRIMARY KEY,
    password      CHAR(64)
                  NOT NULL
);

INSERT INTO client (id, password) VALUES (DEFAULT, 'BruteForce');

Will output:

SELECT id, password FROM client;

ID | PASSWORD                                                        
-: | :---------------------------------------------------------------
 1 | 6BAFE79E0E7360094A490C0B84F2D5BA1C5A6E5C6FE1E45C2E7850966698AF29

---

Hashing and Salting

Adapted from this answer

If you also want to follow best practice, then you should salt the password before hashing:

CREATE TABLE client (
    id            NUMBER(10,0)
                  GENERATED ALWAYS AS IDENTITY
                  CONSTRAINT client__id__pk PRIMARY KEY,
    password      CHAR(64)
                  NOT NULL,
    password_salt VARCHAR2(61)
                  NOT NULL
);

Then your trigger is:

CREATE TRIGGER client_hash_trigger
BEFORE INSERT OR UPDATE ON client
FOR EACH ROW
BEGIN
  IF :new.PASSWORD = :old.PASSWORD THEN
    -- Assume things haven't changed (The chances of a hash collision are vanishingly small).
    -- Make sure the old salt is not replaced if the password hash hasn't changed.
    :new.PASSWORD_SALT := :old.PASSWORD_SALT;
  ELSE
    -- Regenerate a new salt and hash the password.
    :new.PASSWORD_SALT := DBMS_RANDOM.STRING( 'P', FLOOR( DBMS_RANDOM.VALUE( 40, 61 ) ) );
    SELECT STANDARD_HASH ( :new.PASSWORD_SALT || RTRIM( :new.PASSWORD ), 'SHA256' )
    INTO   :new.PASSWORD
    FROM   DUAL;
  END IF;
END;
/

And then:

INSERT INTO client (id, password) VALUES (DEFAULT, 'BruteForce');

May output:

SELECT * FROM client;

ID | PASSWORD                                                         | PASSWORD_SALT                              
-: | :--------------------------------------------------------------- | :------------------------------------------
 1 | 7465541FDF9379B2112D4E92150F594732139E94B79D0EB0247593B4E8CEB3E4 | dhj4GOC8E(xA&8b9f)j@"Y- o$G!UECR\go.SrFaZ<&

db<>fiddle here

这篇关于Oracle SQL Insert Trigger to Hash Password 不起作用(CHAR 问题)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！