Escaping HTML Strings Within MySQL

I am totally copying an earlier post (link and answer below) and reposting it since it is almost 10 years old and perhaps there is a new solution....

Hey,

I am wondering if it is possible to escape HTML strings within MySQL.... I wish to prevent XSS, and would rather use MySQL directly that PHP's htmlspecialchars().

Thanks for you time!



http://forums.devshed.com/mysql-hel...sql-182055.html

Hi,

why would you do this on database level? Processing the data and preparing it for display is clearly the application's responsibility. I mean, you wouldn't use your SELECTs to add HTML tags, right?

Database queries are for fetching and editing data, nothing else.

In fact, I think this idea indicates that you don't properly separate the data model, data processing and the visual presentation (see the MVC pattern).

Hi Jacques1,

Yes, I do follow a MVC model. This particular use is just so dang convenient.

Where do you typically escape the output before displaying on the screen? The controller tells model to get the data, and then passes the data to the view where it is displayed. Would you htmlspecialchars() in the model or the view? I was leaning toward the model as typically the code is less cluttered. But then I have such a nice $stmt->fetchAll(PDO::FETCH_ASSOC) in my model which is no longer so elegant, and instead need to iterate over each to escape. Which makes me question by decision to put escaping in the model, and maybe it should be put in the view....

Escaping data is completely the responsibility of the view. In fact, your controller, model, and especially your database, should not even be aware of the fact that your application outputs HTML. None of them should be doing any escaping of output data in any way; this permanently binds your application to HTML as an output format and basically defeats the purpose of even having a view layer.

Thanks E-Oreo. After hearing you say it, I am in total agreement. Guess that is why a SQL version of htmlspecialchars doesn't and shouldn't exist.