Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:123153
Feedback-ID: i8414410d:Fastmail
User-Agent: Cyrus-JMAP/3.11.0-alpha0-379-gabd37849b7-fm-20240408.001-gabd37849
Precedence: bulk
MIME-Version: 1.0
Message-ID: <8d33e076-b579-450f-8254-587db4bf6ea1@app.fastmail.com>
In-Reply-To: <1d64e5c6-5a77-41a3-8d68-ae88aeed611f@beccati.com>
References: <ff5785b8-ba73-4302-9e1a-afaba34650bb@beccati.com>
 <00079548-1799-4215-8F73-EFDCD795532F@sakiot.com>
 <098996d6-32e4-460c-a82a-aeef3b7e61d3@beccati.com>
 <9A6577BD-3BC5-41E1-965D-4B30D16CDB3D@sakiot.com>
 <a38f4b1c-bb6d-47e2-9220-21493584a5f6@beccati.com>
 <26c47eb5-a60a-459d-89e6-16f6f0634cba@app.fastmail.com>
 <1d64e5c6-5a77-41a3-8d68-ae88aeed611f@beccati.com>
Date: Wed, 17 Apr 2024 19:35:00 +0000
To: "php internals" <internals@lists.php.net>
Subject: Re: [PHP-DEV] [RFC][Discussion] PDO driver specific parsers
Content-Type: text/plain
From: larry@garfieldtech.com ("Larry Garfield")

On Wed, Apr 17, 2024, at 4:19 PM, Matteo Beccati wrote:
> Hey Larry,
>
> Il 17/04/2024 16:51, Larry Garfield ha scritto:
>> This all seems logical, but having separate parsers would mean that the SQL strings are no longer portable, yes?  Eg, many frameworks and CMSes try to (claim to) support multiple DBs transparently.  (MySQL and Postgres and SQLite, usually).  Some even recommend using SQLite for testing, but MySQL for prod.  This change would break that, wouldn't it?  Because the escaping would necessarily be different for MySQL and SQLite, and thus the queries would break on one or the other?
>
> Nope. If you hardcode strings in your SQL, then it's your responsibility 
> to write them with the correct syntax.
>
> For example a `SELECT "foo"` will work on MySQL, but not on Postgres 
> already, and this RFC won't change that.
>
> Likewise, when using single quotes, `SELECT '\\'` will get you a single 
> backslash on MySQL right now, but two backslashes on Postgres, 
> regardless of this RFC.
>
> The only proper way to safely hardcode literals is to use the 
> `PDO::quote` method, which will take care of all the required escaping 
> (and charset stuff), according to the connected database. But then 
> again, most likely using parameters would be best in many circumstances.
>
> As for recommending testing on SQLite when production is on MySQL, I've 
> always found that to be a (huge) foot gun. Of course YMMV ;-)

I did not say I endorse the idea, just that I have seen it done. :-)  And some applications purport to run on your choice of MySQL or Postgres, even if they tend to not test the latter very well.

In any case, good to know that this won't make the situation worse.  It's probably a good idea to include some version of that in the RFC for clarity.

--Larry Garfield