Php – I use thesql_real_escape_string before SQL INSERT, but then have to apply stripslashes to the retrieved data. Is it normal

mysql-real-escape-stringPHPsqlstripslashes

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 !

Best Answer

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?

Related Topic