List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:April 18 1999 5:31am
Subject:Re: The best input filter?
View as plain text  
David Christian wrote:
> 
> 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
> 

Make sure mysqld runs with logging enabled, re-run your script, and look
at the log. You will what query actually went to the server and the
problem will become apparent.

-- 
Sasha Pachev
http://www.sashanet.com
Thread
The best input filter?David Christian18 Apr
  • Re: The best input filter?Sasha Pachev18 Apr
RE: The best input filter?tcobb18 Apr