Migrating PHP PDO to Laravel PDO causes Invalid parameter number

Question

I'm converting a PHP PDO instance to Laravel 5.5. I changed the DB connection from/to:

//$dbc = new PDO ('mysql:host='. $DB_HOST .';dbname='. $DB_NAME, $DB_USER, $DB_PASS);
$dbc = \Illuminate\Support\Facades\DB::connection('foobardb')->getPdo();

For some reason, this is causing my SELECT query to fail.

$query = "SELECT *
          FROM foobartable
          WHERE date_created > :sunday
          AND date_created <= :monday
          AND date_updated > :sunday
          AND date_updated <= :monday";

$stmt = $dbc->prepare($query);
$stmt->bindValue(':monday', $monday, PDO::PARAM_STR);
$stmt->bindValue(':sunday', $sunday, PDO::PARAM_STR);
$stmt->execute();

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

According to the stack trace, the error is specifically triggered on the $stmt->execute();

SQLSTATE[HY093]: Invalid parameter number

I obviously have the correct parameters, and this works just fine using PHP's PDO instance, so why does it not work with Laravel?

Remember you can often simplify this kind of query with `date_updated BETWEEN ? and ?` instead of having to specify the bounds independently. — tadman, Dec 19 '17 at 22:19
`BETWEEN` uses closed boundaries, so `WHERE a BETWEEN b AND c` translates to `a >= b AND a <= c`. To determine if that is equivalent to the rules you're trying to implement, you'd have to look at the bound value types, and the database field types to see whether the boundaries coincide properly (e.g. date / date time / unix timestamp / etc.) — e_i_pi, Dec 19 '17 at 22:46

score 3 · Accepted Answer · answered Dec 19 '17 at 22:06

3

For some reason, Laravel connections will not automatically use PDO::ATTR_EMULATE_PREPARES. Even if a mysql connection. Simply add the option in the connection array to fix this.

'foobardb' => [
    'driver'    => 'mysql',
    'host'      => env('DB_HOST', 'localhost'),
    'port'      => env('DB_PORT', 3306),
    'database'  => 'foobardb',
    'username'  => env('DB_USERNAME', 'forge'),
    'password'  => env('DB_PASSWORD', ''),
    'charset'   => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix'    => '',
    'strict'    => false,
    'engine'    => null,
    //add addition options here
    'options'   => [PDO::ATTR_EMULATE_PREPARES => true]
],

I don't know if this is limited to Laravel 5.5, or if the option will need to be added in earlier versions as well.

Another way to fix this is to just do something like:

$query = "SELECT *
          FROM foobartable
          WHERE date_created > :sunday
          AND date_created <= :monday
          AND date_updated > :sunday2
          AND date_updated <= :monday2";

And then:

$stmt->bindValue(':monday', $monday, PDO::PARAM_STR);
$stmt->bindValue(':sunday', $sunday, PDO::PARAM_STR);
$stmt->bindValue(':monday2', $monday, PDO::PARAM_STR);
$stmt->bindValue(':sunday2', $sunday, PDO::PARAM_STR);

So that it thinks you have the same number of PDO Tokens as you have bindValues.

answered Dec 19 '17 at 22:06

amflare

4,020
3
25
44

You can also bind multiple values at once by supplying an associative array to the `execute()` method. – tadman Dec 19 '17 at 22:51
Does that work without `PDO::ATTR_EMULATE_PREPARES`? – amflare Dec 19 '17 at 23:10
I don't know that there's anything different there, just in how they're passed in. – tadman Dec 19 '17 at 23:16
@tadman - the keys in this case (using an array) would be forced to be unique. ( as they would be the keys of the array ) So it would make no sense to have two array items with the key "sunday" but with different values. – ArtisticPhoenix Dec 19 '17 at 23:24
@ArtisticPhoenix An associative array has a constraint on unique keys, but this applies to placeholder names as well, so maybe it avoids making false assumptions that the order of the `bindValue` calls matter, or that two calls with different values for the same placeholder means two different values get injected, which won't happen. – tadman Dec 19 '17 at 23:27
@tadman - exactly, except that it would use the second assigned value in the array for both placeholders ( I'm like 90% sure of that ).... Its the second because the first if overwritten by it, and then it replaces all occurrences of the placeholder. – ArtisticPhoenix Dec 19 '17 at 23:28
@amflare Maybe you cn help me. Look at this : https://stackoverflow.com/questions/51838922/how-can-i-convert-many-statement-mysql-to-laravel-eloquent – moses toh Aug 15 '18 at 02:37

Migrating PHP PDO to Laravel PDO causes Invalid parameter number

1 Answers1