Re: Double Quotes In Queries

On 4/28/05, pw <p.willis@telus.net> wrote: > Justin Patrin wrote: > > On 4/28/05, pw <p.willis@telus.net> wrote: > > > >>Justin Patrin wrote: > >> > >>>On 4/28/05, pw <p.willis@telus.net> wrote: > >>> > >>> > >>>>Justin Patrin wrote: > >>>> > >>>> > >>>>>On 4/27/05, pw <p.willis@telus.net> wrote: > >>>>> > >>>>> > >>>>> > >>>>>>Justin Patrin wrote: > >>>>>> > >>>>>> > >>>>>> > >>>>>>>>/*text field*/ > >>>>>>>>$avalue=$db->quoteSmart($avalue); > >>>>>>>>$update_sql="UPDATE $table SET > >>>>>>>>".$field."='".$avalue."' WHERE ident=".$rval; > >>>>>>> > >>>>>>> > >>>>>>>You're doubling the quotes here. quoteSmart adds quotes for > >>>>>>>you (but > >>>>>>>only if they're needed). > >>>>>>>$update_sql='UPDATE $table SET > >>>>>>>'.$field.'='.$db->quoteSmart($avalue).' > >>>>>>>WHERE ident='.$rval; > >>>>>>> > >>>>>>> > >>>>>> > >>>>>>I tried. It doesn't work. > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>>>>} > >>>>>>>>echo "$update_sql <BR>\n"; > >>>>>>>>$response=$db->query($update_sql); > >>>>>>>>$db->commit(); > >>>>>>>> > >>>>>>>>/*END*/ > >>>>>>>> > >>>>>>>>It's not a value problem. The update works > >>>>>>>>with any string that doesn't contain quotes. > >>>>>>>>It's been tested and the generated INSERT > >>>>>>>>strings have been tested directly on the psql > >>>>>>>>command line without pear or PHP. > >>>>>>>>So, I know the SQL string syntax is valid. > >>>>>>>>Even if it weren't valid, I would expect to get > >>>>>>>>an error regarding the quotes rather that a > >>>>>>>>truncated field, which I don't. > >>>>>>>> > >>>>>>>>See the Example below. > >>>>>>>> > >>>>>>>>/*TRY WITH PEAR AND PHP*/ > >>>>>>>> > >>>>>>>>--Results From PearDB-- > >>>>>>>> > >>>>>>>>field value before update: 'test record' > >>>>>>>> > >>>>>>>>SQL insert statement: > >>>>>>>>UPDATE tbleqpt SET item='big 19 \" monitor' > >>>>>>>>WHERE rec_id=350 > >>>>>>>> > >>>>>>>>field value after update: 'big 19 ' > >>>>>>>> > >>>>>>>>/*NOW TRY WITH POSTGRESQL*/ > >>>>>>>> > >>>>>>>>--Results From PostgreSQL-- > >>>>>>>> > >>>>>>>>[postgres@DATA]$ psql office > >>>>>>>>Welcome to psql 7.3.4, the PostgreSQL interactive terminal. > >>>>>>>> > >>>>>>>>Type: \copyright for distribution terms > >>>>>>>> \h for help with SQL commands > >>>>>>>> \? for help on internal slash commands > >>>>>>>> \g or terminate with semicolon to execute query > >>>>>>>> \q to quit > >>>>>>>> > >>>>>>>>officedb=# SELECT item FROM tbleqpt WHERE rec_id=350; > >>>>>>>>item > >>>>>>>>--------- > >>>>>>>>big 19 > >>>>>>>>(1 row) > >>>>>>>> > >>>>>>>>officedb=# UPDATE tbleqpt SET item='big 19 \" > >>>>>>>>monitor' WHERE rec_id=350; > >>>>>>>>UPDATE 1 > >>>>>>>>officedb=# SELECT item FROM tbleqpt WHERE rec_id=350; > >>>>>>>> item > >>>>>>>>------------------ > >>>>>>>>big 19 " monitor > >>>>>>>>(1 row) > >>>>>>>> > >>>>>>>>officedb=# > >>>>>>>> > >>>>>>>>As you can see Pear appears to be doing something different > >>>>>>>>with the > >>>>>>>>update than what PostgreSQL does. > >>>>>>>> > >>>>>>>>If the UPDATE statement was not formatted acceptably the > >>>>>>>>original record wouldn't get updated in the first place. > >>>>>>>>This leads me to believe that the problem lies with the > >>>>>>>>query string handling in Pear::DB and not in the SQL > >>>>>>>>statement or postgreSQL. > >>>>>>>> > >>>>>>> > >>>>>>> > >>>>>>>I don't *believe* DB changes the query. > >>>>>> > >>>>>>I didn't believe it either..... > >>>>>>Did you follow what I did above with both PHP/Pear and > >>>>>>PostgreSQL? > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>>>Try hacking into DB's pgsql driver and outputting the query > >>>>>>>just > >>>>>> > >>>>>>>before it sends it to the DB. > >>>>>> > >>>>>>??Huh? > >>>>>>Any idea what file(s) I should hack through? > >>>>>>To be honest, I have enough on my own hands, > >>>>>>with my own code, without having to fix Pear::DB too...<sigh!> > >>>>>> > >>>>>>O.K., I'll take a look but I can't spend a huge amount of > >>>>>>time debugging this. > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>>>Also, output $db->quoteSmart($avalue) and see if it's > >>>>>>>quoted correctly. > >>>>>>> > >>>>>> > >>>>>>It double slashed the output and still managed to UPDATE the > >>>>>>field with truncated data, stopping again at the double quote. > >>>>>>Pear then updates the correct record with truncated data. > >>>>>> > >>>>>>Let's keep in mind here that if there were a problem > >>>>>>with the quotes, the SQL UPDATE shouldn't be able to > >>>>>>happen at all. Bad SQL = No Data Entry/Update. It will > >>>>>>fail. > >>>>>> > >>>>>>Postgres likes the query as it is laid out without > >>>>>>quoteSmart. Pear::DB is just messing up the data value (string). > >>>>>> > >>>>> > >>>>> > >>>>>Well, it sounds to me like there's something horribly wrong here. > >>>>>Again, I'd like the output of $db->quoteSmart($avalue) > >>>>> > >>>>>It *should* be: > >>>>>'hello this character (") is a double quote' > >>>>> > >>>>>According to my quick look. No backslashes. If there was a single > >>>>>quote in there it should get replaced with 2 single quotes: ''. > >>>>> > >>>>>Try: > >>>>>echo $avalue; > >>>>> > >>>>>Does that have backslashes? Do you perhaps have magic_quotes_runtime on? > >>>>> > >>>>>Try running the query with PEAR::DB with 'hello this character > >>>>>(") is > >>>>>a double quote' as the value (no quoteSmart, no variables). > >>>>> > >>>> > >>>>I just compiled PHP clean and installed again. > >>>>All 'magic-quotes' stuff is disabled in php.ini. > >>>> > >>>>This cleans up any additonal backslashes. > >>>>The UPDATE and INSERT still have the same > >>>>problem. > >>>> > >>>>I have previously tried all the things you suggest. > >>>>I am thinking I should revert to an earlier version of > >>>>PHP and or Pear to see if the problem is with this > >>>>version. > >>>> > >>>>PHP 4.3.9 (cli) (built: Apr 28 2005 09:46:30) > >>>>Copyright (c) 1997-2004 The PHP Group > >>>>Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies > >>>> > >>>>If that's the case it may be easier to diff the pgsql > >>>>module to see what's changed. > >>>> > >>>>This presupposes that this problem hasn't been there > >>>>all along. Personally, this is the first time I've > >>>>attempted to enter a pair of double quotes, as a field > >>>>value, using Pear and PHP. > >>>> > >>>>I wonder if anyone else is using postgresql with pear > >>>>and if they could test/comment on this. > >>>> > >>> > >>> > >>>Well I can't help you any more as you're not giving me the data I need > >>>to try to debug your problem. You keep saying you've tried it, but I'd > >>>like to see the output myself. > >>> > >> > >>Sorry, I thought I gave you that: > >> > >>Echoed from PHP: (ie: echo "avalue = $avalue\n";) > >> > >>avalue = value going in with "quotes" > >> > >>Echoed SQL statement from PHP: > >> > >>UPDATE tbleqpt SET item='value going in with "quotes"' WHERE > >>rec_id=350 > >> > >>The resulting record in PostgreSQL has the following value in the 'item' > >>field: > >> > >>value going in with > >> > >> If it were a problem with SQL punctutation PostgreSQL wouldn't > >>allow the record to be entered/updated at all. > >> > > > > > > Ok, one last thing to try. Open up DB/pgsql.php (in your PEAR dir) and put this: > > > > echo $query; > > > > before line 335 in simpleQuery(): > > $result = @pg_exec($this->connection, $query); > > > > What does that query say? If that one is correct, then it looks like > > it's a problem with the pgsql extension. > > > > Echoed from My PHP program: > > UPDATE tbleqpt SET item='value going in with "quotes" again' WHERE > rec_id=350 > > Echoed from pgsql.php: (ie: echo $query; ) > > UPDATE tbleqpt SET item='value going in with "quotes" again' WHERE > rec_id=350 > Then it's definately something wrong with the pgsql extension and/or the client library it's using. Try a different build of PHP. -- Justin Patrin