I have an application where I'm parsing websites and inserting text into a mysql database. As a result, I cannot guarantee the type of data that's being inserted into the database. I'm worried that certain types of data, like single quotes, double quotes, etc. might cause problems for some of my queries, not to mention, some of this data will be output to a HTML file. Take the following example:
my $dbcall = "INSERT INTO mytable (text) VALUES('\"C:\Program Files\Images\image001.jpg\"');
Let's say I call mysql and insert the above command using Perl:DBI, and later retrieve that value. Then if I go to print it to HTML, I might have something like the following:
<IMG SRC="$dbcall">
This would translate to:
<IMG SRC=""C:\Program Files\Images\image001.jpg"">
(notice the double quotation marks "" above)
I guess what I'm getting at is, I have multiple problems, one related to inserting single and/or double quotes into a mysql table, and another related to presenting single and/or double quotes in HTML.
I've thought about doing something like replacing the quotation mark with an ascii code representation, like %020 or whatever. This would work, but what if by some chance the text I parse from the website uses the text "%020" in the data I'm parsing? This could present plenty of problems.
In general, I think what I need is a solution that parses any text that's not in the set [A-Za-z0-9_] into its ascii code equivalent with a %020 type of format.
I'm sure others have dealt with this type of problem, any suggestions?