Prevent mysql injection but allow ' in comments

[tillabong]

Hi im creating a comment page for my website using php. i've been using mysql_real_escape_string. i know this isnt enough to prevent mysql injection but is there a way i could go about doing this cause words like member's gets escaped with a backslash when i show the comment.

thanks.

*update
i've found the answer. using stripslash(). thanks.

[misson]

Alternatively, switch to PDO and use prepared statements. Prepared statement parameters aren't vulnerable to SQL injection; no need to escape or unescape quotes. Of course, you'll still need to handle HTML injection with (e.g.) htmlspecialchars or some sort of whitelist filter.

[marshian]

I think your problem lies elsewhere, with magic quotes (which is now to be deprecated). When this setting is enabled, any input automatically gets escaped, so each " ' \ and NUL characters get escaped with a backslash. Say you enter the string

ab'c

With magic quotes on, you get this in your script:

ab\'c

Then you use mysql_real_escape_string and you get:

ab\\\'c

Therefore, you should use stripslashes() on the string first, after which you can escape it again, mysql-style.

[lemon-tree]

Magic quotes are one of the more frustrating things in PHP. The best way to undo it is using this script:

if (get_magic_quotes_gpc()) {
    $process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
    while (list($key, $val) = each($process)) {
        foreach ($val as $k => $v) {
            unset($process[$key][$k]);
            if (is_array($v)) {
                $process[$key][stripslashes($k)] = $v;
                $process[] = &$process[$key][stripslashes($k)];
            } else {
                $process[$key][stripslashes($k)] = stripslashes($v);
            }
        }
    }
    unset($process);
} 

It will check if magic quotes is turned on, then if it is it will undo magic quotes on all your $_GET, $_POST variables without you having to modify your code.

[marshian]

Magic quotes are one of the more frustrating things in PHP.

Actually only until you find out what's wrong, but they're a major pain when you don't know they exist, but you just get strange results ):
Good thing they're now about to be deprecated, I just hope they don't come up with any new great additions... register_globals and magic_quotes_gpc were just major failures...
I usually use this to undo magic quotes on a given variable, since it's usually not required for most values. (eg. an id can only ever be numeric, magic quotes can't interfere with that)

$var = get_magic_quotes_gpc() ? stripslashes($var) : $var;

For a script where you only need to strip the slashes of a few variables this method takes less cpu time, but your method is easier, you just add it to your header and you can basically forget about it.

[tillabong]

Ok thank you so much for your replies. =)