0

I'm trying to conditionally add some WHERE constraints to a SELECT by using bound parameters as strings.

Eg

$sql = $con->prepare("
    SELECT  `SomeCol`
    FROM    `Table`
    WHERE    ? AND ?");
$sql->bind_param('ss',
    $p1 = $start ? "`Id` > $start" : "1",
    $p2 = $enabled ? "`Enabled` = 1" : "1");
$sql->execute();
$result = $sql->get_result() ?? null;

So, depending on the values of $start (int) and $enabled (bool), the intended SQL would be, say:

$start = 5, $enabled = true:

    SELECT  `SomeCol`
    FROM    `Table`
    WHERE   `Id` > 5 AND `Enabled` = 1;

$start = null, $enabled = true:

    SELECT  `SomeCol`
    FROM    `Table`
    WHERE   1 AND `Enabled` = 1;

$start = 5, $enabled = null:

    SELECT  `SomeCol`
    FROM    `Table`
    WHERE   `Id` > 5 AND 1;

$start = null, $enabled = null:

    SELECT  `SomeCol`
    FROM    `Table`
    WHERE   1 AND 1;

These all work fine in a MySQL direct query but all but the last return 0 rows using a prepared statement (tho no error is thrown). And because there is no SQL query to view from the statement, I can only guess as to what's going wrong.

Can prepared statement parameters be used like this? I don't see why not as I'm just string replacing into the SQL query as far as I can tell.

If not, do I need to do a standard string replace instead of binding parameters using bind_param?

SteveCinq
  • 1,920
  • 1
  • 17
  • 22
  • This was closed as a duplicate but 1) I don't think it is and 2) I searched before posting, so it's likely that anyone else looking for this issue also won't find the other question. – SteveCinq Jun 19 '20 at 18:48
  • You can't bind SQL. You can only bind values. The SQL needs to be fully compiled before you can use parameter binding. If you want to dynamically build SQL you need to find another way. E.g. whitelists – Dharman Jun 19 '20 at 18:51

0 Answers0