In this chapter, we are going to be creating an extremely simple web application to use for demonstration purposes in subsequent chapters. The goal here is to get a full, end-to-end application up and running.
The application we are going to develop will simply be a guestbook, so that anyone can come and post a message into the guestbook.
4.1 Setting Up the Database Service
Any good web application has a database. My database of choice has always been PostgreSQL (www.postgresql.org). There is a myth that PostgreSQL is slow. There was some truth to that—in the 1990s.
However, starting with PostgreSQL 7, PostgreSQL has been a top performer, and it just gets better with every release. Additionally, PostgreSQL has always been fantastic with complex queries, and it remains so today. PostgreSQL aims at no-limit programming. For instance, in a PostgreSQL text column, you can store up to 4 gigabytes in a single column of a single row—and still sort by it. On many databases, most of your time is spent making the data match the preferred architecture of the database. I have found that, with PostgreSQL, the database is much more often already ready for your own data architecture.
While this is not a book on PostgreSQL, we will discuss a few of its features related to clusters of nodes.
This creates all of the necessary directories and files for PostgreSQL to run. Next, we need to set up the authentication method for connecting to our PostgreSQL databases. PostgreSQL stores both its data and its configuration in the directory /var/lib/pgsql/data. The file which controls access to the database is pg_hba.conf.
The first line says to trust all connections coming in locally (i.e., not through the network). Therefore, we won’t need a password when dealing with the database directly on the command line. The second line says that anyone can connect to the database over the network using an appropriate password. This would be somewhat unsafe (we don’t want just anyone being able to connect to our database), except that by default the database only listens on the local address, 127.0.0.1, so right now you can’t connect to it from outside anyway. Be sure to save the file and then exit the editor.
Note that even with the restrictions we have in place (local-only connections, firewalls, etc.), many people would consider the preceding configuration too exposed for their liking. The measures here are for balancing security and ease of learning. For more information about securing PostgreSQL, you should read the documentation on www.postgresql.org about the pg_hba.conf file.
The command runs as the database admin user (-U postgres) and creates a new user named gbuser who can create databases (-d) and prompts you to set a password for this new database user (-P). When it prompts you, set the password to whatever you want and write it down so you have it later. We will use the password mypassword where needed in this book, but note that this would be a terrible password to actually use in production. You will not need to use the password when using the command line since we have it set to trust, but you will need it when connecting from your application.
The command prompt will switch to something like guestbookapp=>, which indicates you are in the database. To quit at any time, you can type q. Like many of the system administration commands, PostgreSQL doesn’t really care where in the filesystem you are when you run its commands. It is communicating to a database service, which is running in its own directory.
The id field was created with type serial, which is PostgreSQL’s mechanism for autonumbering. To view the table you just created, type d gb_entries. When you are done, exit out of the database by entering in q.
4.2 The PHP Code
One file to hold the configuration information and common functions
One file to show a list of guestbook entries
One file to show an individual guestbook entry
One file to enter a new guestbook entry
A CSS stylesheet
This book presumes that you know a modicum of PHP and SQL, but even if you don’t, the code should be straightforward enough to follow no matter what language you are familiar with. Therefore, the files will be presented here without too much comment. You will find the figures containing the code at the end of the chapter.
Figure 4-1 shows the common functions that are included by the other files. It has two functions for getting database connections—one for getting a read-only connection and one for getting a read/write connection. At this point, they both return the same connection (and they are indeed both read/write), but as we develop the application further, we will see how a lot can be gained by separating out connections that are used for reads only and those that are used for reading and writing. These functions both simply use PDO (PHP Data Objects) to grab a connection to the database using a connection string. Note that these connection strings contain the password to the database. Be sure to change the password on both of these connection strings to whatever password you entered for gbuser database user earlier.
It also has the getHeader() and getFooter() functions so we don’t have to write as much HTML. Also, h() is used as a shorter version of htmlspecialchars() so that we can have more secure output.
Figure 4-2 is the PHP script that lists all of the entries in the database. This code simply creates a database statement, executes it, and iterates through the results.
Figure 4-3 shows getting a single entry out of the database. Again, there is a single SQL statement that is prepared and executed, and the results are displayed to the screen.
Figure 4-4 simply shows a form that will be used to create a new guestbook entry. This form posts its data to the program in Figure 4-5. That program creates a new record based on the values entered in. Then, after performing the SQL insert statement, it redirects the user back to the listing screen.
This will give you the last 200 lines of PHP’s error log.
Fix any errors that you have and try again. The most likely error is that there was something mistyped in the program, or the password listed in the connection string doesn’t match the password you set for your PostgreSQL user.
Additional troubleshooting steps can be found in Appendix C if needed. If all goes well, you should have a screen that prompts you to create a new entry. Clicking the link will give you a form to fill out. Once you fill out that form, clicking “Submit” will add the new guestbook entry to the list. You can then click the individual entry to see the full information. If that is not what your application does, then something was probably entered incorrectly.
The Limitations of the Application
The goal of this book is to get you up to speed on how to scale your applications. As such, other important aspects of development such as error handling, logging, sanitizing data, and security hardening are not covered. The goal is to convey an application that can be typed in quickly, is easy to understand fully, and does not require deep platform knowledge to follow or modify. Nonetheless, we have implemented some basic security practices, such as using bindValue to properly escape the values sent in via $_GET and $_POST and using htmlspecialchars() to escape them when sending them back to the user.
Good information about programming with security in mind can be found on the www.owasp.org web site.
Configuration and Common Functions (common.php)
List All Guestbook Entries (list.php)
Single Guestbook Entry (single.php)
New Guestbook Entry (new.php)
Create the Guestbook Entry (create.php)
CSS File (guestbook.css)