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
The best input filter?David Christian18 Apr
  • Re: The best input filter?Sasha Pachev18 Apr
RE: The best input filter?tcobb18 Apr