Trouble escaping / Misc nightmare

[Ian Rastall]

Sorry for the double question. I'm having a terrible time figuring out
how to escape apostrophes in my mySQL database. Perhaps they have to
be escaped in the PHP, using mysql_real_esca pe_string?

This is the code:

http://www.gongfamily.net/code.txt

The page in question is:

http://www.gongfamily.net/daevid.php

the entry being N'existe Pas. Information about the album should show
up on the left when the title is clicked on, but it doesn't happen
whenever there's an apostrophe. This is an escaping problem, I'm sure,
but the real problem is, I'm over my head!

The second problem is on the same page. You can click a table header
to sort the table, but when I click the 'Group' header, I get the
error:

"You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'Group ASC' at line 1"

But I'm lost as to what to do here. I've poured over the code, despite
not being very well-versed in PHP. Again, I'm over my head, and would
truly appreciate any help.

TIA

Ian
--
Was it not a comedy, a strange and stupid
matter, this repetition, this running around
in a fateful circle? (Hermann Hesse)

http://www.bookstacks.org/

[saint exupery]

Re: Trouble escaping / Misc nightmare

'Group' is an invalid name for a column! (mysql get confused with the
"GROUP BY" clause).

[Ian Rastall]

Re: Trouble escaping / Misc nightmare

On 28 Jun 2005 12:32:57 -0700, "saint exupery"
<cristian.melen dez@gmail.com> wrote:
[color=blue]
>'Group' is an invalid name for a column! (mysql get confused with the
>"GROUP BY" clause).[/color]

LOL. Thank you so much. That would probably not have occurred to me in
a hundred years! :-)

Ian
--
Was it not a comedy, a strange and stupid
matter, this repetition, this running around
in a fateful circle? (Hermann Hesse)

http://www.bookstacks.org/

[Malcolm Dew-Jones]

Re: Trouble escaping / Misc nightmare

Ian Rastall (idrastall@gmai l.com) wrote:
: On 28 Jun 2005 12:32:57 -0700, "saint exupery"
: <cristian.melen dez@gmail.com> wrote:

: >'Group' is an invalid name for a column! (mysql get confused with the
: >"GROUP BY" clause).

: LOL. Thank you so much. That would probably not have occurred to me in
: a hundred years! :-)

Apparently mysql can escape keywords using `keyword`.

However, avoiding keywords is the best bet. (I like things like
the_group )


--

This space not for rent.

[Ian Rastall]

Re: Trouble escaping / Misc nightmare

On 28 Jun 2005 12:54:41 -0800, yf110@vtn1.vict oria.tc.ca (Malcolm
Dew-Jones) wrote:
[color=blue]
>However, avoiding keywords is the best bet. (I like things like
>the_group )[/color]

I just changed it to "Band". Same difference. :-) What I'm still
having trouble with, though, is figuring out how to escape my
apostrophes. It must just be one line of code in my HTML somewhere,
but I'm not sure where to put it.

The apostrophe problem only seems to happen with album names, since
those are the ones you click on. It takes whatever the name is, tries
to grab that from the db, and can't, because ... not sure, but I think
it's because the apostrophe is interpreted as a command of some sort.

Ian
--
Was it not a comedy, a strange and stupid
matter, this repetition, this running around
in a fateful circle? (Hermann Hesse)

http://www.bookstacks.org/

[Malcolm Dew-Jones]

Re: Trouble escaping / Misc nightmare

Ian Rastall (idrastall@gmai l.com) wrote:
: On 28 Jun 2005 12:54:41 -0800, yf110@vtn1.vict oria.tc.ca (Malcolm
: Dew-Jones) wrote:

: >However, avoiding keywords is the best bet. (I like things like
: >the_group )

: I just changed it to "Band". Same difference. :-) What I'm still
: having trouble with, though, is figuring out how to escape my
: apostrophes. It must just be one line of code in my HTML somewhere,
: but I'm not sure where to put it.

: The apostrophe problem only seems to happen with album names, since
: those are the ones you click on. It takes whatever the name is, tries
: to grab that from the db, and can't, because ... not sure, but I think
: it's because the apostrophe is interpreted as a command of some sort.

E.g.

$id = mysql_escape_st ring($_REQUEST[id]);

$sql = "select * from the_table where ID='$id'";


Always escape your values before stuffing them into the sql string, (not
just when you think you might need it).


--

This space not for rent.

[Ian Rastall]

Re: Trouble escaping / Misc nightmare

On 28 Jun 2005 13:37:06 -0800, yf110@vtn1.vict oria.tc.ca (Malcolm
Dew-Jones) wrote:
[color=blue]
> $id = mysql_escape_st ring($_REQUEST[id]);
>
> $sql = "select * from the_table where ID='$id'";[/color]

Okay, I think I'm making progress. I changed:

<td><a href="<?php print
$_SERVER['PHP_SELF']."?album=".$row _rsDaevid['Album'];
?>">

to

<td><a href="<?php print
$_SERVER['PHP_SELF']."?album=".mysq l_escape_string ($row_rsDaevid['Album']);
?>">

