TransWikia.com

Writing a SQL recordset as a HTML Table - a bad practice?

Software Engineering Asked by Amessihel on December 12, 2021

I’m maintaining an intranet using ASP Classic. Some of pages looks like one or more HTML tables used for administration tasks.

Those tables are obtained by SQL requests. Server side, they are kind of "raw". They are next decorated client-side with icons and buttons to perform those tasks or getting information from AJAX calls.

Basically, the processing looks like this:

<html>
    <head>
        <script>
            function decorateTables();
                let fileTables = document.getElementById("files");
                // and so on...
            }
        </script>
    </head>
    <body onload="decorateTables()">
        <div class="panel-box">
            <%
                Dim sqlRequest
                sqlRequest = "SELECT name, datecreated FROM files WHERE name LIKE 'adm-%'"
                // Outputs a table whose id is "files"
                outputTableFromSQL("files","extra","parameters", sqlRequest)
            %>
        </div>
    </body>
</html>

I realize this literally execute directly an SQL request inside a dynamic Web page. Is this a bad practice? Are there some guidelines that could help?

I could have perform the HTML table generating with a DB record set rather than an SQL request (which would need to rewrite outputTableFromSQL() as a outputTableFromRS()), but isn’t it like moving the issue further away?

designpatterns and practicessqlweb development

One Answer

SQL recordset as a HTML Table - a bad practice?

No. But I think you're really asking if editing is ok. I say it is ok.

I've done this with ASP (i.e. like "Star Wars" when there was only the one movie). The point was to have user-enterable and editable data. Here is what worked for me.

  • NEVER let the user create, edit, or delete keys.
  • Must keep key with its record to allow user editing. I had the (table) keys in hidden fields in each (HTML) data record/row.
  • Upon saving, each data row, one key at a time, was deleted from the table then re-inserted. There was no logic to determine if a row was edited or not or which fields (table columns) were edited. A "blind" delete-table-record-then-insert was simple, fast, and effective.
  • For user-deleted rows, the key still had to be sent to the SQL server. I may have had a "delete" checkbox to signal a deleted row.
  • New record keys are created in the database. You cannot guarantee uniqueness creating them client side. An incoming record with no key was assumed to be new.
  • I had one table that was always fetched/displayed in it's entirety. In this case the entire table data was deleted in a single command then completely re-written. You will have to decide for yourself if you need to rebuild indexes.

Why this worked:

  • I ignored the fact that record keys could be seen in the raw HTML. Yeah, that's rationalization at an irrational level. Oh well.
  • related-table data was displayed as if a single continuous record. User could not delete a parent record and leave a child record.

I realize this literally execute directly an SQL request inside a dynamic Web page. Is this a bad practice?

YES.

What I should have also done:

  • Parameterized the SQL queries, not use complete SQL commands as string literals.
  • researched ASP security features.

Answered by radarbob on December 12, 2021

Add your own answers!

Ask a Question

Get help from others!

Recent Questions

Recent Answers

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP