PHP PDO mysql query to work with both column = '$var' and column IS NULL

Question

I have a quite long mysql query, selecting data according to status field. I'm calling it for different statuses and it works well, but I have a scenario when I should get all records where status is null ONLY. Is there a way to do this without having to write 2 different sql queries?

Looks like I can't insert 'IS NULL' or '=' without it being rendered as a string.

I want to achieve this:

$sql = "SELECT name, surname FROM ...
       ...             
       WHERE status ?;";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($status === 'undefined' ? 'IS NULL' : " = '$status'"));

After all, here's what I did:

$sql = "SELECT name, surname FROM ...
       ...             
       WHERE status <=> ?;";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($status === 'unfinished' ? null : $status));

look at this answer ... https://stackoverflow.com/a/48644040/915467 — YvesLeBorg, Nov 23 '20 at 10:14
a spaceship in the query and `$stmt->execute([$status === 'undefined' ? NULL : $status]);` — Your Common Sense, Nov 23 '20 at 10:34

Martin · Answer 1 · 2020-11-23T10:26:47.537

Using parameterised queries, as indicated, is a safer way of introducing user input into your SQL statements. However, it has the effect of treating all input as a parameter, and therefore will surroung any string literals with quotes - giving rise to the problem you have.

To deal with this issue, why not just modify the logic of the code:

$sql = "SELECT name, surname FROM ...
       ...             
       WHERE status";

if ($status === 'undefined') {
    $sql .= " IS NULL";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
} else {
    $sql .= " = ?"
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array($status));
}

Edit

Updated to move the execution into the relevant part of the if statement becuase the parameters must not be specified if there is no placeholder in the SQL statement.

PHP PDO mysql query to work with both column = '$var' and column IS NULL

1 Answers1