[plug] Database advice - Newbie

[James Devenish]

In message <1056433368.20746.32.camel at latte.internal.itmaze.com.au>
on Tue, Jun 24, 2003 at 01:42:48PM +0800, Onno Benschop wrote:
> > Specifically, how to construct SQL queries (esp. UPDATEs) without having
> > code that is like "UPDATE blah WHERE ".stuff." blah $stuff"
> 
> Uhm, now I feel ignorant - no better place to be - is there any other
> way?

Hmm, okay, I guess my question is that if that is how you do it, how do
you make it work? (That was a rhetorical question. If it happens to be
fairly easy to explain, and you want to bother, then I would benefit
from the knowledge and would re-examine my existing PHP scripts in
light of it. But...no pressure.)

Basically, I don't like direct string manipulation because: I have to
worry about escaping quote marks, active/control characters, wildcards,
etc. (and, for some purposes, I would probably have to think about when
*not* to quote them). At the same time, I have to consider the character
encoding, for example. One approach might be to over-escape (to be safe)
but then the data might occasionally get stored in the database in
escaped form. That's fine if the data are only ever going to be drawn
through PHP, but it is highly probably that there are going to be other
apps accessing the data. There is probably a "right way" to handle all
this in PHP, but I never learnt it. I would have suspected that PHP's
mysql_escape_string (available since PHP 4.0.3) would go a logn way, but
I'm sure I was disappointed. Perhaps I am after mysql_real_escape_string
(since PHP 4.3.0). I also get a bit worried when using binary data --
I'm not sure if I actually want that to be escaped even though I would
*have* to escape it (this is where I get confused).

The other thing I don't like about string manipulation is that it's hard
to do it flexibly (esp. if the SQL syntax requires a column to be cited
in multiple places within the query string). For me, the best way of
constructing queries is to use an abstraction object to put the logic
together and produce the equivalent SQL query. (Sometime I would do this
during runtime, not just as a code-generation exercise!) Since I first
found such a thing in Java, I stuck with Java.

The principal alternative to direct string manipulation is the use of
'prepared statements'. For these, the SQL query string is used only on a
one-off basis to create the 'prepared statement'. Instead of inserting
data directly, parameterisation is used (each parameter is represented
by the placeholder '?'s, as in Ryan's example). This has the advantage
of efficiency. Also, the responsibility for the handling of strings and
binary data is placed with the DB driver and my code can blissfully
ignore it. (You need to poke values into your query *after* preparing
it. I suppose this sounds messy if you are only using a query once in
your entire application, but I like the "safety" of it.)

I like to avoid using SQL queries in general, though I would usually do
prototyping using them (because of the directness of it, and because it
makes it easy to tweak things and discover that I forgot some index or
other :). After that, I can be confident whether the abstraction layer
is producing good queries.