Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
What You’ll Learn in This Hour:
• SQL and the enterprise
• Front-end and back-end applications
• Accessing a remote database
• SQL and the Internet
• SQL and the intranet
The previous hour covered some advanced SQL topics. These topics build on earlier hours in the book and show you practical applications for the SQL you have learned. In this hour, you focus on the concepts behind extending SQL to the enterprise, which involve SQL applications and making data available to all appropriate members of a company for daily use.
SQL and the Enterprise
Many commercial enterprises have specific data available to other enterprises, customers, and vendors. For example, the enterprise might have detailed information on its products available for customers to access in hopes of acquiring more purchases. Enterprise employee needs are included as well. For example, employee-specific data can be made available, such as for timesheet logs, vacation schedules, training schedules, company policies, and so on. A database can be created, and customers and employees can be allowed easy access to an enterprise’s important data via SQL and an Internet language.
The Back-End Application
The heart of any application is the back-end application. This is where things happen behind the scenes, transparent to the database end user. The back-end application includes the actual database server, the data sources, and the appropriate middleware that connects an application to the Web or a remote database on the local network.
Determining your database implementation is typically the first step in deploying any application, either to the enterprise through a local area network (LAN), to the enterprise’s own intranet, or to the Internet. Deploying describes the process of implementing an application in an environment that is available for use. The database server should be established by an onsite database administrator (DBA) who understands the company’s needs and the application’s requirements.
The middleware for the application includes a web server and a tool capable of connecting the web server to the database server. The main objective is to have an application that can communicate with a corporate database.
The Front-End Application
The front-end application is the part of an application with which an end user interacts. The front-end application is either a commercial, off-the-shelf software product that a company purchases or an application that is developed in-house using other third-party tools. Commercial software can include applications that utilize a web browser to display content. In the Web environment, web browsers such as Firefox and Internet Explorer are often used to access database applications. This allows users to have access to the database without having to install special software.
Did You Know?: There Are Many Different Layers to an Application
The front-end application promotes simplicity for the database end user. The underlying database, code, and events that occur within the database are transparent to the user. The front-end application is developed to relieve the end user from guesswork and confusion, which might otherwise be caused by having to be too intuitive to the system. The new technologies allow the applications to be more intuitive, enabling the end users to focus on the true aspects of their particular jobs, thereby increasing overall productivity.
The tools available for developers today are user friendly and object oriented, by way of icons, wizards, and dragging and dropping with the mouse. Some of the popular tools to port applications to the Web include Borland’s C++Builder and IntraBuilder and Microsoft’s Visual Studio. Other popular applications used to develop corporate-based applications on a LAN include PowerBuilder by Powersoft, Oracle Forms by Oracle Corporation, Visual Studio by Microsoft, and Delphi by Borland.
Figure 23.1 illustrates the back-end and front-end components of a database application. The back end resides on the host server, where the database resides. Back-end users include developers, programmers, DBAs, system administrators, and system analysts. The front-end application resides on the client machine, which is typically each end user’s PC. End users are the vast audience for the front-end component of an application, which can include users such as data entry clerks and accountants. The end user is able to access the back-end database through a network connection—either a LAN or a wide area network (WAN). Some type of middleware (such as an ODBC driver) provides a connection between the front and back ends through the network.
Figure 23.1. A database application.
Accessing a Remote Database
Sometimes the database you are accessing is a local one to which you are directly connected. For the most part, you will probably access some form of a remote database. A remote database is one that is nonlocal, or located on a server other than the server to which you are currently connected, meaning that you must utilize the network and some network protocol to interface with the database.
You can access a remote database in several ways. From a broad perspective, a remote database is accessed via the network or Internet connection using a middleware product. (Both ODBC and JDBC, standard middleware, are discussed in the next section.) Figure 23.2 shows three scenarios for accessing a remote database.
Figure 23.2. Scenarios for accessing a remote database.
Figure 23.2 shows access to a remote server from another local database server, a local front-end application, and a local host server. The local database server and local host server are often the same because the database normally resides on a local host server. However, you can usually connect to a remote database from a local server without a current local database connection. For the end user, the front-end application is the most typical method of remote database access. All methods must route their database requests through the network.
ODBC
Open Database Connectivity (ODBC) allows connections to remote databases through a library driver. A front-end application uses an ODBC driver to interface with a back-end database. A network driver might also be required for a connection to a remote database. An application calls the ODBC functions, and a driver manager loads the ODBC driver. The ODBC driver processes the call, submits the SQL request, and returns the results from the database.
As a part of ODBC, all the relational database management system (RDBMS) vendors have an application programming interface (API) with their database.
JDBC
JDBC is Java Database Connectivity. Like ODBC, JDBC allows connections to remote databases through a Java library driver. A front-end Java application uses the JDBC driver to interface with a back-end database.
OLE DB
OLE DB is a set of interfaces written using the Component Object Model (COM) by Microsoft as a replacement for ODBC. The implementation of OLE DB attempts to extend the feature set of ODBC and address connectivity not only to various database implementations but to nondatabase data stored such as spreadsheets.
Vendor Connectivity Products
In addition to drivers or an API, many vendors have their own products that allow a user to connect to a remote database. Each of these vendor products is specific to the particular vendor implementation and might not be portable to other types of database servers.
Oracle Corporation has a product called Oracle Fusion Middleware that allows connectivity to the Oracle database as well as other applications.
Microsoft produces several products for interacting with its database, such as Microsoft SharePoint Server and SQL Server Reporting Services.
Accessing a Remote Database Through a Web Interface
Accessing a remote database through a web interface is similar to accessing one through a local network. The main difference is that all requests to the database from the user are routed through the web server (see Figure 23.3).
Figure 23.3. A web interface to a remote database.
You can see in Figure 23.3 that an end user accesses a database through a web interface by first invoking a web browser. The web browser connects to a particular URL, determined by the location of the web server. The web server authenticates user access and sends the user request, perhaps a query, to the remote database, which might also verify user authenticity. The database server then returns the results to the web server, which displays the results on the user’s web browser. Using a firewall can control unauthorized access to a particular server.
Watch Out!: Be Mindful of Security Concerns with the Internet
Be careful what information you make available on the Web. Always take precautions to properly implement security at all appropriate levels; that might include the web server, the host server, and the remote database. Be especially careful with Privacy Act data, such as individuals’ Social Security numbers; protect that data, and don’t broadcast it over the Web.
A firewall is a security mechanism that ensures against unauthorized connections to and from a server. One or multiple firewalls can be enabled to patrol access to a database or server.
Additionally, certain database implementations allow you to restrict access to them via IP address. This provides another layer of protection, because you can limit your traffic that has access to the database to the actual set of web servers that are acting as the application layer.
SQL and the Internet
You can embed SQL or use it in conjunction with programming languages such as C# and Java. You can also embed SQL in Internet programming languages, such as Java and ASP.NET. Text from Hypertext Markup Language (HTML), another Internet language, can be translated into SQL to send a query to a remote database from a Web front end. After the database resolves the query, the output is translated back into HTML and displayed on the web browser of the individual executing the query. The following sections discuss the use of SQL on the Internet.
Making Data Available to Customers Worldwide
With the advent of the Internet, data became available to customers and vendors worldwide. The data is normally available for read-only access through a front-end tool.
The data that is available to customers can contain general customer information, product information, invoice information, current orders, back orders, and other pertinent information. Private information, such as corporate strategies and employee information, should not be available.
Home web pages on the Internet have become nearly a necessity for companies that want to keep pace with their competition. A web page is a powerful tool that can tell surfers all about a company—its services, products, and other information—with little overhead.
Making Data Available to Employees and Privileged Customers
A database can be made accessible, through the Internet or a company’s intranet, to employees or its customers. Using Internet technologies is a valuable communication asset for keeping employees informed about company policies, benefits, training, and so on. However, you must be careful when making information available to web users. Confidential corporate or individual information should not be accessible on the Web if possible. Additionally, only a subset, or copy of a subset of a database, should be accessible online. The main production database(s) should be protected at all costs.
Did You Know?: Internet Security Is a Far Less Stable Platform
Database security is much more stable than security on the Internet because database security can be fine-tuned down to the specific levels of the data contained in the system. Although you can implement some security features for data access through the Internet, these are generally limited and not as easily changed as those on the database. Always be sure to use the security features available to you through your database server.
SQL and the Intranet
IBM originally created SQL for use between databases located on mainframe computers and the users on client machines. The users were connected to the mainframes via a LAN. SQL was adopted as the standard language of communication between databases and users. An intranet is basically a small Internet. The main difference is that an intranet is for a single organization’s use, whereas the Internet is accessible to the general public. The user (client) interface in an intranet remains the same as that in a client/server environment. SQL requests are routed through the web server and languages (such as HTML) before being directed to the database for evaluation. An intranet is primarily used for inner-corporate applications, documents, forms, web pages, and email.
SQL requests made through the Internet must be extremely cognizant of performance. In these scenarios, not only must the data be retrieved from the database, but it must be presented to the user through her browser. This normally involves transforming the data into some kind of HTML-compliant code to be displayed on the user’s browser. The web connection might be slower than a normal intranet connection; therefore, the sending of the data back and forth might be slower as well.
Security should play an important role in a database implementation that is exposed via the web. A couple of considerations must be taken into account to ensure that your data is protected. First, if the data is exposed over public networks, you must try to ensure that the data is protected from outside sources that may try to pick up that traffic. Normally, data is transferred in plain text format so that anyone can read it. You might consider as part of your security implementation use of Secure Socket Layer (SSL) to protect the communication. This method uses a certificate to encrypt the data between the client and the application and is typically identified by a website beginning with HTTPS, with the S on the end standing for secure.
Another typical consideration is protecting against unintended data entry through data validation. This can be simply from the user or application entering the wrong type of data into the wrong field or something more nefarious such as an SQL injection attack, where a hacker tries to inject his own SQL code onto the database to be run.
The best way to protect against these types of problems is to restrict access for the user accounts accessing the database from the application. A good way to accomplish this is trying to use stored procedures and functions whenever possibly for the calls against the database. This gives you more control over how the data gets out of the system and how the data gets in. Additionally, it allows you to perform whatever data validation steps may be necessary from the DBA’s point of view to ensure that the data remains consistent.
Summary
Some concepts behind deploying SQL and database applications to the Internet were discussed in this hour. Companies need to remain competitive. To keep up with the rest of the world, it has proven beneficial—almost mandatory—to obtain a presence on the World Wide Web. In establishing this presence, applications must be developed and even migrated from client/server systems to the Internet on a web server. One of the greatest concerns when publishing any kind or any amount of corporate data on the Web is security. Security must be considered, adhered to, and strictly enforced.
This hour discussed accessing remote databases across local networks as well as over the Internet. Each major method for accessing any type of a remote database requires the use of the network and protocol adapters used to translate requests to the database. This has been a broad overview of the application of SQL over local networks, company intranets, and the Internet. After digesting a few quiz and exercise questions, you should be ready to venture into the last hour of your journey through SQL.
Q&A
Q. Why is it important to know if your data is accessed over a public network via the Internet?
A. The data that is sent between a client and a web application is often just plain text. That means that anyone could intercept the traffic and be able to see exactly what the individual saw, such as sensitive data like Social Security numbers or account numbers. You need to encrypt data whenever possible.
Q. Is a back-end database for a web application any different from a back-end database for a client/server system?
A. The back-end database itself for a web application is not necessarily different from that of a client/server system. However, other requirements must be met to implement a web-based application. For example, a web server is used to access the database with a web application. With a web application, end users do not typically connect directly to the database.
Workshop
The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
1. Can a database on a server be accessed from another server?
2. What can a company use to disseminate information to its own employees?
3. Products that allow connections to databases are called what?
4. Can SQL be embedded into Internet programming languages?
5. How is a remote database accessed through a web application?
Exercises
1. Connect to the Internet and look at various companies’ home pages. If your own company has a home page, compare it to the competition’s home pages. Ask yourself these questions about the pages:
• Does any of the page content appear to be dynamic?
• What pages or areas on pages might be data from a back-end database?
• Do there appear to be security mechanisms on the web page? Can a login be entered to access data that might be stored in a database?
• Most modern browsers enable you to view the source code of the page returned. Use your web browser to view the source code. Is there any code that would give you a hint as to what the back-end database is?
• If you uncovered any information in the page’s code, such as a server name or a database username, would you consider this a security flaw?
2. Visit the following websites and browse through the content, latest technologies, and companies’ use of data on the Web (data that appears to be derived from a database):