current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 1 down vote favorite 
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[]);
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.