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
