Defending PHP Web Applications from MySQL
MySQL provides a fast, flexible, stable, open source database back end to many PHP based applications. MySQL provides a number of features that can be used to greatly increase the security of your PHP application. Because MySQL is often used to store critical data for web applications, it is a common target for attackers. Gaining access to the database often allows access to sensitive data. In most development models PHP is used to provide views into the database layer, and in a sense to shield the database from end users. However, flaws in PHP applications can expose the database to malicious users. By utilizing some of the security features in MySQL you can protect your database from flaws introduced at the PHP layer.
Compartmentalize your Accounts
One easy way to protect data in your MySQL database is to compartmentalize your accounts. When creating a new application, create two new accounts. Create one account for the application developer and the other for the application user. The idea behind this strategy is the principle of least privilege. The application developer should be given permissions to a database designated for the application. The developer account should be given necessary privileges to set up the new application in the database, but no others. This account should have a distinct username and password. For the developer account you'll want to use nonstandard privileges such as the ability to create and alter tables. For example your new developer account could be created using the statement:
mysql> GRANT ALTER, CREATE, DELETE, DROP, INSERT, SELECT, UPDATE on 'new_database'.* to 'new_user'@localhost identified by 'new_password';
This statement creates a new account with a password and the privileges that should be necessary to set up new tables and develop applications using the database. Once the database is set up a separate account should be created for the application itself. This account should have privileges limited to only processes the application will require. For instance, the following statement could be used to create an application account:
mysql> GRANT DELETE, INSERT, SELECT, UPDATE on 'new_database'.* to 'app_account'@'localhost' identified by 'Str0ng23passworD?';
This will create a new account for the application. The primary reason for doing this is that the MySQL credentials will likely be listed in files in the application that uses the database. For instance, in a PHP application these details would be listed in PHP files which include the mysql_connect() statement. Because these files sit on the filesystem in plaintext it is important to limit what the account can do (and from where) if it's compromised. By limiting this account's connection location to 'localhost' we ensure that it can only be used from the same machine as the MySQL database. This may have to be altered depending on your deployment, but you should limit the connection scope to the minimum possible.
By limiting privileges the damage from a compromised account can be greatly mitigated. If the application account is compromised the account can't get password hashes out of the MySQL mysql database user table. The account also can't drop tables, although they can delete rows. Thus, the damage to the database as a whole (and the filesystem) is limited, but the damage to the application database can be severe.
Compartmentalizing Queries
Stored routines are predefined queries that can be executed on MySQL. By predefining the queries, you can effectively hard code the functionality to prevent against query manipulations. Stored routines come in two flavors in MySQL. The first is stored functions and the second is stored procedures. Stored functions are user defined functions that can be loaded by the database. These are compiled binaries and can, in some cases, be unsafe. Stored procedures actually use SQL syntax and perform generic database functions specific to their definition.
In order to protect against unwanted SQL queries being executed on your application database the use of MySQL stored procedures can provide great results. A stored procedure is a preset query that can accept some variable data for customization, but is limited in its functionality. Using stored procedure protects your application from performing unscripted actions in the database. The permission associated with stored routines is EXECUTE. Creating an application user that can only used stored routines allows for an even more limited permissions set:
mysql> GRANT EXECUTE on 'new_database'.* to 'app_account'@'localhost' identified by 'Str0ng23passworD?';
A common web application query is selecting data from a database based on the id value of the row. For instance, the following is a common PHP snippet that pulls a row from the 'news' table associated with the user specified GET variable. Assuming the use of the following 'news' table:
mysql> desc news; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | news_id | int(11) | NO | PRI | NULL | auto_increment | | news_title | varchar(255) | YES | | NULL | | | news_body | text | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
We might have the following PHP snippet:
<?php $id = $_GET['id']; mysql_connect('server','username','password'); mysql_select_db('database'); mysql_query('select * from news where id='.$id): ?>
There is an obvious SQL injection vulnerability in this query, of course. In order to protect against this type of injection the developer could use a library like MDB2 or a PHP mysqli prepared statement style query. Creating a MySQL stored procedure to pull this information is quite simple using:
mysql> delimiter // mysql> CREATE PROCEDURE pull_news (IN param1 INT) -> BEGIN -> SELECT * FROM news WHERE news_id = param1; -> END; -> //
This creates a stored procedure called 'pull_news' that can only be instantiated with an integer input. Using the 'call' statement this procedure could be executed using virtually the same PHP code:
<?php $id = $_GET['id']; mysql_connect('server','username','password'); mysql_select_db('database'); mysql_query("call pull_news($id)"): ?>
You'll notice the same insecure code listed above, but it is much more difficult for an attacker to take advantage of this vulnerability, especially if the account the web application uses can only call stored procedures. Because the procedure is hard coded to accept only an integer input, it is invulnerable to parameter injection. Because the web account doesn't have UNION privileges, an attacker couldn't even use many of the popular mechanisms to manipulate the SQL statement. Of course, using the intval() function to force the $id parameter to be an integer is a good idea, but the above code demonstrates the extra strengths that stored procedures introduce.
Conclusions
One disadvantage of using stored routines is the migration of application logic out of the PHP layer and into the database layer. It is handy for maintenance to have all your database, business logic, and presentation logic in one place (the PHP files). Moving some of that logic into SQL code in the MySQL database can make debugging more difficult and cause confusion amongst maintainers reading the PHP code post production. The security gains from this strategy must be weighed against this potential down side.
Note also that if you use stored procedures you'll need to grant privileges to create stored procedures to the application developer account.