Integrate database access into Linux applications

xiaoxiao2021-03-06 56

Build a website supported by the database

Alex Roetter (aroetter@cs.stanford.edu) Linux programmers February 2001

Content: Why use a separate database? MySQL Database Mysql API Security Reference About The Author's Evaluation

This article describes mysql, an effective tool for developing electronic trade 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 paper also provides a simple example written in TCL and C to help you develop web applications that support databases.

An application that must store or access a lot of information can benefit from the use of third-party database products. This must be made even more on multiple instances of the program. Web-based applications (including e-commerce) is its good illustration. Why use a separate database? The web server must have a way to handle the script to store status information for future access. Although it is possible to use the comparison of the original method - such as dump to text files or develop homemade mini databases - but only mature database applications can provide more complex web applications. Because there are some free software packages available for this purpose, there is no great advantage of writing customized database engines. In addition, using a third-party database also makes the web developer do not have to invest in the development and maintenance of the database. The mysql database can be quite easy to integrate databases to Linux applications by using scripting languages and compilation system languages (eg C). A free Mysql (released under GNU Public License) database provides a series of complex SQL features and is easy to integrate into applications. MySQL is fast, multi-threaded, and supports ANSI and ODBC SQL standards. Plus third-party software, MySQL supports a list of transaction security for transaction applications.

What is transaction processing? The transaction is a series of changes to the database that need to be performed at an atomic manner. They either execute all or not. For example, all required database changes have a transaction when selling products on the Web. The database needs to minus customer account balance and product inventory, otherwise fail and one operation is not executed. Regardless of the cause of the server, it should not cause the transaction to be partially performed. For example, the bill is calculated, the product is not delivered, or if the inventory is not true, it 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 allow the database back to the state before the transaction begins. This is to maintain the back of the server next time after failure by maintaining the logs of all database operations, and the copy of its original status table, the next restart server is restarted next time. This time and space overhead is a compromise necessary for the transaction security database system. A single MySQL server controls a range of databases that can all be accessed by the server in a similar way. Each database is actually a set of any number of tables, the concept is similar to the user of other SQL databases. Each table consists of a type of data column. The data can be integer, real value, string, or other type, including original binary stream. Each line in the table is a record stored in the database. MySQL is designed and configured to create a client / server. Server MySQLD can run on any machine that can access from the Internet (preferably on the same or closest machine with the web server to ensure reasonable response time). MySQL client uses requests to contact the MySQL server, modify, or query the database owned by the server. In a web application that supports the database, the database client is a web server or a CGI script generated by the web server. These clients can be written in advanced scripting languages 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 there is a MySQL C API, so it is easy to add mysql support to any existing scripting language or tool. Most scripting languages have completed this step. MySQL APIMYSQL API can be used in various languages, including almost all languages that are actually used by the rear end of the website. With these APIs, we can build a MySQL client controlled by the web server. API (for database access) works based on the connection mode. The first thing that the client must do is to open the connection with the MySQL server. This includes proper authentication of the user's name and password appropriately using the server. After the connection is established, the server selects the specific database you want to use. After the initialization is determined, the client application (for us to serve the CGI script) will freely interact with the database in two ways: You can run a regular SQL command, including adding and deleting a table, and Add a record to them; you can also run the query on the database returned. The query generates a group of records that match the query, and then the client can access the record one by one until all records are finished, or the client cancels the recording search. Once the script completes the operation of the database, the connection to the server is turned off. To build a website that integrated database access, you need to write the CGI script to generate dynamic results according to the database status. The web server launches the CGI script and then outputs the appropriately formatted HTML to their standard output stream. The web server captures HTML to send it back to the client, as the request is to the static HTML page. During the generation of HTML, the script can modify the database, or query and merge the results into their output.

As a way briefly explains one example of the above process, the following code (written in C and TCL) queries a database containing a list of products for sales for sale. This never uses all the characteristics of the two language MySQL APIs, but provides an example of fast, easy extension, and can perform any SQL commands for the content of the database. In this example, the script shows all products below a specific price. In practice, users may enter this price in a web browser and send it to the server. We save the details from the environment variable to determine the details of the HTML form value, as it does not differ from the case that is executed in the CGI script that does not support the database. For the clear start, we assume that some specific parameters are set in advance (for example, the price to query). The following code is implemented using a free TCL Generic Database Interface in TCL. The advantage of such an interface is that the TCL is interpreted, and the code can be quickly developed and quickly modified. TCL Sample #His 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"

