Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:115020
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_EB833A90-DE55-4DE3-A065-03D0D477BC22"
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\))
Message-ID: <4FFA0160-1A05-4DA0-9C9A-79F778443A35@newclarity.net>
Date: Tue, 22 Jun 2021 08:45:11 -0400
Cc: PHP internals <internals@lists.php.net>
To: Pierre <pierre-php@processus.org>
Subject: Sql Object Model Parser & Sanitizer (was [RFC] is_literal)
From: mike@newclarity.net (Mike Schinkel)

--Apple-Mail=_EB833A90-DE55-4DE3-A065-03D0D477BC22
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

> On Jun 22, 2021, at 6:45 AM, Pierre <pierre-php@processus.org> wrote:
>=20
> Le 22/06/2021 =C3=A0 11:28, Dan Ackroyd a =C3=A9crit :
>> On Tue, 22 Jun 2021 at 10:25, Mike Schinkel <mike@newclarity.net> =
wrote:
>>> Should(n't?) PHP add a basic SQL builder class that can be extended =
for special cases, e.g. different flavors of SQL?
>>>=20
>> No. Or at least not yet.
>>=20
>> This type of thing is much better done in userland, where the api can
>> evolve at a fast rate, rather than being limited by the fixed release
>> schedule of PHP.
>=20
> Agreed, PHP is probably not the right place for an SQL builder, =
there's too many dialects, too many standard or non-standard features, =
in the end SQL builders almost always end-up being opinionated by its =
designer's original need, philosophy, or SQL usage habits, and tailored =
for users which use certain paradigms.
>=20
> An SQL query builder is already business domain tied, in some way. Of =
course most are very generic, but often don't handle properly SQL =
advanced or modern features, whereas some other where built for ORMs and =
such and may a reduced advanced SQL features that fits the original =
need.
>=20
> I don't wish to see an SQL query builder in PHP core, instead of =
favoring usage of modern SQL, it'll in my opinion (of course, that's =
subjective) lower the expectation of people and probably normalize =
SQL-92 being the only SQL spoken by people writing PHP (just =
exaggerating a bit).


I think we are talking two different things here.  And I will admit I am =
probably the one that is not using exactly the right term.  I had =
envisioned something, and then Dan said "SQL Builder" I used that term.

Let me clarify by giving what I am referring to a different name:  A SQL =
Object Model with parser and sanitizer functionality.  One with a simple =
interface and one that would need not be tied to or limited by any =
specific dialect of SQL.

To illustrate what I mean I created the following straw man example.  It =
assumes a hypothetical SqlObjectModel classes that needs to be =
instantiated with an objects that implement hypothetical =
`SqlDialectInterface` and `SqlSchemaInterface` instances passed to its =
constructor.  The former defines the rules for the MySql dialect, and =
the latter accesses the schema to validate data and data types. =20

Some "dialects" and "validators" for very well-known and widely used =
database servers such as MySQL could be implemented in PHP core, while =
others could be implemented in userland (the database vendors would be =
good candidate to implement them, actually.)  I don't yet know what =
those interfaces should look like but I am pretty sure that could be =
figured out.

So here is the example (I omitted error handling logic for brevity and =
clarity):

$sql =3D <<<SQL
SELECT=20
    c.id AS company_id,
    c.company_name,
    jo.job_title,
    COUNT(*) AS job_openings =20
FROM companies c
    JOIN jobs_openings jo ON c.id=3Djo.company_id
WHERE 1=3D1
    AND city_name =3D 'Nantes'=20
    AND country_name=3D'France'
    AND job_openings > {openings}
GROUP BY
    c.company_name
LIMIT=20
    {limit}
SQL;

$conn =3D mysqli_connect(...);
$som =3D new SqlObjectModel(new MySqlDialect(), new MySqlSchema($conn));
$som->parse($sql);
$som->set( "limit", $_GET['limit'] );
$som->set( "openings", $_GET['openings'] );
$result =3D mysqli_query( $conn, $som->sql() );

The SQLObjectModel *could* potentially have additional features like the =
ability to get and set the table name, list of fields, joins, where =
clauses, etc. but the core value-add is not in building SQL but rather =
parsing and sanitizing with properly escaped and quoted arguments so =
that known-safe SQL could be generated.

Existing or new SQL builders could use this to take SqlObjectModel() and =
pass its generated SQL through to ensure their SQL they will output is =
safe, which by the way the string could now be tagged as "trusted" =
before the builder returns the SQL to its developer, assuming an =
is_trusted() RFC were to pass.

Similarly, any ORM could ensure its SQL is fully sanitized and then pass =
on to whatever functions are needed to execute the SQL.

So, absolutely nothing here would keep people from using "modern SQL," =
and/or cause people to lower the expectation to normalize SQL-92.  This =
could be a generic query parser and sanitizer that could be configured =
by the "dialect" and "schema" objects passed to it and its scope it =
small, simple and straightforward.

I hope that clarifies what I was thinking?

-Mike=

--Apple-Mail=_EB833A90-DE55-4DE3-A065-03D0D477BC22--