Top Three MySQL Mistakes Done By PHP Programmers

A database is a very basic component for most of the web applications. While using PHP programming language, MySQL is an integral part of the LAMP stack.

PHP is a very easy as well as majority of new PHP Developers can write functional code within a few hours. Here, you can find top three MySQL mistakes I have made.

1. Using MyISAM rather than InnoDB :-

MyISAM is used by default. MyISAM does not support foreign key structure or transactions, which are necessary for data integrity. Second thing, whenever a record is inserted or updated the whole table is locked, which create a big problem on performance as usage grows.

The solution is simple : Use InnoDB.

2. Using PHP’s mysql functions :-

PHP has provided MySQL library functions since first day. Lots of applications depend on mysql_connect, mysql_query, mysql_fetch_assoc, etc.

If we are using MySQL versions 4.1.3 or higher than it is necessary to use the mysqli extension.

Mysqli, and MySQL improved extension, has several benefits like an object oriented interface, prepared statements, multiple statements as well as transaction support.

On another hand, you should consider PDO if you want to support multiple databases.

3. Not sanitizing user input :-

This should be on #1. Validate each string using server side PHP. Do not rely on JavaScript.
The simplest SQL injection attacks depend on code such as:

$username = $_POST["name"];
$password = $_POST["password"];
$sql = "SELECT userid FROM usertable WHERE username='$username' AND password='$password';";
// run query...

This can be cracked by entering “admin'; --” in the username field.

The SQL string will equate to:
SELECT userid FROM usertable WHERE username='admin';

The devious cracker can log in as “admin”; they need not know the password because it’s commented out of the SQL.

Reference: http://www.sitepoint.com/mysql-mistakes-php-developers/