16.7 Connecting to Databases

only for RuBoard - do not distribute or recompile

It is common for a CGI program or script to connect to databases that are external to the web server. External databases can be used for many purposes, such as storing user preferences, implementing shopping carts, and even order processing. When the script runs, it opens a connection to the database, issues a query, gets the result, and then uses the result to formulate a response to the user. On some systems, a new database connection is created each time a new script is run. Other systems maintain a small number of persistent connections which are cached.

Database-backed web sites give a tremendous amount of power and flexibility to the web designer. Unfortunately, this approach can also reduce the overall security of the system: many of the security incidents mentioned earlier in this book happened because an attacker was able to execute arbitrary SQL commands on the database server and view the results. (For example, recall the story of the attacker who was able to obtain tens of thousands of credit card numbers from an e-commerce site.) If you decide to deploy a database server to supplement your web site, it is important to be sure that the server will be deployed and used securely.

16.7.1 Protect Account Information

Before the database server provides results to the script running on the web server, the server needs to authenticate the script to make sure it is authorized to access the information. Most databases use a simple username/password for account authentication, which means the script needs to have a valid username/password and present this information to the database server each time a request is issued.

Among many developers it is common practice to simply code the username and password into the scripts that require access to the database server. Unfortunately, this practice has several problems:

If an attacker is able to view the script, the attacker will learn the username and password.
If many scripts require access to the username and password, then it must be stored in several scripts.
Changing the username and password requires modifying the script. When the script is modified, other changes may be made inadvertently.

Instead of storing the database username and password in the script, a better approach is to store this information in a file on the web server. This approach isolates the authentication information from the script that is performing the database request, which improves both maintainability and security. The server script then opens this file and reads the username and password prior to issuing a database request.

16.7.2 Use Filtering and Quoting to Screen Out Raw SQL

As we mentioned earlier in this chapter, it is extremely important to filter all data from the user to make sure that it contains only allowable characters. When working with SQL servers, it is further important to properly quote data provided by the user before sending the data to the server. These procedures are used to prevent users from constructing their own SQL commands and sending that data to the SQL server.

For example, if you have a web form that asks a person for his name and then stores this information into a database, it might be tempting to simply take the person's name from a field, put that field into a variable called $name, and then construct a SQL command using this variable. Consider:

$name = param('name'); sql_send("insert into names (name) value ('$name');");

Unfortunately, this is not safe: an attacker who has knowledge of your application can provide a specially crafted name that results in arbitrary SQL commands being executed. Consider this name:

Simson Garfinkel')"; delete from names;

When this name is used to build the SQL command, the resultant string will actually be interpreted as three commands one that makes an insertion into the database, a second that deletes all of the data in the names table, and a third that contains a syntax error:

insert into names (name) value ('Simson Garfinkel')"; delete from names; ');

Given this text, most SQL servers will insert a record into the names table, delete all of the data, and then report a SQL error.

The way to protect scripts from these kinds of attacks is to make sure that you first carefully filter incoming data, and that you next quote all of the remaining data properly before sending it to the SQL server.

Quoting is best done with a separate function that is always called whenever any string is sent to the SQL server. If you are using the Perl language, a nifty approach is to have the quote function automatically add the SQL quotes as well. Here is such a function:

sub squote {     my $ret = $_[0];     $ret =~ s/\'\\\'/g;     return '.' . $ret . '.'; }

You could then use this function in the previous example, like this:

$qname = squote(param('name')); sql_send("insert into names (name) value ($qname);");

Another approach is to precompile your SQL queries using variable binding. Variable binding allows you to precompile SQL queries with placeholders instead of actual variables. To return to our original example, you might compile the system using a hypothetical SQL interface that uses the @ sign as a placeholder for variables:

$func = sql_compile("insert into name (name) value (@)");

You might then execute this function with some other hypothetical function:

$name = param('name'); sql_bind($func,1,$name);      # bind the variable name to the first variable sql_exec($func);              # execute the bound function

Different systems will have different syntaxes and APIs for compiling, binding, and executing SQL queries. Some SQL client libraries, such as the MySQL client library, don't support variable binding at all, so it's important to understand the approaches mentioned earlier.

16.7.3 Protect the Database Itself

Finally, it is important that you protect the database server itself:

Configure your firewall or network topology so that it is impossible for people outside your organization to access your database server. For example, you may wish to set up your web server with two Ethernet adapters one that connects to the Internet, and one that connects to a small firewall appliance that, in turn, connects to your database server. The firewall should be set up so that only database queries can pass between the web server and the database server. This setup is shown in Figure 16-5.
Make sure logins on the database server are limited. The individuals with login capabilities should be the system administrator and the database administrator.
Make sure that the database server is backed up, physically protected, and maintained in the same way as your other secure servers.

Figure 16-5. Connecting a database server and a web server to the Internet and your internal network with multiple firewalls.

only for RuBoard - do not distribute or recompile