PHP secure login authentication against SQL Injection

Question

I searched in stackoverflow and google for a way to protect login authentication in PHP against SQL Injection. I have a table in MySQL that contains the username and password of users (I manually add them instead of giving users freedom to sign-up). Before accessing the table, I want to make sure first that the username or password entered by the user does not contain SQL scripts. Can I simply check if the username or password only contained letters and numbers? If so what script checks if the username contains only letters and numbers? I know how to check if it's all letters or all numbers but I want to check if they are a mix of letters or numbers. That being said, I'm a bit worried that not including special characters in username or password makes the authentication even more vulnerable to attack.

Is it alright to force special characters out of username and password? Is there a way to allow special characters but still protect the site from SQL Injections? The description for mysql_real_escape_string states it is already deprecated so I don't want to use that. I'm very new to PHP so I'm not good with prepared statements as well. Right now I'm just thinking about removing quotes, open/close parentheses or spaces from username and password.

Also note that the main login page index.php contains the form but I placed the login authentication on the target PHP page of that form authenticate.php so the validation happens there and the user won't be able to view the source of that page.

You can start with mysqli prepared statement and for securing password, you can use password hash — Logan Wayne
– Logan Wayne, Commented Aug 26, 2015 at 8:15

Scott Arciszewski · Accepted Answer · 2015-08-26 08:44:21Z

Before accessing the table, I want to make sure first that the username or password entered by the user does not contain SQL scripts.

Don't bother.

Can I simply check if the username or password only contained letters and numbers?

You are, of course, perfectly free to enforce whatever rules you want on the username. But you shouldn't be storing the password directly anyway.

Get familiar with password_hash() and password_verify().

I'm very new to PHP so I'm not good with prepared statements as well.

Might I interest you in, EasyDB? It aims to make prepared statements simple and intuitive. It also tries to remove the biggest foot-bullet in PDO: by default, it will emulate prepared statements instead of using true prepared statements. EasyDB turns that setting off for you.

If you've already committed to using PDO without our wrapper library, make sure you turn emulated prepared statements off:

$pdo = new PDO(/* etc */);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

When you're ready to start playing with PDO directly, you can simply do this:

$pdo = $edb->getPdo();
// ...
$stmt = $pdo->prepare('SOME QUERY WITH PLACEHOLDERS');
if ($stmt->execute(['some', 'values'])) {
    // use $stmt to fetchColumns or fetchAll, maybe even with PDO::FETCH_ASSOC?
    // the sky is the limit here
}

And yes, prepared statements do prevent SQL injection.

Collectives™ on Stack Overflow

PHP secure login authentication against SQL Injection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related