Common Errors

(part of the ArsDigita Community System Developer's guide)

Special characters

• String parameters in a URL must be urlencoded
  

  Incorrect:
  ns_write "<a href=\"index.tcl?foo=$foo\">ACS</a>"
  If foo contains a space, ?, & or another character not valid in an URL parameter, foo will not be defined correctly on the next page.

  Solutions:
  Use ns_urlencode to protect an individual string.
  ns_write "<a href=\"index.tcl?foo=[ns_urlencode $foo]\">ACS</a>"
  
  Use export_url_vars to handle one or more url parameters (export_url_vars also filters out undefined variables).
  ns_write "<a href=\"index.tcl?[export_url_vars foo bar]\">ACS</a>"

  Complex case relevant to ACS:
  When you use the return_url parameter to pass a URL through the registration process, both the individual parameters of return_url and the return_url as whole must be urlencoded.

  Incorrect:
  ns_returnredirect "/register/index.tcl?return_url=[ns_urlencode "index.tcl?domain=$domain"]"
  

  Correct:
  ns_returnredirect "/register/index.tcl?return_url=[ns_urlencode "index.tcl?[export_url_vars domain]"]"

• Form values (except for textareas) need to be enclosed in doublequotes. The " character within a form value should be replace with &quot;

  Incorrect:
  ns_write "<input type=text name=test value=$foo>"
  If foo contains a > or ", the value may be cropped or your form may be misdefined.

  Correct:
  ns_write "<input type=text name=test value=\"[philg_quote_double_quotes $foo]\">"

  For text fields, the export_form_value is useful.
  ns_write "<input type=text name=test [export_form_value foo]>"

  One or more hidden variables can be properly output and protected using export_form_vars.
  ns_write "[export_form_vars foo bar]"

• Textarea values are cropped by </textarea>
  To prevent cropping, the browser must not interpret a </textarea> in the textarea value as the end of the textarea. One way to do this is to replace < with &lt; and > with &gt;. ns_quotehtml will do this for you.

  ns_write "<textarea>[ns_quotehtml $foo]</textarea>"

• Values used in a SQL statement need to double quote quotes. ('' must repace ')

  The ' is SQL's escape character.

  Incorrect:
  ns_db dml $db "insert into foo (string_column) values ('$string_column')"

  Solutions:
  If you use set_form_variables_string_trim_DoubleApos or set_the_usual_form_variables to define variables for the keys/value pairs in [ns_conn form], quoted forms of the variables will be defined.

  Correct:
  set_the_usual_form_variables
  ns_db dml $db "insert into foo (string_column) values ('$QQstring_column')"

  Alternatively, you can use DoubleApos or ns_dbquotevalue:
  ns_db dml $db "insert into foo (string_column) values ('[DoubleApos $string_column]')"
  or
  ns_db dml $db "insert into foo (string_column) values ([ns_dbquotevalue $string_colum])"

• Escape special TCL characters with \

  You will often have to escape special TCL characters. ", {, }, [, and ] are the most common examples.

  Incorrect:
  ns_write "<a href="index.tcl">ACS</a>"

  Correct:
  ns_write "<a href=\"index.tcl\">ACS</a>"

User input

• Double clicks
  For any insert, you should always assume the user is going to double click on the submission form. To prevent integrity an constraint or duplicate data, you should generate the sequence id before the insert. This concept is described in depth in the ecommerce chapter in Philip and Alex's Guide to Web Publishing.

• Bad data
  Always consider cases where the user input might be blank, too long, or an incorrect type. Use Maxlength or the standard user error trapping to handle these cases before they result in a database error or data problems.

HTML Tables

• Confining data to tables are the worst case for speed
  If data is contained in a table, the browser must receive all of the data before it can calculate the table layout and produce any output for the user. Refraining from table use or breaking up table will give the user a faster experience because data can be presented as it received. The difference can be extremely dramatic.

• Closing your table tags
  Depending on your browser, failure to close your table tags can result in a blank screen.

Cookies and domains

• Domains with the same IP don't always recognize each other's cookies
  For example, http://photo.net and http://www.photo.net are the same site. However, if a user is at http://photo.net, the server will not recognize a cookie set by a prior visit to http://www.photo.net. In addition, most servers (including AOLserver) may redirect a user to the server's Hostname domain while the user is surfing for various reasons (when redirecting, for example). The cookie chain files (starting with cookie-chain.tcl) should be used to set cookies on both domains.

Infinite Loops

• Infinite loops last until the server is restarted
  Threads continue to process even if you leave or stop a page. Common mistakes are failure to increment a counter inside a loop or relying on a comparison that never becomes valid.

Case sensitivity

• Oracle is case sensitive
  This is important to consider when you want to order by a given column or maintain uniqueness regardless of case, such as the user's email in ACS.

• Column names are lower case
  The names of columns returned through the Arsdigita Oracle driver are always lower case.

Oracle and Nulls

• "IS" must be used instead of "=" for comparison with NULL
  

  Incorrect:
  select user_id from users where home_phone = null

  Correct:
  select user_id from users where home_phone is null

  The only exception to this rule occurs with update:

  update users set home_phone = null where user_id = 10

• In Oracle, '' is not equivalent to NULL

  Incorrect.
  set num_users [database_to_tcl_string $db "select count(user_id) from users where url = '$foo'"]

  If $foo is "", num_users will always be 0.

  Correct:
  set num_users [database_to_tcl_string $db "select count(user_id) from users where (url = '$foo' or ('$foo' is null and url is null))"]

• Oracle uses 3 way logic

  If you wanted to count users that did not visit today:
  

  Incorrect:
  select count(user_id) from users
  where trunc(last_visit) <> trunc(sysdate)

  (This would not include users that had a last_visit of null.)

  Correct:
  select count(user_id) from users
  where (trunc(last_visit) <> trunc(sysdate)
  or last_visit is null)

  See Tips for using Oracle for more discussion of this and other Oracle issues.

Dates

• Ordering dates by a pretty name
  If you use to_char to produce a formatted version of a date column, ordering by this column will order by the alphabetized word, not the sequential date.

  Incorrect:
  set selection [ns_db select $db "select to_char(posting_time,'Month dd, yyyy') as posting_time from bboard order by posting_time"]

  Correct:
  set selection [ns_db select $db "select to_char(posting_time,'Month dd, yyyy') as pretty_posting_time from bboard order by posting_time"]

Concurrency

• Timing issues within one page
  You can still have concurrency bugs even with Oracle if you assume a given page runs in isolation. For example, if you have the following logic:

  if { [database_to_tcl_string $db "select count(id) from foo_table where id=$id"] == 0 } {
      ns_db dml $db "insert into foo_table (id) values ($id)"
  }
  

  If there are two hits to this page, perhaps due to a double click, you can have the following sequence:
  • Page a looks for the row
  • Page b looks for the row
  • Page a inserts
  • Page b inserts

  In these cases, be sure to use select for update to get the appropriate lock.
  For example:

  ns_db dml $db "begin transaction"
  set selection [ns_db $db 0or1row "select id from foo_table where id=$id for update of foo_table.id"]
  

  if [empty_string_p $selection] {
      ns_db dml $db "insert into foo_table (id) values ($id)"
  }
  ns_db dml $db "end transaction"

Database Handles

• Handles inside procedures
  To prevent handle deadlocks, AOLServer requires you to release all database handles from a pool before you can allocate another handle from the same pool.

  To use a database handle in a procedure you should either pass the database handle as a parameter or use the subquery pool. If you do use the subquery pool, you must release the handles before the procedure returns to avoid clashes with other procedures.

• Performing queries using an occupied handle
  If you perform a query and then process each row using a while loop, you should not perform queries inside the loop with the same database handle. To do this type of logic, you must retrieve a second handle when you first access the pool. (Note, you should think carefully before performing another query for every row returned by a first query. It may be far more efficient and scalable to combine the queries into one using joins or a PL/SQL function.)

  Incorrect:
  set db [ns_db gethandle]
  
  set selection [ns_db select $db "select posting_date, bboard.* 
  from bboard
  where sort_key like '$msg_id%'
  and msg_id <> '$msg_id'
  order by sort_key"]
  
  while {[ns_db getrow $db $selection]} {
      set_variables_after_query
      if $upload_p {
          set selection [ns_db select $db "select * from bboard_uploaded_files where msg_id='$msg_id'"]
  
  	set_variables_after_query
  	...code...
      } else {
          ...code...
      }
  }
  
  
  Correct:
  
  set db_conns [ns_db gethandle subquery 2]
  set db [lindex $db_conns 0] 
  set db_sub [lindex $db_conns 1] 
  
  
  set selection [ns_db select $db "select posting_date, bboard.* 
  from bboard
  where sort_key like '$msg_id%'
  and msg_id <> '$msg_id'
  order by sort_key"]
  
  while {[ns_db getrow $db $selection]} {
      set_variables_after_query
      if $upload_p {
          set sub_selection [ns_db select $db_sub "select * from bboard_uploaded_files where msg_id='$msg_id'"]
  	set_variables_after_subquery
  	...code...
      } else {
          ...code...
      }	     
  }	
  

TCL Confusion

• Confusing SQL and TCL
  Remember which language you are using! For example, "&&" in TCL is often confused with "and" in SQL; "==" is used for equality in TCL, but "=" is used in SQL.

  Common mistake:
  if { [info exists foo] and ![empty_string_p $foo] } {
       ns_write "$foo"
  }
  

  Corrected version:
  if { [info exists foo] && ![empty_string_p $foo] } {
       ns_write "$foo"
  }
  

• Using ns_write after ns_return
  ns_return writes a http contentlength header to the connection. If you break up your output strings and use subsequent calls to ns_write, you will encounter sporadic cases of a pages that do not finish.

  Incorrect:
  ns_return 200 text/html "This is the first form fragment"
  ...tcl code...
  ns_write "This is the second form fragment"
  

  Correct:
  append output_string "This is the first form fragment"
  ...tcl code...
  append output_string "This is the second form fragment"
  ns_return 200 text/html $output_string

  or

  ReturnHeaders
  ns_write "This is the first form fragment."
  ...tcl code...
  ns_write "This is the second form fragment."
  

  Concatenation may be a more efficient approach for computation and transmission, but using multiple outputs to the connection will produce a streaming effect. Streaming will allow the user to see some data while the rest is being processed and transmitted and may work better for the user in many cases.

Variables

• Undefined variables
  You can not refer to as variable's value, ie $foo, for a variable that is not defined. Although this seems obvious, this can occur if you
  • Assume a variable was submitted as part of a form. Checkbox and radio buttons that are not checked or selected do not pass their variables to the next page.
  • Assume it was obtained as part of a regexp.
    In this example, second_to_last_visit is not necessarily defined.

    regexp {~second_to_last-([^;]+)} $cookie match second_to_last_visit
    set pretty_second_to_last_visit "[ns_fmttime $second_to_last_visit "%m/%d/%y %r"]"

  The function export_var can be used to protect against undefined variables. For example, export_var foo will return "" if foo is not defined, or foo's value if foo exists.

• Unsafe string comparison
  String comparison using "==" can cause subtle type problems due to the way the TCL intepreter handles types in this case. A safe way to compare strings is with the string compare statement.

  Unsafe:
  if { $foo == "bar" } {
     ...tcl...
  }

  Correct: (Note that a exact match with string compare returns a 0.)
  if { [string compare $foo "bar"] == 0 } {
     ...tcl...
  }

  If you just want to see if the variable is empty, use empty_string_p
  if [empty_string_p $foo] {
     ...tcl...
  }

• Overwriting variables with set_variables_after_query
  set_variables_after_query will overwrite any variables that conflict with column names. A common case of this is when you pass user_id as a form or url variable, and then select the user_id column in a table. Using "select *" is particularly dangerous because changes to the database can break existing pages.

Stupidity

• "Tiny" errors such as a misplaced " or mismatched bracket produce the same result as any other error -- a broken page. Never edit a file without rechecking to see if the function still works.

AOLServer Problems

• AOLserver does not handle a urlencoding of the return character correctly.
  For example:

  set foo "two
  lines"
  ns_write "< a href=index.tcl?foo=[ns_urlencode $foo]">ACS</a>

  If the user clicks to index.tcl, ns_conn form will not contain any data.

  This will be fixed in AOLServer 2.3.3.

• AOLserver admin pages do not handle double quotes properly. If you use the AOLserver admin pages to update a row where one of the values contains a ", the field will be inadvertently cropped at the ".