Fastest way to validate username and password PHP - MySQL - MySQLi - PDO - Prepared Statements

Question

I’m a newbie, and this is my first post. I hope that some day I’ll be able to help out newbies like myself. In the meantime, I thank you in advance for your replies.

Is the script below the fastest and most efficient way to check if a username and password match in a MySQL database?

If it is in fact the most efficient, what would be its equivalent:

1 - Using MySQLi using (Procedural Style).

2 - Using MySQLi using (Object Oriented Style).

3 – Using MySQLi with Prepared Statements - Object Oriented style.

4 – Using PDO with prepared statements - Object Oriented style.

$connection = mysql_connect('localhost','root','pass') or die(mysql_error());
$database = mysql_select_db("database_name",$connection);

$sql = "SELECT count(*) FROM members WHERE username = '$username' AND 
password ='$password' LIMIT 1";

$result = mysql_query($sql) or die(mysql_error());

if (mysql_result($result, 0) > 0){

    echo 'found User <br />';
}

if (mysql_result($result, 0) < 1){

    echo 'username or password does not exist';
}

score 2 · Answer 1 · answered Jun 01 '13 at 00:57

This request is very weak and can be subject to SQL injection. Here how to pass variables to your query:

$query = sprintf("SELECT COUNT(*) FROM members 
    WHERE username='%s' AND password='%s'",
    mysql_real_escape_string($username),
    mysql_real_escape_string($password));

$result = mysql_query($sql) or die(mysql_error());

Otherwise this query seems OK.

In PDO it would be somehow like this:

$pdo = new PDO('mysql:host='.$host.';dbname='.$database, $username, $password);
$query = $pdo->query('YOUR QUERY HERE');

if(is_object($query)) {
  while($result = $query->fetch()) {

    // Do your things here...
  }
}

Once you'll get good with PDO or MySQL, I'll suggest that you start using an ORM like Doctrine. — Chololoco, Jun 01 '13 at 01:03

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

I wouldn't use `mysql_` methods, their getting deprecated.It its advisable to use PDO or mysqli.Also I would be careful with your '$sql' variable there , its very vulnerable to injection, imagine if some one enters this :" ' or 1 = '1 " (without the double quotes) in your password field.

You can check this post for SQL injection prevention : How to prevent SQL injection in PHP? . And you can check this other post for reasons why mysql_* methods were deprecated: Why shouldn't I use mysql_* functions in PHP?

You might also want to check the manuals for PDO and mysqli:

PDO
mysqli

I personally like PDO better and I think it has better community support

Fastest way to validate username and password PHP - MySQL - MySQLi - PDO - Prepared Statements

2 Answers2