Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:115024
To: Kamil Tekiela <tekiela246@gmail.com>, Mike Schinkel <mike@newclarity.net>
Cc: PHP internals <internals@lists.php.net>
References: <4FFA0160-1A05-4DA0-9C9A-79F778443A35@newclarity.net>
 <CAGBsUre=Asp+arHBWK0VdT-YdM=_3rR+WOhQQeghFVwsR+nGUg@mail.gmail.com>
Message-ID: <a10c4105-8e6f-3036-bd4c-276ba369150f@processus.org>
Date: Tue, 22 Jun 2021 15:26:39 +0200
MIME-Version: 1.0
In-Reply-To: <CAGBsUre=Asp+arHBWK0VdT-YdM=_3rR+WOhQQeghFVwsR+nGUg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Subject: Re: [PHP-DEV] Sql Object Model Parser & Sanitizer (was [RFC]
 is_literal)
From: pierre-php@processus.org (Pierre)

Le 22/06/2021 à 15:00, Kamil Tekiela a écrit :
> 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 idea
> behind SQL builder is to generate SQL, not to allow the data to be
> sanitized.
> Every time I hear the word sanitize I get goose bumps. You can't remove any
> characters from a string to make it safe. If you want to use escaping, then
> you need to do it context aware and properly formatted. Don't sanitize
> anything. Format the SQL properly instead.
>
> On a general note. Implementing SQL builder in PHP would be an enormous
> task, which is not feasible. There are so many dialects, so many options,
> and even then it won't ever be accurate as you don't have the full context
> in PHP. SQL is a very powerful language, and building a parser for it in
> PHP would mean that we either limit it to a subset of valid SQL commands,
> or we try to create a super tool that is more powerful than MySQL, Oracle,
> PostgreSQL, etc. combined.
> There's absolutely nothing wrong with writing SQL in PHP and preparing it
> on the server. For database servers that don't support prepared statements
> we already have PDO which is an abstraction library that tries to escape
> and format data within SQL. It works 99% of the time.
>
> The example you suggested already has a simple syntax in PHP.
>
> $conn = mysqli_connect(...);
> $stmt = $conn->prepare($sql);
> $stmt->execute([$_GET['openings'], $_GET['limit']]);
>
> Regards,
> Kamil
>
I fully agree with you. Any attempt to be smart in doing this will 
eventually end-up in no-so-smart corner-case bugs that will be very, 
very hard to deal with. It's almost an impossible mission. And if by any 
luck you'd succeed in making this work, it probably would be a 
maintenance nightmare.

Regards,

--

Pierre