主页 / dba / 问题 / 297529
Accepted
Adam Mulla
Asked: 2021-08-04 06:23:09 +0800 CST

如何使用用户定义的异常 - PostgreSQL 函数

  • 772

您能否提供在 PostgreSQL 函数中使用用户定义异常的语法？

假设我想处理以下用户定义的异常。

SQL Error [22023]: ERROR: password is too short.

有多个 SQLSTATE 错误代码，但无法找到此错误的 SQLSTATE 代码。我在上面使用的是 22023 但没有解决。

我们有以下代码，我们能够管理唯一的违规相关异常，但无法管理“密码太短”。你能帮我语法吗？

代码：

begin
EXECUTE 'ALTER USER ' || $1 || ' WITH PASSWORD '''|| $2||'''' ;
        EXCEPTION WHEN "Password is too short" 
        THEN RAISE DETAIL 'Please check your password';
        
INSERT INTO pwdhistory (usename,password,changed_on) values($1,md5($2),now());
        EXCEPTION WHEN unique_violation 
        THEN RAISE DETAIL 'Password already used earlier. Please try again with another password.';

end;
postgresql plpgsql

1 个回答

  1. Best Answer

    捕获和引发错误

    要捕获错误，请在 PL/pgSQL 代码中仅使用一个子句。 EXCEPTION它可以有多个WHEN子句。（但你似乎只需要一个。见下文。）

    您可以像使用条件名称一样使用错误代码。错误代码列表可以在手册中找到。

    还要考虑关于PL/pgSQL 中的Trapping Errors的 Postgres手册页：

    条件名称可以是附录 A中显示的任何名称。类别名称匹配其类别中的任何错误。[...]此外，可以通过SQLSTATE代码指定错误条件；例如，这些是等价的：

    WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...

    因此，您可以捕获错误并（重新）使用不同的或附加的详细信息提出它们。

    提出自己的错误是另一回事。
    只需RAISE与所有需要的细节一起使用。不需要先捕获错误。

    SQL注入

    与您的核心问题无关，您显示的代码对 SQL 注入是开放的。

    EXECUTE 'ALTER USER ' || $1 || ' WITH PASSWORD '''|| $2||'''' ;

    不要使用这个。

    调用你的函数会很有趣：

    SELECT my_func('user1', 'pw1234567''; DELETE FROM pwdhistory; --');

    （或更糟。）繁荣。看：

    正确处理用户输入。看：

    功能

    您的函数可能如下所示（假设当前的 Postgres 13）：

    CREATE OR REPLACE FUNCTION myfunc(_usename text, _password text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _min_password_length int := 8;  -- specify min length here
BEGIN
   IF length(_password) >= _min_password_length THEN
      EXECUTE format('ALTER USER %I WITH PASSWORD %L', _usename, _password);
   ELSE  -- also catches NULL
      -- raise custom error
      RAISE EXCEPTION 'Password too short!'
      USING ERRCODE = '22023'  -- 22023 = "invalid_parameter_value'
          , DETAIL = 'Please check your password.'
          , HINT = 'Password must be at least ' || _min_password_length || ' characters.';
   END IF;

   
   INSERT INTO pwdhistory
          (usename, password, changed_on)
   VALUES ($1     , md5($2) , now());

EXCEPTION
   -- trap existing error and re-raise with added detail
   WHEN unique_violation THEN  -- = error code 23505   
      RAISE unique_violation
      USING DETAIL = 'Password already used earlier. Please try again with a different password.';
END
$func$;

    db<>在这里摆弄

    注意使用format()正确引用名称和值，并防止 SQL 注入。

    称呼：

    SELECT myfunc('usr', 'pw'); -- PW obviously too short ...

    产生：

    ERROR:  Password too short!
DETAIL:  Please check your password.
HINT:  Password must be at least 8 characters.
CONTEXT:  PL/pgSQL function pg_temp_5.foo(text,text) line 8 at RAISE
SQL state: 22023

    SELECT myfunc('usr', 'repeated_pw');

    产生：

    ERROR:  unique_violation
DETAIL:  Password already used earlier. Please try again with a different password.
CONTEXT:  PL/pgSQL function pg_temp_5.foo(text,text) line 21 at RAISE
SQL state: 23505
    • 2

相关问题