From: tcobb
Date: April 18 1999 5:37am
Subject: RE: The best input filter?
List-Archive: http://lists.mysql.com/mysql/2011
Message-Id: <A0CFA284C004D211B7EE0060082F32A4172F2B@freya.circle.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"

Try using the built-in quote() feature of DBI.

$addr=$dbh->quote($l_addr);
$mid=$dbh->quote($mid);
$city=$dbh->quote($l_city);

Then insert them with just the variable name like:

$statement="INSERT INTO LISTINGS VALUES (0,$mid,$addr,$city)";

-Troy Cobb
 Circle Net, Inc.
 http://www.circle.net

>   -----Original Message-----
>   From: David Christian [mailto:davidjc@stripped]
>   Sent: Saturday, April 17, 1999 4:56 PM
>   To: mysql@stripped
>   Subject: The best input filter?
>   
>   
>   
>   When designing CGI forms for MySQL input, what is the most 
>   efficient and
>   useful filter to make sure that illegal characters don't 
>   get through?
>   
>   I know this is a broad question, but I am primarily looking 
>   for the best
>   ways to handle quotes, question marks, etc. that need 
>   special handling
>   prior to insertion to a MySQL table.
>   
>   I have yet to figure out a way to allow both ' and " as input.
>   
>   Here's an example INSERT that I use:
>   
>   $statement = qq{INSERT INTO listings
>                   
>   VALUES(0,'$mid','$l_addr','$l_city','$l_state','$l_zip')};
>   
>   $sth = $dbh->prepare($statement);
>   $sth->execute or db_err("Unable to execute $statement", 
>   $dbh->errstr);
>   
>   
>   The field values come from an HTML form, and first I run 
>   them through
>   this filter:
>   
>   sub filter {
>     $temp = $_[0];
>     $temp =~ s/([?'"])/\\\1/g; # Escape special characters for SQL
>     return ($temp);
>   }
>   
>   This of course changes ?, ', and " to \?, \', and \".
>   
>   This managed to stop the crash when someone entered ' or ", 
>   and now when
>   someone enters ' it actually shows up in the field.  But 
>   when someone
>   enters " then everything after that is truncated.  I don't know yet
>   whether it's MySQL doing that or my CGI program.
>   
>   However, since I'm going down this road, I was hoping to avoid
>   reinventing the wheel if there is a tried-n-true method for 
>   filtering
>   unwanted input.  (The above filter rule is a snippet -- I 
>   also strip HTML
>   and SSI tags, among other things.)
>   
>   What do YOU use? :-)
>   
>   Thanks in advance for any guidance.
>   
>   David
>   
>   
>   ------------------------------------------------------------
>   ---------
>   Please check 
"http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread1998@stripped

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail mysql-unsubscribe@stripped instead.