Introduction to MySQL on the Web - MySQL - page 600

Must You Always Encode Web Page Output?
If you know a value is legal in a particular context within a web page, you need not
encode it. For example, if you obtain a value from an integer-valued column in a database
table that cannot be NULL , it must necessarily be an integer. No HTML- or URL-encoding
is needed to include the value in a web page, because digits are not special in HTML
text or within URLs. On the other hand, suppose that you solicit an integer value using
a field in a web form. You might be expecting the user to provide an integer, but the user
might be confused and enter an illegal value. You could handle this by displaying an
error page that shows the value and explains that it's not an integer. But if the value
contains special characters and you don't encode it, the page won't display the value
properly, possibly further confusing the user.
Encoding special characters using web APIs
The following encoding examples show how to retrieve values from MySQL and per‐
form both HTML-encoding and URL-encoding on them to generate hyperlinks. Each
example reads a table named phrase that contains short phrases and then uses its con‐
tents to construct hyperlinks that point to a (hypothetical) script that searches for in‐
stances of the phrases in some other table. The table contains the following rows:
mysql> SELECT phrase_val FROM phrase ORDER BY phrase_val;
+----------------------+
| phrase_val |
+----------------------+
| are we "there" yet? |
| cats & dogs |
| rhinoceros |
| whole > sum of parts |
+----------------------+
The goal here is to generate a list of hyperlinks using each phrase both as the hyperlink
label (which requires HTML-encoding) and in the URL as a parameter to the search
script (which requires URL-encoding). The resulting links look something like this:
<a href= "/cgi-bin/mysearch.pl?phrase=are%20we%20%22there%22%20yet%3F" >
are we &quot; there &quot; yet? </a>
<a href= "/cgi-bin/mysearch.pl?phrase=cats%20%26%20dogs" >
cats &amp; dogs </a>
<a href= "/cgi-bin/mysearch.pl?phrase=rhinoceros" >
rhinoceros </a>
<a href= "/cgi-bin/mysearch.pl?phrase=the%20whole%20%3E%20sum%20of%20parts" >
whole &gt; sum of parts </a>
The initial part of the href attribute value will vary per API. Also, the links produced
by some APIs will look slightly different because they encode spaces as + rather than as
%20 .