PHP/Mysql/special characters problem

[Mosher]

Hi all,

I have an issue with php and/or mysql. I have a php form that writes "items"
to a mysql database, including a description of the item. On the mysql
server, "magic_quotes_g pc" is ON.

I am testing it now by putting special characters in the description field,
this is what I am entering:

O'Leary "special edition"

Now, this item data always gets written to the db just fine and shows up in
the db as entered. Seems correct and working just fine up to this point.

My problem is with my "edit item" page. This page allows users to update
items, including the mentioned "descriptio n" field. But when the data is
called back up from the db to display in the "edit item" page and the
description contains double quotes, the description is cut off, and only
shows:

O'Leary

Here is the code (snippet of the important stuff and numbered) on the "edit
item" page:

1. $result = mysql_query("se lect * from inven where product =
'$product'");
2. $row = mysql_fetch_arr ay($result);
3. echo "Descriptio n is: $row[description]";
4. echo "<table width=80% border=1 cellpadding=4 cellspacing=0>" ;
5. ?>
6. <tr><td>Produ ct #:</td><td><input type=text name=product value="<?echo
$row[product]?>"></td></tr>
7. <tr><td>Descrip tion:</td><td><input type=text name=descriptio n
value="<?echo $row[description]?>" size=50></td></tr>
8.
9. <?
10. echo "</table>";
11. echo "<br><br><i nput type=submit name=Update value=Update>";
12. ?>

The important line of code here is line 6, where the value of description
should show. The real value of description that is in the database should be
showing up here, but it is cut off if it contains double quotes. Note also
that the full value (double quotes and all) of description can be seen in
the echo statement at line 3. I'm stumped.

To sum up this problem, data appears to get written to the db just fine. The
"edit item" page is brought up, but the description - if it contains special
characters, is cut off, apparently where there are double quotes. If I go
ahead and update the item, the new value in the db is now cut off and not
what I want.

Any ideas? Thanks in advance.

Mosher

[Pedro Graca]

Re: PHP/Mysql/special characters problem

Mosher wrote:[color=blue]
> 6. <tr><td>Produ ct #:</td><td><input type=text name=product value="<?echo
> $row[product]?>"></td></tr>[/color]
[color=blue]
> The important line of code here is line 6, where the value of description
> should show. The real value of description that is in the database should be
> showing up here, but it is cut off if it contains double quotes. Note also
> that the full value (double quotes and all) of description can be seen in
> the echo statement at line 3. I'm stumped.[/color]

The quotes are there :)
view the source!

This is a HTML problem: you're trying to output HTML similar to
<input value="John "Q" Smith">
and the browser doesn't know how to interpret it

Try html_entities()
<form ...>
<!-- ... -->

<input value="<?php echo html_entites($r ow['product'], ENT_QUOTES); ?>"/>

<!-- ... -->
</form>

http://www.php.net/html_entites

--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Pedro Graca]

Re: PHP/Mysql/special characters problem

I (Pedro Graca) mis-wrote:[color=blue]
> http://www.php.net/html_entites[/color]

Sorry, that should have been

http://www.php.net/html_entities

--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Mosher]

Re: PHP/Mysql/special characters problem

Pedro - thanks so much! That helped and I can now view the full double
quoted data.

However, when I try to "update" the information into the db, the
single/double quoted stuff doesn't get written to the db. I tried an
html_entity_dec ode function, but that only writes single quotes to the db,
not double. Here is my decode code that I put in the same code that you
commented on previously:

<input type=hidden name=descriptio n value="<?echo
html_entity_dec ode($row[description])?>">

It's my understanding that we need to decode the html_entities data before
writing to db, right? Any ideas how I could get the whole string, single and
double quotes included, written into the db?

Thanks again,

Mosher

"Pedro Graca" <hexkid@hotpop. com> wrote in message
news:bsvbqm$1s0 1b$1@ID-203069.news.uni-berlin.de...[color=blue]
> Mosher wrote:[color=green]
> > 6. <tr><td>Produ ct #:</td><td><input type=text name=product[/color][/color]
value="<?echo[color=blue][color=green]
> > $row[product]?>"></td></tr>[/color]
>[color=green]
> > The important line of code here is line 6, where the value of[/color][/color]
description[color=blue][color=green]
> > should show. The real value of description that is in the database[/color][/color]
should be[color=blue][color=green]
> > showing up here, but it is cut off if it contains double quotes. Note[/color][/color]
also[color=blue][color=green]
> > that the full value (double quotes and all) of description can be seen[/color][/color]
in[color=blue][color=green]
> > the echo statement at line 3. I'm stumped.[/color]
>
> The quotes are there :)
> view the source!
>
> This is a HTML problem: you're trying to output HTML similar to
> <input value="John "Q" Smith">
> and the browser doesn't know how to interpret it
>
> Try html_entities()
> <form ...>
> <!-- ... -->
>
> <input value="<?php echo html_entites($r ow['product'], ENT_QUOTES); ?>"/>
>
> <!-- ... -->
> </form>
>
>
> http://www.php.net/html_entites
> --
> --= my mail box only accepts =--
> --= Content-Type: text/plain =--
> --= Size below 10001 bytes =--[/color]

