mysql_real_escape_string()

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

Re: mysql_real_escape_string()

On Sun, 05 Aug 2007 05:01:54 +0200, zach wrote:

> I created a comment form which will inserts the comments into a databa=
se =

> 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 shoul=
d =

> to to make it more secure?
>
>
>
> $handle =3D mysql_connect($host,$user,$password) or die ('Sorry, looks=
=

> like an error occurred.');
>
> $sql =3D "INSERT INTO comments (id, comment, name, quotekey) VALUES (N=
ULL, =

> '$comment', '$name', '$key')";
>
> mysql_real_escape_string($sql);

You've got the point backwards....

$sql =3D "INSERT INTO comments (id, comment, name, quotekey) VALUES (NUL=
L, =

'";
$sql .=3D mysql_real_escape_string($comment);
$sql .=3D "', '";
$sql .=3D mysql_real_escape_string($name);
$sql .=3D "', '";
$sql .=3D mysql_real_escape_string($key);
$sql .=3D "')";

Else, the 'delimiters' (the quotes) for your string will have been escap=
ed =

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

Re: mysql_real_escape_string()

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.

Re: mysql_real_escape_string()

On Aug 4, 8:01 pm, zach wrote:
>
> 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?
>
> $handle = mysql_connect($host,$user,$password)
> or die ('Sorry, lookslike 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);

Nope. You are escaping the whole query instead of just the inputs.
Additionally, you may or may not need to use
mysql_real_escape_string() depending on whether magic_quotes_gpc is
on. In an unrelated vein, there is no need to set an AUTO_INCREMENT
field to NULL in order to auto-increment it; you can simply omit it
from your SQL.

Consider something like this:

function escape_properly($arg) {
if (get_magic_quotes_gpc()) {
return mysql_real_escape_string(stripslashes($arg));
} else {
return mysql_real_escape_string($arg);
}
}

$handle = mysql_connect($host, $user, $password)
or die ('Sorry, lookslike an error occurred.');
$comment = escape_properly($comment);
$name = escape_properly($name);
$key = escape_properly($key);
$sql = 'INSERT INTO comments (comment, name, quotekey) ' .
"VALUES ('$comment', '$name', '$key')";
mysql_real_escape_string($sql);
mysql_select_db($database);
mysql_query($sql);
mysql_close($handle);

Cheers,
NC

Re: mysql_real_escape_string()

Rik wrote:
> On Sun, 05 Aug 2007 05:01:54 +0200, zach wrote:
>
>> 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.

Re: mysql_real_escape_string()

zach wrote:
> Rik wrote:
>> On Sun, 05 Aug 2007 05:01:54 +0200, zach wrote:
>>
>>> 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

Re: mysql_real_escape_string()

zach wrote:
> 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.

The documentation at: http://www.php.net/manual/en/function.mysql-real-escape-stri ng.php
says: "mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string"

.... and the documentation at: http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-str ing.html
says: "must be a valid, open connection. This is needed because the escaping depends on the
character set in use by the server."

The php documentation also says: "If no such link is found, it will try to create one as if
mysql_connect() was called with no arguments."

RTFM... ;-)

Re: mysql_real_escape_string()

Matt Madrid wrote:
> zach wrote:
>> 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.
>
> The documentation at:
> http://www.php.net/manual/en/function.mysql-real-escape-stri ng.php
> says: "mysql_real_escape_string() calls MySQL's library function
> mysql_real_escape_string"
>
> ... and the documentation at:
> http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-str ing.html
> says: "must be a valid, open connection. This is needed because the
> escaping depends on the character set in use by the server."

That's what I was looking for. Thanks.

>
> The php documentation also says: "If no such link is found, it will try
> to create one as if mysql_connect() was called with no arguments."





> RTFM... ;-)

I read the manual which is how I knew it needed an open connection. The
manual only said it needed an open connection, but didn't explain,(or
not very clearly) why it needed it. Not everyone is so inexperienced as
to completely ignore the manual.

Re: mysql_real_escape_string()

Message-ID: from 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/

Re: mysql_real_escape_string()

