Phpsec Databases and SQL

3.1 Exposed Access Credentials

3.2 SQL Injection

Exposed Access Credentials

Most PHP applications interact with a database.
This usually involves connecting to a database server and using access
credentials to authenticate:

<?php

$host = 'example.org';

$username = 'myuser';

$password = 'mypass';

$db = mysql_connect($host, $username, $password);

?>

This could be an example of a file called

db.inc
that is included whenever a connection to the database is needed. This approach
is convenient, and it keeps the access credentials in a single file.

Potential problems arise when this file is
somewhere within document root. This is a common approach, because it makes

include
and
require
statements much simpler, but it can lead to situations that expose your access
credentials.

Remember that everything within document root has
a URL associated with it. For example, if document root is

/usr/local/apache/htdocs,
then a file located at
/usr/local/apache/htdocs/inc/db.inc
has a URL such as
http://example.org/inc/db.inc.

Combine this with the fact that most web servers
will serve
.inc
files as plaintext, and the risk of exposing your access credentials should be
clear. A bigger problem is that any source code in these modules can be exposed,
but access credentials are particularly sensitive.

Of course, one simple solution is to place all
modules outside of document root, and this is a good practice. Both

include
and
require
can accept a filesystem path, so there's no need to make modules accessible via
URL. It is an unnecessary risk.

If you have no choice in the placement of your
modules, and they must be within document root, you can put something like the
following in your
httpd.conf
file (assuming Apache):

<Files ~ "\.inc$">

    Order allow,deny

    Deny from all

</Files>

It is not a good idea to have your modules
processed by the PHP engine. This includes renaming your modules with a

.php
extension as well as using
AddType
to have
.inc files treated as PHP
files. Executing code out of context can be very dangerous, because it's
unexpected and can lead to unknown results. However, if your modules consist of
only variable assignments (as an example), this particular risk is mitigated.

My favorite method for protecting your database
access credentials is described in the PHP Cookbook (O'Reilly) by David Sklar
and Adam Trachtenberg. Create a file,

/path/to/secret-stuff,
that only
root
can read (not
nobody):

SetEnv DB_USER "myuser"

SetEnv DB_PASS "mypass"

Include this file within

httpd.conf
as follows:

Include "/path/to/secret-stuff"

Now you can use

$_SERVER['DB_USER']
and
$_SERVER['DB_PASS']
in your code. Not only do you never have to write your username and password in
any of your scripts, the web server can't read the

secret-stuff
file, so no other users can write scripts to read your access credentials
(regardless of language). Just be careful not to expose these variables with
something like
phpinfo() or

print_r($_SERVER).

SQL Injection

SQL injection attacks are extremely simple to
defend against, but many applications are still vulnerable. Consider the
following SQL statement:

<?php

$sql = "INSERT

        INTO   users (reg_username,

                      reg_password,

                      reg_email)

        VALUES ('{$_POST['reg_username']}',

                '$reg_password',

                '{$_POST['reg_email']}')";

?>

This query is constructed with

$_POST,
which should immediately look suspicious.

Assume that this query is creating a new account.
The user provides a desired username and an email address. The registration
application generates a temporary password and emails it to the user to verify
the email address. Imagine that the user enters the following as a username:

bad_guy', 'mypass', ''), ('good_guy

This certainly doesn't look like a valid
username, but with no data filtering in place, the application can't tell. If a
valid email address is given (shiflett@php.net, for
example), and
1234
is what the application generates for the password, the SQL statement becomes
the following:

<?php

$sql = "INSERT

        INTO   users (reg_username,

                      reg_password,

                      reg_email)

        VALUES ('bad_guy', 'mypass', ''), ('good_guy',

                '1234',

                'shiflett@php.net')"; ?>

Rather than the intended action of creating a
single account (good_guy)
with a valid email address, the application has been tricked into creating two
accounts, and the user supplied every detail of the

bad_guy
account.

While this particular example might not seem so
harmful, it should be clear that worse things could happen once an attacker can
make modifications to your SQL statements.

For example, depending on the database you are
using, it might be possible to send multiple queries to the database server in a
single call. Thus, a user can potentially terminate the existing query with a
semicolon and follow this with a query of the user's choosing.

MySQL, until recently, does not allow multiple
queries, so this particular risk is mitigated. Newer versions of MySQL allow
multiple queries, but the corresponding PHP extension (ext/mysqli)
requires that you use a separate function if you want to send multiple queries (mysqli_multi_query()
instead of
mysqli_query()). Only
allowing a single query is safer, because it limits what an attacker can
potentially do.

Protecting against SQL injection is easy:

§
Filter your data.

This cannot be
overstressed. With good data filtering in place, most security concerns are
mitigated, and some are practically eliminated.

§
Quote your data.

If your database
allows it (MySQL does), put single quotes around all values in your SQL
statements, regardless of the data type.

§
Escape your data.

Sometimes valid data
can unintentionally interfere with the format of the SQL statement itself. Use

mysql_escape_string()
or an escaping function native to your particular database. If there isn't a
specific one,
addslashes() is a
good last resort.

Related Posts by Categories