Posted by shimmyshack on 02/26/07 20:59

On 23 Feb, 23:02, Good Man <h...@letsgo.com> wrote:
> MattMika <mattm...@hotmail.com> wrote innews:t9out25kee9hgnn14r965n7nrcff37pqdq@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.

[Back to original message]