Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:115022
MIME-Version: 1.0
References: <4FFA0160-1A05-4DA0-9C9A-79F778443A35@newclarity.net>
In-Reply-To: <4FFA0160-1A05-4DA0-9C9A-79F778443A35@newclarity.net>
Date: Tue, 22 Jun 2021 14:00:13 +0100
Message-ID: <CAGBsUre=Asp+arHBWK0VdT-YdM=_3rR+WOhQQeghFVwsR+nGUg@mail.gmail.com>
To: Mike Schinkel <mike@newclarity.net>
Cc: Pierre <pierre-php@processus.org>, PHP internals <internals@lists.php.net>
Content-Type: multipart/alternative; boundary="00000000000097f82005c55a5fb2"
Subject: Re: [PHP-DEV] Sql Object Model Parser & Sanitizer (was [RFC] is_literal)
From: tekiela246@gmail.com (Kamil Tekiela)

--00000000000097f82005c55a5fb2
Content-Type: text/plain; charset="UTF-8"

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

--00000000000097f82005c55a5fb2--