framework: [5.2] Can't reuse named database query parameters

Ah, it has to go in an ‘options’ subarray, like this:

        'mysql' => [
            'driver'    => 'mysql',
            ...
            'options' => [
                PDO::ATTR_EMULATE_PREPARES => true,
            ],
        ],

But there is still an error. It has changed, and is now Invalid parameter number: number of bound variables does not match number of tokens (SQL: select * from`users`as`u`where id = :id or id < :id limit 1)'.

(Before, the whole thing was Invalid parameter number (SQL: select * from`users`as`u`where id = :id or id < :id limit 1)')

I didn’t realize until now this was closed. Gotta love the Laravel maintainers closing tickets without comment. Is it fixed? Is it considered not to be a bug? These questions and more left to the reader. What fun.

@victorheid that’s fine for some value you control, but a potential SQL injection vulnerability for any kind of externally-supplied input like form field contents.

I was trying to use this feature, but at the end had all the problems stated in this thread.

What I ended up doing is:

$parameter = 2;
// ...
    ->whereRaw("u.id > $parameter or u.id < $parameter")
// ...

Found that this at least keep readability and don’t require doubling up any variable…

Be careful! 'options' => [ PDO::ATTR_EMULATE_PREPARES => true, ]

By enabling this option, your application may break in several places which you are not aware of. For me it broke Laravel Passport authentication, and also 1,0 stopped casting to booleans, which caused several issues. There might have been even more, so i turned this thing off.

For anyone using Illuminate/Database in isolation, the PDO options are added as an extra key to the Manager::addConnection call, as below:

use Illuminate\Database\Capsule\Manager as Capsule;

$capsule = new Capsule;
$capsule->addConnection([
    'driver' => 'mysql',
    'host' => $db_host,
    'database' => $db_name,
    'username' => $db_username,
    'password' => $db_password,
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'options' => [
        PDO::ATTR_EMULATE_PREPARES => true,
    ]
]);

This DOES allow the parameters to be used in multiple places in within a query according to my tests, which, IMHO, is far preferable to cut & pasting the same value into multiple separate parameters.

From the PHP manual:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

http://php.net/manual/en/pdo.prepare.php

So I guess you’ll have to create multiple keys with the same value.

You can set it ON from your config/database.php file in the required connection configurations:

 PDO::ATTR_EMULATE_PREPARES => true,