从node-pg和SQL注入将自定义类型的数组传递给postgres函数 [英] Passing array of custom type to postgres function from node-pg and SQL injection

问题描述

CREATE TYPE phototable AS (
    photoid integer,
    parentid integer,
    fileextension character varying(20),
    description text,
    tag character varying(100)
);

CREATE FUNCTION addphotos(
          p_placeid integer
        , p_permissiontypeid integer
        , p_description text DEFAULT NULL::text
        , p_photos phototable[] DEFAULT NULL::phototable[]) 

BEGIN
........
END

我正在通过生成SQL查询从node.js（使用node-postres）调用此函数。我想避免这种情况，并希望使用参数化查询，因为我认为它对SQL注入攻击并不安全。我找不到将自定义类型数组传递给node-postgres的查询方法的方法。有没有一种方法可以将自定义类型数组传递给node-postgres的查询函数？

I am calling this function from node.js (using node-postres) by generating an SQL query. I want to avoid it and want to use parameterized queries because I think it is not secure against SQL injection attacks. I was not able to find a way to pass custom type array to the query method of node-postgres. Is there a way to pass custom type array to the query function of node-postgres?

查询：

select * from addphotos(p_placeid:=2210, p_permissiontypeid:=2, p_description:='Party', 
p_photos:=array[row(null, null,'.JPG','smart','6e8f74b2-4c14-4f40-ae19-8abae026a539'),
row(null, null,'.JPG',null,'c4e9f75f-25fa-4893-82f1-44c4791d58e5')]::phototable[]);

推荐答案

我不熟悉node.js，但是您可以为列 p_photos 提供字符串文字，而不是 ARRAY 和 ROW 构造函数（这些函数需要在Postgres一侧执行！）。您的查询将如下所示：

I am not familiar with node.js, but you could provide a string literal for your column p_photos instead of the ARRAY and ROW constructors (which are functions that need to be executed at the Postgres side!). Your query would look like this:

SELECT * FROM addphotos(
    p_placeid := 2210
  , p_permissiontypeid := 2
  , p_description := 'Party'
  , p_photos:='{"(,,.JPG,smart,6e8f74b2-4c14-4f40-ae19-8abae026a539)"
               ,"(,,.JPG,,c4e9f75f-25fa-4893-82f1-44c4791d58e5)"}'::phototable[]
    );

甚至无需显式强制转换也可以工作：

Should even work without explicit cast:

SELECT * FROM addphotos(
    p_placeid := 2210
  , p_permissiontypeid := 2
  , p_description := 'Party'
  , p_photos:='{"(,,.JPG,smart,6e8f74b2-4c14-4f40-ae19-8abae026a539)"
               ,"(,,.JPG,,c4e9f75f-25fa-4893-82f1-44c4791d58e5)"}'
    );

将语法重写为字符串文字的快速简便方法：让Postgres做到：

The fast and simple way to "rewrite" your syntax to a string literal: let Postgres do it:

SELECT array[row(null, null,'.JPG','smart','6e8f74b2-4c14-4f40-ae19-8abae026a539'),
             row(null, null,'.JPG',null,'c4e9f75f-25fa-4893-82f1-44c4791d58e5')]::phototable[]

返回我上面使用的字符串表示形式：

Returns the string representation I used above:

这篇关于从node-pg和SQL注入将自定义类型的数组传递给postgres函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！