Advantages of Stored Programs

Let's look at each of the advantages of stored programs in turn.

12.2.1. They Enhance Database Security

We'll see in Chapter 18 how the default security mode of stored programs (SQL SECURITY DEFINER) permits a stored program to execute SQL statements even if the calling database account lacks the security privileges to execute these statements as native SQL. By granting a database account access to stored programs onlywithout granting direct permissions on underlying tableswe can ensure that access to the database occurs only in the manner defined by our stored programs. We can also ensure that these SQL statements are surrounded by whatever business rule validation or logging is required. This concept is explained in more detail in Chapter 18.

In the event that an application account is compromised (for instance, the password is "cracked"), the attacker will still only be able to execute our stored programs, as opposed to being able to run any ad hoc SQL. While such a situation constitutes a severe security breach, at least we are assured that the hacker will be subject to the same checks and logging as a normal application user. The hacker will also be denied the opportunity to retrieve information about the underlying database schema, which will hinder attempts to perform further malicious activities.

The security advantages of stored programs are a powerful motivation to include stored programs in our applications, especially with today's increasing focus on securing the underlying database. However, the security advantages of stored programs can only be realized if stored programs are used exclusively within an application. This is because, to be fully effective, this strategy requires that the database connection account have no direct access to the underlying database tables; hence, this account must perform operations only through stored programs. One alternative to this approach is to grant read-only access to the underlying tables, and then use stored programs exclusively for update operations. At least then, a malicious user will not be able to make arbitrary changes to the data.

Another security advantage inherent in stored programs is their resistance to SQL injection attacks. As we will see in Chapter 18, a SQL injection attack can occur when a malicious user manages to "inject" SQL code into the SQL code being constructed by the application. Stored programs do not offer the only protection against SQL injection attacks, but applications that rely exclusively on stored programs to interact with the database are virtually immune to this type of attack (provided that those stored programs do not themselves build dynamic SQL strings without fully validating their inputs).

12.2.2. They Provide a Mechanism for Data Abstraction

It is generally a good practice to separate your data access code from your business logic and presentation logic. Data access routines are often used by multiple program modules, and are likely to be maintained by a separate group of developers. A very common scenario requires changes to the underlying data structures, while minimizing the impact on higher-level logic. Data abstraction makes this much easier to accomplish.

The use of stored programs provides a convenient way of implementing a data access layer. By creating a set of stored programs that implement, all of the data access routines required by the application, we are effectively building an API for the application to use for all database interactions.

12.2.3. They Reduce Network Traffic

Stored programs can radically improve application performance by reducing network traffic in certain situations. Several such situations are described in this section.

One scenario involves an application that may need to accept input from the end user, read some data in the database, decide what statement to execute next, retrieve a result, make a decision, execute some SQL, and so on. If the application code is written entirely outside of the database, each of these steps would require a network round trip between the database and the application. The time taken to perform these network trips can easily dominate overall user response time.

Consider a typical interaction between a bank customer and an ATM machine. The user requests a transfer of funds between two accounts. The application must retrieve the balance of each account from the database, check withdrawal limits and possibly other policy information, issue the relevant UPDATE statements, and finally issue a COMMITall before advising the customer that the transaction has succeeded. Even for this relatively simple interaction, at least six separate database queries must be issued, each with its own network round trip between the application server and the database. Figure 12-1 shows the sequence of interactions that would be required without a stored program.

Figure 12-1. Network round trips without a stored program

On the other hand, if a stored program is used to implement the funds transfer logic, only a single database interaction is required. The stored program takes responsibility for checking balances, withdrawal limits, and so on. Figure 12-2 illustrates the reduction in network round trips that occurs as a result.

Figure 12-2. Network round trips involving a stored program

Network round trips can also become significant when an application is required to perform some kind of aggregate processing on very large record sets in the database. If the application needs to (for instance) retrieve millions of rows in order to calculate some sort of business metric that cannot easily be computed using native SQL (average time to complete an order, for instance), then a very large number of round trips can result. In such a case, the network delay may again become the dominant factor in application response time. Performing the calculations in a stored program will reduce network overhead, which might reduce overall response timebut make sure you take into account the considerations outlined in the section "They Can Be Computationally Inferior" later in this chapter. We provide an example of a stored program reducing network traffic in Chapter 22.

12.2.4. They Allow for Common Routines Across Multiple Application Types

While it is commonplace for a MySQL database to be at the service of a single application, it is not at all uncommon for multiple applications to share a single database. These applications may run on different machines and be written in different languages; it may be hardor impossiblefor these applications to share code. Implementing common code in stored programs may allow these applications to share critical common routines.

For instance, in Chapter 8 we created a procedure called txfer_funds that performed a transactional-safe, logged transfer of funds between two accounts. Some versions of the stored procedure contained code for handling deadlocks and an optimistic locking strategy. Now, in a banking application, a transfer of funds transactions might originate from multiple sources, including a bank teller's console, an Internet browser, an ATM, or a phone banking application. Each of these applications could conceivably have its own database access code written in largely incompatible languages, and, without stored programs, we might have to replicate the transaction logicincluding logging, deadlock handling, and optimistic locking strategiesin multiple places in multiple languages.

12.2.5. They Facilitate Division of Duties

It is reasonably commonplace for the responsibility for coding application logic to be held by one set of developers and the responsibility for database design and access routines to be held by a different set of developers. These two groups may have different skill sets, and application development efficiency may be enhanced if the database developers are able to implement the data access routines directly in MySQL using the stored program language.

12.2.6. They May Provide Portability

While all relational databases implement a common set of SQL syntaxtypically SQL99 entry-level or similareach RDBMS offers proprietary extensions to this standard SQL. If you are attempting to write an application that is designed to be independent of the underlying RDBMS vendor, or if you want to avoid RDBMS vendor lock-in, you will probably want to avoid these extensions in your application. However, using these extensions is highly desirable if you want to optimize your use of the underlying database. For instance, in MySQL, you will often want to employ MySQL hints, execute non-ANSI statements such as LOCK TABLES, or use the REPLACE statement.

Using stored programs can help you avoid RDBMS-dependent code in your application layer while allowing you to continue to take advantage of RDBMS-specific optimizations. In theorybut only sometimes in practicestored program calls against different databases can be made to look and behave identically from the application's perspective. Of course, the underlying stored program code will need to be rewritten for each RDBMS, but at least your application code will be relatively portable.

Unfortunately, not all RDBMSs implement stored programs in a consistent manner. This limits the portability that stored programs can offer. We discuss this in more detail in the section "They Do Not Provide Portability" later in this chapter.