which yields an escaped string in the final source code. The page
still doesn't work, though, meaning clicking on the album doesn't pop
up the info on the left. I think it's looking for "N\'existe Pas" in
the db and not finding it.

I looked up the PHP equivalent of "unescape", and found "urldecode" ,
which I tried to use at the point where it calls the album, at:

<h3><?php echo urldecode($row_ rsDaevid['Album']); ?></h3>

but it didn't help. Didn't throw an error, either, but didn't help.
:-)

Am I on the right track here? Thanks for any help. The code, again, is
at:

http://www.gongfamily.net/code.txt

TIA

Ian
--
Was it not a comedy, a strange and stupid
matter, this repetition, this running around
in a fateful circle? (Hermann Hesse)

http://www.bookstacks.org/

[Ken Robinson]

Re: Trouble escaping / Misc nightmare



Malcolm Dew-Jones wrote:[color=blue]
> $id = mysql_escape_st ring($_REQUEST[id]);
>
> $sql = "select * from the_table where ID='$id'";
>
>
> Always escape your values before stuffing them into the sql string, (not
> just when you think you might need it).[/color]

You can also use urlencode($var) or htmlentities($v ar,ENT_QUOTES)
before inserting $var into your database.

Ken

[Malcolm Dew-Jones]

Re: Trouble escaping / Misc nightmare

Ken Robinson (kenrbnsn@rbnsn .com) wrote:


: Malcolm Dew-Jones wrote:
: > $id = mysql_escape_st ring($_REQUEST[id]);
: >
: > $sql = "select * from the_table where ID='$id'";
: >
: >
: > Always escape your values before stuffing them into the sql string, (not
: > just when you think you might need it).

: You can also use urlencode($var) or htmlentities($v ar,ENT_QUOTES)
: before inserting $var into your database.

You can, but you should still use mysql_escape_st ring on the result when
you embed it in an sql query being handled by mysql.


--

This space not for rent.

[Ken Robinson]

Re: Trouble escaping / Misc nightmare



Malcolm Dew-Jones wrote:[color=blue]
> Ken Robinson (kenrbnsn@rbnsn .com) wrote:
> : You can also use urlencode($var) or htmlentities($v ar,ENT_QUOTES)
> : before inserting $var into your database.
>
> You can, but you should still use mysql_escape_st ring on the result when
> you embed it in an sql query being handled by mysql.[/color]

You learn something new all the time. We you retrieve a string that
was stored this way, does MySQL unescape it or is there a function to
do it?

Ken

[Malcolm Dew-Jones]

Re: Trouble escaping / Misc nightmare

Ken Robinson (kenrbnsn@rbnsn .com) wrote:


: Malcolm Dew-Jones wrote:
: > Ken Robinson (kenrbnsn@rbnsn .com) wrote:
: > : You can also use urlencode($var) or htmlentities($v ar,ENT_QUOTES)
: > : before inserting $var into your database.
: >
: > You can, but you should still use mysql_escape_st ring on the result when
: > you embed it in an sql query being handled by mysql.

: You learn something new all the time. We you retrieve a string that
: was stored this way, does MySQL unescape it or is there a function to
: do it?

You do not need to unescape the string when you retrieve it later.

When you use mysql_escape_st ring then it ensures that no unexpected
characters in the value can corrupt the SQL command. This ensures that
the value seen by the database is the correct value, i.e. the original,
unescaped data.

e.g.

$value = "this', 'will mess things up";

$sql1 = "insert into tbl values ('$value')"; # inserts 2 columns!

$esc_value = mysql_escape_st ring($value);

$sql2 = "insert into tbl values ('$esc_value')" ; # this is correct

If you were to now examine the contents of the database then you would see
that the second insert will have inserted a value into just one column,
and the string stored in that column will be

this', 'will mess things up

which is the original contents of the $value variable, i.e. when you
retrieve the value then you do not need to unescape it.

This has nothing to do with escaping it for html of course. If you wish
to use the value later, such as displaying it in a web page, then you
might want to escape it for that task before your use it. If you know you
are only ever going to display the data in a web page then you might wish
to do the html escaping before you save the data so that it is always
displayable in a web page with no additional steps. However, when you
save the html-escaped data in the database then you will still wish to
ensure that it is saved correctly, which is what mysql_escape_st ring is
for.

Often mysql_escape_st ring is not necessary since the data may not have any
' or ; or any other odd characters, but using it for all string values is
a good habit that will save you from unexpected problems in the future
when some one manages to enter some data that is not what you anticipated.

--

This space not for rent.

[Ian Rastall]

Re: Trouble escaping / Misc nightmare

On 28 Jun 2005 19:01:02 -0800, yf110@vtn1.vict oria.tc.ca (Malcolm
Dew-Jones) wrote:
[color=blue]
> $value = "this', 'will mess things up";
>
> $sql1 = "insert into tbl values ('$value')"; # inserts 2 columns!
>
> $esc_value = mysql_escape_st ring($value);
>
> $sql2 = "insert into tbl values ('$esc_value')" ; # this is correct[/color]

