PHP / SQL Security – SQL Commands and Non-String Variables

SQL Injection

SQL (Structured Query Language) is the language used to interface with many database systems, including MySQL, PostgreSQL and MSSQL. Certain words and characters are interpreted specially by SQL, as commands, separators, or command terminators, for instance.

When a user enters data into a form, there is nothing stopping them entering these special commands and characters. Consider the PHP code below:

$query = “INSERT INTO orders(address) VALUES('$_GET['address']')”;
$result = mysql_query($query);

A form with a textbox named address would be used to gather the information for this page. We'll ignore any other form elements for now, but obviously there'd be the order items, a name, possibly a price, a delivery date, and so on, which would also all need storing in a database.

Imagine a perfectly legitimate user comes along and enters the following address

14 King's Way
Kingston
Kingham County

The database would spit back an error because the SQL command would be malformed. In the query, the address value is surrounded by single quotes, because it is a string value. When the database hits the apostrophe in King's Way, it will treat it as the closing single quote, and end the string. The rest of the address will be treated as SQL commands. Since these “commands” don't exist, the database returns to PHP with an error.

Now consider an attacker entering the following information into the form:

14 Kings Way
Kingston
Kingham County');DELETE FROM orders *; INSERT INTO ORDERS(address) VALUES('Your data just got deleted by us. We win

Now, the command will succeed. The expected string data is presented, along with a closing quote. The opening ( after VALUES is closed, and the SQL command is terminated using a semicolon. After this, another command begins, one which tells the database to delete the entire contents of the orders table. Then, because the SQL hard-coded into the PHP contains another closing single quote, a third SQL command is entered, which leaves an open string value. This will be matched up with the final quote in the hard-coded SQL, and the entire command is syntactically correct, as far as SQL is concerned, and will therefore execute with no complaint.

Clearly, it is not desirable for any user to be able to issue arbitrary queries simply by posting data in a form. Luckily for us, as with the PHP and HTML input issues discussed in part 1, PHP provides a solution. The addslashes() and stripslashes() functions step in to prevent the above scenarios, and any number of similar attacks.

addslashes() will escape characters with a special meaning to SQL, such as ' or ; by prefixing them with a backslash (\), the backslash itself is also escaped, becoming \\. stripslashes() performs the opposite conversion, removing the prefix slashes from a string.

When entering data into a database, addslashes() should be run on all user-supplied data, and any PHP generated data which may contain special characters. To guarantee safety, simply run addslashes() on every string input to the database, even if it was generated internally by a PHP function. Similarly, be sure to run stripslashes() when pulling data back out from the database.

Non-String Variables

Since PHP automatically determines the type of a variable, you should also check variables which you expect to be integers or other data types. For instance, the int type in SQL does not need to be quoted, but it is still possible for a string in a PHP variable to be inserted into an SQL query in the position an integer would usually take. Consider the example below.

$query = “INSERT INTO customers(customer_number) VALUES($_POST['number'])”;

If a user supplied the value

0); DROP TABLE customers; CREATE TABLE customers(customer_id

then the same kind of attack as before can be mounted. In this case, simply using addslashes() isn't enough: you will prevent the command execution, but the database will still consider this to be an error as the words are not valid in that context. The only way to ensure against this kind of attack is to perform consistent input validation. Make sure that a value you think should be an integer really is. A regular expression that matches any non-integer characters should return false on a PHP string containing only an “integer”. When that string is treated as an integer by SQL, it will therefore not cause any errors or unexpected code execution.

Database Ownership & Permissions

There are other precautions you may be able to take to prevent some of the more serious SQL injection attacks. One such course of action is to implement access control on the database. Many database packages support the concept of users, and it should be possible to set an owner, with full permissions to modify anything within the database, and other users which may only connect and issue SELECT or INSERT queries, thus preserving any data already entered against DELETE or DROP commands. The specifics of achieving such protection will depend on the database system you're using, and consulting the documentation or user manual should reveal how to implement access control.

The user designated as the database owner should never be used to connect to the database from a PHP script; owner privileges should be used on consoles or web admin interfaces such as phpmysqladmin. If a script requires the DELETE or UPDATE commands, it should ideally use a separate user account to the standard account, so that the standard account can only add data using INSERT, and retrieve data using SELECT. This separation of permissions prevents attacks by limiting the effectiveness of any one SQL injection avenue. If, by poor or forgetful programming, a user can inject SQL into one script, they will gain only SELECT / INSERT permissions, or only UPDATE / DELETE permissions, and never sufficient permissions to drop entire tables or modify the table structure using the ALTER command.

File Permissions

Data in a database system must be stored somehow on disk. The database system itself is responsible for exactly how the data is stored, but usually there will be a data/ directory under which the database keeps its files. On a shared hosting system, or a system which allows users some access to the filesystem, it is essential to reduce the permissions on this file to a bare minimum; only the system user under which the database process itself runs should have read or write access to the data files. The web server does not need access as it will communicate with the database system for its data, instead of accessing the files directly.

Making Database Connections

PHP usually connects to the database management system through a TCP socket or a local domain socket (on UNIX/Linux). Where possible, you should prevent connections to this socket from IP addresses or processes other than the web server, and any other process which needs access to the data (for example, if you have internal order processing software which does not run through the web server). If the web server and the database server are on the same computer, and no other services are running which may be exploited to provide a database connection, it should be sufficient to allow only the local host (given by the hostname localhost or the IP address 127.0.0.1) access to the TCP port on which the database manager is listening. If the web server and database server are on different machines, the IP of the web server should be specified explicitly. In short, limit the access to the database as much as possible without breaking anything that needs access to it. This should help to ensure that the only access channel is via your PHP scripts, and those have been written securely enough to check for unexpected or unauthorised data and reject it before it reaches the database.

Database Passwords In Scripts

Finally, a word on database passwords. Each database user should be assigned a password, and your scripts will need this password in order to initiate a connection to the database. Ideally, scripts containing configuration data such as the database username and password should be stored outside of the web server's document root. This prevents a casual attacker retrieving the plain text of the configuration file and obtaining the database password.

Other methods to consider are to use a .php extension for the file, instead of the commonly used .inc extension, for included files. The .php extension ensures that the file is passed through PHP before output is sent to the user's browser, and so it is possible to prevent display of data within the file simply by not echoing it!

.htaccess files provide a third method of protecting against password grabbing. If you deny web access to files whose names begin with .databaseconfig, for instance, a user cannot easily obtain the file through the web server directly.

Of course, a user may still be able to exploit file access security vulnerabilities in scripts to obtain, or even to change, the contents of the file.

How to check for PHP vulnerabilities

The best way to check whether your web site & applications are vulnerable to PHP security attacks is by using a Web Vulnerability Scanner. A Web Vulnerability Scanner crawls your entire website and automatically checks for vulnerabilities to PHP attacks. It will indicate which scripts are vulnerable so that you can fix the vulnerability easily. Besides PHP security vulnerabilities, a web application scanner will also check for SQL injection, Cross site scripting & other web vulnerabilities.