> On Jun 22, 2021, at 9:00 AM, Kamil Tekiela <tekiela...@gmail.com> wrote: > > 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 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. 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. 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. 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 — to use your words — "format the SQL properly." But maybe I am wrong? Can you explain how what I am proposing would not be: - "Removing the characters" needed to make it safe? - 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? 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. 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. 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. "Nothing _wrong_ with" (per se), is true. 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. > > $conn = mysqli_connect(...); > $stmt = $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. 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
