HTML text input and using the input as a variable in a script(tcl)/sql(sqlite)

Question

Hello all, I'm very VERY new at this whole web thing. And I'm just very confused in general. Basically, what I want to do is take an input via text using HTML and adding that input to database, table trans. Should be simple but I am lost.

    <li>Transaction Number</li>
    <li><input type=|text| name=|tnumber| </li> // do i need to use value?
    <li>Employee Name</li>
    <li><input type=|text| name=|ename| </li>
    <li><input type=|SUBMIT| value=|Add|></li>


    ......
    ......
    sqlite3 db $::env(ROOT)/database.db
    db eval {INSERT INTO trans VALUES ($tnumber, $ename)}
    db close

They are both in a same file and there are only two fields to the database to keep things simple. What I can see here is that tnumber and ename aren't declared as variables. So how do I do that so that the text input is assigned to respective variables?

Answer 1

You're after the ncgi package (in Tcllib), which you can use to decode the values in the form and extract them into variables. To do this, you'd have a webpage with a form like this:

<form method="POST" action="/cgi-bin/addTransaction.tcl">
  <li>Transaction Number: <input type="text" name="tnumber">
  <li>Employee Name:      <input type="text" name="ename">
  <li><input type="submit" value="Add">
</form>

(I'll not go into much more depth with the details of writing a form on a webpage.)

Then, in your program (addTransaction.tcl) you do something like this:

package require ncgi
package require sqlite3

ncgi::parse

set xact [ncgi::value "tnumber"]
set name [ncgi::value "ename"]

sqlite3 db $::env(ROOT)/database.db
db eval {INSERT INTO trans VALUES ($xact, $name)}
db close

Note that I've changed the names of the variables to be different from the ones in the webform. This is to show that they do not need to be the same. A few other things to note are that you can make the transaction number field optional (supply a default via the second optional argument to ncgi::value) and it might also be better to do generation of transaction ids in the database (but that's another separate question; I've focussed in this answer on how to couple from the web into SQLite).

Of more interest is the fact that the above code is free of SQL injection attacks. However, whenever you come to taking the contents of the database and sending it back out to the web, you'll need to be careful again or you'll leave open XSS attacks. That's when the html package (Tcllib again) is useful, since it lets you generate correct hazard-free output with minimal effort. But that's another question...