framework: laravel PDO invalid attribute exception with PHP 8.2.9/8.1.22

An alternative to downgrading to PHP 8.1.21 or 8.2.8, if is not possible or not an option, the following could be of help.

Set a custom connection class to be used by Laravel by registering it in the service container:

Extend SqlServerConnector:

class CustomSqlServerConnector extends \Illuminate\Database\Connectors\SqlServerConnector
{
    protected $options = [
        PDO::ATTR_CASE => PDO::CASE_NATURAL,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
        PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE => true,
    ];
}

Then, from AppServiceProvider, register it in the service container.

$this->app->bind('db.connector.sqlsrv', CustomSqlServerConnector::class);

It has been released!

https://github.com/microsoft/msphpsql/releases/tag/v5.11.1

An alternative to downgrading to PHP 8.1.21 or 8.2.8, if is not possible or not an option, the following could be of help.

Set a custom connection class to be used by Laravel by registering it in the service container:

Extend SqlServerConnector:

class CustomSqlServerConnector extends \Illuminate\Database\Connectors\SqlServerConnector
{
    protected $options = [
        \PDO::ATTR_CASE => \PDO::CASE_NATURAL,
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_ORACLE_NULLS => \PDO::NULL_NATURAL,
        \PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE => true,
    ];
}

Then, from AppServiceProvider, register it in the service container.

$this->app->bind('db.connector.sqlsrv', CustomSqlServerConnector::class);

For those who do not have the option of rolling back to 8.2.8/8.1.21 and have to implement this solution as a stopgap instead:

If you’re adding this custom connector somewhere within the logic of your app (i.e., in app/Database/Connectors), don’t forget to either add a backslash before all uses of PDO (as I’ve done in the quote above), or add a use PDO; statement at the top – otherwise you’ll get complaints that the class App\Database\Connectors\PDO cannot be found.

Okay i already check. This is from PHP side. When you use PHP 8.2.9 and 8.1.22, you will get this kind of problem. Downgrade PHP version to 8.2.8, the problem is solved.

I have same problem with this. Already format a laptop and make a fresh installation with PHP and Laravel. I got same error message Illuminate \ Database \ QueryException SQLSTATE[IMSSP]: An invalid attribute was designated on the PDO object.

My solution…

I have reviewed the PDO documentation, and you can specify the “error mode” with the PDO::ATTR_ERRMODE attribute: https://www.php.net/manual/en/pdo.error-handling.php

