Laravel MSSQL Adhoc queries instead of prepared

Question

We are using MSSQL in combination with Laravel which uses the dblib PDO library from PHP. I've looked in the illuminate database part and see that it uses the PDO->prepare($query)->execute($bindings). So I am expecting that it would execute a prepared statement at the SQL database.

But when I check the table to analyse the queries that were run. I see that the queries didn't run as a prepared statement but instead the queries were classified as an Adhoc query.

Do any of you guys know why PHP/Laravel/FreeTDS/MSSQL is behaving like this or have any ideas on how to fix it?

Thanks!

We've tested this, and after swapping from freetds/pdo_dblib to sqlsrv/pdo_sqlsrv, prepared statements are now working. We don't have the expertise to track down /why/ everything is coming up as ad-hoc when coming via pdo_dblib, unfortunately. — mikey, Feb 27 '20 at 05:47

mikey · Answer 1 · 2020-02-27T09:25:51.977

I think prepared statements via freetds -> pdo_dblib are unsupported. I do not, however, have proof of this and apologise for making an 'answer' that is non-definitive. My hope is that this pushes the issue further along / provides some surface area for those with more knowledge.

Is it expected this functionality is supported? I would say yes.

https://stackoverflow.com/a/38361662/3080207
- http://www.freetds.org/userguide/choosingtdsprotocol.htm

As per that above thread, freetds 4.2 states:

dynamic queries (also called prepared statements) are not supported.

However, the omission of that from later versions would imply it is supported (we are using freetds 7.3).

docker-container:/test# tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.1.24
             freetds.conf directory: /usr/local/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.3
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: no
                             GnuTLS: no
                               MARS: yes

docker-container:/test# cat /etc/freetds/freetds.conf
[global]
    tds version = 8.0
    text size = 2147483647
    client charset = UTF-8

How am I determining this isn't working?

Unrelated to Laravel (although still PHP - 7.1) I am unable to get prepared statements working using the pdo_dblib driver.

The query I'm using to ascertain prepared statements are actually being used:

select cp.objtype, st.text, cp.*
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where text like '%LIMIT_TO_SPECIFIC_TEST%'
order by cp.objtype desc, usecounts desc, refcounts desc

I've tried a very simple query taking 2 int parameters, with the PHP code looking like:

$sql = "SELECT id FROM ExampleTable WHERE foreignIdOne = ? AND foreignIdTwo = ?";
$statement = $this->_connection->prepare($sql);
$valueOne = 2;
$valueTwo = 56;
$statement->bindParam(1, $valueOne, PDO::PARAM_INT);
$statement->bindParam(2, $valueTwo, PDO::PARAM_INT);
$statement->execute();

Is there a workaround?

Using the /exact/ same PHP code and changing the underlying driver to pdo_sqlsrv, I can begin to see the usecounts increase, whereas with pdo_dblib I do not see that.

The above SO thread had a 'solution' of swapping to the ODBC driver (although not clarifying why this isn't working with dblib).

What is the actual cause?

So, it is still unclear to me why this functionality doesn't seem to be working. The only other information I can find about this is from a buried php.net bug from 2017:

https://bugs.php.net/bug.php?id=74592

Laravel MSSQL Adhoc queries instead of prepared

1 Answers1