Início / dba / Perguntas / 297529
Accepted
Adam Mulla
Asked: 2021-08-04 06:23:09 +0800 CST

Como usar a exceção definida pelo usuário - Função PostgreSQL

  • 772

Você pode fornecer a sintaxe para usar a exceção definida pelo usuário na função PostgreSQL?

Suponha que eu queira lidar com a exceção definida pelo usuário.

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

Existem vários códigos de erro SQLSTATE, mas não é possível encontrar qual é o código SQLSTATE para este erro. Usei acima que é 22023 mas não resolveu.

Temos abaixo o código que podemos gerenciar a exceção relacionada à violação exclusiva, mas não podemos gerenciar "a senha é muito curta". poderia me ajudar com a sintaxe?

Código:

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 respostas

  1. Best Answer

    Capturando e levantando erros

    Para interceptar erros , use apenas uma EXCEPTION cláusula no código PL/pgSQL. Pode ter várias WHENcláusulas. (Mas você parece precisar apenas de um . Veja abaixo.)

    Você pode trabalhar com códigos de erro como com nomes de condição. A lista de códigos de erro pode ser encontrada no manual .

    Considere também a página de manual do Postgres sobre interceptação de erros em PL/pgSQL :

    Os nomes das condições podem ser qualquer um dos mostrados no Apêndice A. Um nome de categoria corresponde a qualquer erro em sua categoria. [...] Além disso, uma condição de erro pode ser especificada por SQLSTATEcódigo; por exemplo, estes são equivalentes:

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

    Assim, você pode capturar erros e (re-)aumentá-los com detalhes diferentes ou adicionais.

    Levantar seus próprios erros é uma questão diferente.
    Basta usar RAISEcom todos os detalhes desejados. Não é necessário interceptar um erro primeiro.

    injeção SQL

    Não relacionado à sua pergunta principal, seu código exibido está aberto à injeção de SQL.

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

    Não use isso.

    Seria divertido chamar sua função com:

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

    (Ou pior.) Boom. Ver:

    Manipule a entrada do usuário corretamente. Ver:

    Função

    Sua função pode ficar assim (assumindo o Postgres 13 atual):

    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<>fique aqui

    Observe o uso de format()para citar corretamente o nome e o valor e defenda-se contra injeção de SQL.

    Ligar:

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

    Produz:

    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');

    Produz:

    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

relate perguntas