zach wrote:
> Matt Madrid wrote:
>> zach wrote:
>>> 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.
>>
>> The documentation at:
>> http://www.php.net/manual/en/function.mysql-real-escape-stri ng.php
>> says: "mysql_real_escape_string() calls MySQL's library function
>> mysql_real_escape_string"
>>
>> ... and the documentation at:
>> http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-str ing.html
>> says: "must be a valid, open connection. This is needed because the
>> escaping depends on the character set in use by the server."
>
> That's what I was looking for. Thanks.
>
>>
>> The php documentation also says: "If no such link is found, it will
>> try to create one as if mysql_connect() was called with no arguments."
>
>
>> RTFM... ;-)
>
> I read the manual

I'm sure you did.. I was trying to get you to read it again.

> which is how I knew it needed an open connection.

ahh.. this is why I threw in that last part about it trying to open a
connection itself if none is found. If you found this out by reading the
docs, and not because you got an error, it means that you might have a
mysql server running on the same system that was automatically connected
to, and so mysql_real_escape_string() works even though you didn't open
a connection or give it one as an argument.

But what if you are actually connecting to a different server with a
different charset *after* using mysql_real_escape_string(). Then you
might have some problems.

> The manual only said it needed an open connection, but didn't explain,(or
> not very clearly) why it needed it. Not everyone is so inexperienced as
> to completely ignore the manual.

Some people are. Wasn't trying to be rude, just point you in the right direction.

Matt M.

Re: mysql_real_escape_string()

Matt Madrid wrote:
> zach wrote:
>> Matt Madrid wrote:
>>> zach wrote:
>>>> 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.
>>>
>>> The documentation at:
>>> http://www.php.net/manual/en/function.mysql-real-escape-stri ng.php
>>> says: "mysql_real_escape_string() calls MySQL's library function
>>> mysql_real_escape_string"
>>>
>>> ... and the documentation at:
>>> http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-str ing.html
>>> says: "must be a valid, open connection. This is needed because the
>>> escaping depends on the character set in use by the server."
>>
>> That's what I was looking for. Thanks.
>>
>>>
>>> The php documentation also says: "If no such link is found, it will
>>> try to create one as if mysql_connect() was called with no arguments."
>>
>>
>>> RTFM... ;-)
>>
>> I read the manual
>
> I'm sure you did.. I was trying to get you to read it again.
>
>> which is how I knew it needed an open connection.
>
> ahh.. this is why I threw in that last part about it trying to open a
> connection itself if none is found. If you found this out by reading the
> docs, and not because you got an error, it means that you might have a
> mysql server running on the same system that was automatically connected
> to, and so mysql_real_escape_string() works even though you didn't open
> a connection or give it one as an argument.
>
> But what if you are actually connecting to a different server with a
> different charset *after* using mysql_real_escape_string(). Then you
> might have some problems.
>
>> The manual only said it needed an open connection, but didn't
>> explain,(or not very clearly) why it needed it. Not everyone is so
>> inexperienced as to completely ignore the manual.
>
> Some people are. Wasn't trying to be rude, just point you in the right
> direction.
>
> Matt M.
>
>

I found it out both ways, I first got an error, read the manual, and
fixed it. That when I got confused because I couldn't figure out why it
needed the connection. Thanks for the help.

zach

Re: mysql_real_escape_string()

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/08/02/command-line-again/

Re: mysql_real_escape_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.

Re: mysql_real_escape_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

Re: mysql_real_escape_string()

zach wrote:
> 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

Zach,

When dealing with other products, it's a good idea to be familiar with
their API's, also.

Many PHP functions are just wrappers for C functions, as
mysql_real_escape_string() is for the function of the same name. PHP
won't document MySQL requirements - it's not their job, the requirements
can be MySQL level dependent, and it's a waste of time and electrons to
duplicate what is already supplied.

Just like you won't find any documentation for the C function
mysql_real_escape_string() in the C language references. In fact, they
don't even mention the function.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: mysql_real_escape_string()

On Sun, 05 Aug 2007 21:46:59 +0200, zach wrote:

> 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:


[quote]
....
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.
[/quote]

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

Re: mysql_real_escape_string()

Rik wrote:
> On Sun, 05 Aug 2007 21:46:59 +0200, zach wrote:
>
>> 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:
>
>
> [quote]
> ...
> 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.
> [/quote]
>
> 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

Re: mysql_real_escape_string()

On Aug 5, 12:45 am, Toby A Inkster
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