Re: Weird SQL Problem, Heredoc + Bind Parameters Solution + Questions

i thought i posted this to the group, but i just
posted to myself... -lol-

--- operationsengineer1(at)yahoo(dot)com wrote:

> --- operationsengineer1(at)yahoo(dot)com wrote:
>
> > --- Michael Glaesemann <grzm(at)myrealbox(dot)com> wrote:
> >
> > > On Sep 8, 2005, at 8:48 AM,
> > > <operationsengineer1(at)yahoo(dot)com>
> > > <operationsengineer1(at)yahoo(dot)com> wrote:
> > >
> > > > i use adodb and the code is as follows (the
> sql
> > > should
> > > > be pretty evident - even if you don't follow
> > > adodb).
> > > >
> > > > $result_update = $db->Execute('UPDATE t_defect
> > SET
> > > > reworker_id = ' . $reworker_id . ',
> > > rework_completed =
> > > > \'t\', rework_notes = ' . $rework_notes . ',
> > > > rework_date = '. $db->DBDate(time()) . 'WHERE
> > > > t_defect.defect_id = ' . $defect_id );
> > > >
> > > > my problem is this... the text input for
> > > > $rework_notes kicks out a pgsql error as
> > > follows...
> > > >
> > > > 'ERROR: column "aaaaaaaaaaaaaa" does not
> exist'
> > >
> > > I know this is probably more than what you're
> > asking
> > > for, but there
> > > may be a few things you can do to make it easier
> > for
> > > you to catch and
> > > fix these types of errors. Here's another way to
> > > write code that I
> > > think will do what you want, and may perhaps be
> > > easier to maintain.
> > >
> > > This assumes $reworker_id, and $defect_id are
> > > numeric.
> > >
> > > I haven't used the ADODB DBDate construct much,
> > but
> > > if I'm reading
> > > the docs and your code correctly, you want to
> > insert
> > > the current
> > > timestamp. If this is the case, you can also use
> > the
> > > SQL-standard
> > > CURRENT_TIMESTAMP, which will do the same thing
> > and
> > > is easier to
> > > read. If rework_date is actually a date column,
> > > PostgreSQL will do
> > > the right thing and truncate the timestamp to
> > date.
> > > Or, of course,
> > > you could use CURRENT_DATE as well. In this
> case,
> > > you wouldn't need
> > > the $rework_date variable. If you want to
> > construct
> > > the a different
> > > date or timestamp, I'd still abstract out a
> > variable
> > > to make it
> > > easier to use the heredoc method (the <<<
> stuff).
> > > Heredocs can be
> > > much easier to maintain and read than
> > concatenating
> > > a string. You can
> > > think of a heredoc as just a big "" string,
> which
> > > means it does
> > > variable interpolation.
> > >
> > > $rework_date = $db->DBDate(time()); // currently
> > > unused
> > >
> > > /*
> > > Is rework_completed a boolean column? If so, you
> > may
> > > as well just
> > > write out 'true', which is easier to read, and
> you
> > > don't need to
> > > escape it. I've made this assumption in
> rewriting
> > > the query.
> > >
> > > The ADODB library has a handy qstr() method that
> > > handles string
> > > quoting for you.
> > > */
> > >
> > > $rework_notes = $db->qstr($rework_notes);
> > >
> > > $sql = <<<_EOSQL
> > > UPDATE t_defect
> > > SET reworker_id = $reworker_id
> > > , rework_completed = true
> > > , rework_notes = $rework_notes
> > > , rework_date = CURRENT_TIMESTAMP
> > > WHERE t_defect.defect_id = $defect_id
> > > _EOSQL;
> > >
> > > $result_update = $db->Execute($sql);
> > >
> > > As I said, much more than what you asked for.
> But
> > it
> > > should fix the
> > > quoting problem. :)
> > >
> > >
> > > Michael Glaesemann
> > > grzm myrealbox com
> >
> > Michael,
> >
> > i'm trying apply your technique along with the
> bind
> > variables technique.
> >
> > old method (works):
> >
> > $result = $db->Execute("INSERT INTO t_customer
> > (customer_name, customer_entry_date) VALUES
> (?,?)",
> > array($db->qstr($customer_name),
> > $db->DBDate(time())));
> >
> > new method (yields blank white screen with no
> error
> > messages, db or otherwise):
> >
> > $sql_insert = <<<_EOSQL
> >
> > INSERT INTO t_customer (customer_name,
> > customer_entry_date)
> > VALUES (?,?)
> >
> > _EOSQL;
> >
> > $result = $db->Execute($sql_insert,
> > array($customer_name, CURRENT_TIMESTAMP));
> >
> > does using a heredoc preclude me from being able
> to
> > bind variables to parameters or have i found
> another
> > novel (to me, anyway!) way to jack my code up?
> >
> > tia...
>
> the 'wsof' (white screen of death) was my fault...
> i
> tried to use a $db method before i instantiated it
> (hey, i think my temrinology is right! -lol-).
>
> anyway, this code works...
>
> --------------
> $customer_name = $_POST['customer_name'];
>
> $sql_insert = <<<_EOSQL
>
> INSERT INTO t_customer (customer_name,
> customer_entry_date) VALUES (?,?)
>
> _EOSQL;
>
> $result = $db->Execute($sql_insert,
> array($customer_name, $db->DBDate(time())));
> --------------
>
> if i use $db->qstr() on $customer_name, then quotes
> get added and input into the db.
>
> note: CURRENT_TIMESTAMP would not work in place of
> DBDate(time()) in this specific case - probably b/c
> the adodb class doesn't recognize it.
>
> is the above code safe from sql injection and other
> type of attacks?
>
> should i use regex to exclude the character ';' (no
> quotes) from being input or is this deemed an
> unrealistic constraint on my users?
>
> tia...
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com
>
=== message truncated ===

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com