I am checking input against a database like this:
$email = $_POST['newsletter_email'];
$email = filter_var($email, FILTER_SANITIZE_EMAIL);
and then:
$is_user = $db->count_rows('users', "WHERE email='" . $email . "'");
The question is, is it good enough? Or should I also use mysql_real_escape_string
?
There are two parts to this question.
$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
if ($email !== false) {
// Well, you've got yourself a valid email address!
}
Don't use mysql_real_escape_string()
. The proper way to prevent SQL Injection is to use Parameterized Queries. Also known as Prepared Statements.
Recommended reading:
Your code should look like this:
$stmt = $db->prepare('SELECT count(userid) FROM users WHERE email = ?');
if ($stmt->exec([ $email ])) {
$is_user = $stmt->fetchColumn(0);
}
If that seems too cumbersome for you, you can load up the EasyDB library and make your query look like this:
$is_user = $db->cell('SELECT count(userid) FROM users WHERE email = ?', $email);
Please retire the use of input escaping and adopt prepared statements. They send the query and the parameters in separate packets, which means altering the query structure is not possible, even with Unicode hacks.
You must use prepared statements correctly (i.e. NEVER concatenate user input with the query string passed to prepare()
) to achieve this guaranteed level of security.
Furthermore, in PHP, some drivers will by default emulate the prepared statements. From the PHP manual:
PDO::ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them. Use this setting to force PDO to either always emulate prepared statements (if
TRUE
), or to try to use native prepared statements (ifFALSE
). It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query. Requires bool.
Emulated prepared statements does not offer the same security guarantee (i.e. data/instruction separation) as true prepared statements. To be safe, disable emulated prepares.
$db = new PDO(/* etc */);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);