Re: Double Quotes In Queries

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