Example 15-9. Attacking the database hosts
operating system (MSSQL Server)
<?php
$query = "SELECT * FROM products WHERE id LIKE '%$prod%'";
$result = mssql_query($query);
?>
If attacker submits the value a%' exec master..xp_cmdshell
'net user test testpass /ADD' -- to $prod, then the $query
will be:
<?php
$query = "SELECT * FROM products
WHERE id LIKE '%a%'
exec master..xp_cmdshell 'net user test testpass /ADD'--";
$result = mssql_query($query);
?>
MSSQL Server executes the SQL statements in the batch including
a command to add a new user to the local accounts database.
If this application were running as sa and the MSSQLSERVER
service is running with sufficient privileges, the attacker
would now have an account with which to access this machine.
Note: 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 similarly vulnerable in another manner.
Avoiding techniques
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 any kind
of input, especially that 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)
and onwards to the Perl compatible Regular Expressions support.
If the application waits for numerical input, consider
verifying data with is_numeric(), or silently change its
type using settype(), or use its numeric representation
by sprintf(). Example 15-10. A more secure way to compose
a query for paging
<?php
settype($offset, 'integer');
$query = "SELECT id, name FROM products ORDER BY name
LIMIT 20 OFFSET $offset;";
// please note %d in the format string, using %s would
be meaningless
$query = sprintf("SELECT id, name FROM products ORDER
BY name LIMIT 20 OFFSET %d;",
$offset);
?>
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 cracked.
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
logging. 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. More
detail is generally better than less.
