从node-pg向postgres函数传递自定义类型的数组和SQL注入

本文是小编为大家收集整理的关于从node-pg向postgres函数传递自定义类型的数组和SQL注入的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

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注入攻击的安全.我找不到将自定义类型数组传递到节点postgres的查询方法的方法.有没有办法将自定义类型数组传递给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侧执行！).您的查询看起来像这样:

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[]
    );

甚至应该在没有明确演员的情况下工作:

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做到这一点:

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

返回上面使用的字符串表示:

本文地址：https://www.itbaoku.cn/post/1764195.html

百度翻译此文有道翻译此文

问题描述

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

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?

Query:

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[]);

推荐答案

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

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:

查看全文

上一篇：fe_sendauth: 在Cloud9 IDE上设置PostgreSQL数据库后没有提供密码的错误

下一篇：在PostgreSQL 9.3+的JSON，它是如此的丑陋？