This article describes MySQL, an effective tool for developing e-commerce and other complex, dynamic websites using third-party databases. MySQL is a fast, multi-threaded and full-featured SQL server. In addition to describing the basic architecture of the MySQL system, this article also provides simple examples written in Tcl and C++ to help you develop database-supported Web applications. This article describes MySQL, an effective tool for developing e-commerce and other complex, dynamic websites using third-party databases. MySQL is a fast, multi-threaded and full-featured SQL server. In addition to describing the basic architecture of the MySQL system, this article also provides simple examples written in Tcl and C++ to help you develop database-supported Web applications.
An application that must store or access large amounts of information can greatly benefit from using a third-party database product. This is especially true when access to information must occur on multiple instances of the program. Web-based applications (including electronic trade) are good examples of it.
Why use a standalone database?
Web servers must have a way for their processing scripts to store information about their state for later access. Although it is possible to use more primitive methods - such as dumping to a text file or developing a homemade mini-database - only a full-fledged database application can provide all the services required by a more complex Web application. There is not much benefit in writing a custom application-specific database engine since there are some freely available software packages available for this purpose. In addition, using a third-party database also eliminates the need for web developers to devote themselves to the task of developing and maintaining a database.
MySQL database
Integrating databases into Linux applications can be quite easy through the use of scripting languages and compiled system languages such as C. The freely available MySQL (distributed under the GNU Public License) database provides a sophisticated set of SQL functions and is easy to integrate into applications. MySQL is fast, multi-threaded, and supports ANSI and ODBC SQL standards. Together with third-party software, MySQL supports transaction-safe tables for transaction processing applications.
Note: What is transaction processing?
A transaction is a series of changes to a database that need to be performed atomically. Either all of them must be executed, or none of them must be executed. For example, all necessary database changes when selling a product on the Web form a single transaction.
The database needs to subtract both the customer account balance and the product inventory, otherwise it fails and neither operation is performed.
A server crash for whatever reason should not cause transactions to be partially executed. For example, overbilling, products not being delivered, or inaccurate inventory may be the result of partially completed transactions.
A database that supports transaction processing can encapsulate a set of database code in a transaction. Any failure during the execution of the transaction will cause the database to roll back to the state before the transaction started.
This is achieved by maintaining a log of all database operations, as well as a copy of its original state table, allowing rollback operations the next time the server is restarted after a failure. This time and space overhead is a trade-off necessary for a transaction-safe database system.
A single MySQL server controls a series of databases, all of which are accessed in a similar manner through the server. Each database is actually a set of any number of tables, similar in concept to users of other SQL databases. Each table consists of typed data columns. The data can be integers, real values, strings, or other types, including raw binary streams. Each row in the table is a record stored in the database.
MySQL is designed and structured as a client/server. The server mysqld can run on any machine that can be accessed from the Internet (preferably on the same machine as the Web server or as close as possible to ensure reasonable response times). The MySQL client uses requests to contact the MySQL server to modify or query the database owned by the server. In a database-enabled Web application, the database client is the Web server or a CGI script generated by the Web server. These clients can be written in a high-level scripting language or a low-level system language, as long as a database API exists for that language. In Linux, most scripting languages are implemented in C, and since the MySQL C API exists, it should be easy to add MySQL support to any existing scripting language or tool. Most scripting languages have already completed this step.
MySQL API
The MySQL API is available in a variety of languages, including almost all of the languages actually used to write website backends. Using these APIs we can build a MySQL client controlled by a web server.
The API (for database access) works in connection-based mode. The first thing the client must do is open a connection to the MySQL server. This includes appropriately authenticating the connection using a username and password known to the server. After the connection is established, the server selects a specific database to use. Once initialization is determined, the client application (in our case, the server-side CGI script) is free to interact with the database in one of two ways: it can run regular SQL commands, including adding and dropping tables, and Add records to them; you can also run queries against the database that returns results. The query generates a set of records that match the query, and the client can then access the records one at a time until all records have been viewed, or the client cancels pending record retrieval. Once the script has finished working with the database, the connection to the server is closed.
To build a website that integrates database access, you need to write CGI scripts to generate dynamic results based on the database state. Web servers launch CGI scripts and then output appropriately formatted HTML to their standard output stream. The web server captures the HTML and sends it back to the client as if the request was for a static HTML page. In the process of generating HTML, scripts can modify the database, or they can query and incorporate the results into their output.
As an example to briefly explain the above process, the following code (written in C and Tcl) queries a database containing a list of products for sale by a company. This by no means uses all the features of the MySQL API in both languages, but provides a quick and easily extensible example that can execute any SQL command on the contents of the database. In this example, the script displays all products below a specific price. In practice, the user might enter the price into a Web browser and then send it to the server. We've left out the details of reading from environment variables to determine HTML form values because it's no different than executing in a CGI script that doesn't support a database. For the sake of clarity, we assume that certain parameters (such as the price to be queried) are set in advance.
The following code is implemented in Tcl using the freely available Tcl Generic Database Interface. The advantage of such an interface is that Tcl is interpreted and can quickly develop and modify the code.
Tcl example
#This code prints out all products in the database
# that are below a specified price (assumed to have been determined
# beforehand, and stored in the variable targetPrice)
# The output is in HTML table format, appropriate for CGI output
#load the SQL shared object library. the Tcl interpreter could also
#have been compiled with the library, making this line unnecessary
load /home/aroetter/tcl-sql/sql.so
#these are well defined beforehand, or they could
#be passed into the script
set DBNAME "clientWebSite";
set TBLNAME "products";
set DBHOST "backend.company.com"
setDBUSER "mysqluser"
set DBPASSWD "abigsecret"
set targetPrice 200;
#connect to the database
set handle [sql connect $DBHOST $DBUSER $DBPASSWD]
sql selectdb $handle $DBNAME ;# get test database
#run a query using the specified sql code
sql query $handle "select * from $TBLNAME where price <= $targetPrice"
#print out html table header
puts "<table border=4>"
puts "<th>Product Id <th width=200>Description <th>Price ($)"
#output table rows - each fetchrow retrieves one result
#from the sql query
while {[set row [sql fetchrow $handle]] != ""} {
set prodid [lindex $row 0]
set descrip [lindex $row 1]
set price [lindex $row 2]
puts "<tr><td>$prodid <td align=center>$descript <td>$price"
}
puts "</table>"
#empty the query result buffer - should already be empty in this case
sql endquery $handle
#close the db connection - in practice this same connection
#is used for multiple queries
sql disconnect $handle
The code below is the equivalent script written in C++ using the official MySQL C++ API MySQL++. The advantage of this version is that it is compiled and therefore faster than interpreted languages. Database code frequently used on a specific site should be written in C or C++ and then accessed by scripts or directly from the web server to improve overall runtime.
C++ example
#include
#include
#include
const char *DBNAME = "clientWebSite";
const char *DBTABLE = "products";
const char *DBHOST = "backend.company.com";
const char *DBUSER = "mysqluser";
const char *DBPASSWD = "abigsecret":
int main() {
try {
//open the database connection and query
Connection con(DBNAME, DBHOST, DBUSER, DBPASSWD);
Query query = con.query();
//write valid sql code to the query object
query << "select * from " << DBTABLE;
//run the query and store the results
Result res = query.store();
//write out the html table header
cout << "<table border=4>n";
cout << "<th>Product Id <th width=200>Description"
<< "<th>Price ($)" << endl;
Result::iterator curResult;
Row row;
//iterate over each result and put it into an html table
for (curResult = res.begin(); curResult != res.end(); curResult++) {
row = *curResult;
cout << "<tr><td align=center>" << row[0]
<< "<td>" << row[1]
<< "<td>" << row[2] << endl;
}
cout << "</table>" << endl;
} catch (BadQuery er) {
// handle a bad query (usually caused by a sql syntax error)
cerr << "Error: " << er.error << endl;
return -1;
} catch (BadConversioner) {
//handle conversion errors out of the database as well
cerr << "Error: Can't convert "" << er.data << "" to a ""
<< er.type_name << ""." << endl;
return -1;
}
return 0;
}
security
Creating Web-powered applications on the Web has some issues that developers need to consider. All issues related to CGI programs on the Web server, such as Web server processing permissions and input checks on the script side, still need to be considered.
In addition, it is also necessary to maintain the security of the database system. This involves securing the database server's permissions system and making connections from database clients to the server secure.
MySQL provides an in-depth security system that some people describe as "advanced but not standard." MySQL allows client access based on the username, client host, and database to be accessed. To create a secure system, have all users use strong passwords and don't give them any access they don't absolutely need. This includes seemingly innocuous privileges such as handle privileges that allow a user to view all running processes, including those that change other users' passwords. The best approach is to run the server process itself as an unprivileged Unix user so that if one database is compromised, it doesn't bring down the entire system. This is similar to running httpd as user nobody instead of root. Tables describing system access are stored as separate MySQL databases and can be updated by the MySQL root user. Note that the MySQL server grants privileges based on MySQL usernames, which are different from Unix usernames. However, there is a MySQL root username, which has full permissions on the database. Once the server determines who the connecting clients are and what they are trying to connect to, access is controlled based on a given set of permissions. To prevent hostnames in the access table from being spoofed by DNS, you can enter the IP addresses of all hosts, or ask the server to resolve the IP addresses back to the original hostnames to make it more difficult for others to intercept DNS requests and answers.
In addition to server access tables, communication with the server must also be secure. When logging on to the server from the client, the password is not sent in plain text; however, all subsequent SQL commands will be sent in plain text. For greater security, use ssh to set up port forwarding. It encrypts all communication between the server and client, preventing anyone from observing it in transit. Data from the client is sent to the port on the client's local machine where the local ssh server is listening. It is used by the local ssh server, encrypted and sent to the remote ssh server, which decrypts it and forwards it to the MySQL server port.
In practice, the safest approach is to run the database server on the same machine as the web server and let the CGI scripts generated by the web server communicate with the MySQL server via UNIX (native) sockets. This setting allows the database administrator to disable all remote connections to the MySQL server. If the web and database servers must be on different machines, encrypt all communications between them, or connect the two machines through their own dedicated, physically isolated networks. Create only one user account (other than the root user) used by the web server to log in to the database server.
Database-driven websites are powerful tools that allow developers to create dynamic sites that provide updated information and allow client-initiated changes to persist across multiple sessions. The use of back-end databases is essential for managing users of e-commerce and other applications. By using freely available software, it is possible to build a database-driven site that securely integrates database connectivity into the site's existing CGI architecture.