I sort of wonder if there's an alternate syntax that would give everything SQL needs but would not be as vulnerable to injection. Either that or a binary mapping that would trivially allow a text front end but have length-delimited parts.
Little Bobby Tables deserves to have his name accurately represented in the database. This could involve proper escaping (difficult) or parameterized queries/prepared statements (easy). But many sqli mitigations just reject his name out of hand.
I will say that sometimes I'm a little thankful for SQL injection, or at least a little bit of leeway for abuse. Many applications implement partial search using "like '${str}%'" and if they don't escape % I can also search for my string in the middle. There have been times when I'm able to inject additional columns or rename things to exfiltrate data that should have been available but the app developer didn't make it accessible. Unfortunately, in the wrong hands it is able to break things. Even accidentally.
The problem is not really syntax. Almost any syntax which embeds user supplied strings is vulnerable to syntax hacks.
The only way I could see to avoid an embedded string altering syntactic meaning is if the syntax either gives the length of the parameter before it, or surrounds the parameter with some unguessable values. For example:
SELECT "10:xxxxxxxxxx" FROM USERS;
SELECT "s63hfe&:xxxxxxxxxxs63hfe&" FROM USERS;
"just" use concat or similar (depending on your database flavour) with "?":
#!/usr/bin/env perl
use 5.020_000;
use warnings;
use DBI qw<>;
my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:');
my $sth = $dbh->prepare('SELECT ("quux-" || ?) AS foo');
$sth->execute('bar');
say $sth->fetchrow_hashref->{foo};
I was thinking like an application of JSON that would be more likely to be produced by stringify than by concatenation. Then escaping would come "for free". It wouldn't guarantee the right thing but it would make it much more likely.
Little Bobby Tables deserves to have his name accurately represented in the database. This could involve proper escaping (difficult) or parameterized queries/prepared statements (easy). But many sqli mitigations just reject his name out of hand.
I will say that sometimes I'm a little thankful for SQL injection, or at least a little bit of leeway for abuse. Many applications implement partial search using "like '${str}%'" and if they don't escape % I can also search for my string in the middle. There have been times when I'm able to inject additional columns or rename things to exfiltrate data that should have been available but the app developer didn't make it accessible. Unfortunately, in the wrong hands it is able to break things. Even accidentally.