The best method to prevent SQL injections is to use current technology. The MySQL mysql_ family of functions is deprecated and will be removed from PHP in a future revision.
You should use prepared statements with either MySQLi or PDO instead.
These technologies use prepared statements and parameterized queries. SQL statements are parsed by the database server separately from any parameters. It is impossible for an attacker to inject malicious SQL.
You basically have two options to achieve this:
MySQLi:
$stmt = $dbConnection->prepare('SELECT * FROM table WHERE name = ?');
$stmt->bind_param('s', $name);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
// do something with $row
}
PDO:
$stmt = $pdo->prepare('SELECT * FROM table WHERE name = :name');
$stmt->execute(array(':name' => $name));
foreach ($stmt as $row) {
// do something with $row
}
What happens is that the SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name) you tell the database engine what you want to filter on. Then when you call execute the prepared statement is combined with the parameter values you specify.
The important thing here is that the parameter values are combined with the compiled statement, not a SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters you limit the risk of ending up with something you didn't intend. Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course).
