Making SQL/HTML Injection attacks programmatically impossible

Question

Apparently having 100% prevention of SQL Injection and XSS attacks is easier said than done, but why?

Can't static code analysis tools ensure that all user supplied input vectors (including user tainted variables) are sanitized? Or enforcing it with a restrictive programming language or framework?

Wouldn't the below rules make XSS and SQL Injection impossible?

• SQL Injection immunity - enforce every query with the database is done with prepared statements and bind variables.
  • For rare circumstances where prepared statements harm performance in an unacceptable way, enforce strong validation and/or character escaping rules for the context of the underlying database technology.
• HTML Injection immunity - enforce all HTML generated pages to be done with templates (much like prepared statements with bind variables) where every user supplied variable, or user tainted variable, is put into slots with context specific rules for escaping or stripping special characters.
• Encoding Mixup immunity - enforce encoding consistency between all inputs and outputs.
  • E.g. Requiring <meta charset="utf-8"> as a header on all HTML templates and ensuring all data is processed with that same encoding.

So what's the problem with this idea?

• Is this just not feasible for all use cases? If so, what are some example use-cases where these rules are not feasible?
  • I suppose this might not be feasible for already existing web applications that were developed without following these rules, and re-designing them might not be cost-effective from a business standpoint. But what about new web-app development projects? Can these rules be programmatically enforced before allowing it to enter production
• Is my assumption just wrong about these rules preventing 100% of these attacks?

References:

• https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
• https://cheatsheetseries.owasp.org/cheatsheets/Cross_Site_Scripting_Prevention_Cheat_Sheet.html
• https://www.owasp.org/images/5/5a/Unicode_Transformations_Finding_Elusive_Vulnerabilities-Chris_Weber.pdf

Answer 1

Nothing is ever impossible.

But SQL injection vulnerabilities have gone down with ORMs and query builders which prevent the most common mistakes. In my experience, applications which use a secure(ish) by default template engine also have fewer XSS issues.

But the main problems are:

• Input sanitation will never solve all issues as you can't sanitize the input for all contexts.
• That leaves proper handling at the sink. You'd need to flag all insecure function calls (eg native query), you can't have any variables in your prepared query, and you can't make any exceptions ('the input isn't user-controlled, so it's fine', 'For rare circumstances where [...]', because it will very quickly become unmanageable). This will severely restrict usability for developers.
• XSS is very versatile and not all cases can be prevented via templating (eg DOM XSS).

Examples: SQL injection

Let's take PHP as example. You could flag all uses of the various SQL functions (query, etc) and only allow use of prepare. You'd also need to check that no variable is passed to prepare:

$stmt = $conn->prepare("SELECT test from table WHERE x=?");
$stmt->bind_param("s", $test);

But what if you want the tablename to be variable? Or if it's a complex query which you first need to build? Eg:

$filterquery = " WHERE ";
for ($filter in $filterarray) {
    $filterquery = ...
}

$stmt = $conn->prepare("SELECT test from $tablename" . $filterquery);
$stmt->bind_param("s", [...]);

That's something you couldn't allow, as checking if $filterquery is secure is non-trivial. But disallowing variables in queries will severely restrict your developers.

You could use a querybuilder and check that only the binding functions accept parameters (eg setParameter in doctrine), but again you would need to prevent any variables from being passed to other functions such as where). As there are legitimate use-cases for that, it would also restrict usability for developers.

Examples: XSS

With XSS, this becomes even more difficult. First, you'd need to check for all functions which output data and prevent that (echo, print, die, etc) so outputting can only be done via the template engine (this is certainly doable).

But you can't build HTML code dynamically outside of the template, because you can't pass complex variables to it (no way to know if the data is (partially) user-controlled).

You can now HTML-encode the data by default to prevent many cases of XSS. But how do you know if you are in a JavaScript context? Or a src attribute context (where you need to prevent javascript: links)? That's also non-trivial to check (or you need your developers to flag which context the variable is printed in, which is error-prone).

And then there's DOM-based XSS. And maybe you want to allow some users to post a limited subset of HTML (which you can filter, but for which you'd need exceptions in your template).