I'm combing through a legacy app updating SQL to prevent Injections and XSS vulnerabilities. I know to apply PHP's htmlspecialchars() anything that is directly passed to a script and displayed on a page.
Should I be stripping every non-numeric database field even if it is from a generally not-editable lookup table? I'm thinking that might provide stronger protection -- you assume someone haxored ur database and work from there.
If there is no external interface to the lookup table, then you probably don't need to scrub the data coming out of those tables for security reasons. But it might be easier to always scrub data you are presenting rather than adding exceptions.
Also, if the data in the lookup tables is safe for HTML output, what happens when you switch to CSV output? Is it still safe? Still escaped properly?
"Defense in depth". You don't necessarily assume your database has been hacked, but you certainly should be defensively programming and not assuming that the database holds no bad characters, data, etc.
I recommend that you do input validation on the data coming in, output validation on anything you are writing out, and do each of those at each layer in the system (e.g., browser-web server, application-database).
The answer depends on what's in that table column and how it is intended to be used. In most cases escaping the data before inserting it into html is the right thing to do.
There might be situations where the purpose of a table is to store fragments of html which an administrator can update in order to display certain data on the site. Usually this would be called a CMS. If that is what you are building, then you would not want to escape it before including it in html. In such a situation being able to insert a script would be a feature, not a bug.
Of course in such a setting you have to be careful what gets put into that table. It should be equally impossible for an outsider to put data into that table as to put a static html file on the server.
Even when an authorized administrator enters data into the table, sanitizing it would be a good idea in order to catch syntax errors as well as unbalanced tags.
What I described here is of course just the exception to the rule that all non-constant strings should be escaped before being included in html.
Skipping the escaping just because you "know" those strings from the database can never contain html tags would be laziness that will come back and bite you later. If in doubt use escaping.
