Início / dba / Perguntas / 173862
Accepted
Ced
Asked: 2017-05-18 11:15:05 +0800 CST

Por que colname como parâmetro de entrada da função = não é uma boa ideia?

  • 772

Alguém me disse que usar entrada de texto para nomes de coluna e formatá-la, como faço abaixo, raramente é uma boa ideia. Quando perguntei por que, no entanto, uma resposta não foi dada. Isso foi no postgresql IRC, e esses caras parecem saber das coisas. Então eu gostaria de saber por que não é aconselhável? Estou principalmente me perguntando se isso abre a porta para injeção de sql. 

create or replace function getItemsOrderBy(order_by_p text)
RETURNS TABLE (id int) AS $$


BEGIN

    return query EXECUTE format('
    SELECT id
    FROM items 
    ORDER BY %s', order_by_p) ;

END;

Ele também disse para usar executecom usingem vez disso, então qual é a diferença entre isso:

return query EXECUTE format('
SELECT id
FROM items 
ORDER BY %s', order_by_p) ;

e isto :

return query EXECUTE '
SELECT id
FROM items 
ORDER BY $1' USING order_by_p ;

Minha função é mais complexa do que a acima - a parte do formato é apenas parte dela. Eu tenho a opção de criar uma função que pode lidar com vários casos (para pedidos) ou criar vários deles para lidar com todos os pedidos. Eu senti que fazer apenas um era mais prático. Não ter nenhuma função não é uma opção.

Na verdade, estou usando pg-promise, mas fiquei com a impressão de que, como estou fazendo muitas idas e vindas entre o back-end e o banco de dados (enviar algo, aguardar resposta, calcular outra coisa, enviar novamente ..) eu deveria ir com função e deixe tudo acontecer de uma vez.

postgresql postgresql-9.4

2 respostas

  1. Best Answer

    Há duas perguntas aqui,

    1. Qual é a diferença entre EXECUTE .. USINGeEXECUTE FORMAT()
    2. Por que agrupar ou gerar instruções SQL simples em uma função procedural é uma má ideia?

    A diferença entre EXECUTE .. USINGeEXECUTE FORMAT()

    Dos documentos,

    A string de comando pode usar valores de parâmetro, que são referenciados no comando como $1, $2, etc. Esses símbolos referem-se a valores fornecidos na cláusula USING. Esse método geralmente é preferível a inserir valores de dados na string de comando como texto: ele evita a sobrecarga de tempo de execução de converter os valores em texto e vice-versa, e é muito menos propenso a ataques de injeção de SQL, pois não há necessidade de citar ou escapando. Um exemplo é:

    EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
  INTO c
  USING checked_user, checked_date;

    Observe que os símbolos de parâmetro só podem ser usados ​​para valores de dados — se você quiser usar nomes de tabela ou coluna determinados dinamicamente, você deve inseri-los na string de comando textualmente. Por exemplo, se a consulta anterior precisasse ser feita em uma tabela selecionada dinamicamente, você poderia fazer isso:

    EXECUTE 'SELECT count(*) FROM '
    || quote_ident(tabname)
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

    Uma abordagem mais limpa é usar a especificação %I do format() para nomes de tabelas ou colunas (strings separadas por uma nova linha são concatenadas):

    EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

    Um EXECUTE com uma string de comando constante simples e alguns parâmetros USING, como no primeiro exemplo acima, é funcionalmente equivalente a apenas escrever o comando diretamente em PL/pgSQL e permitir que a substituição de variáveis ​​PL/pgSQL aconteça automaticamente. A diferença importante é que EXECUTE irá replanejar o comando a cada execução, gerando um plano específico para os valores dos parâmetros atuais; enquanto o PL/pgSQL pode criar um plano genérico e armazená-lo em cache para reutilização. Em situações em que o melhor plano depende fortemente dos valores dos parâmetros, pode ser útil usar EXECUTE para garantir positivamente que um plano genérico não seja selecionado.

    Então você tem alguns argumentos aqui.

    1. Você pode usar os doisEXECUTE FORMAT() ... USING
    2. USINGpermite que o plano seja armazenado em cache.
    3. USINGpermite que os símbolos permaneçam símbolos e impede que eles tenham que ser convertidos em texto e reescapados.
    4. USINGnão pode ser usado com identificadores, apenas literais.

    Embrulhar e gerar instruções SQL simples em uma função procedural é uma má ideia.

    Quanto à outra parte da questão,

    Então eu gostaria de saber por que não é aconselhável? (Envolvendo instruções SQL simples em plpgsql.)

    Há muitas razões para isso,

    • funções como tal obscurecem os custos (plural) para o planejador e exigem que o usuário
      1. defina o custo de execução (ou use uma estimativa bastante boba)
      2. 9.6+ estabelecer se é seguro/restrito/inseguro
      3. 9.6+ estabelecer se tem efeitos colaterais estritos/imutáveis/voláteis
    • eles obscurecem os internos para o usuário.
    • eles evitam o pushdown de predicado.
    • eles complicam as permissões (agora você também precisa acessar a função).
    • eles levantam a barreira para manutenção, agora você tem que definir o conjunto de resultados que a função retornaTABLE (id int)
    • eles apresentam todos os tipos de problemas com ORMs.

    E não é SQL. Você está construindo uma nova linguagem em cima de um DBMS. Por quê?

    Quanto ao componente dinâmico, existem outras maneiras de contornar o problema. Tomemos, por exemplo, a declaração exata fornecida, o pior cenário é onde você vê que

    SELECT * FROM getItemsOrderBy($col);

    Você tem que escrever explicitamente o pedido. Por pior que seja, é uma solução melhor na minha opinião.

    SELECT id FROM items ORDER BY col1
SELECT id FROM items ORDER BY col2
SELECT id FROM items ORDER BY col3

    Um passo ainda a mais seria usar uma biblioteca que fornece algum tipo de assistência para gerar, comopg-promise

    let args = { orderBy: "col1" };
if ( args.orderBy !== 'col1' ) {
  throw new Error "invalid orderBy Column";
}
db.manyOrNone( 'SELECT id FROM items ORDER BY ${orderBy~}', args );

    Ou DBIx::Abstract ou um ORM como DBIx::Class .

    • 2

  2. Apenas abordando sua segunda pergunta sobre EXECUTEe USING:

    O elefante na sala: a alternativa sugerida é um completo absurdo por vários motivos.

    A USINGcláusula ofEXECUTE é usada para passar valores . Nem literais, nem identificadores, nem outros elementos de sintaxe, apenas valores. Os mesmos valores que você pode passar para instruções preparadas porque, internamente, é exatamente isso que acontece. A instrução é preparada e executada usando valores fornecidos por USING.

    Isso é um absurdo múltiplo:

    retornar consulta EXECUTE '
SELECIONAR ID
DE itens
PEÇA POR $1' USANDO order_by_p;
    1. ORDER BYnão faz sentido com um valor constante, seria apenas ruído.
    2. order_by_pdeve ser um nome de coluna, que deve ser interpretado como identificador, não como valor de dados. Você nunca usa USINGpara isso. Veja acima.
    3. Se você concatenar identificadores em strings SQL, você deve escapá-los para evitar injeção de SQL e outros erros sorrateiros. Com format()o uso do %Iespecificador ou com quote_ident().

    Funcionaria assim:

    CREATE OR REPLACE FUNCTION get_items_orderby(order_by_p text)
  RETURNS TABLE(id int) AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
   SELECT id
   FROM   items 
   ORDER  BY %I', order_by_p);
END
$func$ LANGUAGE plpgsql;

    Contanto que você não aninhe essa função em consultas externas (além de SELECT * FROM my_func()), e não precise otimizar o desempenho (como para muitas chamadas repetidas na mesma sessão), não há nada de errado nisso.

    Observe que os identificadores diferenciam maiúsculas de minúsculas e você deve fornecer a ortografia correta. Respostas relacionadas:

    • 2

relate perguntas