Dynamic Query

[Andrew Hall-6]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Hi,

I'm a novice PostgreSQL developer from an Oracle background and am trying to replicate some Oracle functionality in PostgreSQL / plpgSQL.

I'm trying to write a stored function to implement a search: the function has several parameters - the value of any could be 'null' on any given invocation, indicating that this parameter does not represent a data item being searched on.

In Oracle, this could be implemented as follows - this implementation copes with missing values and allows the user of bind variables - helping to guarantee performance and also providing protection against SQL Injection:

FUNCTION fnGetStandardUsers
(
  p_in_aur_username         IN VARCHAR2
, p_in_is_account_enabled IN VARCHAR2
)
RETURN SYS_REFCURSOR
IS

    l_SQL     VARCHAR2(32767 CHAR) DEFAULT   
                                               ' SELECT '
                                           || '        vsaur.aur_id                 id '
                                           || '      , vsaur.aur_username           '
                                           || '      , vsaur.aur_is_account_enabled '
                                           || '   FROM '
                                           || '        app_data.v_standard_app_user vsaur '
                                           || '  WHERE '
                                           || '        1 = 1 ';

BEGIN

  IF p_in_aur_username IS NOT NULL THEN
    l_SQL := l_SQL || ' AND vsaur.aur_username LIKE ''%''||:p_in_aur_username||''%'' ';
  ELSE
    l_SQL := l_SQL || ' AND (1 = 1 OR :p_in_aur_username IS NULL) ';
  END IF;

   OPEN
         l_dataSet
     FOR
         l_SQL
   USING
         UPPER(p_in_aur_username);

   RETURN l_dataSet;

END fnGetStandardUsers;

Is there a recommended way to translate this function into plpgSQL which would protect me from SQL Injection (most important for me) and use bind variables (of secondary importance?

The postgresql documentation seems to suggest that I can use the RETURN QUERY EXECUTE feature, or simply build my query with a string and execute it (I don't see how the latter can protect me from SQL Injection though???)

Any help would be appreciated!

Thanks,

Andrew

---

Use Windows Live Messenger for free on selected mobiles. Learn more.

[Tom Lane-2]

Andrew Hall <andrewah@...> writes:
> Is there a recommended way to translate this function into plpgSQL which would protect me from SQL Injection (most important for me) and use bind variables (of secondary importance?

See quote_literal() and/or quote_nullable().  On the whole though I
think you'd be best off not using a dynamically-constructed query at
all --- given the desired %'s in the LIKE pattern, there is not going
to be any benefit at all from using an unparameterized query.  Just
write it out without all the string-construction.

                        regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql