TDDI48 Project, Part 3: User Accounts

Written by Jonas Kvarnström. Please report any bugs, inconsistencies or vague requirements as soon as possible. Please include the course code "TDDI48" in the subject!

In this part of the project, you will determine what kind of user-related information you need to store in the user account database, and you will set up an SQL database (MySQL) to store this information. You will also create a number of forms and servlets/JSP pages that allow users to register new accounts and edit or remove existing accounts.

Throughout the project, please remember that the number of exercises or extensions you implement is not the only criterion on which your grades are based. Do not neglect basic usability or code quality in order to save time to get additional extensions implemented. Remember, if your project is lacking in usability, or your code is not of sufficiently high quality, your project might not be accepted or you may not achieve the grade you are aiming for!

Revision history

No changes since the version released 2007-01-24.

Exercise 1: User information

Now it's time to start thinking about the user account database. The first step is to determine what information you want to store about your users. Write this down (a simple text file is enough) and determine which SQL data types you want to use. Think about this carefully! You will most likely have to add more fields later, but the more you can do right now the better. You can read more about available datatypes in the MySQL manual.

(Much of this has probably already been done, when you were designing the entry form for new users. However, that exercise was focused on the entry form; this exercise is focused on the information that you will need. Maybe you will need more information than you originally thought?)

The following is the absolute minimum amount of information that you will need in order to keep track of your users:

We strongly encourage you to add any other information that you may want, but we leave the exact set of fields up to you. For example, you might want to add a link to a picture of this user (or an icon to be shown beside his messages), a signature to be used when the user writes a message, an ICQ address, a flag stating whether the user wants his information to be private or whether it can be shown to other users, and so on. Eventually you may also want to keep track of information such as the number of messages a user has written for statistics purposes.

1.1: Creating a Table

You have already set up a database, but it does not contain any tables. Once you have determined which fields you want and which datatypes they should have, you will need to create at least one table that will contain this information.

To create a table, start the command line program mysql and give it the appropriate SQL commands (see also Beginning MySQL Tutorial and MySQL Basics for information about the syntax):

Exercise 2: Form for new users

Make sure that the HTML input form that you created in part 1 contains input fields for all the relevant information you need to collect. It should also contain a welcome message for the new user, a submit button (naturally), and maybe a reset button.

As before, you should make sure you read and follow the HTML and CSS requirements as well as the design requirements! You should also validate your page using the HTML validator as described in the HTML/CSS requirements.

At the moment you can't connect this form to the database, so you can't give it an action attribute. This will be done later.

Exercise 3: Connecting the forms to the database

Now you have an SQL user database and a form to use for entering information for a new user account. The only thing you need to actually be able to add new users to the database is a servlet that takes care of the information sent from the form and adds it to the database.

Please note that even though you may want to use JSP for most of your project, the first couple of exercises should be implemented using servlets. This is intended to give you some experience in writing pure servlets, after which you are free to choose between JSP and servlets on your own.

After updating the deployment descriptor in this way, you must also tell the "new user" form to send its input to the new servlet when the submit button is pressed. This is done by setting the method attribute of the <form> tag in the HTML document to "get" or "post", and setting the action attribute to /forum/newuser.

What remains is to actually write the servlet. Both doGet() and doPost() should do the same thing (which you can accomplish by calling doGet() from doPost() and placing the true method implementation in doGet(), for example): Accept the information from the user entry form, check whether it seems correct (all required fields filled in), make sure there isn't already a user with the same key, and then add the user to the database.

If something is wrong (user already exists, can't contact database, I/O exception, SQL exception, ...), this should be reported to the user by sending him an error message. This error message should be written in HTML, and should be a complete document including a DOCTYPE. Here's an example:

    if (error) {
        out.println("<!DOCTYPE ...>");
        out.println("  <head>");
        out.println("  <title>...");
        out.println("  <link rel='stylesheet' ...>");
        out.println("<span class='error'>A user with key '" + key +
                    "' already exists!"</span>");

An alternative solution would be to use a JSP page as the error page, and to use forwarding or including (with RequestDispatcher) to show this page. With these techniques, information such as the user key can be provided to the JSP page by using request.setAttribute(...) in the servlet to store the required information in the current request and then using request.getAttribute() in the JSP page to retrieve this information.

Database Access in Servlets

During the lectures, we discussed how to access database drivers through the DriverManager. Here, however, we will use another approach: Using a DataSource.

Resin will help you with much of the work underlying the setup of a DataSource. In order to be able to access the database from your servlet, all you have to do is make a copy of the (commented) sample <database> tag in ~/resin/conf/resin.conf, make sure to place the copy outside any comments in the same file, and then configure it for the MySQL server you set up in the previous exercise. You can leave the JNDI name "jdbc/mysql" as it is.

Note that the port you specify in the database tag in resin.conf is the port number where MySQL is running, not the port number where the web server is running.

To get a connection from a servlet, you do the following:

Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/mysql");
Connection con = ds.getConnection();  

Where "jdbc/mysql" is the JNDI name from the resin database configuration. You may also need to use try/catch statements to catch NamingExceptions, which could have occured if you had forgotten to add the definition of "jdbc/mysql" in the Resin configuration file. Database access in JSP pages will be discussed in the next exercise.

NOTE: When you import the DataSource class, make sure you get javax.sql.DataSource and not the identically named class from the activation package. Otherwise you will not be able to use getConnection().

For more information about JDBC, please see the following web resources:

Deploying the servlet

To be able to test the servlet, you must deploy it to the web server deployment directory, /home/noone123/resin/doc/tddi48/. IDEA allows you to do this simply by making the project (Build | Make Project: Ctrl-F9).

Open the new user form, fill in some values, and submit the form. Does it work?

Exercise 4: Listing users

Now that you can add users, you also want to be able to list users. This should be done by querying the database dynamically whenever the user wants to view the guest book.

Write a servlet that lists all existing users together with relevant information about them (not necessarily all fields in the database -- that's up to you to decide). You might call this servlet something like "ListUsers", which would make the servlet class "", and you could configure it at the URL pattern "/listusers".

As before, you should implement both doGet() and doPost().

If you show users' home pages and/or e-mail addresses, they should be clickable hyper links (e-mail addresses have "mailto:" URLs; for example,

Design and styling

As usual, you should try to generate a web page that looks good and is easy to read. Use CSS for styling. You could for example invent your own style tags for various items such as user names, e-mail addresses, and so on, and then surround user names with <span class="username">...</span>. Even if the definitions of these CSS classes are not always different — user names might be formatted in exactly the same way as e-mail addresses — they are conceptually different and giving them their own classes will therefore potentially be useful in the future.

Scalability: Page size

Listing all users in a single page works now but is a bad idea when you have thousands of registered users. Therefore you should only show a certain number of users on each page. The start index could be determined by a query parameter: If you ask for /forum/listusers, you get user 1 to 30 (for example) with a link to /forum/listusers?start=31, and so on. You retrieve the value of this explicitly specified query parameter in exactly the same way as you would retrieve the value of a form field named "start".

Performance: It's easy to retrieve all the users (into an ArrayList, for example), and then display only the ones you want. Unfortunately this is not very efficient, if you have thousands of users and you only want to display 30 of them at a time. Instead, you can use the MySQL LIMIT parameter to retrieve only a limited number of results. For example, to retrieve users 91 through 120:

    SELECT * FROM users ORDER BY name LIMIT 90,30;

In PostgreSQL, the corresponding syntax would be:

    SELECT * FROM users ORDER BY name LIMIT 30 OFFSET 90;

Security: Safe HTML – escaping tags entered by users

Since users may enter characters that are used by HTML ("<" and "&") you should convert them to their corresponding character names, such as "&lt;" and "&amp;". Otherwise, users can "sabotage" your guest book page by entering HTML codes in one of the text fields. For example, they could enter "</html>" to end the HTML page you are generating, and anything you write to the page after that would not be shown in the web browser.

This conversion should be done when the message is being displayed, so that the original message is available unaltered in the database. It should not be done before you store messages, topic names etc. in the database! The information stored in the database should be independent of the use of HTML.

In some places, it may make sense to disallow certain characters completely. For example, you might not want user names to contain "&" at all. On the other hand, the message forum you will design in the next part should definitely be able to display "&" characters in messages!

Exercise 5: Listing users using JSP

You should now write a JSP page with functionality equivalent to the ListUsers servlet from the previous exercise.

The intention of this exercise is precisely to make you do the same thing in two different ways, to make sure that you understand the relative advantages of the two approaches. For that reason, do not take the "easy way out", pasting most of your servlet code into the JSP page. Make sure that you actually make use of the different properties of JSP pages as compared to servlet classes!

Create a new JSP page by right-clicking your resources directory, selecting New | JSP file, and entering "listusers.jsp". Use JSP together with JSTL to duplicate the functionality of the servlet you wrote in the previous exercise.

Setting up JSTL in IDEA

The Resin server naturally contains an implementation of JSTL. However, IDEA isn't automatically aware of this implementation. To make IDEA aware of JSTL, you need to add the two JAR files in TDDI48/misc/jakarta-taglibs-standard-1.1.2/lib/ as IDEA global libraries or project libraries (similarly to how you set up other libraries during the setup phase).

Having done this, you can then include the following tags at the top of your JSP file in order to provide access to the core and sql tag libraries:

<%@ taglib prefix="c" uri="" %>
<%@ taglib prefix="sql" uri="" %>  

Similar tags should be added if you want to use other JSTL tag libraries such as fmt (formatting).

Database Access in JSP Pages

To access a DataSource from a JSP file using JSTL, set the data source as default by adding the following parameter to the web.xml file:


Where dataSourceName is the JNDI name specified in resin.conf (for example, jdbc/mysql). Any <sql:query> tag will now automatically use the default connection without any extra code or information being given!

Exercise 6: Searching for users

Add a form that allows you to specify a name pattern to search for.

Modify the existing servlet or JSP page to accept the input from this form. The servlet or JSP page should check whether a name pattern parameter is present. If it is, it should only display users matching the pattern. If not, it should display all users.

The pattern should use either Java regular expressions (which means that information about all users has to be transferred from the SQL database before Java code can be used to filter the users that are actually shown) or the SQL "like" operator (in which case the SQL querying mechanism does most of the work and only the users that actually match are transferred to your servlet or JSP page).

(If you decide to use MySQL-specific query syntax, you can also use MySQL's built-in support for regular expressions; see the MySQL manual for more information.)

The design of the search form is up to you. You may choose to have a very minimal search form, where it is only possible to search for a certain name, or you may implement a more advanced form where you can search in any single field or in several fields.


As always when using input received from a potentially untrusted user, you must also consider security aspects.

For example, suppose you use the SQL "like" operator, and you don't filter the user's search pattern for valid characters before executing the query. Then they may be able to enter characters that end the query and start a new query, allowing them to execute arbitrary SQL code on your database server.

Consider for example if you were to send the query "SELECT * FROM users WHERE name LIKE '" + pattern + "'", where pattern is the pattern a user specified.

If the user enters the pattern somename'; DELETE TABLE users;, the query string will be:

    SELECT * FROM users WHERE name LIKE 'somename'; DELETE TABLE users;'

One way of achieving this is by escaping apostrophes and quotes using the standard SQL escape syntax. Another method (at a potential performance penalty) uses PreparedStatement instead:

    PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE name LIKE ?");
    ps.setString(1, pattern);
    ResultSet rs = ps.executeQuery();

If you use JSTL, there is also support for PreparedStatements in <sql:query> tags:

    <sql:query var="queryResult">
        SELECT Login, Password, Email FROM users
        WHERE Login LIKE ?
        ORDER BY Login
        LIMIT ...
        <sql:param value="${}"/>

This will bypass the ordinary parsing step for the parameter(s) indicated by '?'. Anything in the pattern variable, including any quotes or semicolons, will be part of the pattern.

Exercise 7: Editing users

Add a form that lets you enter the key (for example, the e-mail address) of a user whose account you want to edit.

When you submit this form, it should be sent to a JSP page which generates an HTML form containing all the editable information about the user. You can start by taking the contents of the basic new user entry form, but make it an active JSP page instead. Make sure users cannot edit their keys, since the key must uniquely identify an user even though other information may change!

When the user submits the second form, it should be sent to a servlet or JSP page which stores the new information in the database. As always, you must have proper error checking.

Exercise 8: Deleting users

Extend the "edit user" form to add a button that deletes the users. Add new servlets or JSP pages, or extend existing servlets or JSP pages, in whatever way you think seems reasonable.

Exercise 9: Passwords and Session Handling

Add access control and session handling.

Users should be able to log in, and then remain logged in until they have logged out.

Users should have passwords. Passwords should never be displayed in plain text. Users should be able to create passwords when registering an account, and they should be able to change passwords when editing their account. New passwords should always be entered twice, and should only be accepted if both inputs are identical.

All relevant servlets and JSP pages should be extended to provide access control. For example, users should only be able to delete or edit themselves.

Whenever the user wants to do something that requries a password, you should check whether a session is already set up and a password has been entered. If not, send him to an HTML form where he can enter user name and password and which sets up a proper session.


You should redirect the user to a login form before he enters any important information that would be lost through the redirection. For example, it would be quite annoying if you entered a long message and were then told that you have forgotten to log in so now you have to enter the message again!

If the form that you use for writing a new message is simply a plain HTML file, where there is no chance to check whether the user has entered a password before he starts typing, then this has to change – the form must be replaced with a servlet or a JSP document, which (1) checks whether the user is logged in, (2) if so, generates the same HTML form that used to be stored in the HTML file, and (3) otherwise, redirects him to the login servlet.

It would also be annoying if the login servlet didn't send you back to the page you came from once you had successfully logged in. This means the login servlet must somehow know where to send you after you've entered the correct password. This can be achieved using techniques discussed during the lectures (storing the user's final destination in a hidden field in the login form, so that it will be available once the user submits the form and the login code can redirect to the intended page).

Exercise 10: Consistency and Design

You've written a number of servlets, JSP pages and plain web pages, and probably learned a bit along the way. Now it's time to step back and consider what you've done.

This is a real exercise. Don't skip it just because the goals are more vague than in the previous exercises.

Talk to us. Feel free to ask us for suggestions for changes and improvements!

[Check links]

(Continued in part 4 of the project...)

Jonas Kvarnström
Senast ändrad: 2007-01-24 16:31