Laravel set the PDO::ERRMODE_EXCEPTION value by default, the quick and easy solution is set value to PDO::ERRMODE_SILENT in config/database.php with “options” key`:

'sqlsrv' => [
    'driver' => 'sqlsrv',
    ...
    'options' => [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT,
    ]
],

Another solution here is to create a custom connector and override the binding. This then also gives protection in the future to fix any object issues before a global patch becomes available, albeit you’d need to manage any updates/new requirements too.

<?php

namespace App\Database\Connectors;

use Illuminate\Database\Connectors\SqlServerConnector;
use PDO;

class CustomSqlServerConnector extends SqlServerConnector
{
    /**
     * Override the default options array to prevent the SQLSRV error:
     * SQLSTATE[IMSSP]: An invalid attribute was designated on the PDO object.
     *
     * @var array
     */
    protected $options = [
        PDO::ATTR_CASE => PDO::CASE_NATURAL,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
    ];
}

Then in App\Providers\AppServiceProvider add in the following:

/**
 * Register any application services.
 *
 * @return void
 */
public function register()
{
    $this->app->bind('db.connector.sqlsrv', \App\Database\Connectors\CustomSqlServerConnector::class);
}

The lead of PDO SQLSRV seems to have a negative opinion of hotfixes.

Well no one likes doing hotfixes but when you manage something used by so many people that is broken…

Really hoping a final fix isn’t weeks away.

It looks like this was fixed in microsoft/msphpsql@c648caf

Yes. @SakiTakamachi got that PR merged but still waiting for another msphpsql release. Next release wasn’t planned until end of the year though so waiting to hear back if they will do a patched fix in the mean time (really hope they do)

My solution to this for now was to re-write my docker container to build off the php image instead of Ubuntu. Using apt to install php prevented me from downgrading PHP easily between minor releases.

So if you can downgrade to php 8.1.21 that is the best solution until msphpsql releases a patched version.

@rickycheers

Does this work?

I think it can’t be replaced with DI because it’s directly new in ConnectionFactory.

Oops, excuse me, you can DI this.

I haven’t had time to test it yet, but I think it will probably look something like this.

If you wrap it with try catch, it will work as expected, so I think something like this would work too.

    protected function createPdoConnection($dsn, $username, $password, $options)
    {
        $stringifyFetches = null;
        if (array_key_exists(PDO::ATTR_STRINGIFY_FETCHES, $options)) {
            $stringifyFetches = $options[PDO::ATTR_STRINGIFY_FETCHES];
            unset($options[PDO::ATTR_STRINGIFY_FETCHES]);
        }

        $pdo = new PDO($dsn, $username, $password, $options);

        if (! is_null($stringifyFetches)) {
            try {
                $pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, $stringifyFetches);
            } catch (PDOException $e) {
                // do nothing
            }
        }

        return $pdo;
    }

https://github.com/SakiTakamachi/laravel-sqlsrv-err-avoid

@taylorotwell Please unpin this from the “issue” tab since it’s been closed for over 3 months.

Its released! I updated works good.

WIll do production tonight. A

On Fri, Sep 8, 2023 at 1:48 AM Saki Takamachi @.***> wrote:

It has been released!

https://github.com/microsoft/msphpsql/releases/tag/v5.11.1

— Reply to this email directly, view it on GitHub https://github.com/laravel/framework/issues/47937#issuecomment-1711112108, or unsubscribe https://github.com/notifications/unsubscribe-auth/ALF5SJDZYIUJ6X3IP4NBYKLXZKWS5ANCNFSM6AAAAAA3BXP6AQ . You are receiving this because you commented.Message ID: @.***>

On a positive note, it looks like there may be a patch this week from Microsoft.

This situation is an example of that. By killing the exception from trying to set PDO::ATTR_STRINGIFY_FETCHES, values returned from a query may have a different type than your application is expecting.

In this case, you don’t have to worry about that.

This is because the exception is raised at the end after the attribute is accepted by the driver.

This i think a more complete and dynamic solution, not only for PDO::ATTR_STRINGIFY_FETCHES… and not overwrite default connector options

I would be careful as the long term ramifications of silently killing any PDOException related to setting attributes may cause future issues. Suppose we encounter an issue where the driver from Microsoft drops support for an attribute that we expected to have a certain effect on the results of a query.

~This situation is an example of that. By killing the exception from trying to set PDO::ATTR_STRINGIFY_FETCHES, values returned from a query may have a different type than your application is expecting.~

Edit: As noted by Saki below, she did test that the try/catch solution for this scenario is fine. However, I do think it is worth being careful with the try/catch solution, as it will catch any PDOException related to setting attributes, not just for this specific scenario. Ideally, this would never be an issue, as Saki pointed out in a separate thread, the setAttribute method should only return false on failure (not raise an exception), however we see here that Microsoft’s messed this up once, there’s no telling if they might do it again in the future in a different way that ends up impacting the results of a query.

An alternative to downgrading to PHP 8.1.21 or 8.2.8, if is not possible or not an option, the following could be of help.

Set a custom connection class to be used by Laravel by registering it in the service container:

Extend SqlServerConnector:

class CustomSqlServerConnector extends \Illuminate\Database\Connectors\SqlServerConnector
{
    protected $options = [
        PDO::ATTR_CASE => PDO::CASE_NATURAL,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
        PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE => true,
    ];
}

Then, from AppServiceProvider, register it in the service container.

$this->app->bind('db.connector.sqlsrv', CustomSqlServerConnector::class);

This is the solution that worked for me.

In App\Providers, I created a file called CustomSqlServerConnector.php.

I placed the following code in that file:

<?php

namespace App\Providers;

use PDO;

class CustomSqlServerConnector extends \Illuminate\Database\Connectors\SqlServerConnector
{
    protected $options = [
        PDO::ATTR_CASE => PDO::CASE_NATURAL,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
        PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE => true,
    ];
}

Then I edited app/Providers/AppServiceProvider.php and now the file looks like this:

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        $this->app->bind('db.connector.sqlsrv', CustomSqlServerConnector::class);
    }

    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        //
    }
}

That seems like the cleanest workaround until we see Microsoft update the driver.

Since there was no response, I opened an issue. https://github.com/microsoft/msphpsql/issues/1474

These are the results with and without the option

You are right, in that case it returns false positives.

My solution is really quick and temporary… to be able to unlock the environments where the bug is occurring right now.

The final solution should be done at https://github.com/microsoft/msphpsql/

True but if you cant roll back this saves you behind… I rolled back production with in a hour after finding these issues back when .8 was out but now .11 i cant on dev and this was a decent temp fix. Is there a way to suppress just that stringafi issue? … On Wed, Sep 6, 2023 at 9:18 AM rianhe @.> wrote: This solution will silence EVERY PDO crash, including wrong queries which now will return empty array. I think it’s too dangerous… Are you sure? I have tried to execute wrong queries and exceptions are thrown… — Reply to this email directly, view it on GitHub <#47937 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/ALF5SJCKVXRQUYQG442PWMDXZBZ2BANCNFSM6AAAAAA3BXP6AQ . You are receiving this because you commented.Message ID: @.>

This will give back a result instead of an exception, the value will be [] and the logic after this query will treat it as a REAL VALUE, I think it’s crazy to use this without a custom error handling for each query…

The lead of PDO SQLSRV seems to have a negative opinion of hotfixes. I’m still convinced that the problem is on the PDO SQLSRV side, but it’s also true that it’s getting harder to fix early.

So, I proposed a temporary workaround on the php-src side. If you are following information on this issue, please check this: https://github.com/php/php-src/issues/12038

I see your point, but I think it doesn’t seem to fit the context of this issue.

If you wrap it with try catch, it will work as expected, so I think something like this would work too.

Don’t use a try catch to swallow errors. That is extremely bad practice.

Hello - just wanted to say thank you! 👍 After about 45mins of searching, I finally found this thread and it tells me everything I needed to know.

@SakiTakamachi thank you for your fix ServiceProvider - time saving!

@huynguyen-twentyci Yes, PDO SQLSRV does not support PDO::ATTR_TIMEOUT.

Also, you should write your other options like this:

'options' => [
    //
],
'encrypt' => true,
'trust_server_certificate' => true,
'MultipleActiveResultSets' => '0',

I have prepared a repository that will be somewhat better than a steady build. https://github.com/SakiTakamachi/pdo-sqlsrv-extend

However, this should only be used when “there is really no other option but to use this”.

We should revert back to the prev php version and wait for an official driver to be released.

I took a closer look at the code and it looks like it’s throwing a different exception after all. I will prepare a fix PR.

Opps, when I looked at it again, I found that the exception was caught.

I’ll check it out properly again.