01
Aug
2012
DOs and DON’Ts of PHP and MySQL
During the latest project at work I’ve been doing a lot of PHP with MySQL, and as usual had to search around for a few specific things (fun with multiple LEFT JOINs anyone?) . What surprised me is the sheer amount of bad information out there, and people who are just getting into writing PHP and SQL queries will assume it’s all great and copy it.
So I’d like to present a very simple DOs DON’Ts post here, it’s by no means complete and it’s more of a resource for people who may not entirely understand what the code is doing, and want to be sure they’re copying something that won’t leave big security holes in their software. I won’t be explaining the problems of SQL injection etc. here, there’s plenty of details on the web.
DON’T use the mysql_ functions
< ?
$username = $_POST['username'];
$password = $_POST['password'];
$sql = "INSERT into users (user,password) VALUES ($username, $password)";
mysql_query($sql);
?>
$username = $_POST['username'];
$password = $_POST['password'];
$sql = "INSERT into users (user,password) VALUES ($username, $password)";
mysql_query($sql);
?>
Still DON’T use them, even if you escape your input
< ?
$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
$sql = "INSERT into users (user,password) VALUES ($username, $password)";
mysql_query($sql);
?>
$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
$sql = "INSERT into users (user,password) VALUES ($username, $password)";
mysql_query($sql);
?>
DO use PDO and prepared statements
< ?
$stmt = $db->prepare("INSERT into users (user,password) VALUES (:user, :pass)");
$stmt->bindParam(':user', $_POST['username']);
$stmt->bindParam(':pass', $_POST['password']);
$stmt->execute();
?>
$stmt = $db->prepare("INSERT into users (user,password) VALUES (:user, :pass)");
$stmt->bindParam(':user', $_POST['username']);
$stmt->bindParam(':pass', $_POST['password']);
$stmt->execute();
?>
DO better and validate your input as well
< ?
if(is_valid_username($_POST['username'])) {
$stmt = $db->prepare("INSERT into users (user,password) VALUES (:user, :pass)");
$stmt->bindParam(':user', $_POST['username']);
$stmt->bindParam(':pass', $_POST['password']);
$stmt->execute();
} else {
die('Invalid username');
}
?>
if(is_valid_username($_POST['username'])) {
$stmt = $db->prepare("INSERT into users (user,password) VALUES (:user, :pass)");
$stmt->bindParam(':user', $_POST['username']);
$stmt->bindParam(':pass', $_POST['password']);
$stmt->execute();
} else {
die('Invalid username');
}
?>
