Nowadays, databases are cardinal components of any web
based application by enabling websites to provide varying
dynamic content. Since very sensitive or secret informations
can be stored in such database, you should strongly consider
to protect them somehow.
To retrieve or to store any information you need to
connect to the database, send a legitimate query, fetch the
result, and close the connecion. 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 realize, 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: defence in depth. In the
more place you take the more action to increase the
protection of your database, the less probability of that an
attacker succeeds, and exposes or abuse any stored secret
information. Good design of the database schema and the
application deals with your greatest fears.
The first step is always to create the database,
unless you want to use an existing third party's one. 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 one of
these 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 to
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 reimplement 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.
You may want to estabilish the connections over SSL to
encrypt client/server communications for increased
security, or you can use ssh to encrypt the network
connection between clients and the database server. If
either of them is done, then monitoring your traffic and
gaining informations in this way will be a hard work.
SSL/SSH protects data travelling from the client to
the server, SSL/SSH does not protect the persistent data
stored in a database. SSL is an on-the-wire protocol.
Once an attacker gains access to your database
directly (bypassing the webserver), the stored sensitive
data may be exposed or misused, unless the information is
protected by the database itself. Encrypting the data is a
good way to mitigate this threat, but very few databases
offer this type of data encryption.
The easiest way to work around this problem is to
first create your own encryption package, and then use it
from within your PHP scripts. PHP can assist you in this
case with its several extensions, such as Mcrypt and
Mhash, covering a wide variety of encryption
algorithms. The script encrypts the data be stored first,
and decrypts it when retrieving. See the references for
further examples how encryption works.
In case of truly hidden data, if its raw
representation is not needed (i.e. not be displayed),
hashing may be also taken into consideration. The
well-known example for the hashing is storing the MD5 hash
of a password in a database, instead of the password
itself. See also crypt() and
md5().
Many web developers are unaware of how SQL queries can
be tampered with, and assume that an SQL query is a trusted
command. It means that SQL queries are able to circumvent
access controls, thereby bypassing standard authentication
and authorization checks, and sometimes SQL queries even
may allow access to host operating system level
commands.
Direct SQL Command Injection is a technique where an
attacker creates or alters existing SQL commands to expose
hidden data, or to override valuable ones, or even to
execute dangerous system level commands on the database
host. This is accomplished by the application taking user
input and combining it with static parameters to build a
SQL query. The following examples are based on true
stories, unfortunately.
Owing to the lack of input validation and connecting
to the database on behalf of a superuser or the one who can
create users, the attacker may create a superuser in your
database.
|
// in case of PostgreSQL 0; insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passwd) select 'crack', usesysid, 't','t','crack' from pg_shadow where usename='postgres'; -- // in case of MySQL 0; UPDATE user SET Password=PASSWORD('crack') WHERE user='root'; FLUSH PRIVILEGES; |
注: It is common technique to force the SQL parser to ignore the rest of the query written by the developer with -- which is the comment sign in SQL.
A feasible way to gain passwords is to circumvent your
search result pages. What the attacker needs only is to try
if there is any submitted variable used in SQL statement
which is not handled properly. These filters can be set
commonly in a preceding form to customize WHERE, ORDER BY, LIMIT and OFFSET clauses in
SELECT statements. If your database supports the UNION construct, the attacker may try
to append an entire query to the original one to list
passwords from an arbitrary table. Using encrypted password
fields is strongly encouraged.
SQL UPDATEs are also subject to attacking your
database. These queries are also threatened by chopping and
appending an entirely new query to it. But the attacker
might fiddle with the SET clause.
In this case some schema information must be possessed to
manipulate the query successfully. This can be acquired by
examing the form variable names, or just simply brute
forcing. There are not so many naming convention for fields
storing passwords or usernames.
// $uid == ' or uid like'%admin%'; -- $query = "UPDATE usertable SET pwd='...' WHERE uid='' or uid like '%admin%'; --"; // $pwd == "hehehe', admin='yes', trusted=100 " $query = "UPDATE usertable SET pwd='hehehe', admin='yes', trusted=100 WHERE ...;" |
A frightening example how operating system level
commands can be accessed on some database hosts.
$query = "SELECT * FROM products WHERE id LIKE '%a%' exec master..xp_cmdshell 'net user test testpass /ADD'--"; $result = mssql_query($query); |
注: Some of the examples above is tied to a specific database server. This does not mean that a similar attack is impossible against other products. Your database server may be so vulnerable in other manner.
You may plead that the attacker must possess a piece
of information about the database schema in most
examples. You are right, but you never know when and how
it can be taken out, and if it happens, your database may
be exposed. If you are using an open source, or publicly
available database handling package, which may belong to
a content management system or forum, the intruders
easily produce a copy of a piece of your code. It may be
also a security risk if it is a poorly designed one.
These attacks are mainly based on exploiting the
code not being written with security in mind. Never trust
on any kind of input, especially which comes from the
client side, even though it comes from a select box, a
hidden input field or a cookie. The first example shows
that such a blameless query can cause disasters.
Never connect to the database as a superuser or
as the database owner. Use always customized users
with very limited privileges.
Check if the given input has the expected data
type. PHP has a wide range of input validating
functions, from the simplest ones found in Variable Functions and in Character Type Functions
(e.g. is_numeric(),
ctype_digit() respectively) onwards the Perl compatible Regular
Expressions support.
If the application waits for numerical input,
consider to verify data with
is_numeric(), or silently change its type
using settype(), or use its numeric
representation by sprintf().
|
Quote each non numeric user input which is
passed to the database with
addslashes() or
addcslashes(). See
the first example. As the examples shows, quotes
burnt into the static part of the query is not
enough, and can be easily hacked.
Do not print out any database specific
information, especially about the schema, by fair
means or foul. See also Error Reporting and Error Handling and Logging
Functions.
You may use stored procedures and previously
defined cursors to abstract data access so that users
do not directly access tables or views, but this
solution has another impacts.
Besides these, you benefit from logging queries
either within your script or by the database itself, if
it supports. Obviously, the logging is unable to prevent
any harmful attempt, but it can be helpful to trace back
which application has been circumvented. The log is not
useful by itself, but through the information it
contains. The more detail is generally better.