Skip site navigation (1) Skip section navigation (2)

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

From: <operationsengineer1(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Weird SQL Problem, Heredoc + Bind Parameters Solution + Questions
Date: 2005-09-12 21:16:40
Message-ID: 20050912211640.1980.qmail@web33312.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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 

pgsql-novice by date

Next:From: operationsengineer1Date: 2005-09-12 23:46:18
Subject: psql, pg_dumpall, remote server questions...
Previous:From: Obe, Regina DND\MISDate: 2005-09-12 12:24:24
Subject: Re: Weird SQL Problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group