在PL/pgSQL中使用USING关键字清理用户输入 [英] Sanitize user input with the USING keyword in PL/pgSQL

问题描述

这是我创建search_term的方式:

    IF char_length(search_term) > 0 THEN
        order_by := 'ts_rank_cd(textsearchable_index_col, to_tsquery(''' || search_term || ':*''))+GREATEST(0,(-1*EXTRACT(epoch FROM age(last_edited)/86400))+60)/60 DESC';
        search_term := 'to_tsquery(''' || search_term || ':*'') @@ textsearchable_index_col';
    ELSE
        search_term := 'true';
    END IF;

我在使用PLPGSQL函数时遇到了一些麻烦:

I am having some trouble with a PLPGSQL function:

    RETURN QUERY EXECUTE '
        SELECT
            *
        FROM
            articles
        WHERE
            $1 AND
            ' || publication_date_query || ' AND
            primary_category LIKE ''' || category_filter || ''' AND
            ' || tags_query || ' AND
            ' || districts_query || ' AND
            ' || capability_query || ' AND
            ' || push_notification_query || ' AND
            ' || distance_query || ' AND
            ' || revision_by || ' AND
            ' || publication_priority_query || ' AND
            ' || status_query || ' AND
            is_template = ' || only_templates || ' AND
            status <> ''DELETED''
        ORDER BY ' || order_by || ' LIMIT 500'
        USING search_term;
    END; $$;

返回错误:

AND的参数必须为布尔型，而不是字符64处的文本

argument of AND must be type boolean, not type text at character 64

相对于:

        RETURN QUERY EXECUTE '
            SELECT
                *
            FROM
                articles
            WHERE
                ' || search_term || ' AND
                ' || publication_date_query || ' AND
                primary_category LIKE ''' || category_filter || ''' AND
                ' || tags_query || ' AND
                ' || districts_query || ' AND
                ' || capability_query || ' AND
                ' || push_notification_query || ' AND
                ' || distance_query || ' AND
                ' || revision_by || ' AND
                ' || publication_priority_query || ' AND
                ' || status_query || ' AND
                is_template = ' || only_templates || ' AND
                status <> ''DELETED''
            ORDER BY ' || order_by || ' LIMIT 500';
        END; $$;

...有效.我想念什么吗?
我的目标是清理用户输入.

... which works. Am I missing something?
My goal is to sanitize my user input.

推荐答案

如果您的某些输入参数可以为 NULL 或 empty ，在这种情况下应将其忽略，您最好根据用户输入动态地构建整个语句-并完全省略相应的WHERE/ORDER BY子句.

If some of your input parameters can be NULL or empty and should be ignored in this case, you best build your whole statement dynamically depending on user input - and omit respective WHERE / ORDER BY clauses completely.

关键是在此过程中正确，安全(优雅地)处理NULL和空字符串.对于初学者来说，search_term <> ''是比char_length(search_term) > 0更聪明的测试.参见:

The key is to handle NULL and empty string correctly, safely (and elegantly) in the process. For starters, search_term <> '' is a smarter test than char_length(search_term) > 0. See:

• 检查空值或空值"

您需要对PL/pgSQL有深入的了解，否则您可能会陷入困境.您的案例的示例代码:

And you need a firm understanding of PL/pgSQL, or you may be in over your head. Example code for your case:

CREATE OR REPLACE FUNCTION my_func(
         _search_term            text = NULL  -- default value NULL to allow short call
       , _publication_date_query date = NULL 
    -- , more parameters
       )
  RETURNS SETOF articles AS
$func$
DECLARE
   sql       text;
   sql_order text;   -- defaults to NULL

BEGIN
   sql := concat_ws(' AND '
    ,'SELECT * FROM articles WHERE status <> ''DELETED'''  -- first WHERE clause is immutable
    , CASE WHEN _search_term <> ''            THEN '$1 @@ textsearchable_index_col' END  -- ELSE NULL is implicit
    , CASE WHEN _publication_date_query <> '' THEN 'publication_date > $2'          END  -- or similar ...
 -- , more more parameters
   );

   IF search_term <> '' THEN  -- note use of $1!
      sql_order  := 'ORDER BY ts_rank_cd(textsearchable_index_col, $1) + GREATEST(0,(-1*EXTRACT(epoch FROM age(last_edited)/86400))+60)/60 DESC';
   END IF;

   RETURN QUERY EXECUTE concat_ws(' ', sql, sql_order, 'LIMIT 500')
   USING  to_tsquery(_search_term || ':*')  -- $1  -- prepare ts_query once here!
        , _publication_date_query           -- $2  -- order of params must match!
     -- , more parameters
   ;

END
$func$  LANGUAGE plpgsql;

我为函数参数添加了默认值，因此您可以忽略在调用中不适用的参数.喜欢:

I added default values for function parameters, so you can omit params that don't apply in the call. Like:

SELECT * FROM my_func(_publication_date_query => '2016-01-01');

更多:

• 具有可变数量输入参数的函数
• 被遗忘的赋值运算符"="和普通的":: ="

• Functions with variable number of input parameters
• The forgotten assignment operator "=" and the commonplace ":="

请注意concat_ws()的战略用途.参见:

Note the strategic use of concat_ws(). See:

• 如何在Postgres SELECT中串联列?

这是一个有很多解释的相关答案:

Here is a related answer with lots of explanation:

• 测试具有可变参数的函数中的空值

这篇关于在PL/pgSQL中使用USING关键字清理用户输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！