Thread: Could be a FAQ: prepare/execute in PHP?
My company is migrating our PHP4 web sites from Interbase (boo!) to PostgreSQL (yay!). One big snag, though, is that we had been using the ibase_prepare() and ibase_execute() functions quite heavily throughout all of the sites, and PHP's PostgreSQL API doesn't *seem* to include similar functionality. Now, I don't know that it would have a negative performance impact. Frankly, I haven't delved into the code, so for all I know, it uses a lot of internal caching to emulate prepare/execute. The main problem, though, is that the prepare/execute style lets you avoid a lot of quoting issues when inserting data. For example, in our old code, we'd use: $sth = ibase_prepare($dbh, "insert into foo (bar, baz) values (?, ?)"); ibase_execute($sth, $var1, $var2); Since the PHP backend would map the placeholders directly to the fields bar and baz, we didn't have to worry about any great level of sanity checking. Any values of $var1 and $var2 would be cheerfully written into bar and baz, regardless of quotes or embedded SQL statements or anything else. The lack of an pg_execute() function means that we'll have to build the SQL queries from scratch and rewrite large chunks of our code. So, does anyone have any suggestions for how we can work around this, or how other people have handled the situation, or even why the PostgreSQL API is better or worse than the MySQL/Interbase API? A million thanks, -- Kirk Strauser Internet Software Engineer NMotion, Inc.
What is your magic quotes setting?
If you do:
pg_exec ($dbh, "insert into foo (bar, baz) values ($var1, $var2)");
With magic quotes on php will escape everything for you.
I suspect your biggest chore is going to be rewriting code that loops through resultsets...
You might want to look into one of the database abastration classes (phplib.sourceforge.net or pear).
culley
>>> Kirk Strauser <kirk@nmotioninc.com> 11/28/01 08:53AM >>>
My company is migrating our PHP4 web sites from Interbase (boo!) to
PostgreSQL (yay!). One big snag, though, is that we had been using the
ibase_prepare() and ibase_execute() functions quite heavily throughout all
of the sites, and PHP's PostgreSQL API doesn't *seem* to include similar
functionality.
Now, I don't know that it would have a negative performance impact.
Frankly, I haven't delved into the code, so for all I know, it uses a lot of
internal caching to emulate prepare/execute. The main problem, though, is
that the prepare/execute style lets you avoid a lot of quoting issues when
inserting data. For example, in our old code, we'd use:
$sth = ibase_prepare($dbh, "insert into foo (bar, baz) values (?, ?)");
ibase_execute($sth, $var1, $var2);
Since the PHP backend would map the placeholders directly to the fields bar
and baz, we didn't have to worry about any great level of sanity checking.
Any values of $var1 and $var2 would be cheerfully written into bar and baz,
regardless of quotes or embedded SQL statements or anything else. The lack
of an pg_execute() function means that we'll have to build the SQL queries
from scratch and rewrite large chunks of our code.
So, does anyone have any suggestions for how we can work around this, or how
other people have handled the situation, or even why the PostgreSQL API is
better or worse than the MySQL/Interbase API?
A million thanks,
--
Kirk Strauser
Internet Software Engineer
NMotion, Inc.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--- Kirk Strauser <kirk@nmotioninc.com> wrote: > So, does anyone have any suggestions for how we can work around > this, or how > other people have handled the situation, or even why the PostgreSQL > API is > better or worse than the MySQL/Interbase API? You might do yourself a favor and do your code conversion with the PHPLIB (http://phplib.sourceforge.net/) database abstraction layer. It is very intuitive and easy to use and allows you to change between PG, MySQL, Oracle, etc with virtually no re-coding. Brent __________________________________________________ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1
----- Original Message ----- From: Brent R. Matzelle <bmatzelle@yahoo.com> Sent: Wednesday, November 28, 2001 3:25 PM > --- Kirk Strauser <kirk@nmotioninc.com> wrote: > > So, does anyone have any suggestions for how we can work around this, or how > > other people have handled the situation, or even why the PostgreSQL API is > > better or worse than the MySQL/Interbase API? > > You might do yourself a favor and do your code conversion with the > PHPLIB (http://phplib.sourceforge.net/) database abstraction layer. > It is very intuitive and easy to use and allows you to change between > PG, MySQL, Oracle, etc with virtually no re-coding. As of 4.0.6 PHP has a DBX extension, which is an equivalent to Perl's DBI and does whatever you described above. We used it for a quite a while on a production system without having any problems of any sort.