Database Security
Nowadays, databases are cardinal components of any web based
application by enabling websites to provide varying dynamic
content. Since very sensitive or secret information can be
stored in a database, you should strongly consider protecting
your databases.
To retrieve or to store any information you need to connect
to the database, send a legitimate query, fetch the result,
and close the connection. Nowadays, the commonly used query
language in this interaction is the Structured Query Language
(SQL). See how an attacker can tamper with an SQL query.
As you can surmise, PHP cannot protect your database by
itself. The following sections aim to be an introduction
into the very basics of how to access and manipulate databases
within PHP scripts.
Keep in mind this simple rule: defense in depth. The more
places you take action to increase the protection of your
database, the less probability of an attacker succeeding
in exposing or abusing any stored information. Good design
of the database schema and the application deals with your
greatest fears.
Designing Databases
The first step is always to create the database, unless
you want to use one from a third party. When a database
is created, it is assigned to an owner, who executed the
creation statement. Usually, only the owner (or a superuser)
can do anything with the objects in that database, and in
order to allow other users to use it, privileges must be
granted.
Applications should never connect to the database as its
owner or a superuser, because these users can execute any
query at will, for example, modifying the schema (e.g. dropping
tables) or deleting its entire content.
You may create different database users for every aspect
of your application with very limited rights to database
objects. The most required privileges should be granted
only, and avoid that the same user can interact with the
database in different use cases. This means that if intruders
gain access to your database using your applications credentials,
they can only effect as many changes as your application
can.
You are encouraged not to implement all the business logic
in the web application (i.e. your script), instead do it
in the database schema using views, triggers or rules. If
the system evolves, new ports will be intended to open to
the database, and you have to re-implement the logic in
each separate database client. Over and above, triggers
can be used to transparently and automatically handle fields,
which often provides insight when debugging problems with
your application or tracing back transactions.
