In addition to keeping all of our software up-to-date, there are a few things we can do to keep our databases more secure. While a complete treatment of security would require a full book for each of the database servers, we will give you some general strategies here.
Users and the Permissions System
Spend time getting to know the authentication and permissions system of the database server that you have chosen to use. A surprising number of database attacks succeed simply because people have not taken the time to make sure the system is secure.
Make sure that all accounts have passwords. One of the first things you do with any database server is make sure that the database super user (root) has a password. Ensure that these passwords do not contain words from the dictionary: Even passwords such as 44horseA are less secure than passwords such as FI93!!xl2@. If you are worried about the ease with which passwords can be memorized, you can use the first letter of every word in a particular sentence with a pattern of capitalization, such as IwTbOtIwTwOt, from "It was the best of times, it was the worst of times" (A Tale of Two Cities, Charles Dickens).
Many databases (including older versions of MySQL) install an anonymous user with more privileges than you would probably like. While investigating and becoming comfortable with the permissions system, make sure that any default accounts do exactly what you want them to and remove those that do not.
Make sure that only the super user account has access to the permissions tables and administrative databases. Other accounts should only have permissions to access or modify the databases or tables they need.
To test this, try the following and verify that an error occurs:
Until you have tried each of these, you cannot be sure that your system's authentication system is adequately protected.
Sending Data to the Server
As we have repeatedly stated throughout this book, you should never send unfiltered data to the server. By using the various functions provided by the database extensions to escape strings (such as mysqli_real_escape_string or mssql_escape_string), we give ourselves a basic level of protection.
However, we should do more than rely on this function; we should do data type checking for each field from an input form. If we have a username field, we want to be sure that it doesn't contain kilobytes of data or characters that we do not want to see in usernames. By doing this validation in code, we can provide better error messages to reduce the security risk to our databases. Similarly, for numeric and date/time data, we can verify the relative sanity of values before passing them to the server.
Finally, we can use prepared statements on servers where it is available to do much of the escaping for us and make sure that everything is in quotes where necessary.
There are tests we can do to make sure our database is correctly handling our data:
Connecting to the Server
There are a few ways we can keep our database servers secure through our control of connections to them. One of the easiest is to restrict from where people are allowed to connect. Many of the permissions systems used in the various database management systems allow you to specify not only a username and password for a user, but also from which machines they are allowed to connect. If the database server and web server/PHP engine are on the same machine, then it makes sense to only allow connections from localhost, or the IP address used by that machine. If our web server is always on one computer, there is nothing wrong with only allowing users to connect to the database from that machine.
Many database servers are incorporating the ability to connect to them via encrypted connections (usually using the SSL protocol) in their features. If you have to connect with a database server over the open Internet, you want to use an encrypted connection. If it is not available, consider using a product that does tunneling, a fiendishly clever idea in which a secure connection is made from one machine to another, and TCP/IP ports (such as port 80 for HTTP or 25 for SMTP) are routed over this secure connection to the other computer, which sees the traffic as local.
Finally, you should be sure that the number of connections that the database server is configured to handle is greater than the number of connections that the web server and PHP are going to spawn. We mentioned earlier that the 1.3.x series of Apache HTTP Server is able to launch up to 150 servers by default. With the default number of connections allowed in my.ini for MySQL set to 100, we already have a mismatched configuration.
To fix this, we should make the following modification in our my.ini file:
We have allocated one extra since MySQL always saves one of the connections for the root user. That way, even when the server is fully loaded, the super user can log in and take action.
Running the Server
When running the database server, there are also a number of actions we can take to keep it safe. First, we should never run it as the super user (root on Unix, administrator on Windows). If the server ever became compromised, our entire system would be in jeopardy. In fact, MySQL will refuse to run as the super user unless you force it to (which is discouraged).
Once you have set up the database software, most programs have you change the ownership and permissions on the database directories and files to keep them away from prying eyes. Make sure you do this and check that the database files are still not owned by the super user (in which case the nonsuper user database server process might not write to its own database files).
Finally, instead of creating users with a broad set of permissions because "they might need that someday," create them with the least number possible and add permissions only when they are absolutely needed.