In the pursuit of better coding practices and security understanding I
just disabled magic_quotes_gpc on our webserver(php-4.3.11,
mysql-4.1.11) and am implementing escaping on GPC data thats used in
queries or written to the DB.

When I execute an insert of $_POST["description"]; with the value
O'Reilly

- without mysql_real_escape_string() I get a SQL syntax error near the
single quote.

- with mysql_real_escape_string() the field is written as O'Reilly in
the db field.

I was under the impression that escaped strings would be written to
the DB like O/'Reilly, but its not.

The manual says:
"Returns the escaped string, or FALSE on error."

So mysql removes the escapes once it determines data to be escaped
properly? I assume for the purpose that you dont have to stripslashes
from queried data? Is this right?

Also, should I worry about escaping all data in GPC or only user input
$_POST and possibly modified $_GET data? For instance, should I escape
$_POST arrays populated by checkboxes? I assume it could be hacked for
injection purposes as well so should be checked.

TIA
Matt Mika

"These animals evacuate ethyl alcohol from their bowels and carbon dioxide from their urinary organs. Thus, one can observe how a specially lighter fluid is exuded from the anus and rises vertically whereas a stream of carbon dioxide is ejected at very short intervals from enormously long genitales."

Justus Freiherr von Liebig - 1839

MattMika <mattmika [at] hotmail.com> wrote in
news:t9out25kee9hgnn14r965n7nrcff37pqdq [at] 4ax.com:


> When I execute an insert of $_POST["description"]; with the value
> O'Reilly
>
> - without mysql_real_escape_string() I get a SQL syntax error near the
> single quote.
>
> - with mysql_real_escape_string() the field is written as O'Reilly in
> the db field.
>
> I was under the impression that escaped strings would be written to
> the DB like O/'Reilly, but its not.

Hi

It's working as it should. It's escaping the single quote because as
you noted in your first point, it causes a syntax error because MySQL
interprets it as part of a command/instruction to the database.

The escape slash is there strictly for MySQL to recognize the single
quote as a part of the data you are inserting, not as part of the
command you are sending to MySQL.

You really wouldn't want "O/'Reilly" as a name in your database table if
the persons real name is "O'Reilly", would you? Then you would have to
call stripslashes(); when calling *all* your data!!!! And that would
certainly mess things up if a value in your table was "Sleater/Kinney".

As an aside, just note that if you call mysql_real_escape_string
directly on an array, it will mess up your array; use it on the VALUES
in the array as opposed to the array itself.

On 23 Feb, 23:02, Good Man <h... [at] letsgo.com> wrote:
> MattMika <mattm... [at] hotmail.com> wrote innews:t9out25kee9hgnn14r965n7nrcff37pqdq [at] 4ax.com:
>
> > When I execute an insert of $_POST["description"]; with the value
> > O'Reilly
>
> > - without mysql_real_escape_string() I get a SQL syntax error near the
> > single quote.
>
> > - with mysql_real_escape_string() the field is written as O'Reilly in
> > the db field.
>
> > I was under the impression that escaped strings would be written to
> > the DB like O/'Reilly, but its not.
>
> Hi
>
> It's working as it should. It's escaping the single quote because as
> you noted in your first point, it causes a syntax error because MySQL
> interprets it as part of a command/instruction to the database.
>
> The escape slash is there strictly for MySQL to recognize the single
> quote as a part of the data you are inserting, not as part of the
> command you are sending to MySQL.
>
> You really wouldn't want "O/'Reilly" as a name in your database table if
> the persons real name is "O'Reilly", would you? Then you would have to
> call stripslashes(); when calling *all* your data!!!! And that would
> certainly mess things up if a value in your table was "Sleater/Kinney".
>
> As an aside, just note that if you call mysql_real_escape_string
> directly on an array, it will mess up your array; use it on the VALUES
> in the array as opposed to the array itself.

and yes escape everything that goes into the db, because it is *easy*
to change it on the way to your server, if you ever play online games
and see someone has got a high score of 40billion 456million and 2,
that's how they did it, trivial.
Further you should also consider making sure that what you get *out*
is what you expect. After all, if you are using the same db for your
app as you do for a less "important" html form, the attack could come
via that and you tables infected with something that will alter the
html output, so use htmlentities or equivalent to protect your
application from XSS occurring from the DB, as well as using
mysql_real_scape_string to protect your DB from injection.