从用户输入中使用动态列名打开游标的安全方法

I am trying write function which open cursor with dynamic column name in it. And I am concerned about obvious SQL injection possibility here. I was happy to see in the fine manual that this can be easily done, but when I try it in my example, it goes wrong with

我正在尝试使用动态列名打开游标的写入功能。我担心这里有明显的SQL注入可能性。我很高兴在精美的手册中看到这可以轻松完成,但是当我在我的例子中尝试它时,它出错了

error: column does not exist.

错误:列不存在。

My current attempt can be condensed into this SQL Fiddle. Below, I present formatted code for this fiddle.

我当前的尝试可以压缩到这个SQL小提琴中。下面,我提供了这个小提琴的格式化代码。

The goal of tst() function is to be able to count distinct occurances of values in any given column of constant query.

tst()函数的目标是能够计算常量查询的任何给定列中不同的值的出现。

I am asking for hint what am I doing wrong, or maybe some alternative way to achieve the same goal in a safe way.

我要求提示我做错了什么,或者可能是以安全的方式实现同一目标的另一种方法。

    CREATE TABLE t1 (
        f1 character varying not null,
        f2 character varying not null
    );
    CREATE TABLE t2 (
        f1 character varying not null,
        f2 character varying not null
    );
    INSERT INTO t1 (f1,f2) VALUES ('a1','b1'), ('a2','b2');
    INSERT INTO t2 (f1,f2) VALUES ('a1','c1'), ('a2','c2');

    CREATE OR REPLACE FUNCTION tst(p_field character varying)
        RETURNS INTEGER AS
    $BODY$ 
    DECLARE 
        v_r record; 
        v_cur refcursor; 
        v_sql character varying := 'SELECT count(DISTINCT(%I)) as qty 
                                    FROM t1 LEFT JOIN t2 ON (t1.f1=t2.f1)'; 
    BEGIN  
        OPEN v_cur FOR EXECUTE format(v_sql,lower(p_field)); 
        FETCH v_cur INTO v_r; 
        CLOSE v_cur; 
        return v_r.qty; 
    END; 
    $BODY$ 
    LANGUAGE plpgsql;

Test execution:

SELECT tst('t1.f1')

Provides error message:

提供错误消息:

ERROR: column "t1.f1" does not exist
Hint: PL/pgSQL function tst(character varying) line 1 at OPEN

1 个解决方案

#1

This would work:

这可行:

SELECT tst('f1');

The problem you are facing: format() interprets parameters concatenated with %I as one identifier. You are trying to pass a table-qualified column name that consists of two identifiers, which is interpreted as "t1.f1" (one name, double-quoted to preserve the otherwise illegal dot in the name.

您遇到的问题:format()将与%I连接的参数解释为一个标识符。您正在尝试传递由两个标识符组成的表限定列名称,该标识符被解释为“t1.f1”(一个名称,双引号以保留名称中的其他非法点)。

If you want to pass table and column name, use two parameters:

如果要传递表名和列名,请使用以下两个参数:

CREATE OR REPLACE FUNCTION tst2(_col text, _tbl text = NULL)
  RETURNS int AS
$func$
DECLARE
   v_r record;
   v_cur refcursor;
   v_sql text := 'SELECT count(DISTINCT %s) AS qty
                  FROM t1 LEFT JOIN t2 USING (f1)';
BEGIN
   OPEN v_cur FOR EXECUTE
      format(v_sql, CASE WHEN _tbl <> ''  -- rule out NULL and ''
                         THEN quote_ident(lower(_tbl)) || '.' ||
                              quote_ident(lower(_col))
                         ELSE quote_ident(lower(_col)) END);
   FETCH v_cur INTO v_r;
   CLOSE v_cur;
   RETURN v_r.qty;
END
$func$ LANGUAGE plpgsql;

Aside: It's DISTINCT f1- no parentheses around the column name, unless you want to make it a row type.

旁白:它是DISTINCT f1-列名称周围没有括号,除非你想让它成为行类型。

Actually, you don't need a cursor for this at all. Faster, simpler:

实际上,根本不需要光标。更快,更简单:

CREATE OR REPLACE FUNCTION tst3(_col text, _tbl text = NULL, OUT ct bigint) AS
$func$
BEGIN
   EXECUTE format('SELECT count(DISTINCT %s) AS qty
                   FROM t1 LEFT JOIN t2 USING (f1)'
                 , CASE WHEN _tbl <> ''  -- rule out NULL and ''
                        THEN quote_ident(lower(_tbl)) || '.' ||
                             quote_ident(lower(_col))
                        ELSE quote_ident(lower(_col)) END)
   INTO ct;
   RETURN;
END
$func$ LANGUAGE plpgsql;

I provided NULL as parameter default for convenience. This way you can call the function with just a column name or with column and table name. But not without column name.

为方便起见,我提供了NULL参数默认值。这样,您只需使用列名或列名和表名即可调用该函数。但不是没有列名。

Call:

SELECT tst3('f1', 't1');
SELECT tst3('f1');
SELECT tst3(_col := 'f1');

Same as for test2().

与test2()相同。

SQL Fiddle.

#1