Re: Double Quotes In Queries

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; } 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. Peter