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/