Hi Malcolm. From my palty understanding of PHP, :-), it seems as if
you're talking about inserting values into a database. The values I'm
worried about are already in there. I use phpMyAdmin to build my
database, and I can see when I browse the table in question that the
apostrophes aren't causing a problem.

The HTML/PHP prints out the string just fine on the web page
(apostrophe and all). This is a string which can be clicked on, at
which point the browser sends the text of the link back to the
database, asking for a record with that string. It doesn't find it,
because the apostrophe is messing things up somehow. I would guess
this is a query somewhere in my HTML/PHP document, perhaps:

$album=mysql_es cape_string($_G ET['album']);

or

$query_rsDaevid = "SELECT * FROM daevid ORDER BY $sort_order ASC";

Nothing seems to help, though, and this is probably a simple thing (to
someone else, at least). :-) Sorry if I'm taking up too much space on
the ng. Just developing a real headache over this.

Ian
--
Was it not a comedy, a strange and stupid
matter, this repetition, this running around
in a fateful circle? (Hermann Hesse)

http://www.bookstacks.org/

[Marcin Dobrucki]

Re: Trouble escaping / Misc nightmare

Ian Rastall wrote:[color=blue]
> On 28 Jun 2005 12:32:57 -0700, "saint exupery"
> <cristian.melen dez@gmail.com> wrote:
>[color=green]
>>'Group' is an invalid name for a column! (mysql get confused with the
>>"GROUP BY" clause).[/color]
>
> LOL. Thank you so much. That would probably not have occurred to me in
> a hundred years! :-)[/color]

When in doubt what my php/mysql combo is trying to do, I restart
mysql with "--log=/tmp/dumplog" option, and then can see what exactly is
passed to mysql from the script. After that, I can fire away the mysql
client, and copy/paste queries, and investigate at leisure.

/m

[saint exupery]

Re: Trouble escaping / Misc nightmare

you cant grab it cuz the apostrophe is escaped on the db.

you MUST escape the string before adding it to the db using
addslashes():
mysql_query("IN SERT INTO `blabla` (`a`,`b`) VALUES ('" .
addslashes($var 1) . "','" . addslashes($var 2) . "')");

use stripslashes() when outputting to html:
echo stripslashes($r esult['b'])

u should be fine with that. in theory, you should never urlencode()
when outputting or urldecode() when inputting (it`s the browser`s job
to do that)

[Malcolm Dew-Jones]

Re: Trouble escaping / Misc nightmare

Ian Rastall (idrastall@gmai l.com) wrote:
: On 28 Jun 2005 19:01:02 -0800, yf110@vtn1.vict oria.tc.ca (Malcolm
: Dew-Jones) wrote:

: > $value = "this', 'will mess things up";
: >
: > $sql1 = "insert into tbl values ('$value')"; # inserts 2 columns!
: >
: > $esc_value = mysql_escape_st ring($value);
: >
: > $sql2 = "insert into tbl values ('$esc_value')" ; # this is correct

: Hi Malcolm. From my palty understanding of PHP, :-), it seems as if
: you're talking about inserting values into a database.

My examples were inserts, but any data put into a query string should be
escaped so the string is interpretted correctly. The escaping ensures the
string contains the original data (not the escaped data) when it arrives
at the database.

e.g.
$id = "somebody's data";
$escaped_id = mysql_escape_st ring($id);
$sql = "select * from tbl where id='$escaped_id '";



: The values I'm
: worried about are already in there. I use phpMyAdmin to build my
: database, and I can see when I browse the table in question that the
: apostrophes aren't causing a problem.

It's hard to know with 100% certainty what is in the database because
phpMyAdmin will have had to escape the data to display it. I would want
to use mysql> to confirm the characters are what I think they are. (And I
wonder if the character set can make a difference, the character may look
like a ' but is it the same binary value as your data that looks like a '
(?)


: The HTML/PHP prints out the string just fine on the web page
: (apostrophe and all).

My question is whether the apostrophe you see is stored as an apostrophe
(') or as something else, such as &apos; which will end up looking
correct in the browser, depending on what phpMyAdmin does when displaying
the data.


: This is a string which can be clicked on, at
: which point the browser sends the text of the link back to the
: database, asking for a record with that string. It doesn't find it,
: because the apostrophe is messing things up somehow.

The query from the browser must correctly encode the ' too. Again, the
string could actually be something else and just look like a '.


I am thinking I would open a temp text file and store all the values you
are receiving at each stage you use them, so you can examine the bytes
later in a text editor.

# PSEUDO code

fp = fopen("/tmp/my-file.txt","w");

$album = $_GET['album'];
printf(fp,'$alb um = $_GET['album'] => [%s]\n",$album);

$album=mysql_es cape_string($al bum);
printf(fp,'mysq l_escaped $album = [%s]\n",$album);

$sql = "select * from .etc.etc ";
printf(fp,'the sql string=[%s]\n",$sql);

# etc, also dump the data from the database same way


: Nothing seems to help, though, and this is probably a simple thing (to
: someone else, at least). :-) Sorry if I'm taking up too much space on
: the ng. Just developing a real headache over this.

The niggly little things are always the trickiest.


--

This space not for rent.