<html>
<!--AD_DND-->
<head>
<title>Common Errors</title>
</head>
<body bgcolor=#ffffff text=#000000>
<h2>Common Errors</h2>
by database backed website application programmers,
by <a href="http://teadams.com">Tracy Adams</a>
<p>
(part of the <a href="developers.html">ArsDigita Community System
Developer's guide</a>)
<hr>
This document contains common errors and important facts to be aware of when
building new ACS pages. The examples use the standard ACS platform (Oracle, AOLserver, Tcl), but most demonstrate generic issues. Obtaining a sound understanding of these issues is a good exercise in learning the fine points of database backed website design.
<p>
<h3>Special characters</h3>
<ul>
<li><b>String parameters in a URL must be urlencoded</b><br>
<blockquote>
Incorrect:<br>
ns_write "<a href=\"index.tcl?<font color=red>foo=$foo</font>\">ACS</a>"
<br>
If foo contains a space, ?, & or another character not valid in an URL parameter, foo will not be defined correctly on the next page.
<p>
Solutions:<br>
Use <code>ns_urlencode</code> to protect an individual string.
<br>
ns_write "<a href=\"index.tcl?<font color=red>foo=[ns_urlencode $foo]</font>\">ACS</a>"<br>
<br>
Use <code>export_url_vars</code> to handle one or more url parameters (<code>export_url_vars</code> also filters out undefined variables).<br>
ns_write "<a href=\"index.tcl?<font color=red>[export_url_vars foo bar]</font>\">ACS</a>"
<p>
</blockquote>
Complex case relevant to ACS:<br>
When you use the <code>return_url</code> 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.
<p>
<blockquote>
Incorrect:<br>
ns_returnredirect "/register/index.tcl?return_url=[<font color=red>ns_urlencode</font> "index.tcl?<font color=red>domain=$domain</font>"]"<br>
<p>
Correct:<br>
ns_returnredirect "/register/index.tcl?return_url=[<font color=red>ns_urlencode</font> "index.tcl?<font color=red>[export_url_vars domain]</font>"]"
</blockquote>
<li><b> Form values (except for textareas) need to be enclosed in doublequotes. The " character within a form value should be replace with &quot;</b>
<blockquote>
Incorrect:<br>
ns_write "<input type=text name=test value=<font color=red>$foo</font>>"
<br>
If foo contains a > or ", the value may be cropped or your form may be misdefined.
<p>
Correct:<br>
ns_write "<input type=text name=test value=<font color=red>\"[philg_quote_double_quotes $foo]\"></font>"
<p>
For text fields, the <code>export_form_value</code> is useful.
<br>
ns_write "<input type=text name=test <font color=red>[export_form_value foo]</font>>"
<p>
One or more hidden variables can be properly output and protected using
</code>export_form_vars</code>. <br>
ns_write "<font color=red>[export_form_vars foo bar]</font>"
</blockquote>
<li><b>Textarea values are cropped by </textarea></b><br>
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;. <code>ns_quotehtml</code> will do this for you.
<blockquote>
ns_write "<textarea><font color=red>[ns_quotehtml $foo]</font></textarea>"
</blockquote>
<li> <b>Values used in a SQL statement need to double quote quotes. ('' must repace ') </b>
<blockquote>
The ' is SQL's escape character.
<p>
Incorrect:<br>
ns_db dml $db "insert into foo (string_column) values (<font color=red>'$string_column'</font>)"
<p>
Solutions:<br>
If you use <code>set_form_variables_string_trim_DoubleApos</code> or <code> set_the_usual_form_variables</code> to define variables for the keys/value pairs in [ns_conn form], quoted forms of the variables will be defined.
<p>
Correct:<br>
<font color=red>set_the_usual_form_variables</font><br>
ns_db dml $db "insert into foo (string_column) values (<font color=red>'$QQstring_column'</font>)"
<p>
Alternatively, you can use <code>DoubleApos</code> or <code>ns_dbquotevalue</code>:
<br>
ns_db dml $db "insert into foo (string_column) values (<font color=red>'[DoubleApos $string_column]'</font>)"
<br>
or
<br>
ns_db dml $db "insert into foo (string_column) values (<font color=red>[ns_dbquotevalue $string_colum]</font>)"
</blockquote>
<li><b>Escape special TCL characters with \ </b>
<blockquote>
You will often have to escape special TCL characters. ", {, }, [, and ] are the most common examples.
<p>
Incorrect:<br>
ns_write "<a href=<font color=red>"index.tcl"</font>>ACS</a>"
<p>
Correct:<br>
ns_write "<a href=<font color=red>\"index.tcl\"</font>>ACS</a>"
</blockquote>
</ul>
<h3>User input</h3>
<ul>
<li><b>Double clicks</b><br>
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 <a href="http://photo.net/wtr/thebook/ecommerce.html">the ecommerce</a> chapter in <a href="http://photo.net/wtr/thebook/">Philip and Alex's Guide to Web Publishing</a>.
<p>
<li><b>Bad data</b><br>
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.
</ul>
<H3>HTML Tables</h3>
<ul>
<li><b>Confining data to tables are the worst case for speed</b><br>
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.
<p>
<li><b>Closing your table tags</b><br>
Depending on your browser, failure to close your table tags can result in a blank screen.
</ul>
<H3>Cookies and domains</H3>
<ul>
<li><b>Domains with the same IP don't always recognize each other's cookies</b><br>
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.
</ul>
<H3>Infinite Loops</h3>
<ul>
<li><b>Infinite loops last until the server is restarted</b><br>
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.
</ul>
<h3>Case sensitivity</h3>
<ul>
<li><b>Oracle is case sensitive</b> <br>
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.
<p>
<li> <b>Column names are lower case</b><br>
The names of columns returned through the Arsdigita Oracle driver
are always lower case.
</ul>
<H3>Oracle and Nulls</H3>
<ul>
<li><b> "IS" must be used instead of "=" for comparison with NULL</b><br>
<blockquote>
Incorrect:<br>
select user_id from users where <font color=red> home_phone = null </font>
<p>
Correct:<br>
select user_id from users where <font color=red> home_phone is null</font>
</blockquote>
The only exception to this rule occurs with update:
<blockquote>
update users set <font color=red>home_phone = null</font> where user_id = 10
</blockquote>
<li> <b>In Oracle, '' is not equivalent to NULL</b>
<blockquote>
Incorrect.<br>
set num_users [database_to_tcl_string $db "select count(user_id) from users where <font color=red>url = '$foo'</font>"]
<p>
If $foo is "", num_users will always be 0.
<p>
Correct:<br>
set num_users [database_to_tcl_string $db "select count(user_id) from users where (<font color=red>url = '$foo'
or ('$foo' is null and url is null)</font>)"]
</blockquote>
<li><b>Oracle uses 3 way logic</b>
<p>
If you wanted to count users that did not visit today:<br>
<blockquote>
Incorrect:<br>
select count(user_id) from users <br>
where <font color=red>trunc(last_visit) <> trunc(sysdate)</font>
<p>
(This would not include users that had a <code>last_visit</code> of null.)
<p>
Correct:<br>
select count(user_id) from users<br>
where (<font color=red>trunc(last_visit) <> trunc(sysdate)<br>
or last_visit is null</font>)
</blockquote>
<i>See <a href="http://photo.net/wtr/oracle-tips.html"</a>Tips for using Oracle</a> for more discussion of this and other Oracle issues.</i>
</ul>
<h3>Dates</h3>
<ul>
<li><b>Ordering dates by a pretty name</b><br>
If you use <code>to_char</code> to produce a formatted version of a date column, ordering by this column will order by the alphabetized word, not the sequential date.
<blockquote>
Incorrect:<br>
set selection [ns_db select $db "select to_char(posting_time,'Month dd, yyyy') as <font color=red>posting_time</font>
from bboard
order by <font color=red>posting_time</font>"]
<p>
Correct:<br>
set selection [ns_db select $db "select to_char(posting_time,'Month dd, yyyy') as <font color=red>pretty_posting_time</font>
from bboard
order by <font color=red>posting_time</font>"]
</blockquote>
</ul>
<h3>Concurrency</h3>
<ul>
<li><b>Timing issues within one page</b><br>
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:
<blockquote>
if { [database_to_tcl_string $db "select count(id) from foo_table where id=$id"] == 0 } {<br>
ns_db dml $db "insert into foo_table (id) values ($id)"<br>
}<br>
</blockquote>
If there are two hits to this page, perhaps due to a double click, you can have the following sequence:
<ul>
<li> Page a looks for the row
<li> Page b looks for the row
<li> Page a inserts
<li> Page b inserts
</ul>
<p>
In these cases, be sure to use select for update to get the appropriate lock.
<br>
For example:
<blockquote>
ns_db dml $db "<font color=red>begin transaction</font>"<br>
set selection [ns_db $db 0or1row "select id from foo_table where id=$id <font color=red>for update of foo_table.id</font>"]<br>
<p>
if [empty_string_p $selection] {<br>
ns_db dml $db "insert into foo_table (id) values ($id)"<br>
}<br>
ns_db dml $db "<font color=red>end transaction</font>"
</blockquote>
</ul>
<h3>Database Handles</h3>
<ul>
<li><b>Handles inside procedures</b><br>
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.
<p>To use a database handle in a procedure you should either pass the database handle as a parameter or use the <code>subquery</code> pool. If you do use the <code>subquery pool</code>, you must release the handles before the procedure returns to avoid clashes with other procedures.
<p>
<li><b>Performing queries using an occupied handle</b><br>
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.)
<blockquote><pre>
Incorrect:
<font color=red>set db [ns_db gethandle]</font>
set <font color=red>selection</font> [ns_db select <font color=red>$db</font> "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<r>
if $upload_p {
set <font color=red>selection</font> [ns_db select <font color=red>$db</font> "select * from bboard_uploaded_files where msg_id='$msg_id'"]<br>
<font color=red>set_variables_after_query</font>
...code...
} else {
...code...
}
}
Correct:
<font color=red>
set db_conns [ns_db gethandle subquery 2]
set db [lindex $db_conns 0]
set db_sub [lindex $db_conns 1]
</font>
set <font color=red>selection</font> [ns_db select <font color=red>$db</font> "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 <font color=red>$db $selection</font>]} {
set_variables_after_query
if $upload_p {
set <font color=red>sub_selection</font> [ns_db select <font color=red>$db_sub</font> "select * from bboard_uploaded_files where msg_id='$msg_id'"]
<font color=red>set_variables_after_subquery</font>
...code...
} else {
...code...
}
}
</pre>
</blockquote>
</ul>
<h3>TCL Confusion</h3>
<ul>
<li><b>Confusing SQL and TCL</b><br>
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.
<blockquote>
Common mistake:<br>
if { [info exists foo] <font color=red>and</font> ![empty_string_p $foo] } {<br>
ns_write "$foo"<br>
}<br>
<p>
Corrected version:<br>
if { [info exists foo] <font color=red>&&</font> ![empty_string_p $foo] } {<br>
ns_write "$foo"<br>
}<br>
</blockquote>
<li> <b>Using ns_write after ns_return</b><br>
<code>ns_return</code> writes a http contentlength header to the connection. If you break up your output strings and use subsequent calls to <code>ns_write</code>, you will encounter sporadic cases of a pages that do not finish.
<blockquote>
Incorrect:<br>
<font color=red>ns_return 200 text/html</font> "This is the first form fragment"<br>
...tcl code...<br>
<font color=red>ns_write</font> "This is the second form fragment"<br>
<p>
Correct:<br>
<font color=red>append output_string</font> "This is the first form fragment"<br>
...tcl code...<br>
<font color=red>append output_string</font> "This is the second form fragment"<br>
<font color=red>ns_return 200 text/html</font> $output_string
<p>
or
<p>
<font color=red>ReturnHeaders<br>
ns_write</font> "This is the first form fragment."<br>
...tcl code...<br>
<font color=red>ns_write</font> "This is the second form fragment."<br>
</blockquote>
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.
</ul>
<H3>Variables</h3>
<ul>
<li><b>Undefined variables</b><br>
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
<ul>
<li> 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.
<li> Assume it was obtained as part of a regexp.<br>
In this example, second_to_last_visit is not necessarily defined.
<blockquote>
regexp {~second_to_last-([^;]+)} $cookie match <font color=red>second_to_last_visit</font><br>
set pretty_second_to_last_visit "[ns_fmttime <font color=red>$second_to_last_visit</font> "%m/%d/%y %r"]"
</blockquote>
</ul>
The function <code>export_var</code> can be used to protect against undefined variables. For example, <code>export_var foo</code> will return "" if foo is not defined, or foo's value if foo exists.
<p>
<li><b>Unsafe string comparison</b><br>
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.
<blockquote>
Unsafe:<br>
if { <font color=red>$foo == "bar"</font> } { <br>
...tcl...<br>
}
<p>
Correct: (Note that a exact match with string compare returns a 0.)<br>
if { <font color=red>[string compare $foo "bar"] == 0</font> } { <br>
...tcl... <br>
}
<p>
If you just want to see if the variable is empty, use <code>empty_string_p</code><br>
if <font color=red>[empty_string_p $foo]</font> {<br>
...tcl...<br>
}
</blockquote>
<p>
<li><b>Overwriting variables with set_variables_after_query</b><br>
<code>set_variables_after_query</code> 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.
<p>
</ul>
<h3>Stupidity</H3>
<ul>
<li> "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.
</ul>
<p>
<hr>
The following a current problems should be fixed in the future.
<h3>AOLServer Problems</h3>
<ul>
<li>AOLserver does not handle a urlencoding of the return character correctly.<br>
For example:
<blockquote>
set foo "two<br>
lines"
<br>
ns_write "< a href=index.tcl?foo=[ns_urlencode $foo]">ACS</a>
</blockquote>
If the user clicks to index.tcl, <code>ns_conn form</code> will not contain any data.
<p>
<i>This will be fixed in AOLServer 2.3.3.</i>
<p>
<li> 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 ".
</ul>
<hr>
<a href="http://teadams.com"><address>teadams@mit.edu</address></a>
</body>
</html>