Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead.
A few guidelines for escaping special characters in SQL statements.
Don't use MySQL. This extension is deprecated. Use MySQLi or PDO instead.
MySQLi
For manually escaping special characters in a string you can use the mysqli_real_escape_string function. The function will not work properly unless the correct character set is set with mysqli_set_charset.
Example:
$mysqli = new mysqli('host', 'user', 'password', 'database');
$mysqli->set_charset('charset');
$string = $mysqli->real_escape_string($string);
$mysqli->query("INSERT INTO table (column) VALUES ('$string')");
For automatic escaping of values with prepared statements, use mysqli_prepare, and mysqli_stmt_bind_param where types for the corresponding bind variables must be provided for an appropriate conversion:
Example:
$stmt = $mysqli->prepare("INSERT INTO table (column1, column2) VALUES (?,?)");
$stmt->bind_param("is", $integer, $string);
$stmt->execute();
No matter if you use prepared statements or mysqli_real_escape_string, you always have to know the type of input data you're working with.
So if you use a prepared statement, you must specify the types of the variables for mysqli_stmt_bind_param function.
And the use of mysqli_real_escape_string is for, as the name says, escaping special characters in a string, so it will not make integers safe. The purpose of this function is to prevent breaking the strings in SQL statements, and the damage to the database that it could cause. mysqli_real_escape_string is a useful function when used properly, especially when combined with sprintf.
Example:
$string = "x' OR name LIKE '%John%";
$integer = '5 OR id != 0';
$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string($string), $integer);
echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 5
$integer = '99999999999999999999';
$query = sprintf("SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string($string), $integer);
echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 2147483647
