Re: Double Quotes In Queries

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