Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:123150
Feedback-ID: i6f4c46c2:Fastmail
Message-ID: <1d64e5c6-5a77-41a3-8d68-ae88aeed611f@beccati.com>
Date: Wed, 17 Apr 2024 18:19:34 +0200
Precedence: bulk
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: [PHP-DEV] [RFC][Discussion] PDO driver specific parsers
To: Larry Garfield <larry@garfieldtech.com>,
 php internals <internals@lists.php.net>
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>
Content-Language: it
In-Reply-To: <26c47eb5-a60a-459d-89e6-16f6f0634cba@app.fastmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
From: php@beccati.com (Matteo Beccati)

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 ;-)


Cheers
-- 
Matteo Beccati

Development & Consulting - http://www.beccati.com/