When a program uses a DBMS, it transmits its requests through a specially designed API. Most use a text-oriented interface based on the Structured Query Language (SQL), a standard in the database community. SQL supports queries to search and extract data from the database, plus other operations to add or update data in the database.

The DBMS generally operates as a separate process. Other programs connect to the DBMS through a socket-like interface. The SQL commands arrive via a connection between the program and the DBMS. When the program establishes the connection, it provides a user ID and selects the database to use. The DBMS will check the access permissions for that user ID and establish the connection if allowed.

FIGURE 15.22 shows a typical SQL query using the SELECT operation. We choose the arguments to SELECT according to what data we want to extract from the database. In the figure, the operation takes place on the database from Figure 15.21. A query’s result usually produces a data table.

The SELECT example contains four arguments. The first argument lists the fields returned in the result. There may be one or more fields returned. If the query indicates “*” instead of a list of fields, then the query returns all fields.

The second argument is optional. The argument, prefixed by INTO, names a table that receives the query’s results. The third argument, prefixed by FROM, indicates the table containing the data to be queried. A single database may contain several tables, so this argument always appears.

The final argument, prefixed by WHERE, provides the criteria for selecting records to include in the answer. If we omit WHERE, then the query returns all records and only includes the fields listed in the first argument. The WHERE clause usually compares the contents of a named field in a particular record and accepts that record if the comparison succeeds. A single WHERE clause may perform several comparisons, combined with the logical operators “AND” and “OR.”

If we perform the query in Figure 15.22 on the sample tables in Figure 15.21, the result yields a table named Temp. The table contains three records, each with a single ArticleNo field.

Many modern organizations maintain a data warehouse, which contains a copy of all of the organization’s transaction data for the purpose of analysis and reporting. The analysis process is sometimes called data mining, in which analysts seek insight into the organization’s operations by studying large quantities of detailed data.

Although not all organizations structure their databases into a data warehouse, all organizations rely heavily on collecting and analyzing enterprise data. To publish an accurate balance sheet for the corporate annual report, the enterprise must systematically collect and analyze the enterprise’s financial information.

In general, database security relies on the same general concepts and mechanisms as file system security. Most databases require identification and authentication before they accept queries or other operations from remote sources. Sophisticated database systems also maintain “database user” tables that associate individual users with specific access rights. On a database, access rights are similar to those of files and directories described in Section 3.1: the right to create, modify, or delete items and collections thereof.

Databases may, however, enforce more detailed access restrictions. Even though access controls to files might be analogous to those for accessing tables, a database also may restrict a user’s right to see or modify particular fields in particular tables. This is similar to allowing a user to modify a spreadsheet, but restricting which columns may be modified.

Important challenges in database security are embodied in the terms aggregation and inference. If we forbid Eve from looking at certain fields, but still allow her to make queries including those fields, then she may be able to infer the values of hidden fields. For example, we might hide salaries from Eve, but she may still infer salary information by making clever queries.

Most modern websites provide different services and capabilities to different people. The general public may see a site’s general contents, but they aren’t allowed to add information or make other changes. Most sites require visitors to register and identify themselves before they are allowed to modify the site. (As of 2019, Wikipedia is probably the most famous of the few internet sites where anonymous visitors may make changes.) When revisiting most sites, registered users must authenticate themselves; this typically requires a password.

The password database, like other databases on a CMS, resides in a database table. The Users table shown earlier in Figure 15.21 contains the basic data we require: a user ID and a password. This example does not use hashed passwords. A properly designed website should hash its passwords.

In this example, Alice logs into a CMS-based website. The CMS performs the operation in two parts. The first part displays a web page to collect the user ID and performs a database query to collect the user’s database record. The second part displays a separate web page to collect the password and performs a separate database query to verify it.

FIGURE 15.23 follows the first part of the login process from Alice’s browser through the CMS to the database. The process starts in the upper left of the figure. After Alice navigates to the login screen the following steps occur:

1. Alice types her user ID “alice” into the login screen and presses “Enter.” The browser has collected Alice’s keystrokes. When it receives the “Enter” key, it starts processing the user ID.

2. The browser constructs a POST command to transmit Alice’s user ID to the server. The POST command executes the script “checkuserid.php” and passes the value “alice” for the argument userid.

   Although not shown here, the POST command also carries a cookie that ties this POST command to the password Alice will enter later.

