Re: Double Quotes In Queries

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). Peter