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
