Re: Weird SQL Problem, Heredoc + Bind Parameters Solution + Questions
От | |
---|---|
Тема | Re: Weird SQL Problem, Heredoc + Bind Parameters Solution + Questions |
Дата | |
Msg-id | 20050912211640.1980.qmail@web33312.mail.mud.yahoo.com обсуждение исходный текст |
Список | pgsql-novice |
i thought i posted this to the group, but i just posted to myself... -lol- --- operationsengineer1@yahoo.com wrote: > --- operationsengineer1@yahoo.com wrote: > > > --- Michael Glaesemann <grzm@myrealbox.com> wrote: > > > > > On Sep 8, 2005, at 8:48 AM, > > > <operationsengineer1@yahoo.com> > > > <operationsengineer1@yahoo.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 по дате отправления: