Basically, it means that you must store the username and password to the database in a directory that the web server cannot access directly. For example, if you use PHP, and your Apache configuration points to /project-root/www
, you could store the login information in /project-root/config/db.php
; if you store it in, say, /project-root/www/config/db.php
. Assuming that your server configuration isn't broken, this means that db.php cannot be accessed over the internet.
You also need to get the permissions for the config file right. The account the web server runs under (ideally a dedicated account for your application) should be the only one with any kind of access to the config file. In a UNIX environment, assuming Apache is configured to run your application's virtual host under your own account, 600 or even 400 is a suitable permission set. This ensures that other users with access to the same machine (except root) cannot read your configuration file.
Additionally, you'll want to take a few more security measures:
- Create a database user specially for the application, and give it just enough permissions to do its job (SELECT, UPDATE, INSERT and DELETE on the tables it needs is usually enough). Do not give the user any permissions on any other databases: if your application is compromised, you can at least mitigate the risk for other databases. If the user also has permissions on system databases, a successful attacker can possibly elevate permissions by creating new users through the system tabls and granting them more permissions. Specifically, never ever use administrative database accounts for production websites.
- Use a strong password. Since you won't have to manually type the password a lot, a sequence of, say, 32 random bytes, converted to base64, should make for a good enough password.
- If the database runs on the same machine as the web server, configure it to refuse connections from anything but localhost. If it runs on a dedicated server, use a whitelist of allowed hosts, and put the whole thing behind a properly configured firewall.
- If you use a quasi-interpreted language such as PHP (where the source code sits on the web server), it is a good idea to write your configuration as source code too, and use an appropriate filename extension. This way, if someone manages to request the configuration file over the internet, it will be executed, and produce a blank page, still hiding the database credentials from the attacker (this is a last straw though; you should never rely on this alone).
And of course, you need to check your application for general security flaws, especially SQL injection and path traversal: these are the most common ways of getting at your database.
If the application is really sensitive, you need to take more precautions, such as encrypting configuration files, but for your average low-profile website, this should do the trick.
There are lots of ways to persist data in a desktop application.
A database is one choice. You would probably have to provide an installer unless you're using a file based database such as SQLite.
You might also just write to a file - either a text file, an XML file, serializing objects, etc.
On a side note, while it is possible to store images in a database, it's been my experience that it's more trouble than it's worth. I tend to write them as files to a folder then track them some other way (database, etc).
Best Answer
The complex error handling is usually provided by libraries, and if your application is well-behaved, it shouldn't be needed too much anyway. The complex error handling required during attempting to create a new connection is far more likely to be invoked.
Reseting the state of the connection is trivial and inexpensive; it's built into all modern connection managers that I'm aware of.
Nothing can reduce the overheads of creating a new connection to a database.