Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:115021
To: Mike Schinkel <mike@newclarity.net>
Cc: PHP internals <internals@lists.php.net>
References: <4FFA0160-1A05-4DA0-9C9A-79F778443A35@newclarity.net>
Message-ID: <33ec1992-7176-2757-af67-d9d51a602fca@processus.org>
Date: Tue, 22 Jun 2021 14:57:54 +0200
MIME-Version: 1.0
In-Reply-To: <4FFA0160-1A05-4DA0-9C9A-79F778443A35@newclarity.net>
Content-Type: multipart/alternative;
 boundary="------------658A795BE293F68CAEDEA5A6"
Content-Language: en-US
Subject: Re: Sql Object Model Parser & Sanitizer (was [RFC] is_literal)
From: pierre-php@processus.org (Pierre)

--------------658A795BE293F68CAEDEA5A6
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

Le 22/06/2021 à 14:45, Mike Schinkel a écrit :
>
>     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.
>
>     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 = <<<SQL
>     SELECT
>     c.id <http://c.id> AS company_id,
>         c.company_name,
>         jo.job_title,
>         COUNT(*) AS job_openings
>     FROM companies c
>         JOIN jobs_openings jo ON c.id <http://c.id>=jo.company_id
>     WHERE 1=1
>         AND city_name = 'Nantes'
>         AND country_name='France'
>         AND job_openings > {openings}
>     GROUP BY
>         c.company_name
>     LIMIT
>         {limit}
>     SQL;
>
>     $conn = mysqli_connect(...);
>     $som = new SqlObjectModel(new MySqlDialect(), new MySqlSchema($conn));
>     $som->parse($sql);
>     $som->set( "limit", $_GET['limit'] );
>     $som->set( "openings", $_GET['openings'] );
>     $result = 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

Actually, it makes more sense.

It's even funnier if you consider it, it's more or less what I'm doing 
in my own SQL builder for find and replace placeholders, after the query 
builder actually built the query. I'm not replacing placeholders with 
values, but I'm replacing my own API arbitrary placeholders with those 
expected by the underlaying dialect or low-level API (I'm not naive 
enough to concatenate user strings in SQL, I'm leaving the hard escaping 
work to the server or lower-level db connector API using prepared 
queries when the low level driver doesn't handle it properly).

But that said, what you describe already intersects with most SQL 
builders own features, and since they all have very different 
architecture, I'm not sure this kind of helper would be that much 
beneficial. But that something that could make sense to be discussed.

In all cases, thanks for answering.

Regards,

--

Pierre



--------------658A795BE293F68CAEDEA5A6--