-3

I have a simple table containing user_id and user_hash.

All of the hashes need to be deleted except some ones, via an AJAX call. The hashes that need to be kept are sent thru a POST variable.

HTML code

<button data-hash="'M3FYck9KemhVSk5iYW9YcFpSTExMQT09', 'eTlSQ0NMSW1PT2NLOTNaUHlPWEtKQT09'" class="purge">Purge</button>

JQuery code

$('.purge').on('click', function (e) {
   e.preventDefault();
   hash = $(this).attr('data-hash');
   $.ajax({
      url: "includes/db.php",
      type: "POST",
      data: { type: 'purge', hash: hash },
      success: function () {
      }
   });
});

PHP code of db.php

elseif ($_POST["type"] == 'purge') {
   $stmt = $conn->prepare("DELETE FROM users_hash WHERE hash NOT IN (:hash)");
   $arr_update = array('hash' => $_POST["hash"]);
   $stmt->execute($arr_update);
}

But this delete all the records of users_hash!

However, if I add this code in the db.php file

echo "DELETE FROM users_hash WHERE hash NOT IN (".$_POST["hash"].")";

This is displayed in the Chrome Devtools

DELETE 
FROM users_hash 
WHERE hash NOT IN ('M3FYck9KemhVSk5iYW9YcFpSTExMQT09',
                   'eTlSQ0NMSW1PT2NLOTNaUHlPWEtKQT09')

which works as expected.

Preparing the query with PDO changes something, but I dont know what.

Akina
  • 39,301
  • 5
  • 14
  • 25
Jibeji
  • 453
  • 4
  • 14
  • Does this answer your question? [PHP - Using PDO with IN clause array](https://stackoverflow.com/questions/14767530/php-using-pdo-with-in-clause-array) – 04FS Sep 21 '20 at 07:21

1 Answers1

1

PDO sends your list as ONE literal, i.e. as

DELETE 
FROM users_hash 
WHERE hash NOT IN ('M3FYck9KemhVSk5iYW9YcFpSTExMQT09, eTlSQ0NMSW1PT2NLOTNaUHlPWEtKQT09')

Use NOT FIND_IN_SET:

$stmt = $conn->prepare("DELETE FROM users_hash WHERE NOT FIND_IN_SET(hash, :hash)");
Akina
  • 39,301
  • 5
  • 14
  • 25
  • Just to settle the recent discussion, I checked the explain. FIND_IN_SET(field, 'csv') didn't use an index. And a quick google-ing finds that [it is not only me](https://bugs.mysql.com/bug.php?id=37564). Hence your solution, albeit a sleek one, cannot be recommended as a generic approach. – Your Common Sense Sep 21 '20 at 10:15