MYSQLI to PDO guidance

Question

I am slowly converting pages of a website where there is MSQLI to PDO. But I have this section of a login script which I am not to sure if it can be done in PDO and if it can't does anyone have any recommendations on how to make it secure. It relates to the section of code below, it is a multi-query which relates to $pass_fail variable and then a single select query $pass_fail_query variable which I can probably work out. But its the multi query I struggle with when using PDO how do I execute it?

$pass_fail = "
  DELETE FROM `login_fail`
  WHERE
    `last_fail_login` < DATE_SUB(NOW(), INTERVAL 5 MINUTE);
";

$pass_fail .= "
  INSERT INTO login_fail (
    user_id,
    email,
    last_fail_login,
    fail_login_ip
  ) VALUES (
    '$user_id',
    '$email',
    '$last_login_date',
    '$ip'
  );
";

$pass_fail .= "
  UPDATE members SET
    `last_fail_login` = '$last_login',
    `fail_login_ip`= '$ip'
  WHERE
    email = '$email'
";

$pass_fail_query = "
  SELECT
    *
  FROM `login_fail`
  WHERE
    `email` = '$email'
    AND `last_fail_login` > date_sub(now(), interval 5 minute)
";

PDO does support running multiple queries in 1 call. However, I would urge you to look into [Prepared Statements](https://www.php.net/manual/en/pdo.prepare.php) to prevent SQL injections. The vast majority of the time using PHP variables in SQL strings is a **huge mistake**. — Nicholas Summers, May 22 '19 at 20:21
AFAIK there's absolutely nothing that requires using multi-query. You can do the same thing by executing each query with a separate call, and that's how you do it in PDO. — Barmar, May 22 '19 at 21:01

score 1 · Accepted Answer · answered May 22 '19 at 21:07

Simply create them as separate queries.

$pass_fail_delete = $pdo->prepare("
  DELETE FROM `login_fail`
  WHERE
    `last_fail_login` < DATE_SUB(NOW(), INTERVAL 5 MINUTE);
");
$pass_fail_insert = $pdo->prepare("
  INSERT INTO login_fail (
    user_id,
    email,
    last_fail_login,
    fail_login_ip
  ) VALUES (
    :user_id,
    :email,
    :last_login_date,
    :ip
  );
");
$pass_fail_update = $pdo->prepare("
  UPDATE members SET
    `last_fail_login` = :last_login,
    `fail_login_ip`= :ip
  WHERE
    email = :email
");
$pass_fail_query = $pdo->prepare("
  SELECT
    *
  FROM `login_fail`
  WHERE
    `email` = :email
    AND `last_fail_login` > date_sub(now(), interval 5 minute)
");

Then instead of using mysqli_multi_query() to execute $pass_fail in one call, execute the 3 queries:

$pass_fail_delete->execute();
$pass_fail_insert->execute([':user_id' => $user_id, ':email' => $email, ':last_login_date' => $last_login_date, ':ip' => $ip]);
$pass_fail_update->execute([':last_login' => $last_login, ':ip' => $ip, ':email' => $email]);
$pass_fail_query->execute([':email' => $email]);

Thank you very much Barmar, think I finally understand how to use pdo now — codingnewbie, May 23 '19 at 05:29

MYSQLI to PDO guidance

1 Answers1