1

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?

Improve this question
5
  • 1
    I honestly wouldn't worry about best practices with such an old framework. Classic ASP is no longer supported by Microsoft. I would instead focus on keeping it working and planning for its replacement.
    – Dan Wilson
    Jul 23, 2020 at 15:43
  • @Dan Wilson, actually Classic ASP seems to be still supported by Microsoft.
    – Amessihel
    Jul 23, 2020 at 15:49
  • I'd still not worry about best practices and plan replacement. It's just too painful to work with legacy technology compared to new alternatives. Specially the broad range of tools we have for testing
    – Christopher Francisco
    Jul 23, 2020 at 17:08
  • @ChristopherFrancisco, yes of course. I'd say it could be interesting to have a hindsight view on this.
    – Amessihel
    Jul 23, 2020 at 17:14
  • 1
    @ChristopherFrancisco, unfortunately, engineers have not the high ground on replacement decisions. Especially external ones.
    – Amessihel
    Jul 23, 2020 at 19:05

1 Answer 1

Reset to default
1

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.
Improve this answer
1
  • Thank you for your detailed answer. In fact editing/deleting stuff happens to be performed through icons/buttons submitting POST requests, themselves calling functions, themselves executing requests. But yeah some key values are somewhat visible in the HTML source since editing functions need them (although there could be a way to obfuscate them). By parametrized queries, you were thinking about a homemade feature?
    – Amessihel
    Jul 24, 2020 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.