Passing a table as parameter

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

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

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

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