This article describes MySQL, an effective tool for developing electronic trade and other complex and dynamic websites using third-party databases. MySQL is a fast, multi-threaded, and fully functional 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 Web applications that support databases. One
This article describes MySQL, an effective tool for developing electronic trade and other complex and dynamic websites using third-party databases. MySQL is a fast, multi-threaded, and fully functional 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 Web applications that support databases. One
This article describes MySQL, an effective tool for developing electronic trade and other complex and dynamic websites using third-party databases. MySQL is a fast, multi-threaded, and fully functional 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 Web applications that support databases.
An application that must store or access a large amount of information can benefit from using third-party database products. This is especially true when access to information must be performed on multiple instances of the program. Web-based applications (including electronic trade) are good examples.
Why is an independent database used?
The Web server must have a processing script to store the status information for future access. Although it is possible to use more primitive methods-such as dumping to text files or developing self-made mini databases-only mature database applications can provide all the services required by more complex Web applications. Because some free software packages can be used for this purpose, writing custom application-specific database engines is not very advantageous. In addition, the use of third-party databases also frees Web developers from investing in database development and maintenance tasks.
MySQL database
By using the script language and compiled system language (such as C), it is quite easy to integrate the database into a Linux application. MySQL databases available for free (released under the GNU Public License) provide a series of complex SQL functions and are easy to integrate into applications. MySQL is fast and multithreading and supports ANSI and odbc SQL standards. With third-party software, MySQL supports transaction security tables for transaction processing applications.
Note: What is transaction processing?
A transaction is a series of changes made to the database that need to be performed in an atomic manner. Either they must be executed in full or none of them. For example, when selling products on the Web, all necessary database changes constitute a transaction.
The database needs to deduct the customer account balance and product inventory at the same time, otherwise the operation fails and is not executed.
No matter what causes the server to crash, the transaction should not be partially executed. For example, billing calculation, product delivery, or inventory inaccuracy 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 transaction execution will cause the database to roll back to the state before the transaction starts.
This is achieved by maintaining logs of all database operations and copies of their original status tables. rollback is allowed when the server is restarted next time after a failure. This time and space overhead is a necessary compromise for the transaction security database system.
A single MySQL server controls a series of databases, which can be accessed through the server in a similar way. Each database is actually a group of any number of tables, similar to other SQL database users. Each table is composed of data columns with data types. The data can be an integer, real value, string, or other type, including the original binary stream. Each row in the table is a record stored in the database.
MySQL is designed and constructed as a client/server. The server mysqld can run on any machine that can access the Internet (preferably on the same or nearest machine as the Web server to ensure a reasonable response time ). The MySQL client uses a request to contact the MySQL server to modify or query the database owned by the server. In Web applications that support databases, database clients are Web servers or CGI scripts generated by Web servers. These clients can be written in advanced scripting or low-level system languages, as long as there is a database API in this language. In Linux, most scripting languages are implemented in C. because MySQL c apis exist, it is easy to add MySQL support to any existing scripting language or tool. Most scripting languages have completed this step.
MySQL API
MySQL APIs can be used in a variety of languages, including almost all languages used to compile the website backend. Using these APIs, we can build MySQL clients controlled by Web servers.
APIS (for database access) Work in connection-based mode. The first thing the client must do is to open the connection with the MySQL server. This includes using the username and password recognized by the server to authenticate the connection. After a connection is established, the server selects the specific database to be used. After Initialization is confirmed, the client application (for example, the CGI script on the server side) can freely interact with the database in one of the two ways: You can run regular SQL commands, including adding and deleting tables and adding records to them. You can also query the databases that return results. The query generates a set of records that match the query. Then, the client can access the records one by one until all the records are viewed, or the client cancels the temporary record retrieval. Once the script completes database operations, the connection to the server is closed.
To build a website accessed by an integrated database, you need to write a CGI script to generate dynamic results based on the database status. The Web server starts the CGI script and then outputs properly formatted HTML to their standard output stream. The Web server captures HTML and sends it back to the client, as if the request was for a static HTML page. During the HTML generation process, the script can modify the database or query and merge the results into their output.
As an example of a simple explanation of the above process, the following code (written in C and Tcl) queries a database that contains a list of products sold by a company. This does not use all the features of the MySQL API in the two languages, but provides examples of fast and simple scaling that can execute any SQL commands on the database content. In this example, the script shows all products that are lower than the specified price. In practice, users may enter the price in a Web browser and send it to the server. We do not need to read from environment variables to determine the details of HTML form values, because it is no different from the execution in CGI scripts that do not support databases. For clarity, we assume that specific parameters (such as the price to be queried) are set in advance ).
The following code uses the free Tcl Generic Database Interface to implement it in Tcl. The advantage of such an interface is that Tcl is interpreted and can quickly develop and modify 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 unnecessaryload /home/aroetter/tcl-sql/sql.so#these are well defined beforehand, or they could#be passed into the scriptset DBNAME "clientWebSite";set TBLNAME "products";set DBHOST "backend.company.com"set DBUSER "mysqluser"set DBPASSWD "abigsecret"set targetPrice 200;#connect to the databaseset handle [sql connect $DBHOST $DBUSER $DBPASSWD]sql selectdb $handle $DBNAME ;# get test database#run a query using the specified sql codesql query $handle "select * from $TBLNAME where price <= $targetPrice"#print out html table headerputs "
"puts "
| Product Id |
Description |
Price (\$)"#output table rows - each fetchrow retrieves one result#from the sql querywhile {[set row [sql fetchrow $handle]] != ""} { set prodid [lindex $row 0] set descrip [lindex $row 1] set price [lindex $row 2] puts " |
| $prodid |
$descrip |
$price"}puts " |
"#empty the query result buffer - should already be empty in this casesql endquery $handle#close the db connection - in practice this same connection#is used for multiple queriessql disconnect $handle
|
The following code is equivalent scripts written in C ++ using the official MySQL C ++ API MySQL ++. The advantage of this version is that it is compiled and therefore faster than the interpreted language. Database code that is often used on a specific site should be written in C or C ++ and then accessed by scripts or directly by Web servers to improve the overall running time.
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 << "
\n"; cout << "
| Product Id |
Description" << " |
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 << " |
| " << row[0] << " |
" << row[1] << " |
" << row[2] << endl; } cout << " |
" << endl; } catch (BadQuery er) { // handle a bad query (usually caused by a sql syntax error) cerr << "Error: " << er.error << endl; return -1; } catch (BadConversion er) {//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
There are some issues that developers need to consider when creating Web-Supported Applications on the Web. All questions about CGI programs on the Web server, such as the Web server's permission to process and script-side input checks, still need to be considered.
In addition, it is also necessary to maintain the security of the database system. This involves protecting the permission system of the database server and ensuring the connection security from the database client to the server.
MySQL provides an in-depth security system, which is described as "advanced but not standard ". MySQL allows access to the client based on the user name, client host, and database to be accessed. To create a secure system and allow all users to use strong passwords, do not give them any access they do not absolutely need. This includes seemingly harmless privileges, such as processing privileges that allow users to view all running processes, including those that change passwords of other users. The best way is to run the server process itself by a non-privileged Unix user, so that if a database is leaked, it will not kill the entire system. This is similar to running httpd with the user nobody instead of the root user. Description the tables accessed by the system are stored as separate MySQL Databases and can be updated by the MySQL root User. Note that the MySQL server grants privileges based on the MySQL user name, which is different from the Unix user name. However, there is a MySQL root User Name, which has all permissions on the database. Once the server determines who the connected client is and what they are trying to connect to, it controls access based on a given set of permissions. To prevent the host name in the Access Table from being electronically spoofed by DNS, you can enter the IP addresses of all hosts, or ask the server to resolve the IP address back to the original host name to make it more difficult for others to intercept DNS requests and answer.
In addition to the server access table, communication with the server must be secure. When a client logs on to the server, the password is not sent in plain text; however, all subsequent SQL commands are sent in plain text. To achieve higher security, use ssh to set port forwarding. It encrypts all communications between the server and the client to prevent observation during transmission. The data from the client is sent to the port listened by the local ssh server on the local machine of the client. It is used by the local ssh server and encrypted and sent to the remote ssh server for decryption and forwarding to the MySQL server port.
In practice, the safest way is to run the database server on the machine where the Web server is located, and run the CGI script generated by the Web server through UNIX (local) the socket communicates with the MySQL server. This setting allows the database administrator to disable all remote connections to the MySQL server. If the Web and database servers must be located on different machines, encrypt all communications between them, or connect the two machines through their own dedicated, physically isolated network connections. Create only one user account used by the Web server (except for the root user account) to log on to the database server.
Database-driven websites are powerful tools that allow developers to create dynamic sites that provide information updates and allow changes initiated by clients to continue between multiple sessions. The use of backend databases is essential for users who manage electronic trade and other applications. By using free software, it is possible to establish a database-driven site and securely integrate database connectivity into the existing CGI architecture of the site.
