Thread: Passing a table as parameter
Hi,
Is there any way for a SQL or PL/pgSQL function to receive a table
as parameter? As an illustration, consider the dummy example below.
Note that functions get_from_data1 and get_from_data2 follow essentially
the same pattern; it would be nice to define instead a single polymorphic
function parameterised on the id and table. Is this possible?
Thanks in advance!
Jon
CREATE TABLE data1 (id int4, content text);
CREATE TABLE data2 (id int8, content text);
CREATE FUNCTION get_from_data1 (int4)
RETURNS SETOF text
LANGUAGE sql STABLE AS
$$
SELECT content FROM data1 WHERE id = $1;
$$;
CREATE FUNCTION get_from_data2 (int8)
RETURNS SETOF text
LANGUAGE sql STABLE AS
$$
SELECT content FROM data2 WHERE id = $1;
$$;
Hello 2011/3/21 Jon Smark <jon.smark@yahoo.com>: > Hi, > > Is there any way for a SQL or PL/pgSQL function to receive a table > as parameter? As an illustration, consider the dummy example below. > Note that functions get_from_data1 and get_from_data2 follow essentially > the same pattern; it would be nice to define instead a single polymorphic > function parameterised on the id and table. Is this possible? > > Thanks in advance! > Jon > > > CREATE TABLE data1 (id int4, content text); > CREATE TABLE data2 (id int8, content text); > > > CREATE FUNCTION get_from_data1 (int4) > RETURNS SETOF text > LANGUAGE sql STABLE AS > $$ > SELECT content FROM data1 WHERE id = $1; > $$; > > > CREATE FUNCTION get_from_data2 (int8) > RETURNS SETOF text > LANGUAGE sql STABLE AS > $$ > SELECT content FROM data2 WHERE id = $1; > $$; > > you can pass a table name as parameter only: CREATE FUNCTION foo(tablename text) RETURNS SETOF text AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT content FROM ' || quote_ident(tablename); END; $$ LANGUAGE plpgsql; Regards Pavel Stehule > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Mar 21, 2011, at 11:13 PM, Jon Smark wrote:
>
> Is there any way for a SQL or PL/pgSQL function to receive a table
> as parameter? As an illustration, consider the dummy example below.
> Note that functions get_from_data1 and get_from_data2 follow essentially
> the same pattern; it would be nice to define instead a single polymorphic
> function parameterised on the id and table. Is this possible?
Try Something like given below:
CREATE OR REPLACE FUNCTION tab_pass(text) returns setof numeric
as
$$
DECLARE
rec record;
BEGIN
for rec in execute 'select empno from '||$1||' where id = 1'
LOOP
return next rec.empno;
END LOOP;
return next rec.empno;
END;
$$ language plpgsql;
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com
2011/3/21 Vibhor Kumar <vibhor.kumar@enterprisedb.com>: > > On Mar 21, 2011, at 11:13 PM, Jon Smark wrote: > >> >> Is there any way for a SQL or PL/pgSQL function to receive a table >> as parameter? As an illustration, consider the dummy example below. >> Note that functions get_from_data1 and get_from_data2 follow essentially >> the same pattern; it would be nice to define instead a single polymorphic >> function parameterised on the id and table. Is this possible? > > > Try Something like given below: > CREATE OR REPLACE FUNCTION tab_pass(text) returns setof numeric > as > $$ > DECLARE > rec record; > BEGIN > for rec in execute 'select empno from '||$1||' where id = 1' > LOOP > return next rec.empno; > END LOOP; > return next rec.empno; > END; > $$ language plpgsql; it can work too, but there is sql injection risk. Do newer 'SELECT ... FROM ' || tabname || ' ... Regards Pavel Stehule > > > Thanks & Regards, > Vibhor Kumar > EnterpriseDB Corporation > The Enterprise PostgreSQL Company > vibhor.kumar@enterprisedb.com > Blog:http://vibhork.blogspot.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Mar 22, 2011, at 1:32 AM, Pavel Stehule wrote: > it can work too, but there is sql injection risk. > > Do newer 'SELECT ... FROM ' || tabname || ' ... > > Regards > > Pavel Stehule Yes true. Same with the following too: CREATE FUNCTION foo(tablename text) RETURNS SETOF text AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT content FROM ' || quote_ident(tablename); END; $$ LANGUAGE plpgsql; To prevent from sql injection user can try with SQL Protect: http://www.enterprisedb.com/docs/en/9.0/sqlprotect/Table%20of%20Contents.htm Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.kumar@enterprisedb.com Blog:http://vibhork.blogspot.com
2011/3/21 Vibhor Kumar <vibhor.kumar@enterprisedb.com>: > > On Mar 22, 2011, at 1:32 AM, Pavel Stehule wrote: > >> it can work too, but there is sql injection risk. >> >> Do newer 'SELECT ... FROM ' || tabname || ' ... >> >> Regards >> >> Pavel Stehule > > Yes true. Same with the following too: > CREATE FUNCTION foo(tablename text) > RETURNS SETOF text AS $$ > BEGIN > RETURN QUERY EXECUTE 'SELECT content FROM ' || quote_ident(tablename); > END; > $$ LANGUAGE plpgsql; > > To prevent from sql injection user can try with SQL Protect: > http://www.enterprisedb.com/docs/en/9.0/sqlprotect/Table%20of%20Contents.htm > simply thinks as using USAGE clause or functions quote_ident, quote_literal are faster and absolutly secure :). Software like SQL Protect is good for old unsecured applications but better do development well. Regards Pavel Stehule > Thanks & Regards, > Vibhor Kumar > EnterpriseDB Corporation > The Enterprise PostgreSQL Company > vibhor.kumar@enterprisedb.com > Blog:http://vibhork.blogspot.com > >
On Mar 22, 2011, at 1:52 AM, Pavel Stehule wrote: > simply thinks as using USAGE clause or functions quote_ident, > quote_literal are faster and absolutly secure :). Software like SQL I don't think usage of quote_ident in current requirement of user, would prevent sql injection. Running sql multiple times, someone can guess the tabename which can give data: ERROR: relation "am" does not exist LINE 1: SELECT content FROM am ^QUERY: SELECT content FROM amCONTEXT: PL/pgSQL function "foo" line 2 at RETURN QUERY SQL Protect will make above message something like given below: ERROR: SQLPROTECT: Illegal Query: relations Which stops user guessing relation. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.kumar@enterprisedb.com Blog:http://vibhork.blogspot.com
2011/3/21 Vibhor Kumar <vibhor.kumar@enterprisedb.com>: > > On Mar 22, 2011, at 1:52 AM, Pavel Stehule wrote: > >> simply thinks as using USAGE clause or functions quote_ident, >> quote_literal are faster and absolutly secure :). Software like SQL > > I don't think usage of quote_ident in current requirement of user, would prevent sql injection. > Running sql multiple times, someone can guess the tabename which can give data: > ERROR: relation "am" does not exist > LINE 1: SELECT content FROM am ^QUERY: SELECT content FROM amCONTEXT: PL/pgSQL function "foo" line 2 at RETURN QUERY > > SQL Protect will make above message something like given below: > ERROR: SQLPROTECT: Illegal Query: relations > it is different view on security. When you have not a security gap, then is irelevant if somebody has unlimited number of trials. SQL Protect is "security by obscurity" - a logout can be a good sign for blind injection. well usage of quote_ident and quote_literal is a perfect protection against sql injection. Wrong query doesn't mean a problem. Problem is when attacker can change a semantic of SQL query. Pavel > Which stops user guessing relation. > > Thanks & Regards, > Vibhor Kumar > EnterpriseDB Corporation > The Enterprise PostgreSQL Company > vibhor.kumar@enterprisedb.com > Blog:http://vibhork.blogspot.com > >