I use mysql_real_escape_string before SQL INSERT, but then have to apply stripslashes to my retrieved data. Is it normal?

Question

I'm no PHP/SQL expert, and I've juste discovered that i had to apply mysql_real_escape_string to secure my SQL INSERTS.

I made a function using several advice found on the net, here it is:

function secure($string)
{
if(is_numeric($string)) 
    { $string = intval($string); }
    elseif (is_array($string)) 
    {
        foreach ($string as $key => $value) {
            $string[$key] = secure($value);
        }
    } 
    else if ($string === null) 
    {
        $string = 'NULL';
    }
    elseif (is_bool($string)) 
    {
        $string = $string ? 1 : 0;
    } 
    else 
    {
        if (get_magic_quotes_gpc()) { $value = stripslashes($string); } 
        $string = mysql_real_escape_string($string);
        $string = addcslashes($string, '%_');
    }
    return $string;
}

Thing is, when I have a look at my tables content, it contains backslashes. And then logically, when I retrieve data I have to apply stripslashes to it to remove these backslashes.

Magic Quotes are off.

QUESTION 1) Now I think that even though I use mysql_real_escape_string to secure my data before SQL insertion, backslashes should not appear in my content ? Can you confirm this ?

QUESTION 2) If not normal, why are these backslashes appearing in my phpMyAdmin content and retrievals ? What did I did wrong ?

QUESTION 3) A guess I have is that mysql_real_escape_string could be applied twice, isn't it ? If so, what could be a function to prevent mysql_real_escape_string being applied many times to a same string, leading to many \\ to a same escapable character ?

Thanks a lot by advance for your inputs guys !

Answer 1

oh, what a senseless function. I know it's not your fault but ones who wrote it in their stupid articles and answers.

Get rid of it and use only mysql_real_escape_string to escape strings.

you have mixed up everything.

• first, no magic quotes stuff should be present in the database escaping function.
  if you want to get rid of magic quotes, do it centralized, at the very top of ALL your scripts, no matter if they deal with the database or not.

• most of checks in this function are useless. is_bool for example. PHP will convert it the same way, no need to write any code for this.

• LIKE related escaping is TOTALLY distinct matter, and has nothing to do with safety.

• is numeric check is completely useless, as it will help nothing.

Also note that escaping strings has nothing to do with security.
I's just a syntax rule - all strings should be escaped. No matter of it's origin or any other stuff. Just a strict rule: every time you place a string into query, it should be quoted and escaped. (And of course, if you only escape it but not quote, it will help nothing)

And only when we talk of the other parts of query, it comes to the SQL injection issue. To learn complete guide on this matter, refer to my earlier answer: In PHP when submitting strings to the database should I take care of illegal characters using htmlspecialchars() or use a regular expression?

Answer 2

Your stripslashed $string is stored to the wrong variable $value instead of $string:

if (get_magic_quotes_gpc()) { $value = stripslashes($string); } 

should be

if (get_magic_quotes_gpc()) { $string = stripslashes($string); }

Answer 3

Are you sure you aren't calling mysql_real_escape_string more than once, each time you call it with escapable characters you will end up adding more and more slashes. You want to call it only once. Also, why are you also calling addcslashes? mysql_real_escape_string should be enough. If you call it only once, you should never have to call stripslashes on the data after retrieving it from the database.

You can't really tell if mysql_real_escape_string is applied more than once, I'd suggest going back and re-reading your code carefully, try debug printing the values just before they are inserted into the db to see if they are look 'over-slashed'.

Btw, if you are using prepared statements (e.g. via mysqli) you dont need to escape your strings, the DB engine does this for you, this could be the problem too.

Answer 4

Remove addslashes completely from all of your code. This is the leading cause for slashes being inserted into database.

function escape($string) {
    if (get_magic_quotes_gpc()) {
        $string = stripslashes($string);    
    }
    return mysql_real_escape_string($string); 
}

Always check if magic_quotes_gpc is enabled, if it is perform stripslashes and escape the data.

Escaped = "don\'t use addslashes"

When it goes into database the '\' is removed.