mysql_real_escape_string()

[zach]

I created a comment form which will inserts the comments into a database
and displays them immediately. I want to make sure that its safe from
users inserting unwanted data into the database or executing queries.

Here's my php code, is this done right? Is there anything else I should
to to make it more secure?



$handle = mysql_connect($ host,$user,$pas sword) or die ('Sorry, looks
like an error occurred.');

$sql = "INSERT INTO comments (id, comment, name, quotekey) VALUES (NULL,
'$comment', '$name', '$key')";

mysql_real_esca pe_string($sql) ;


mysql_select_db ($database);

mysql_query($sq l);

mysql_close($ha ndle);



Thanks,
Zach Wingo

[Rik]

Re: mysql_real_esca pe_string()

On Sun, 05 Aug 2007 05:01:54 +0200, zach <wackzingo@gmai l.comwrote:

I created a comment form which will inserts the comments into a database
and displays them immediately. I want to make sure that its safe from
users inserting unwanted data into the database or executing queries.
>
Here's my php code, is this done right? Is there anything else I should
to to make it more secure?
>
>
>
$handle = mysql_connect($ host,$user,$pas sword) or die ('Sorry, looks
like an error occurred.');
>
$sql = "INSERT INTO comments (id, comment, name, quotekey) VALUES (NULL,
'$comment', '$name', '$key')";
>
mysql_real_esca pe_string($sql) ;

You've got the point backwards....

$sql = "INSERT INTO comments (id, comment, name, quotekey) VALUES (NULL,
'";
$sql .= mysql_real_esca pe_string($comm ent);
$sql .= "', '";
$sql .= mysql_real_esca pe_string($name );
$sql .= "', '";
$sql .= mysql_real_esca pe_string($key) ;
$sql .= "')";

Else, the 'delimiters' (the quotes) for your string will have been escaped
too.

Where do $comment,$name & $key come from BTW? I hope you;re not relying on
register_global s.....

mysql_select_db ($database);
>
mysql_query($sq l);
>
mysql_close($ha ndle);

Is normally done automatically on the end of the request, but as long as
you;re finished with the database for the request a good thing to do.

--
Rik Wasmus

[Matt Madrid]

Re: mysql_real_esca pe_string()

zach wrote:

>
$sql = "INSERT INTO comments (id, comment, name, quotekey) VALUES (NULL,
'$comment', '$name', '$key')";
>
mysql_real_esca pe_string($sql) ;

Normally, you want to escape the string, not the whole query.

$comment = mysql_real_esca pe_string($comm ent);

Now create your query.

[zach]

Re: mysql_real_esca pe_string()

Rik wrote:

On Sun, 05 Aug 2007 05:01:54 +0200, zach <wackzingo@gmai l.comwrote:
>

