Prevent SQL Injections by Using PHP Programming and MySQL Programming

Web applications that utilize back-end databases are potentially vulnerable to the common SQL Injection. By submitting a segment of database code through the form, the PHP Program’s MySQL commands might be modified or enhanced for no good. Consider a typical contact form that receives an email address with a single field. The intent is to make receipt of the user’s email address using a PHP Program and insert it into a Newsletters Database. Let’s discuss  how  SQL Injection is performed and prevented…

The PHP Program would typically be written to connect to the MySQL Database, construct the MySQL Query to be sent to the database, then the MySQL Query would be executed. The submitted email address would be dynamically inserted into the query string using PHP. Our standard MySQL Query String would be assembled from the following PHP line:

mysql_query(‘ INSERT into newsletters ” ‘ . $_GET[’email’] . ‘ ” ‘ ) ;

The vulnerability in this method is associated with Data Cleansing to prevent malicious code from being added to our MySQL Query. If we allowed the submitted email address to be inserted into the MySQL Query String without inspection, we might miss some submitted SQL language that could cause harm if executed. An expected value might be bob@hisdomain.com and this would not be a problem. An unexpected value entered into the email form field might be the following:

“; DELETE FROM newsletters WHERE “1=1

…resulting in the following MySQL Query to be submitted…

mysql_query(‘ INSERT into newsletters ” “; DELETE FROM newsletters WHERE “1=1” ‘ ) ;

A malicious hacker submitting this SQL Injection might cause the result of deleting all email addresses in the table. This result will not happen with the PHP MySQL extension, since it prevents the execution of multiple queries by default. However, never relay on defaults to protect your databass of file systems. Use the appropriate methods of Data Validation and Data Cleansing wherever approporiate.

There are several methods to prevent this type of attack including both PHP Commands and MySQL Database Commands. Quotation methods can be used to allow the injection string to pass as quted text, so the injection is not executable. PHP Programming would include an addslashes() method to slash-out the illegal characters. MySQL Programming would include a mysql_real_escape_string() method to quote-out the illegal structure. The disadvantage of this method is that the garbage data still ends up in your database, despite having been made inert.

A better method of handling the submitted data is to follow a methodical sequence of validation, cleansing and execution commands, with error handling structures and messaging to the user/hacker. First, Data Validation is critical. The anticipated data from the form would have the pattern of an email address. There are many free pattern scripts on the Internet to use, most often with a Regular Expression command such as eregi or eregi_replace. eregi would look to prove the pattern meets expectations and allows it to continue to the next component in or method, where eregi_replace is used to replace patterns that do not meet our expectations. eregi is more appropriate since it filters anything not meting pattern expectations.

After the pattern has been proven, an additional layer of protection can be employed using the eregi_replace command, which would allow the elimination of characters (Data Cleansing) that should never be seen in an email address, such as illegal characters. If the eregi pattern filter has worked correctly above, this is not necessary, and is only a redundant layer for protection. When used alone,it would expect a valid email to be submitted and replace any characters not expected in a standard email address. The problem of invalid data recurs here, so this is not a best method on its own.

The addslashes() command can effectively be associated with the commands and method above. Illegal characters are slashed-out, allowing them to be inserted safely into the database. Again, why would we want illegal characters inside an email address in the first place? We would want to prevent this from being inserted, so this becomes a redundant layer of protection that is not necessarily necessary.

The essence of Data Validation and Data Cleansing is to first check if the submitted data contains anything we didn’t expect, then we remove or disable the unexpected characters and content. Best methods dictate that we not simply disable invalid and illegal content, but rather reject it altogether. This introduces the need for Error Handling and error Messaging.

When the PHP Program detects illegal characters during the Data Validation portion of our PHP Program, there should be an escape clause. An appropriate Error Message should be constructed to display to the user/hacker, and the origination form displayed again for correction. Not only does this provide a safe and usefulpathway for a valid user, it provides verification to the hacker that our PHP Program does not allow SQL Injection. In fact, the pattern portion of our PHP Program can detect if SQL INjection patterns exist, and return a ore aggressive message to the hacker informing them that their IP and access attempt have been reported and a traceroute initiated to find their location and ISP. We can also offer that we have used their IP Address to cross-reference a geographical IP Address translation database to find their physical location. This is overkill, but it’s all useful to deter hackers and assist valid users.

PHP Programmers must use Data Validation and Data Cleansing on ALL PHP Programs that are capable of receiving and processing data from users. Regardless of the presence of a form and any client-side form validation, the PHP Program MUST perform these checks. Form data may be submitted directly to the server without using the intended user form. The only best defense is well written PHP Programs to eliminate Security Vulnerabilities and avoid their exploitation.

One thought on “Prevent SQL Injections by Using PHP Programming and MySQL Programming

Comments are closed.