Embedding html code in stored procedures

Question

We seem to have a few developers here who think creating stored procedures that spit out HTML or Javascript code is a legitimate thing to do. In my mind this is the ultimate abuse of the separation of concerns model. Is doing something like this people have often seen people doing?

Answer 1

Horribly wrong! Just my opinion though.

Answer 2

This is a classic novice error.

If you have to put mark up in SP output, you should at least use your own standardized encoding and then have the application process it into HTML/Javascript.

For example

"<javascriptpopup>[outputuotputoutput]</javascriptpopup>"

or

"<prettyfont>[outputuotputoutput]</prettyfont>"

Answer 3

Yucko. There are a few issues:

• Can't 'skin' the app - move to a totally new presentation like Flex, desktop forms, etc.
• You prevent graphic designers or UI experts from working in an environment that's productive for them.
• If you mix your HTML storage (some in templates, some in the db, some in app code), it's absolutely awful to track down UI issues.
• No IDE DOM/layout validation
• You can't preview or prototype without running the db.

Answer 4

A self-evident violation of the "low coupling, high cohesion" principle.

I can't imagine how they would suggest applying CSS formatting to such a beast.

Answer 5

if this is done haphazardly it is probably a violation of the separation of concerns principle of layering

on the other hand, sprocs expressely written to generate html from database info can in some cases be very legit and efficient, esp. for highly dynamic soft-coded web sites, i.e. where part of the web site structure is encoded in the database, or where the database itself contains HTML fragments...

Answer 6

Yes, I have seen many people do it, unfortunately. You're right though: it is vile.

Usually layer-separation problems are when two adjacent layers mix - you get business logic in the database layer, or presentation logic in the business layer. But this skips a layer entirely, putting user-side-presentation miles from where it belongs! Bound to be an unmaintainable horror.

If the scoundrels are unconvinced by such pleas for sanity, you may be able to catch them on security concerns. Database-layer functionality in stored procedures is unlikely to know how to escape text for output to HTML or JS-string-literal, resulting in very probable script-injection hacks leading to XSS attacks. For example if a user calls himself "Brian von < script>steal(document.cookie)< /script>" and that gets crudely concatenated into a stored procedure HTML result...

Answer 7

Utlimate no-no. Aside from all the previous concerns like security, low coupling and layering, what happens when your company wants to syndicate the content, serve it to mobile devices (wap, etc.), use it in text based emails or print, etc.

Answer 8

I don't think the problem is separation of concerns so much as sprocs just lack the tools to do this right.

Also anyone else coming across this code is going to have problems figuring it out, and it's going to be very hard to source-control, integrate and unit test.

The only exception would be if your database actually stores Javascript or HTML that's edited elsewhere, as part of a CMS for instance.

Answer 9

I survived a job in a shop where the entire application emitted all HTML, thankfully using references to external CSS/JS.

At the time the project started, there was no support in Oracle for separate web/application server - everything went through PL/SQL.

Sometimes you just gotta use whatcha got.

Having said that, I don't believe there is any excuse for generating View level artifacts from Stored Procedures in any of the modern DBs or application architectures.