3. The server receives the POST command and runs the “checkuserid” script. This script takes the argument value assigned to userid and inserts it into a database SELECT command. The script sends the SELECT command to the DBMS.

4. The DBMS executes the SELECT command. The WHERE clause locates Alice’s user record. The DBMS saves the result in a temporary table named Login12. If Login12 contains no records, then the login fails. This happens if the SELECT command doesn’t find any records with a matching user ID.

If the Login12 table contains Alice’s record, then the CMS continues with the second part of the login: It sends Alice’s client the password page. The page provides a cookie that associates Alice’s browser with the Login12 temporary database table.

The CMS performs the same general steps a second time to check Alice’s password. The password travels via a POST command and the CMS embeds it in a SELECT command. The CMS uses the cookie from Alice’s POST command to retrieve the Login12 table and complete the login check.

Eve will now masquerade as Alice by using a command injection attack. We use the same CMS login process described in the previous section. This time, however, we will omit password hashing.

When Eve performs the first step of the login process, she provides Alice’s user ID. The process unfolds exactly as shown in Figure 15.23. The server creates the temporary Login12 table and awaits Alice’s password. FIGURE 15.24 follows the command injection attack as it travels from the client to the DBMS:

1. The website prompts for Alice’s password. Instead of typing in a password, Eve types in a specially crafted text string. The first letter “x” represents a guess at Alice’s password, but it won’t matter what password Eve types there.

2. The browser takes Eve’s text string and embeds it in a POST command, assigned to the pw argument. The actual text looks like code, because the browser converts it to omit all blanks and URL-like punctuation. The “%27” represents a single quote character and the “+” signs stand in for individual space characters. The POST command also carries a cookie to tell the server which table contains its login record. Most systems do this indirectly; they put a session identifier in the cookie and store the table identifier with other session information.

3. The CMS converts the POST command into a SELECT command to check Alice’s password. Eve’s text string appears as the underlined text. The CMS sends the command to the DBMS.

4. The DBMS runs the SELECT command. Eve’s specially crafted text string recasts the WHERE clause into an expression that is always true, regardless of Alice’s password.

This type of attack subverts the Chain of Control by tricking the DBMS into interpreting part of the password as an SQL expression. We saw how a buffer overflow redirects the CPU to execute the wrong instructions (Section 2.3.1); here, the command insertion attack redirects the DBMS to perform the wrong password comparison.

FIGURE 15.25 shows the format of the SELECT command used to verify Alice’s password. The FROM clause is filled in from cookie information. The WHERE is completed with the password text.

If the server hashes passwords, it performs the hash before inserting the value into the SELECT command. It is not unusual for a CMS to omit password hashing; some programmers believe it’s safest to handle passwords as little as possible. Imprudence leads to disaster here.

Eve’s injection attack exploits a feature of the SQL WHERE clause: The clause may perform a simple comparison between a single field and a data value, or it may make several comparisons and use the combined results. Eve’s attack transforms the WHERE clause from a simple password comparison into a two-part comparison.

On the left of FIGURE 15.26, we see the simple comparison that happens when Alice types her actual password. On the right, we see the two-part comparison that Eve injects with the typed password.

The two-part comparison uses the “OR” operation to combine the results of two different comparisons. The first part compares the password against an arbitrary value and will usually be logically false—but the second part of the two-part comparison is always logically true. The “OR” operation combines the two comparisons; if either is true, then the comparison yields a match.

This example uses a very simple technique to pass the temporary table name from the first part of the login to the second part: It places the table name in a cookie. The second step copies the cookie name into the SELECT statement.

This simple approach yields another avenue for command injection. The semicolon character (“;”) marks the end of a command in SQL. Assume that Eve could intercept the HTTP text containing the password, modify it, and send it on its way. She might also attack successfully if she appends a semicolon to the table name and the semicolon is copied into the SELECT command. SQL will treat the semicolon as the end of the statement and may signal success. The dangling WHERE clause may or may not produce an error, depending on the DBMS.

This particular example of command injection relied on plaintext passwords. If the CMS used password hashing, the command injection itself would have been scrambled, defeating the injection.

Large websites, however, may have hundreds or thousands of other text fields. Hashing passwords won’t prevent command injections in those other fields. A clever command injection may retrieve any information from the site’s database, posing serious privacy risks for customers as well as security risks for the site.