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

[tremby]

When attempting a query like this:

DB::table('users as u')
    ->select('id')
    ->whereRaw('u.id > :id or u.id < :id', [
        'id' => 2,
    ])
    ->first();

a QueryException is thrown with the message Invalid parameter number. This looks to me like a bug. Or is this the expected behaviour? I would have expected a named parameter to be usable any number of times.

(Obviously the query above could be rewritten; it's just to show the issue.)

[themsaid]

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.

[tremby]

@willrowe,

Named parameters are not used in Laravel, the bindings are made positionally.

Not true. Under Running Raw SQL Queries in the manual there's a "Using Named Bindings" section which says:

Instead of using ? to represent your parameter bindings, you may execute a query using named bindings:

$results = DB::select('select * from users where id = :id', ['id' => 1]);

[tremby]

@themsaid, that's interesting; thanks. That may be case closed, or maybe emulation mode should be on by default. A number of resources I've just read recommend switching it on anyway, at least for MySQL.

Do you know how to switch it on in Laravel, off hand?

[themsaid]

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

 PDO::ATTR_EMULATE_PREPARES => true,

[tremby]

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 * fromusersasuwhere id = :id or id < :id limit 1)'.

(Before, the whole thing was Invalid parameter number (SQL: select * fromusersasuwhere id = :id or id < :id limit 1)')

[j4m3s]

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.

[ursuleacv]

Yii query builder allows the same parameter to be used multiple times. It's annoying, especially when writing complex queries with a lot of parameters that are used multiple times

[tremby]

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.

[j4m3s]

@tremby could you post the whole of your query in a three-backtick block? I'll run it using standalone Illuminate/Database setup and see if I get the same error as you do. I've been using it with the PDO::ATTR_EMULATE_PREPARES => true for some months and haven't had an issue.

I have seen a similarly message to yours in the past where I had an apostrophe (quote-mark) within a comment but was being interpreted as part of the actual query.

[tremby]

@j4m3s, is the query in my original post (from 2016-03-13, so I no longer have any idea exactly what I was trying to do) a suitable example?

[j4m3s]

Ah sorry @tremby, I only use flat SQL rather than the query builder so it may well be that your problem comes from within the query builder. That would explain why I haven't seen any issues with it since adding the extra PDO setting.

[chimit]

Laravel documentation is unclear here. I spent a lot of time trying to reuse one binding multiple times (table prefix).

[tylik1]

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.

[victorheid]

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...

[tremby]

@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.

[victorheid]

@tremby true. It's quite annoying that it does not work as intended to. I prefer whereRaw for writing more complex queries as it can get quite hard to understand when using too much ORM magic.