clean input to database

[rikmoncur]

When exporting data from mysqladmin to MSExcel, the presence of any commas etc breaks the excel spreadsheet.
This was my first function I ever wrote to clean out the damaging characters from the input form.
I was just wondering if php already has a built-in function for this or if there is a more elegant way of writing this or is this even the right approach.

Please don't laugh too hard..! 🙂

Function strips
,@;\n\r'"\
from input

function stripper($data) 
{ 
$filename2 = str_replace(",",  "", "$data");
$filename3 = str_replace("@",  "", "$filename2");
$filename4 = str_replace(";",  "", "$filename3");
$filename5 = str_replace("'",  "", "$filename4");
$filename6 = str_replace('"',  "", "$filename5");
$filename7 = str_replace("\r",  " ", "$filename6");
$filename9 = htmlspecialchars ($filename8 = str_replace("\n",  "", "$filename7"));
$filename10 = stripslashes($filename9);

  return $filename10;
}

[djjjozsi]

you could store the replacements in an array:

$f=array('"','&','+','!','','');
$string=str_replace($f,'',$string);

lets search in the internet, there are good exemples about preg_replace filtering.

rikmoncur;10891906 wrote:

When exporting data from mysqladmin to MSExcel, the presence of any commas etc breaks the excel spreadsheet.
This was my first function I ever wrote to clean out the damaging characters from the input form.
I was just wondering if php already has a built-in function for this or if there is a more elegant way of writing this or is this even the right approach.

Please don't laugh too hard..! 🙂

Function strips
,@;\n\r'"\
from input

function stripper($data) 
{ 
$filename2 = str_replace(",",  "", "$data");
$filename3 = str_replace("@",  "", "$filename2");
$filename4 = str_replace(";",  "", "$filename3");
$filename5 = str_replace("'",  "", "$filename4");
$filename6 = str_replace('"',  "", "$filename5");
$filename7 = str_replace("\r",  " ", "$filename6");
$filename9 = htmlspecialchars ($filename8 = str_replace("\n",  "", "$filename7"));
$filename10 = stripslashes($filename9);

  return $filename10;
}

[rikmoncur]

wow! excellent stuff!
that shortens it down to:

function stripper($data) 
{
$f = array('@',',',';','"','\'','\\r','\\n','\\');
$string = stripslashes(htmlspecialchars(str_replace($f,'',$data)));
return $string;
} 

(not sure it even needs the stripslashes now?)
thanx!

[djjjozsi]

you could test it how works with that.

and i try that in these days 🙂

maybe that could do with preg_replace, but its worksin every cases,
and i'm not get on well with preg_replace.

<?
error_reporting(E_ALL);
ini_set("display_errors", 1);
$i=0;
while($i<250)
{
if( ($i>0 AND $i<49) OR ($i>58 AND $i<65) OR ($i>90 AND $i<97) OR ($i>122 AND $i<255))
$del[]=chr($i);

$string.=chr($i);
$i++;
}

print $string;   //this is the original string. 
print "<hr>";
print str_replace($del,"",$string);

?>

rikmoncur;10891917 wrote:

wow! excellent stuff!
that shortens it down to:

function stripper($data) 
{
$f = array('@',',',';','"','\'','\\r','\\n','\\');
$string = stripslashes(htmlspecialchars(str_replace($f,'',$data)));
return $string;
} 

(not sure it even needs the stripslashes now?)
thanx!

[laserlight]

hmm... but wouldn't such removal of characters corrupt your data? Perhaps you should be figuring out how to properly escape them for the target format.

[Kudose]

<?php
/**
   * Function to replicate mysql_real_escape_string
   *
   * @param string $text
   * @return string
   */
  function db_clean($text){
    $replace = array(
                  "\x00"  => '\x00',
                  "\n"    => '\n',
                  "\r"    => '\r',
                  '\\'    => '\\\\',
                  "'"     => "\'",
                  '"'     => '\"',
                  "\x1a"  => '\x1a'
                );
    return strtr($text, $replace);
  }

[rikmoncur]

thanx for your replies.

I'll have a look at that function later, kudose - interested to see what the x00 and x1a mean!

Laserlight, the data doesn't get corrupted so much as it becomes a wee bit more difficult to read as the punctuation gets stripped out.
I need it for online questionnaires and the respondents fill in forms but will insist on using commas, line breaks and paragraphs etc. That really busts the excel spreadsheet when exported from mysqladmin.
If I could find a way around that then that would be ideal.

[laserlight]

rikmoncur wrote:

Laserlight, the data doesn't get corrupted so much as it becomes a wee bit more difficult to read as the punctuation gets stripped out.

That is precisely what I mean by the corruption of data. The English literature has examples where missing punctuation can result in different interpretations as the reader guesses where to re-insert the punctuation.