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