[Agelmar]

Re: PHP/Mysql/special characters problem

Mosher wrote:[color=blue]
> Pedro - thanks so much! That helped and I can now view the full double
> quoted data.
>
> However, when I try to "update" the information into the db, the
> single/double quoted stuff doesn't get written to the db. I tried an
> html_entity_dec ode function, but that only writes single quotes to
> the db, not double. Here is my decode code that I put in the same
> code that you commented on previously:
>
> <input type=hidden name=descriptio n value="<?echo
> html_entity_dec ode($row[description])?>">
>
> It's my understanding that we need to decode the html_entities data
> before writing to db, right? Any ideas how I could get the whole
> string, single and double quotes included, written into the db?
>
> Thanks again,
>
> Mosher[/color]

NO... you do not need to decode anything before flushing it to the db. All
you need to do is shove it in (being sure to call something like
mysql_escape_st ring first, obviously)

[Pedro Graca]

Re: PHP/Mysql/special characters problem

Mosher wrote:[color=blue]
> It's my understanding that we need to decode the html_entities data before
> writing to db, right? Any ideas how I could get the whole string, single and
> double quotes included, written into the db?[/color]

From DB to browser
htmlentities()

From browser to DB
mysql_escape_st ring()

But beware of magic quotes (I don't have them on)
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Agelmar]

Re: PHP/Mysql/special characters problem

