如何在Postgres和PHP 5.6中使用字符串中的参数？

Question

How do I use a parameter inside a quote with Postgres? I keep getting the error: "SQLSTATE[HY093]: Invalid parameter number: :beginDaysAgo"

When we look at these lines:

WHERE a.balance <= (a.autorefill_threshold+:amountAboveThreshold)
    AND ((t.created_at <= ( current_timestamp-INTERVAL  \':beginDaysAgo days\')) AND ( t.created_at >= (current_timestamp) - INTERVAL \':totalDays days\'))

The first parameter is not generating an error for me. It's the ones inside the quote.

Which means the first parameter, amountAboveThreshold works, but it obviously can't search within the string.

Basically, when I just use the PHP Variable inside instead of a parameter, it works perfectly, or when I just put a number in. So for instance, when I put the number 20 and then 21 for those two parameters, beginDaysAgo, and totalDays, respectively, it works perfectly.

But it's when I try to use the parameters -- which is the correct and safe way to do it -- it does not work.

 public function getClientsWithBalanceBelowThreshold(
    $amountAboveThreshold=100.00,
    $beginDaysAgo = 0,
    $amountOfDays = 1
) {

    $totalDays = $amountOfDays + $beginDaysAgo;
    //this one works
    if ((double)$amountAboveThreshold!=$amountAboveThreshold)
        throw new \TypeError("Type Mismatch");
    $conn = $this->em->getConnection();


$conn = $this->em->getConnection();
        $sql = '
          SELECT DISTINCT ON (l.public_id) a.balance, a.public_id as account_public_id, a.organization_name, a.autorefill_threshold,
          l.name  as listing_name, l.paused, l.public_id,
          t.balance_before,
          t.balance_after, t.created_at, t.type
        FROM transaction as t INNER JOIN account a
            ON t.account_id = a.account_id
        INNER JOIN listing as l ON a.account_id = l.account_id
        WHERE a.balance <= (a.autorefill_threshold+:amountAboveThreshold)
        AND ((t.created_at <= ( current_timestamp-INTERVAL  \':beginDaysAgo days\')) AND ( t.created_at >= (current_timestamp) - INTERVAL \':totalDays days\'))
        AND t.balance_before !=  t.balance_after
        AND t.type != \'credit\'
        ORDER BY  l.public_id, a.balance DESC, t.created_at, l.account_id;
        ';
    $stmt = $conn->prepare($sql);
    $stmt->bindParam('amountAboveThreshold', $amountAboveThreshold);
    $stmt->bindParam('beginDaysAgo', $beginDaysAgo);
    $stmt->bindParam('totalDays', $totalDays);
    $stmt->execute();
    var_dump($stmt->fetchAll());die;

The full error I receive is ...

"SQLSTATE[HY093]: Invalid parameter number: :beginDaysAgo" #0 /var/www/clientreachapi.com/releases/2018_03_10_14_54_58/vendor/doctrine/db al/lib/Doctrine/DBAL/Statement.php(141): Doctrine\DBAL\Driver\PDOStatement->bindParam('beginDaysAgo', '18', 2, NULL)
#1 /var/www/clientreachapi.com/releases/2018_03_10_14_54_58/src/Rb/ReportingApiBundle/ClientThreshold/ClientBelowThresholdReport.php(77): Doctrine\DBAL\Statement->bindParam('beginDaysAgo', '18')
#2 /var/www/clientreachapi.com/releases/2018_03_10_14_54_58/src/Rb/ReportingApiBundle/Command/ClientBelowThresholdReportCommand.php(61): Rb\ReportingApiBundle\ClientThreshold\ClientBelowThresholdReport->getClientsWithBalanceBelowThreshold('120.00', '18', '2')
#3 /var/www/clientreachapi.com/releases/2018_03_10_14_54_58/vendor/symfony/symfony/src/Symfony/Component/Console/Command/Command.php(259): Rb\ReportingApiBundle\Command\ClientBelowThresholdReportCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#4 /var/www/clientreachapi.com/releases/2018_03_10_14_54_58/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php(863): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#5 /var/www/clientreachapi.com/releases/2018_03_10_14_54_58/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php(192): Symfony\Component\Console\Application->doRunCommand(Object(Rb\ReportingApiBundle\Command\ClientBelowThresholdReportCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#6 /var/www/clientreachapi.com/releases/2018_03_10_14_54_58/vendor/symfony/symfony/src/Symfony/Bundle/FrameworkBundle/Console/Application.php(92): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#7 /var/www/clientreachapi.com/releases/2018_03_10_14_54_58/vendor/symfony/symfony/src/Symfony/Component/Console/Application.php(123): Symfony\Bundle\FrameworkBundle\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#8 /var/www/clientreachapi.com/releases/2018_03_10_14_54_58/app/console(29): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput))
#9 {main}

Answer 1

I think when you have :beginDaysAgo and :totalDays quoted in the query they're going to be interpreted as literal strings rather than placeholders for parameters. I would suggest removing the quotes and the days part from the SQL and leaving just the placeholders, like this:

(t.created_at <= (current_timestamp - INTERVAL :beginDaysAgo))
AND ( t.created_at >= (current_timestamp - INTERVAL :totalDays))

And then appending the days part to the numeric values before you bind them to the prepared statement:

$beginDaysAgo = "$beginDaysAgo days";
$amountOfDays = "$amountOfDays days";