Re: Double Quotes In Queries

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. -- Justin Patrin