Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id AA59A6335AB for ; Mon, 19 Oct 2009 19:39:48 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 01624-04 for ; Mon, 19 Oct 2009 22:39:32 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from col0-omc2-s5.col0.hotmail.com (col0-omc2-s5.col0.hotmail.com [65.55.34.79]) by mail.postgresql.org (Postfix) with ESMTP id A5FDA6334C1 for ; Mon, 19 Oct 2009 19:39:37 -0300 (ADT) Received: from COL122-W44 ([65.55.34.72]) by col0-omc2-s5.col0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Mon, 19 Oct 2009 15:39:35 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_936b120a-d4e5-4979-b28d-7906b7185bb2_" X-Originating-IP: [86.130.78.103] From: Andrew Hall To: Subject: Dynamic Query Date: Mon, 19 Oct 2009 22:39:35 +0000 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 19 Oct 2009 22:39:35.0614 (UTC) FILETIME=[08B75DE0:01CA510D] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.417 tagged_above=-10 required=5 tests=AWL=2.181, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/73 X-Sequence-Number: 33272 --_936b120a-d4e5-4979-b28d-7906b7185bb2_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi=2C I'm a novice PostgreSQL developer from an Oracle background and am trying t= o replicate some Oracle functionality in PostgreSQL / plpgSQL. I'm trying to write a stored function to implement a search: the function h= as several parameters - the value of any could be 'null' on any given invoc= ation=2C indicating that this parameter does not represent a data item bein= g searched on. In Oracle=2C this could be implemented as follows - this implementation cop= es with missing values and allows the user of bind variables - helping to g= uarantee performance and also providing protection against SQL Injection: FUNCTION fnGetStandardUsers ( p_in_aur_username IN VARCHAR2 =2C p_in_is_account_enabled IN VARCHAR2 ) RETURN SYS_REFCURSOR IS l_SQL VARCHAR2(32767 CHAR) DEFAULT =20 ' SELECT ' || ' vsaur.aur_id = id ' || ' =2C vsaur.aur_username= ' || ' =2C vsaur.aur_is_accou= nt_enabled ' || ' FROM ' || ' app_data.v_standard_= app_user vsaur ' || ' WHERE ' || ' 1 =3D 1 '=3B BEGIN IF p_in_aur_username IS NOT NULL THEN l_SQL :=3D l_SQL || ' AND vsaur.aur_username LIKE ''%''||:p_in_aur_user= name||''%'' '=3B ELSE l_SQL :=3D l_SQL || ' AND (1 =3D 1 OR :p_in_aur_username IS NULL) '=3B END IF=3B OPEN=20 l_dataSet=20 FOR l_SQL USING UPPER(p_in_aur_username)=3B RETURN l_dataSet=3B END fnGetStandardUsers=3B Is there a recommended way to translate this function into plpgSQL which wo= uld protect me from SQL Injection (most important for me) and use bind vari= ables (of secondary importance? The postgresql documentation seems to suggest that I can use the RETURN QUE= RY EXECUTE feature=2C 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=2C Andrew =20 _________________________________________________________________ Use Windows Live Messenger for free on selected mobiles http://clk.atdmt.com/UKM/go/174426567/direct/01/= --_936b120a-d4e5-4979-b28d-7906b7185bb2_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi=2C

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

I'm trying to write a stored function to implement a search: the fu= nction has several parameters - the value of any could be 'null' on any giv= en invocation=2C indicating that this parameter does not represent a data i= tem being searched on.

In Oracle=2C this could be implemented as fol= lows - this implementation copes with missing values and allows the user of= bind variables - helping to guarantee performance and also providing prote= ction against SQL Injection:

FUNCTION fnGetStandardUsers
(
&nb= sp=3B p_in_aur_username =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B IN VARCHAR2
=2C p_in_is_account_enabled IN VARCHAR2
) RETURN SYS_REFCURSOR
IS

 =3B =3B =3B l_SQL =3B=  =3B =3B =3B VARCHAR2(32767 CHAR) DEFAULT =3B =3B = =3B
 =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B ' SELECT '
 =3B =3B =3B =3B =3B =3B =3B&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B || ' =3B = =3B =3B =3B =3B =3B =3B vsaur.aur_id =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B id '
 =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B || ' =3B =3B =3B =3B =3B =2C vsaur.aur_usernam= e =3B =3B =3B =3B =3B =3B =3B =3B =3B&n= bsp=3B '
 =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B || ' =3B =3B&nb= sp=3B =3B =3B =2C vsaur.aur_is_account_enabled '
 =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B || ' =3B =3B FROM '
 =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B || ' =3B =3B =3B =3B =3B =3B&nb= sp=3B app_data.v_standard_app_user vsaur '
 =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B || ' =3B WHERE '
 =3B =3B =3B =3B =3B&n= bsp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B || '&= nbsp=3B =3B =3B =3B =3B =3B =3B 1 =3D 1 '=3B
BEGIN

 =3B IF p_in_aur_username IS NOT NULL THEN
 =3B&nb= sp=3B =3B l_SQL :=3D l_SQL || ' AND vsaur.aur_username LIKE ''%''||:p_i= n_aur_username||''%'' '=3B
 =3B ELSE
 =3B  =3B l_SQL :=3D= l_SQL || ' AND (1 =3D 1 OR :p_in_aur_username IS NULL) '=3B
 =3B EN= D IF=3B

 =3B =3B OPEN
 =3B =3B =3B =3B&n= bsp=3B =3B =3B =3B l_dataSet
 =3B =3B =3B = =3B FOR
 =3B =3B =3B =3B =3B =3B =3B =3B= l_SQL
 =3B =3B USING
 =3B =3B =3B =3B = =3B =3B =3B =3B UPPER(p_in_aur_username)=3B

 =3B&nbs= p=3B RETURN l_dataSet=3B

END fnGetStandardUsers=3B

Is there a= recommended way to translate this function into plpgSQL which would protec= t 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=2C or simply build my query= with a string and execute it (I don't see how the latter can protect me fr= om SQL Injection though???)

Any help would be appreciated!

Th= anks=2C

Andrew



Use Windows Live M= essenger for free on selected mobiles. Learn more. = --_936b120a-d4e5-4979-b28d-7906b7185bb2_--