Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:115030
User-Agent: Cyrus-JMAP/3.5.0-alpha0-530-gd0c265785f-fm-20210616.002-gd0c26578
Mime-Version: 1.0
Message-ID: <9e8faf2b-fdc7-49f4-bdc1-0822841616e2@www.fastmail.com>
In-Reply-To: <F861461F-D070-4808-8B2E-A9BBF753CE6C@newclarity.net>
References: <4FFA0160-1A05-4DA0-9C9A-79F778443A35@newclarity.net>
 <CAGBsUre=Asp+arHBWK0VdT-YdM=_3rR+WOhQQeghFVwsR+nGUg@mail.gmail.com>
 <F861461F-D070-4808-8B2E-A9BBF753CE6C@newclarity.net>
Date: Tue, 22 Jun 2021 10:41:09 -0500
To: "php internals" <internals@lists.php.net>
Content-Type: text/plain
Subject: =?UTF-8?Q?Re:_[PHP-DEV]_Sql_Object_Model_Parser_&_Sanitizer_(was_[RFC]_i?=
 =?UTF-8?Q?s=5Fliteral)?=
From: larry@garfieldtech.com ("Larry Garfield")

On Tue, Jun 22, 2021, at 8:39 AM, Mike Schinkel wrote:
> > On Jun 22, 2021, at 9:00 AM, Kamil Tekiela <tekiela246@gmail.com> wrote:
> > 
> > Hi Mike,
> > 
> > Please don't do this. We already have PDO with prepared statements. The data must be bound. This is the secure way of writing SQL queries. 
> 
> The problem is that over 40% of the web currently runs on PHP code that 
> using mysqli.  That CMS does not support PDO nor prepared statements, 
> and is unlikely to switch to it anytime some in the foreseeable future. 

WordPress is not going to leverage anything we do here until and unless there is a major change of leadership and culture at that project.  Please don't waste any mental effort on it; they clearly waste no mental effort on what the rest of the PHP community considers good, secure practices.  Anything involving them is tilting at windmills.

Mike, speaking as someone who has written an SQL abstraction layer and query builder with significant usage (Drupal 7-9), you are *grossly* under-estimating the complexity of what you describe.  It might be possible to hack together for SQL92, aka "what most PHP devs actually use because they haven't noticed that it's not 1992 anymore", but that's already been done.  We have DBTNG in Drupal, we have Doctrine, problem solved.

Modern SQL, though, is a stupidly complex and stupidly inconsistent beast.  Most of the syntax beyond the basics is different on every damned database.  The official spec *is not even publicly available*, and costs a lot of money to access.  And no DB engine actually supports all of it; they all support different subsets with their own different extensions that may or may not be comparable.

Building a tool that parses an arbitrary string to an AST for a spec that is inconsistent, inaccessible, and not implemented correctly by anyone is a fool's errand, and that's just the first part of it.  That's not even getting into designing an API for people to modify it, or questions of performance, or compiling the AST back into a DB-specific string, AND then doing parameter binding which varies from one database to another.

You're talking about reimplementing major portions of MySQL, PostgreSQL, Oracle, etc. themselves in PHP, all at the same time.  Well, good luck, you're going to need it.

Personally I've long since concluded that database portability is no longer an achievable or even desirable feature.  SQL is just too fragmented a language, leaving you with a least common denominator that is grossly under-whelming for modern needs.  If you want more than SQL92, it's not really viable anymore.

--Larry Garfield