>I created a comment form which will inserts the comments into a
>database and displays them immediately. I want to make sure that its
>safe from users inserting unwanted data into the database or executing
>queries.
>>
>Here's my php code, is this done right? Is there anything else I
>should to to make it more secure?
>>
>>
>>
>$handle = mysql_connect($ host,$user,$pas sword) or die ('Sorry, looks
>like an error occurred.');
>>
>$sql = "INSERT INTO comments (id, comment, name, quotekey) VALUES
>(NULL, '$comment', '$name', '$key')";
>>
>mysql_real_esc ape_string($sql );

>
You've got the point backwards....
>
$sql = "INSERT INTO comments (id, comment, name, quotekey) VALUES
(NULL, '";
$sql .= mysql_real_esca pe_string($comm ent);
$sql .= "', '";
$sql .= mysql_real_esca pe_string($name );
$sql .= "', '";
$sql .= mysql_real_esca pe_string($key) ;
$sql .= "')";
>
Else, the 'delimiters' (the quotes) for your string will have been
escaped too.
>
Where do $comment,$name & $key come from BTW? I hope you;re not relying
on register_global s.....
>

>mysql_select_d b($database);
>>
>mysql_query($s ql);
>>
>mysql_close($h andle);

>
Is normally done automatically on the end of the request, but as long as
you;re finished with the database for the request a good thing to do.
>
--Rik Wasmus

Ok, something that confuses me is why does mysql_real_esca pe_string need
a link or connection to the database if its simply escaping a string. I
thought the whole point was to do the work before it ever goes to a
database, so I wouldn't expect it to need a connection.

[zach]

Re: mysql_real_esca pe_string()

zach wrote:

Rik wrote:

>On Sun, 05 Aug 2007 05:01:54 +0200, zach <wackzingo@gmai l.comwrote:
>>

>>I created a comment form which will inserts the comments into a
>>database and displays them immediately. I want to make sure that its
>>safe from users inserting unwanted data into the database or
>>executing queries.
>>>
>>Here's my php code, is this done right? Is there anything else I
>>should to to make it more secure?
>>>
>>>
>>>
>>$handle = mysql_connect($ host,$user,$pas sword) or die ('Sorry, looks
>>like an error occurred.');
>>>
>>$sql = "INSERT INTO comments (id, comment, name, quotekey) VALUES
>>(NULL, '$comment', '$name', '$key')";
>>>
>>mysql_real_es cape_string($sq l);

>>
>You've got the point backwards....
>>
>$sql = "INSERT INTO comments (id, comment, name, quotekey) VALUES
>(NULL, '";
>$sql .= mysql_real_esca pe_string($comm ent);
>$sql .= "', '";
>$sql .= mysql_real_esca pe_string($name );
>$sql .= "', '";
>$sql .= mysql_real_esca pe_string($key) ;
>$sql .= "')";
>>
>Else, the 'delimiters' (the quotes) for your string will have been
>escaped too.
>>
>Where do $comment,$name & $key come from BTW? I hope you;re not
>relying on register_global s.....
>>

>>mysql_select_ db($database);
>>>
>>mysql_query($ sql);
>>>
>>mysql_close($ handle);

>>
>Is normally done automatically on the end of the request, but as long
>as you;re finished with the database for the request a good thing to do.
>>
>--Rik Wasmus

>
>
Ok, something that confuses me is why does mysql_real_esca pe_string need
a link or connection to the database if its simply escaping a string. I
thought the whole point was to do the work before it ever goes to a
database, so I wouldn't expect it to need a connection.

I forgot to mention, the comment, name and key variables come from a
form via the post method. And thanks again for the help. Do you have a
job or do you just sit around helping people all day? lol

[Geoff Berrow]

Re: mysql_real_esca pe_string()

Message-ID: <ZKudnd9htoY8_C jbnZ2dnUVZ_vfin Z2d@wavecable.c omfrom zach
contained the following:

>I forgot to mention, the comment, name and key variables come from a
>form via the post method.

So you need to do something like
$comment =mysql_real_esc ape_string($com ment); on each user input.

Of course, you may want to do other validation prior to that. If
someone is trying to hack into your system, why store their attempt? You
might also want to check for content length, or banned text such as URLs

If the value meant to be an integer I use intval() rather than
mysql_real_esca pe_string()

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Toby A Inkster]

Re: mysql_real_esca pe_string()

NC wrote:

$sql = 'INSERT INTO comments (comment, name, quotekey) ' .
"VALUES ('$comment', '$name', '$key')";
mysql_real_esca pe_string($sql) ;

?? You should not be escaping the entire SQL query like this!

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 45 days, 11:24.]

Command Line Interfaces, Again

http://tobyinkster.co.uk/blog/2007/08/02/command-line-again/

[Paul Lautman]

Re: mysql_real_esca pe_string()

zach wrote:

The manual only said it needed an open connection, but didn't
explain,(or not very clearly) why it needed it.

But Matt quoted from the manual the text:
"This is needed because the escaping depends on the character set in use by
the server."

This is in the first paragraph of the section in the manual.

[zach]

Re: mysql_real_esca pe_string()

Paul Lautman wrote:

zach wrote:

>The manual only said it needed an open connection, but didn't
>explain,(or not very clearly) why it needed it.

But Matt quoted from the manual the text:
"This is needed because the escaping depends on the character set in use by
the server."
>
This is in the first paragraph of the section in the manual.

I read the PHP manual and it said nothing about this, it only mentioned
that in the mysql manual. I am writing a PHP script and so I looked in
the PHP manual. Who would have thought that writing a script in PHP,
people would expect you to look to other manuals for information.

zach

[Rik]

Re: mysql_real_esca pe_string()

On Sun, 05 Aug 2007 21:46:59 +0200, zach <wackzingo@gmai l.comwrote:

Paul Lautman wrote:

>zach wrote:

>>The manual only said it needed an open connection, but didn't
>>explain,(or not very clearly) why it needed it.

>But Matt quoted from the manual the text:
>"This is needed because the escaping depends on the character set in
>use by the server."
> This is in the first paragraph of the section in the manual.

>
I read the PHP manual and it said nothing about this, it only mentioned
that in the mysql manual.I am writing a PHP script and so I looked in
the PHP manual. Who would have thought that writing a script in PHP,
people would expect you to look to other manuals for information.

Euhm:
<http://www.php.net/mysql_real_esca pe_string>

....
Escapes special characters in the unescaped_strin g, taking into account
the current character set of the connection so that it is safe to place it
in a mysql_query().
....
link_identifier

The MySQL connection. If the link identifier is not specified, the last
link opened by mysql_connect() is assumed. If no such link is found, it
will try to create one as if mysql_connect() was called with no arguments.
If by chance no connection is found or established, an E_WARNING level
warning is generated.

Well, it's all there, the character set, the connection, the opening of an
anonymous one... I knew it and I have never read the portion in the MySQL
manual that mentions the use of mysql_real_esca pe_string.

I guess reading is an art.
--
Rik Wasmus

[zach]

Re: mysql_real_esca pe_string()

Rik wrote:

On Sun, 05 Aug 2007 21:46:59 +0200, zach <wackzingo@gmai l.comwrote:
>

>Paul Lautman wrote:

>>zach wrote:
>>>The manual only said it needed an open connection, but didn't
>>>explain,(o r not very clearly) why it needed it.
>>But Matt quoted from the manual the text:
>>"This is needed because the escaping depends on the character set in
>>use by the server."
>> This is in the first paragraph of the section in the manual.

>>
>I read the PHP manual and it said nothing about this, it only
>mentioned that in the mysql manual.I am writing a PHP script and so I
>looked in the PHP manual. Who would have thought that writing a script
>in PHP, people would expect you to look to other manuals for information.

>
Euhm:
<http://www.php.net/mysql_real_esca pe_string>
>

...
Escapes special characters in the unescaped_strin g, taking into account
the current character set of the connection so that it is safe to place
it in a mysql_query().
...
link_identifier
>
The MySQL connection. If the link identifier is not specified, the last
link opened by mysql_connect() is assumed. If no such link is found, it
will try to create one as if mysql_connect() was called with no
arguments. If by chance no connection is found or established, an
E_WARNING level warning is generated.

>
Well, it's all there, the character set, the connection, the opening of
an anonymous one... I knew it and I have never read the portion in the
MySQL manual that mentions the use of mysql_real_esca pe_string.
>
I guess reading is an art.
--Rik Wasmus

I read that. That's how I fixed the connection error. I didn't supply a
link identifier or have an open connection to that point in the script.
I just didn't know why it needed the connection. And this, "taking into
account the current character set of the connection", as someone who is
very new that makes no sense. I had no idea what that meant. Which is
why I asked here. Less than 1 month ago I didn't know how to do much
more than echo something to the screen.

Yes, reading is an art if you know what it is you're looking for.

Zach

[NC]

Re: mysql_real_esca pe_string()

On Aug 5, 12:45 am, Toby A Inkster <usenet200...@t obyinkster.co.u k>
wrote:

NC wrote:

$sql = 'INSERT INTO comments (comment, name, quotekey) ' .
"VALUES ('$comment', '$name', '$key')";
mysql_real_esca pe_string($sql) ;

>
?? You should not be escaping the entire SQL query like this!

Indeed. Forgot to delete the unnecessary line from the OP's code
after trying to explain why it should be deleted. :) Thanks for
pointing it out!

Cheers,
NC