CI 3.1.13 and PHP 8.1: query builder with sqlsrv driver is trying to use backticks as the escape character

[ssmutz]

I recently upgraded my development server to PHP 8.1 due to Microsoft removing support for 8.0 from its pecl packages for sqlsrv. However I'm running in to this issue with queries created by query builder.

The queries being created are using backticks instead of double quotes or square brackets as the escape character.

When running most queries, I get errors like this

Error Number: 42000/102

[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near '`'.

SELECT `RecordId`, `EmployeeId`, `TimeIn`, `TimeOut` FROM `EmployeeHours` WHERE `EmployeeId` = 1 ORDER BY `TimeIn` DESC OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

Filename: models/Timeclock_model.php

Line Number: 111

Where as on my 8.0 production server, the same query is generated like this

SELECT "RecordId", "EmployeeId", "TimeIn", "TimeOut"
FROM "EmployeeHours"
WHERE "EmployeeId" = '1'
ORDER BY "TimeIn" DESC
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY  

Writing out the full query string myself works, but is not ideal. I'd rather be able to continue to use the query builder.

[jamieburchell]

@ssmutz Are you somehow using the wrong database driver?

Here's the escape character used for each driver:

[ssmutz]

I'm using the sqlsrv driver, which is the same one that works fine on PHP 8.0. I changed nothing in CI since 3.1.13 supposedly supports PHP 8.1.

[jamieburchell]

Not sure where you read that, but I can tell you 3.1.13 hasn't been updated in years. That's why a few forks have emerged.

[ssmutz]

I mostly read it here in the issues while searching for info on 8.1, 8.2, and 8.3 support. Most of the posts I found implied that PHP 8.1 was the latest I could use without resorting to using forks. A few of those posts are even from "contributors" here.

In any case, I put together an environment with the old sqlsrv pecl package that still supports 8.0 and am preparing to start rewriting the project with CI 4. I was hoping for a quick fix to help keep the project on CI 3 a bit longer while also upgrading to PHP 8.1.

[jamieburchell]

Not sure. I can't see anywhere in the PDO or sqlsrv drivers that use a backtick, less why it would change in PHP 8.1 :/

Are you using the ODBC driver? Could you try PDO or sqlsrv drivers directly maybe?