pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew McMillan <andrew@catalyst.net.nz>
To: pg 043g9j2g <pg043g9j2g@gmail.com>
Cc: pgsql-php@postgresql.org
Subject: Re: newbie issues with PDO / stored procedures
Date: Thu, 26 Jun 2008 23:54:13 +1200
Message-ID: <1214481253.8013.22.camel@hippy.mcmillan.net.nz> (raw)
In-Reply-To: <486186CF.80107@gmail.com>
References: <4850F066.7070306@gmail.com>
	<1213333327.18669.172.camel@hippy.mcmillan.net.nz>
	<486186CF.80107@gmail.com>

On Tue, 2008-06-24 at 19:44 -0400, pg 043g9j2g wrote:

> >   
> It helped a lot! Thank you!
> 
> Now I am using DSQL in my application in a form like 'SELECT * FROM 
> my_usp(:param);' and using PDO's bindParam() function to pass in params.
> 
> So I think I am still achieving the intended security of using SPs 
> against SQL injection by not directly inserting request args in my SQL, 
> as well as being able to pass in params without explicitly creating a 
> VIEW first.

If your reasons for doing all this obfuscation are simply to avoid SQL
injection issues, then it seems to me that you are putting your efforts
into the wrong place.

PDO's bindParam() function is *already* achieving your goal.  Or PDO is,
when used correctly, even without binding parameters to names.

By jumping through these sorts of hoops you are making your code
significantly less maintainable (code needs to be maintained in two
different languages in two different places), and less efficient (the
database cannot plan as effectively), and more fragile (the separate
code bases may get out of sync, introducing a whole new set of
possibilities for error)....  and I could go on more too :-)



> I have accomplished this by defining a VIEW for my desired output schema 
> first and having the function return that type, but, is there any simple 
> "generic" type I can use as a return type to avoid having to do so? 
> Right now I am working in a RAD/prototyping mode and it slows me down a 
> bit to have to formalize everything in that way.

If you want to enforce read-only, (on top of the SQL injection avoidance
which PDO bought you already) then a view is a good way.  By default all
views in PostgreSQL are read-only, though they can be made writable with
a little extra effort.

Regards,
					Andrew McMillan.


-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 6, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
  Don't tell any big lies today.  Small ones can be just as effective.
-------------------------------------------------------------------------





view thread (4+ messages)

Message-ID: <1214481253.8013.22.camel@hippy.mcmillan.net.nz>
Permalink:  ../1214481253.8013.22.camel@hippy.mcmillan.net.nz/
Also on:    postgresql.org/message-id/1214481253.8013.22.camel@hippy.mcmillan.net.nz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: andrew@catalyst.net.nz, pg043g9j2g@gmail.com
  Subject: Re: newbie issues with PDO / stored procedures
  In-Reply-To: <1214481253.8013.22.camel@hippy.mcmillan.net.nz>
  References: <4850F066.7070306@gmail.com> <1213333327.18669.172.camel@hippy.mcmillan.net.nz> <486186CF.80107@gmail.com> <1214481253.8013.22.camel@hippy.mcmillan.net.nz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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