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,$password) or die ('Sorry, looks
like an error occurred.');

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

mysql_real_escape_string($sql);
mysql_select_db($database);

mysql_query($sql);

mysql_close($handle);

Thanks,
Zach Wingo

[Rik]

On Sun, 05 Aug 2007 05:01:54 +0200, zach <wa*******@gmail.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,$password) or die ('Sorry, looks
like an error occurred.');

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

mysql_real_escape_string($sql);

You've got the point backwards....

$sql = "INSERT INTO comments (id, comment, name, quotekey) VALUES (NULL,
'";
$sql .= mysql_real_escape_string($comment);
$sql .= "', '";
$sql .= mysql_real_escape_string($name);
$sql .= "', '";
$sql .= mysql_real_escape_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_globals.....

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

[Matt Madrid]

zach wrote:

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

mysql_real_escape_string($sql);

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

$comment = mysql_real_escape_string($comment);

Now create your query.

[zach]

Rik wrote:

On Sun, 05 Aug 2007 05:01:54 +0200, zach <wa*******@gmail.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,$password) or die ('Sorry, looks
like an error occurred.');

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

mysql_real_escape_string($sql);

You've got the point backwards....

$sql = "INSERT INTO comments (id, comment, name, quotekey) VALUES
(NULL, '";
$sql .= mysql_real_escape_string($comment);
$sql .= "', '";
$sql .= mysql_real_escape_string($name);
$sql .= "', '";
$sql .= mysql_real_escape_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_globals.....

>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_escape_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]

zach wrote:

Rik wrote:

>On Sun, 05 Aug 2007 05:01:54 +0200, zach <wa*******@gmail.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,$password) or die ('Sorry, looks
like an error occurred.');

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

mysql_real_escape_string($sql);

You've got the point backwards....

$sql = "INSERT INTO comments (id, comment, name, quotekey) VALUES
(NULL, '";
$sql .= mysql_real_escape_string($comment);
$sql .= "', '";
$sql .= mysql_real_escape_string($name);
$sql .= "', '";
$sql .= mysql_real_escape_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_globals.....

>>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_escape_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]

Message-ID: <ZK******************************@wavecable.comfro m 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_escape_string($comment); 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_escape_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]

NC wrote:

$sql = 'INSERT INTO comments (comment, name, quotekey) ' .
"VALUES ('$comment', '$name', '$key')";
mysql_real_escape_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/0...nd-line-again/

[Paul Lautman]

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]

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]

On Sun, 05 Aug 2007 21:46:59 +0200, zach <wa*******@gmail.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_escape_string>

....
Escapes special characters in the unescaped_string, 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_escape_string.

I guess reading is an art.
--
Rik Wasmus

[zach]

Rik wrote:

On Sun, 05 Aug 2007 21:46:59 +0200, zach <wa*******@gmail.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_escape_string>

...
Escapes special characters in the unescaped_string, 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_escape_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]

On Aug 5, 12:45 am, Toby A Inkster <[e-mail]>
wrote:

NC wrote:

$sql = 'INSERT INTO comments (comment, name, quotekey) ' .
"VALUES ('$comment', '$name', '$key')";
mysql_real_escape_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