[Animats]

" I still hate to compose SQL in code, but there is no better way."

The one big problem with SQL is intermixing query language and query parameters. This requires escaping and is the source of many PHP vulnerabilities. Instead of

    do_sql('SELECT * FROM tab WHERE name = "abc"')

there should have been a standard where you wrote

    do_sql('SELECT * FROM tab where name = V1", "abc")

or something like that. So you don't do string operations on the query parameters.

[setr]

Aren’t you referring to parameterized/prepared queries? Any proper DB driver/library should have this feature.

    do_sql('SELECT * FROM tab where name = ?", "abc")

https://www.php.net/manual/en/pdo.prepared-statements.php

I recall getting curious when I was younger where the deranged advice of “sanitize your queries” came from — realizing that you should be able to simply tell the database that this is a string, not part of the query itself. SQL injection should barely exist as a concept, let alone be the #1 web vulnerability.

IIRC it turned out MySQL supported parameterized queries for ages, but the stdlib php MySQL library just didn’t add support for it. This discovery solidified my understanding that PHP has been giving developers brain damage for decades.

[_y5hn]

Unless you're using hardcoded values, using bind variables is the only proper way. Otherwise, the DBMS has difficulty recognizing the same query pattern for performance, and you're open to injection vulnerabilities for security.

https://www.databasestar.com/sql-bind-variables/

The big problem is the shitty workplace environment of today that discourages sharing, learning and proper software development.

A proper build-pipeline will include security scanners that detects these vulnerabilities automagically.

[throwaddzuzxd]

I don't know if it's a standard but both Postgres and MySQL have PREPARE statements, and any sane language driver will implement it correctly