0

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}

1 Answer 1

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";
Sign up to request clarification or add additional context in comments.

2 Comments

Or maybe doing the concatenation in SQL using || for instance (haven’t tried it)
@jcaron Yeah, I was thinking something like that could work too, but I'm not confident enough with postgres to suggest the specific way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.