pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDynamic Query
2+ messages / 2 participants
[nested] [flat]
* Dynamic Query
@ 2009-10-19 22:39 Andrew Hall <andrewah@hotmail.com>
2009-10-19 22:55 ` Re: Dynamic Query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Andrew Hall @ 2009-10-19 22:39 UTC (permalink / raw)
To: pgsql-sql
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
http://clk.atdmt.com/UKM/go/174426567/direct/01/=
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Dynamic Query
2009-10-19 22:39 Dynamic Query Andrew Hall <andrewah@hotmail.com>
@ 2009-10-19 22:55 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2009-10-19 22:55 UTC (permalink / raw)
To: Andrew Hall <andrewah@hotmail.com>; +Cc: pgsql-sql
Andrew Hall <andrewah@hotmail.com> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-10-19 22:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-19 22:39 Dynamic Query Andrew Hall <andrewah@hotmail.com>
2009-10-19 22:55 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox