Login

PHP Programs to Prevent MySQL Injection or HTML Form Abuse

It has been known for a while that if a form is unsecured, malicious code in the form of a MySQL injection will be initiated to attack the site. HTML forms such as drop down menus, search boxes and check boxes are all susceptible entry points for this type of abuse. This article will explain what happens in this kind of attack, and how to prevent it.

Known Security Issues and Background

The intent of MySQL injection is to take over the website database and administration to steal information. A common open source database like MySQL has been used by many website developers to store important information such as passwords, credit cards, usernames, personal information and administrative information.

This is a serious form of hacking that affects a lot of websites nowadays. You can easily spot it in Google search results with a warning sign: “This site may harm your computer” or when you are visiting the site “Malware detected” in browsers such as Google Chrome or Mozilla Firefox.

MySQL is popular because it is used in conjunction with PHP, the most popular server side scripting language. We also know that PHP is the primary language of Linux-Apache based servers that dominate the Internet. So this means that PHP can be easily exploited by hackers just as spyware is in Windows.

The hacking starts with inputting malicious code into an unsecured website form (via drop menu forms, search boxes, contact forms, inquiry forms and check boxes). Other advanced forms can be injected via session–related URLs, but this topic is beyond the scope of this article. (For details of this process, please refer to the flowchart on the next page).

The malicious code will then be transported to the MySQL database and hence “injected.” To see how this works, first consider the following basic and normal MySQL SELECT statement query:

SELECT * FROM xmen WHERE username = ‘wolverine’

This query will ask the database with the "xmen" table to return a certain piece of data in the MySQL for the username "wolverine."

In the web forms, a user will enter
wolverine
, and then this data will be passed to the MySQL query, resulting in the above statement.

If the input is not validated, a hacker can craft the input in such a way as to gain control of the database, for example setting the username to:

‘ OR ”=”

You may think having the normal PHP and MySQL syntax to process inputs is safe because every time they may enter malicious codes they will get an “Invalid query” message, but it is not.

In reality there are a lot of smart hackers, and they can easily work on this. Once there is a security breach, correcting this will become difficult. It involves cleaning the database and revamping the administrative access.

{mospagebreak title=The Flow of User Input (Without Validation)}

If you are a beginner in MySQL and PHP, it would be useful to illustrate the flow of user information. Please check the screen shot of what will happen if the user input is not validated:

You can observe that even after the database or malware clean up, as long as the website form input is not validated, we will see re-infection of the website. Two common misconceptions of MySQL injection attacks are as follows:

The webmaster thinks that malware injection can be cleaned up using anti-virus or anti-spyware software. It cannot be cleaned up in this way because this type of infection exploits the weaknesses of MySQL databases. It cannot simply be removed by any spyware or anti-virus program.

MySQL injection is a virus infection as a result of having copied infected files from another server or outside sources. It is not. This type of infection is a result of having someone type malicious codes into any unprotected forms in the website, and then gaining access to the database. Virus infection as a result of a MySQL injection can be cleaned by removing the malicious scripts and not by using anti-virus programs.

Flow of User Input (With Validation)

It is highly important to back up a clean database and put it outside the server. It is very easy to export a set of MySQL tables and save it to your desktop, for example. Double check to make sure the database contains the exact and clean entries that you expect.

Then go to your server and temporarily shut the forms input first. This means the form cannot accept data to be processed; this will also mean that your website will be shut down, too.

Then start the clean up process. First, on your server, clean up any mess left by the MySQL injection. Change all database, FTP and website passwords.

In the worst-case scenario, if you clean up late, you can double check for any backdoor programs running on your server. These backdoor programs are some form of Trojan installed by the hacker. Remove it completely and change all the FTP permissions to something for which hackers cannot simply write a file to any directory. Scan your server for Trojans and installed malware. In a truly worst- case scenario, they may be hidden.

This is when you modify the PHP script that will process the form data. A good principle to prevent MySQL injection is: DO NOT EVER TRUST USER DATA. Remember, the Internet is a public place, and forms are exposed to all types of people, good or bad. Your form is analogous to the doors of your home, which you should be locking when you are away to prevent criminals from entering.

User input validation is very important to prevent MySQL injection and to make sure that all inputs that go to the database are sanitized and will not cause database errors or infection.

Below is what happens to a validated user input:

In the above example the data will not be forwarded to the MySQL database without first verifying that the user input is valid. Designing a filter to screen out user input depends on your application, but I will outline helpful tips below:

If the input to a form is numeric, then you can validate if it is a number by just testing if it is equal or greater than 0.001 (assuming you do not accept a zero).

If the input is an email address, then it will validate if the characters are only a combination of allowable characters like “@”, A-Z, a-z or some numbers. A quotation mark is not acceptable in an email address.

If the input is the name of a person or a username, then it will validate if it does not contain any illegal characters such as ‘ ‘ and * which are malicious characters that can be used for SQL injection.

{mospagebreak title=Validating Numeric Input}

The script below validates whether the input is a valid number from 0.001 to infinity. It’s worth noting, however, that in a PHP program you can even allow a certain range of numbers to be used. Using this validating script ensures that input to that form is a number and nothing else.

Suppose that in your program there are three numeric variables; you will need to validate them, and we will name them num1, num2 and num3:

The AND condition can be extended to accommodate more than three numbers. So if you have 10, you will just need to expand the AND statements.

This can be used to validate a form that accepts only numbers, such as contract numbers, license numbers, telephone numbers, etc.

{mospagebreak title=Validating Text and Email Input}

This will be used to validate form input such as a username, first name and last name of the person, as well as the email address.

<?php

//Validate text input

if (! preg_match(‘/^[-a-z.-@,'s]*$/i’,$_POST['name']))

{

}

else

if ($empty==0)

{

}

else

{

}

?>

The good thing about this validation script is that it will not accept blank input. Some malicious users also manipulate database by entering a blank input. In the above script, it validates only one text variable, “$name.” This means that if you have three text variables, you can make three validating scripts for each of those variables, to make sure every variable is clean before it goes into the database.

Want stronger protection?

If you need stronger protection you can validate the user input using the above scripts and
mysql_real_escape_string
; this will offer secondary protection in case the above validation scripts fail due to some reason. Discussing this feature is beyond the scope of this article and you can read useful resources on:
http://www.php.net/mysql_real_escape_string

However, before you can use this feature, you must be connected to a MySQL database, or else it will return an error. Some really talented hackers can play around with
mysql_real_escape_string, which is why it is highly recommended to have a double filter in your scripts (validating scripts +
mysql_real_escape_string
) to make hacking much more difficult.