Set dbuser "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 useing the specified sql code

SQL Query $ Handle "SELECT * from $ TBLNAME Where Price <= $ targetprice"

#Print Out HTML Table HEADER

PUTS "

Product ID

Description

Price (/ $)"

#output Table Rows - Each Fetchrow Retrieves One Result

#from the sql query

While {[SET ROW [SQL Fetchrow $ Handle]! = ""} {

Set Product [Lindex $ ROW 0] SET DESCRIP [Lindex $ ROW 1]

Set Price [Lindex $ ROW 2]

Puts

$ supplid

$ descrip

$ price

}

PUTS ""

#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 buy for multiple queries

SQL Disconnect $ Handle

The following code is an equivalent script written in C using formal MySQL C API MySQL . The advantage of this version is that it is compiled, so it is faster than explaining the language. The database code that is often used in a specific site should be written in C or C , and then accessed by the script or directly by the web server to improve the overall runtime. C example

#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 <<

description

<< "

Product ID

Price ($) << endl;

Result :: Iterator Curresult;

Row row;

// Iteerate over Each Result and Put IT INTO an HTML TABLE

FOR (curResult = res. begin (); curresult! = res.und (); 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 is created on the Web to have some developers to consider problems with Web. All issues related to the CGI program on the web server, such as the input check of the Web server processing permission and script, but also need to consider. In addition, it is also necessary to maintain the security of the database system. This involves protecting the license system for the database server, as well as connecting from the database client to the server. MySQL provides a deep security system, some people describe it "advanced but not standard". MySQL allows clients to be accessed based on user names, client hosts, and databases to access. To create a secure system, let all users use strong passwords, don't give them any access to absolute needs. This includes the hypermath privilege, for example, allows users to see all the process privileges that are running (including changes to other user passwords). The best way is to run the server process itself with no privacy UNIX users, so if a database is leaked or not to hit the entire system. This is similar to the user Nobody rather than the root user. The table describing the system is stored as a separate MySQL database, which can be updated by the MySQL root user. Note that the MySQL server grants privileges based on the MySQL username, which is different from UNIX usernames. However, there is a MySQL root username, which has all permissions to the database. Once the server determines who is connected to the client, and after what they are trying to connect, the access right is controlled according to a given set of permissions. To prevent the host name from the host name from DNS electronic deception, you can enter all host IP addresses, or request the server to resolve the IP address back to the original host name to intercept the DNS request and answer more difficult. In addition to the server access table, communication with the server must also be safe. When logging in to the server from the client, the password is not sent in plain text; but all subsequent SQL commands will be sent in plain text. To achieve higher security, use SSH to set the port forwarding. It encrypts all communication between the server and the client to prevent someone from observing it in the transmission. Data from the client is sent to the port listening to the local SSH server in the client's local machine. It is used by a local SSH server and is encrypted to a remote SSH server, which is decrypted and forwarded 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 allows the CGI script generated by the web server via UNIX (local) socket to communicate 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 on different machines, encrypt all communications between them, or connect two machines through their own dedicated, physically isolated network connections. Just create a user account used by the web server (except root users) to log in to the database server. Software-driven websites are some powerful tools that allow developers to create dynamic sites that provide update information and make changes initiated by the client in multiple sessions. The use of backend databases is essential for managing electronic trade and other applications. By using software available software, it is possible to establish a database-driven site, secure database arrangement to the site's existing CGI architecture. Reference

For the latest news and articles, please visit the MySQL homepage. Find the official interface of C with MySQL on MySQL . Tcl GDBI (Generic Database Interface is a TCL MySQL API in the TCL example herein. Detailed to understand the Secure Shell (SSH) for encrypting all communication between mysql clients and servers. It is used in communication on invisible networks (such as public Internet any part). About the author Alex Roetter has more than 5 years of Linux programming experience, and writes a few years of MySQL database programs. He holds a bachelor's degree in computer science at Stanford University. You can contact him through Aroetter@cs.stanford.edu.

转载请注明原文地址:https://www.9cbs.com/read-89534.html

9cbs

New Post(0)