Page 1 of 1

Best Way For Stripslashes

Posted: Wed Jan 13, 2010 6:33 pm
by dougp23
I have a pretty large PHP/MySQL app, and I am in the process of upgrading the scripts from very late PHP3/very early PHP4 to a more modern release.

In this app, we store a lot of user name information. So question for everyone, person's name is John D'Angelo.
What do you store in MySQL? D'Angelo or D/'Angelo?

I started one way (not storing the slashes) and it made some of my queries go crazy. So then I started storing the slashes, and that made the other queries go crazy!

So what's best practice? Store the slashes in MySQL or don't store them?

Re: Best Way For Stripslashes

Posted: Wed Jan 13, 2010 6:45 pm
by Eran
The best practice is to use mysql_real_escape_string() or it's mysqli/PDO equivalent. It escapes the string for much more than quotes and doesn't change the data stored in the database.

Re: Best Way For Stripslashes

Posted: Wed Jan 13, 2010 6:59 pm
by dougp23
So for instance, $fname =John $lname= D'Angelo (from a POST on web page)

$fname2=mysql_real_escape_string($fname);
$lanme2=mysql_real_escape_string($lname);

And we say INSERT into PEOPLES (fname,lname) VALUES ($fname2, $lname2);
(this is a VERY basic INSERT query, most of mine are dynamically built with a lot of concatenation, etc)

Example:
//No errors on validation, insert new record
if ($msgFormErr==""){
//Insert Student Bio
$sSQL = "insert into studentbio (" .
"studentbio_internalid," .
"studentbio_active," .
"studentbio_lname," .
"studentbio_fname," .
"studentbio_mi," .
---snip----

Don't I now store D\'Angelo in my database? Which when I then go to display, I must stripslash it so it displays correctly?

Re: Best Way For Stripslashes

Posted: Wed Jan 13, 2010 7:07 pm
by AbraCadaver
dougp23 wrote:So for instance, $fname =John $lname= D'Angelo (from a POST on web page)

$fname2=mysql_real_escape_string($fname);
$lanme2=mysql_real_escape_string($lname);

And we say INSERT into PEOPLES (fname,lname) VALUES ($fname2, $lname2);
(this is a VERY basic INSERT query, most of mine are dynamically built with a lot of concatenation, etc)

Example:
//No errors on validation, insert new record
if ($msgFormErr==""){
//Insert Student Bio
$sSQL = "insert into studentbio (" .
"studentbio_internalid," .
"studentbio_active," .
"studentbio_lname," .
"studentbio_fname," .
"studentbio_mi," .
---snip----

Don't I now store D\'Angelo in my database? Which when I then go to display, I must stripslash it so it displays correctly?
No. Not unless you have something automatic turned on like magic_quotes_gpc (which escapes post, get data) and then mysql_real_escape_string() a post, get var. Build your own function, especially if the app is portable or in case settings change later etc...

Code: Select all

function prep4db($var, $user_data=true) {
    if($user_data && magic_quotes_gpc()) {
        $var = stripslashes($var);
    }
    return mysql_real_escape_string($var);
}

Re: Best Way For Stripslashes

Posted: Wed Jan 13, 2010 7:18 pm
by dougp23
I am trying to follow along, but getting lost, lol!

It seems if $lname= D'Angelo

and I use the mysql_real_escape_string($lname),

I now have D'\Angelo

And if I build a SQL insert statement , aren;t I then INSERTING D'\Angelo??
Or does PHP or MySQL know enough to drop the \

Re: Best Way For Stripslashes

Posted: Wed Jan 13, 2010 7:19 pm
by Eran
It is used for escaping only (ie, as to not break the query). It will not be stored in the database, and you do not to remove it when retrieving said data.

Re: Best Way For Stripslashes

Posted: Thu Jan 14, 2010 10:51 am
by pickle
With the value "D'Angelo", there are a number of things you need to do.
  • Check if magic_quotes is turned on. If it is, then $_POST['lname'] will be "D\'Angelo". I usually fix this with:

    Code: Select all

    $lname = (get_magic_quotes_gpc()) ? stripslashes($_POST['lname']) : $_POST['lname'];
    If you're not familiar with ternary operators, that statement essentially says "If magic_quotes are on, remove any slashes from what was in POST, otherwise just give me what was in POST.
    After this, $lname will always be "D'Angelo"
  • You then need to escape it for the database using mysql_real_escape_string() like has already been suggested.

    Code: Select all

    $lname = mysql_real_escape_string($lname);
    $lname will now = "D\'Angelo" & is ready to be inserted into the database.
  • In your query, quote all values with single quotes. If you use double-quotes, I don't think MySQL will recognize the fact that you're escaping the single quote in the name - because it's not necessary. Anyway - MySQL will strip the escaping slash when the value is inserted, so the last name in your database will be "D'Angelo"