How to prevent MySQL injection attacks?

1. What is MySQ injection?

SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

2. Example of MySQL injection.

Suppose we have SQL code:

----------------------------------------------

SELECT fieldlist
  FROM table
 WHERE field = '$EMAIL';

--------------------------------------

By entering EMAIL as "anything' OR 'x'='x",
the resulting SQL is:

-----------------------------------------------

SELECT fieldlist
  FROM table
 WHERE field = 'anything' OR 'x'='x';

------------------------------------------

'x'='x' clause is guaranteed to be true no matter what the first clause is.

This OR clause of 1 will always be true and so every single entry in the "customers" table would be selected by this statement!

3. How to prevent  MySQL injection attacks?

Injection Prevention - mysql_real_escape_string()

What mysql_real_escape_string does is to take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.

prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a. 

Note: mysql_real_escape_string function

only works if you are already connected to a database.

Example #1 Simple mysql_real_escape_string() example

<?php// Connect
 $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    OR die(mysql_error());
// Query$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password)); 
?>