Re: Double Quotes In Queries

On 4/27/05, pw <p.willis@telus.net> wrote: > Justin Patrin wrote: > > On 4/27/05, pw <p.willis@telus.net> wrote: > > > >>Justin Patrin wrote: > >> > >>>On 4/27/05, pw <p.willis@telus.net> wrote: > >>> > >>> > >>>>Hello, > >>>> > >>>>I have pearDB connected to PostgreSQL. > >>>> > >>>>When I make an insert into a varchar field everything works fine, > >>>>**UNLESS** I have double quotes in my data for that field. > >>>> > >>>>For example if I use the following INSERT directly in postgres it works > >>>>fine: > >>>> > >>>>INSERT INTO mytable (a_text_field) VALUES ( 'one 19" monitor' ); > >>>> > >>>>However if I use pear and PHP to insert this record the data gets > >>>>truncated to > >>>> > >>>>'one 19' > >>>> > >>>>Even adding a backslash as an escape character doesn't resolve the > >>>>problem. > >>>>ie:INSERT INTO mytable (a_text_field) VALUES ( 'one 19\" > >>>>monitor' ); > >>>> > >>>>I think I must be missing something in my php.ini or something. > >>>>Is there a flag for this? > >>>> > >>> > >>> > >>>Try using DB::quoteSmart(). > >>> > >> > >>Hello, > >> > >>Thanks for your help. > >>quoteSmart doesn't appear to fix the problem. > >> > >>Do I run the complete query string through quoteSmart > >>or just the field data? > >> > >>So far I've just run the field data through which adds > >>a slash in front of the double quote. (just like my previous experiments) > >> > > > > > > Just field data. Read the manual page for that function. > > > > Could you post the code where you create your query? > > > > Hello, > > Here's the code: > > /*BEGIN*/ > > $field="some_column_name"; > $table="some_table_name"; > $avalue="hello this character (\") is a double quote"; > $rval=12; > > if($quotes==0) > { > /*numerical field*/ > $update_sql="UPDATE $table SET ".$field."=".$avalue." WHERE > ident="..$rval; > } > else > { > /*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; > } > 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. Try hacking into DB's pgsql driver and outputting the query just before it sends it to the DB. Also, output $db->quoteSmart($avalue) and see if it's quoted correctly. -- Justin Patrin