Matching regular expression in an if statement in PostgreSQL

Question

I have written the following function:

CREATE FUNCTION Validate_Password_Hash() RETURNS trigger AS $$
BEGIN
    IF (NEW.Password ~* '^[a-f0-9]{64}$')
    THEN
        RAISE EXCEPTION 'The password hash is invalid! Please use SHA-256. You tried to insert: %', NEW.Password;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

And I attach it to a trigger to fire before an INSERT. However, even if a provide a valid SHA-256 hash, the exception still gets raised, which implies the hash doesn't match the pattern. What could possibly be the problem here?

user2693928 · Accepted Answer · 2016-11-17 11:37:19Z

2

Reverse the logic. You are raising exception if the hash is valid.

Try this:

CREATE FUNCTION Validate_Password_Hash() RETURNS trigger AS $$
BEGIN
    IF (NEW.Password !~* '^[a-f0-9]{64}$')
    THEN
        RAISE EXCEPTION 'The password hash is invalid! Please use SHA-256. You tried to insert: %', NEW.Password;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

answered Nov 17, 2016 at 11:37

user2693928

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Matching regular expression in an if statement in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related