Pedro Graca wrote:[color=blue]
> Mosher wrote:[color=green]
>> It's my understanding that we need to decode the html_entities data
>> before writing to db, right? Any ideas how I could get the whole
>> string, single and double quotes included, written into the db?[/color]
>
> From DB to browser
> htmlentities()
>
> From browser to DB
> mysql_escape_st ring()
>
> But beware of magic quotes (I don't have them on)[/color]

you will likely want to do something like
$string = get_magic_quote s_gpc() ?
mysql_escape_st ring(stripslash es($string)) : mysql_escape_st ring($string);

[Mosher]

Re: PHP/Mysql/special characters problem

Guys,

I tried to use the mysql_escape_st ring($descripti on), but it didn't work.
When I enter this string from the description field:

O'Leary "special"

....it sends this to the db:

O\\\'Leary \\

....and because magic quotes in on, this is what actually got written to the
db:

O\'Leary \

Also, any information that comes after the description field has now
dissapeared. I am in "special character" hell! Help!!! Remember that
magic_quotes_gp c is 'ON'.

Thanks,

Mosher



"Agelmar" <ifetteNOSPAM@c omcast.net> wrote in message
news:bt1v7e$2gp ck$1@ID-30799.news.uni-berlin.de...[color=blue]
> Pedro Graca wrote:[color=green]
> > Mosher wrote:[color=darkred]
> >> It's my understanding that we need to decode the html_entities data
> >> before writing to db, right? Any ideas how I could get the whole
> >> string, single and double quotes included, written into the db?[/color]
> >
> > From DB to browser
> > htmlentities()
> >
> > From browser to DB
> > mysql_escape_st ring()
> >
> > But beware of magic quotes (I don't have them on)[/color]
>
> you will likely want to do something like
> $string = get_magic_quote s_gpc() ?
> mysql_escape_st ring(stripslash es($string)) : mysql_escape_st ring($string);
>
>[/color]

[Ken Robinson]

Re: PHP/Mysql/special characters problem

"Mosher" <mosh_king2000@ yahoo.com> wrote in
news:fY6dnclyS5 FIf2ii4p2dnA@co mcast.com (in part):
[color=blue]
> Guys,
>
> I tried to use the mysql_escape_st ring($descripti on), but it didn't
> work. When I enter this string from the description field:
>
> O'Leary "special"
>
> ...it sends this to the db:
>[/color]

I've come in late, but you may want to try:

From the form to DB: urlencode(strip slashes($string ))
From the DB to the display: urldecode($db_s tring)

Ken Robinson

[Mosher]

Re: PHP/Mysql/special characters problem

Hi Ken,

Thanks for this, but it did not work. Once again (prior to your code), the
data gets written to the db just fine, quotes and all. But when I call it
back up to edit it, that is where the problem is. First, the field data:

O'Leary "special"

....only displayed O'Leary when called back up. I then was able to get around
that by the following line of code:

<input type=text name=descriptio n value="<? echo
htmlentities($r ow[description])?>">

This did display the full data above with quotes, etc. But when I look in
the actual source code of the webpage being displayed, it shows:

O'Leary&quot;sp ecial&quot;

....in the field and when I try to write to db, it only writes:

O'Leary

Any other ideas?

Thanks,

Mosher

"Ken Robinson" <sendspamhere@r bnsn.com> wrote in message
news:4588c0d0f3 7219fe9a4f67582 e107916@news.te ranews.com...[color=blue]
> "Mosher" <mosh_king2000@ yahoo.com> wrote in
> news:fY6dnclyS5 FIf2ii4p2dnA@co mcast.com (in part):
>[color=green]
> > Guys,
> >
> > I tried to use the mysql_escape_st ring($descripti on), but it didn't
> > work. When I enter this string from the description field:
> >
> > O'Leary "special"
> >
> > ...it sends this to the db:
> >[/color]
>
> I've come in late, but you may want to try:
>
> From the form to DB: urlencode(strip slashes($string ))
> From the DB to the display: urldecode($db_s tring)
>
> Ken Robinson[/color]

[Ken Robinson]

Re: PHP/Mysql/special characters problem

"Mosher" <mosh_king2000@ yahoo.com> wrote in
news:9NqdnaqolK xu6mSi4p2dnA@co mcast.com:
[color=blue]
> Hi Ken,
>
> Thanks for this, but it did not work. Once again (prior to your code),
> the data gets written to the db just fine, quotes and all. But when I
> call it back up to edit it, that is where the problem is. First, the
> field data:
>
> O'Leary "special"
>
> ...only displayed O'Leary when called back up. I then was able to get
> around that by the following line of code:
>
> <input type=text name=descriptio n value="<? echo
> htmlentities($r ow[description])?>">
>
> This did display the full data above with quotes, etc. But when I look
> in the actual source code of the webpage being displayed, it shows:
>
> O'Leary&quot;sp ecial&quot;
>
> ...in the field and when I try to write to db, it only writes:
>[/color]

I've had similar problems. It's in the DB with the quotes and I used to
run around in circles trying to get it back in after displaying it on a
form and getting the value back. That's why I've started to store the
urlencoded format in the database.

So you would do:
<input type=text name=descriptio n value="<? echo urldecode($row
[description])?>">
in your form.

In your database update command use something like: "update .... set
description='". urlencode(strip slashes($_POST['description']))."'..."

You might have to write a one-time job to update all the text fields in
your database to conform to the new method or you can just implement it
and each field will be updated as time goes on.

Ken

[steven mestdagh]

Re: PHP/Mysql/special characters problem

> This did display the full data above with quotes, etc. But when I look in[color=blue]
> the actual source code of the webpage being displayed, it shows:
>
> O'Leary&quot;sp ecial&quot;
>
> ...in the field and when I try to write to db, it only writes:
>
> O'Leary
>
> Any other ideas?[/color]

after form submission, i use
$_POST['var'] = mysql_escape_st ring(stripslash es($_POST['var']));
before submitting $_POST['var'] to my database.

maybe you can also echo $_POST['var'] after you submit the form to check
what is in it.

if you use the GET method, the &quot; entity may cause problems as all
variables are transmitted like

http://php.net/script.php?var1=foo&var2=bar&var3=a_&quot;problem&quot;

if this is the case, maybe just try the POST method instead?

good luck
steven.

[Mosher]

Re: PHP/Mysql/special characters problem

Ken - the whole thing works now! But the problem was caused by another
issue. I believe that there was another query writing the data to db (so it
was getting written twice) and due to this, there was the problem - one
query was correct and the other wasn't. I removed the latter query and now
it is working. It had me totally stumped because this hasn't been too much
of an issue for me before.

Anyway, thanks much for the advice. Perhaps I'll try your ideas next time.

Later,

Mosher

"Ken Robinson" <sendspamhere@r bnsn.com> wrote in message
news:70499a4b13 da5b8d11ff30ca4 36b1de6@news.te ranews.com...[color=blue]
> "Mosher" <mosh_king2000@ yahoo.com> wrote in
> news:9NqdnaqolK xu6mSi4p2dnA@co mcast.com:
>[color=green]
> > Hi Ken,
> >
> > Thanks for this, but it did not work. Once again (prior to your code),
> > the data gets written to the db just fine, quotes and all. But when I
> > call it back up to edit it, that is where the problem is. First, the
> > field data:
> >
> > O'Leary "special"
> >
> > ...only displayed O'Leary when called back up. I then was able to get
> > around that by the following line of code:
> >
> > <input type=text name=descriptio n value="<? echo
> > htmlentities($r ow[description])?>">
> >
> > This did display the full data above with quotes, etc. But when I look
> > in the actual source code of the webpage being displayed, it shows:
> >
> > O'Leary&quot;sp ecial&quot;
> >
> > ...in the field and when I try to write to db, it only writes:
> >[/color]
>
> I've had similar problems. It's in the DB with the quotes and I used to
> run around in circles trying to get it back in after displaying it on a
> form and getting the value back. That's why I've started to store the
> urlencoded format in the database.
>
> So you would do:
> <input type=text name=descriptio n value="<? echo urldecode($row
> [description])?>">
> in your form.
>
> In your database update command use something like: "update .... set
> description='". urlencode(strip slashes($_POST['description']))."'..."
>
> You might have to write a one-time job to update all the text fields in
> your database to conform to the new method or you can just implement it
> and each field will be updated as time goes on.
>
> Ken[/color]