Newsgroups: php.internals Path: news.php.net Xref: news.php.net php.internals:115025 Return-Path: Delivered-To: mailing list internals@lists.php.net Received: (qmail 65580 invoked from network); 22 Jun 2021 13:21:32 -0000 Received: from unknown (HELO php-smtp4.php.net) (45.112.84.5) by pb1.pair.com with SMTP; 22 Jun 2021 13:21:32 -0000 Received: from php-smtp4.php.net (localhost [127.0.0.1]) by php-smtp4.php.net (Postfix) with ESMTP id 243151804C3 for ; Tue, 22 Jun 2021 06:39:48 -0700 (PDT) X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on php-smtp4.php.net X-Spam-Level: X-Spam-Status: No, score=-1.9 required=5.0 tests=BAYES_00,DKIM_SIGNED, DKIM_VALID,HTML_MESSAGE,RCVD_IN_DNSWL_NONE,RCVD_IN_MSPIKE_H2, SPF_HELO_NONE,SPF_NONE autolearn=no autolearn_force=no version=3.4.2 X-Spam-Virus: No X-Envelope-From: Received: from mail-qk1-f169.google.com (mail-qk1-f169.google.com [209.85.222.169]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-256) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by php-smtp4.php.net (Postfix) with ESMTPS for ; Tue, 22 Jun 2021 06:39:47 -0700 (PDT) Received: by mail-qk1-f169.google.com with SMTP id c23so3992849qkc.10 for ; Tue, 22 Jun 2021 06:39:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=newclarity-net.20150623.gappssmtp.com; s=20150623; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=YmoF37UZ0fX6PAq9u4ZbXHAHZoOMh7CZrMw0YgPvT8c=; b=JfRyt2LUQrRcdBQBANnuQeh/0ZeYkwd7xsRm8YYVLU9Bn+9UOuW8+hoYkHQF4eEQ2V RwUQt3XhYIdouO3fXDKCc9OoeQFYEC6B0/pCF+LJxuI2Wl1a5qw0M0RdVpM+jeQIg3E+ Tcp9HYYVWfCFdMqDiafQMbK9Fg3aR2/dg5PVwcwgdSX1OAdmJ7Ga7xLJTjnC2vhWEoab hKE7anNFVyCHBU5FNN3yzGbg6ofrVFPU0Ebl0V4vkaXksb2t74gY3OnvVW3sppNYoPHt pi03BJMoSsk2IIhrdczWlXI+68suUNpfkacRGnSD15oPKw1Zv9S7kmNWsPwvpRA2psgI nJwg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=YmoF37UZ0fX6PAq9u4ZbXHAHZoOMh7CZrMw0YgPvT8c=; b=URzHVcQEWEtrECmAkLuYRjO0xRcCE9PxbkwsdCTOYlBHNuhPqAVQ4tIPtesJmkrZ8n TFdLOTJkqJHHbCRqhWuFDcHUPNiS3mrP27LbapqW6JWpdGcTpki6E8ExjpwzLw5aUPAW 46QLuzs4Gsi6LKf+HgpdnyEPDOLtqekozX7wIGp5HsFHF7v003Juox6cSVZ+q+0aHCqV ImHd+BcBlsR18ILs0jKgwwCA9vAQEr+ugXYxgF/R51802M9/ULmNsT3kq5cB/OHikluT rGcSvFPDZgNyDLniwvdI21o+T/fH7TiBQRnkOOr+OnIr8wrM5qUc2ydSfAq9Jtf6XshD vO2w== X-Gm-Message-State: AOAM532jymc8UK+CeAf6N6SpQwsl6xtKYuxEma3W1aqb3VTKzs+UNSEJ uX4G5QjF8pYyE++QMI3YgFAfBw== X-Google-Smtp-Source: ABdhPJxMindHQN2IyOcvuFj79hBpLB7rd0eAqi4YDNTcNNT69NUqbe+NWgviD5vxo13fETM5ZQ0Mwg== X-Received: by 2002:a05:620a:a87:: with SMTP id v7mr4366759qkg.468.1624369185463; Tue, 22 Jun 2021 06:39:45 -0700 (PDT) Received: from [192.168.1.10] (c-24-98-254-8.hsd1.ga.comcast.net. [24.98.254.8]) by smtp.gmail.com with ESMTPSA id o5sm12496221qkl.25.2021.06.22.06.39.43 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 22 Jun 2021 06:39:44 -0700 (PDT) Message-ID: Content-Type: multipart/alternative; boundary="Apple-Mail=_59265CAA-91C0-40B8-B8FF-510A4881A3CE" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\)) Date: Tue, 22 Jun 2021 09:39:42 -0400 In-Reply-To: Cc: PHP internals To: Kamil Tekiela References: <4FFA0160-1A05-4DA0-9C9A-79F778443A35@newclarity.net> X-Mailer: Apple Mail (2.3608.120.23.2.7) Subject: Re: [PHP-DEV] Sql Object Model Parser & Sanitizer (was [RFC] is_literal) From: mike@newclarity.net (Mike Schinkel) --Apple-Mail=_59265CAA-91C0-40B8-B8FF-510A4881A3CE Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jun 22, 2021, at 9:00 AM, Kamil Tekiela = wrote: >=20 > Hi Mike, >=20 > 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.=20= 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. =20= A SQL object model parser and sanitizer could more easily be used = incrementally by that CMS since PDO does not share connections with = mysqli (AFAIK, anyway.) > The idea behind SQL builder is to generate SQL, not to allow the data = to be sanitized.=20 That is why the title of this email said "Parse and Sanitizer" not = "Builder." > 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.=20 I believe you are latching onto the word sanitizing as you understand it = and ignoring the context of the discussion. What I believe I am proposing is to implement an object that would be = SQL-syntax-aware and =E2=80=94 to use your words =E2=80=94 "format the = SQL properly." =20 But maybe I am wrong? Can you explain how what I am proposing would not = be: - "Removing the characters" needed to make it safe? =20 - Context aware? Note that I did not specifically talk about removing characters, and I = did talk about context. I talked about parsing the SQL so as to ensure = it is safe. > On a general note. Implementing SQL builder in PHP would be an = enormous task, which is not feasible. Is it possible you just scanned the first email in this thread and did = not fully read it? =20 For the email to which you replied I literally wrote: 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 = (wrongly.)Let me clarify by giving what I am referring to a different = name: A SQL Object Model with parser and sanitizer functionality. What I was discussing would not be the enormous task you are referring = to. I was not proposing creating anything at all like these first two = Google results for "php sql query builder: [1] and [2]. I was proposing = a SQL object model, parser and "safe SQL generator" (I changed the last = word since you triggered on it.) > 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. My email addressed how dialects and options would be handled. Using = dependency injection of objects that define each dialect and provide = access to the schema. > 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.=20 That is a false binary. It would not be a huge undertaking to create a = generic query parser that used an object implementing an interface to = provide it with the information needed to correctly parse a query = _*enough*_ to sanitize it. =20 And classes defining any given SQL subset could, if not in PHP core, be = written in userland. And the DB vendor is a likely candidate to write = them too. > 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.=20 "Nothing _wrong_ with" (per se), is true. =20 But for applications that have 15 years of legacy code using mysql(i), = PDO is a non-starter. > The example you suggested already has a simple syntax in PHP.=20 >=20 > $conn =3D mysqli_connect(...); > $stmt =3D $conn->prepare($sql); > $stmt->execute([$_GET['openings'], $_GET['limit']]); If PHP were to add functionality to core to use prepared statements with = the mysqli connection, this discussion _might) be different. But, as I said, for the CMS that controls >40% of the web PDO is a = non-starter. OTOH, what about when a developer needs to parameterize field and table = names in PDO[3]? PDO does not address that aspect of assembling a = known-safe SQL string and requires manual sanitization. What I am = proposing _*would*_ address this. =20 IOW, PDO is a tool for a different albeit overlapping use-case, and = could even leverage PDO which as applicable to achieve its objectives. -Mike [1] https://github.com/nilportugues/php-sql-query-builder [2] https://github.com/ClanCats/Hydrahon [3] https://stackoverflow.com/a/182353/102699 --Apple-Mail=_59265CAA-91C0-40B8-B8FF-510A4881A3CE--