', ", \, etc in values... inserting into MySQL

KandieMan101 posted this at 02:40 — 6th August 2003.

Joined: Jan 2003

What is the best way to handle this?

If a script that requires data with these characters to be inserted into MySQL, and it would be used on various servers with various configurations, what would be the best method to handle it?

Currently, all I can think of is:

<?php
// METHOD 1
addslashes(stripslashes($_POST['value'])) 
// Original... but I have NEVER seen anything like this used hehe

// METHOD 2
ini_set(\"magic_quotes_gpc\", 1)
// magic_quotes_gpc can be ineffective in the sense that it addslashes to ALL data... I've never seen slashes in numbers... :)
// Also, some server configurations may not allow this to be set at run time...
?>

Sooooo.... suggestions please!?

SonicMailer Pro
The best mailing list manager has just gotten better!
Click here for a full list of features!

Suzanne posted this at 04:48 — 6th August 2003.

She has: 5,507 posts

Joined: Feb 2000

What problems are you having putting the data in?

I find putting it into TEXT fields I just put the data in. Sometimes when I need to put it in and take it out, edit it and put it back in, I will use PHP to maintain special HTML entities, but I've never worried about bits like semi-colons or single quotes or back-slashes. Perhaps erroneously!

Of course, I haven't done a lot of protection work with the data because I work mostly with off-web intranets or passworded areas.

This is from my comments script, originally from Stephanie at http://www.climbtothestars.org/coding/comments/ (see her site for the original code). I added the url bit and something else that I can't remember, but may not be in this section, anyway.

<?php
    //clean up the message text
    
    $name = strip_tags($name); // remove all tags
    $name = htmlspecialchars($name); // remove all bad characters
    $text=$message; 
    $message = strip_tags($message,\"<strong><em>\"); // remove all tags except ...
    $message = ereg_replace(\"[[:alpha:]]+://[^<>[:space:]]+[[:alnum:]/]\" ,\"<a href=\\"\\0\\"> \\0</a>\", $message); // this makes URLs active hyperlinks
 
    if (get_magic_quotes_gpc()) {
        $message = stripslashes($message);
        $name = stripslashes($name);
      }
    
?>

m3rajk posted this at 14:48 — 6th August 2003.

They have: 461 posts

Joined: Jul 2003

first grab the info.
then strip the slashes. then do any cleaning you want of the data prior to inserting it into the mysql db
then use addslashes. that's what it's there for.

edit:
my cleaning statement does all that at once:

<?php
function clean($userInput){
   $userInput=addslashes(htmlentities(stripslashes(rawurldecode($userInput)), ENT_QUOTES));
   return $userinput;
}
?>

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

m3rajk posted this at 14:58 — 6th August 2003.

They have: 461 posts

Joined: Jul 2003

hmmm... if i'm not mistaken the only issues are null byte,s, single quotes, double quotes and backslashes...which means by the time it gets to addslashes, the only one i might have left is the backslashes...

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

Mark Hensler posted this at 19:55 — 6th August 2003.

He has: 4,048 posts

Joined: Aug 2000

METHOD 1 - Not advisable. If you strip slashes when magic_quotes_gpc is off, you may be stripping slashes that are part of the data, and not part of PHP escaping characters.

METHOD 2 - I've never seen a server that allows you to effectively set this at run time. By the time the first line of PHP is interpreted, the interpreter has already escaped all GPC data (assuming magic_quotes_gpc is on).

So whats a guy/gal to do? (not tested)

<?php
set_magic_quotes_runtime(0);

function fix_slashes($arr)
{
    if (get_magic_quotes_gpc()) return $arr;
    foreach ($arr as $key=>$val) {
        if (is_array($arr)) $arr[$val] = fix_slashes($val);
        else $arr[$val] = addslashes($val);
    }
    return $arr;
}

$GLOBALS = fix_slashes($GLOBALS);
?>

I have no idea why you're using rawurldecode() or htmlentities().

Mark Hensler
If there is no answer on Google, then there is no question.

KandieMan101 posted this at 20:12 — 6th August 2003.

They have: 140 posts

Joined: Jan 2003

Hi Mark,

Thanks that looks closer to what I had in mind. I recently came up with this... what do you think?

<?php
if (!get_magic_quotes_gpc()) {
    foreach($_POST as $k=>$v) {
        $_POST[$k] = mysql_escape_string($v);
    }
}
?>

???

SonicMailer Pro
The best mailing list manager has just gotten better!
Click here for a full list of features!

Mark Hensler posted this at 20:23 — 6th August 2003.

He has: 4,048 posts

Joined: Aug 2000

I've not used mysql_escape_string(). My only concern would be using the string in non-mysql related operations as I don't know what to expect.