Does SQL have any inherant weaknesses?

Question

A prof said that SQL injection is misnamed because it actually occurs from the program generating the SQL (e.g. Perl, PHP etc.) and I'm wondering is SQL "complex" enough to have any vulnerabilities that are actually it's fault?

Answer 1

SQL has the same vulnerability than all other dynamically interpreted programming languages (this includes shell scripts, Javascript, PHP...), which I call "data is code". Such languages have a concrete syntax which is meant for human consumption ("select * from users where...": words and signs which the human brain grasps). Their processing entails a first step which is called parsing: the sequence of characters is broken down into an internal structure which describes the meaning of the expression, in a way which the computer can use efficiently. Parsing is a well-known subject but even if algorithms for it have been known for decades, it is still relatively complex for the human programmer.

When a language is dynamically interpreted, it makes it seductively easy to build the expression from dynamically obtained parameters. E.g. "select * from users where id = $paramIdFromWebForm;". SQL injection vulnerabilities hide there. The problem is fundamental: by building the expression in the concrete syntax, the programmer is doing an ad hoc reversal of the parsing. To make such things robust, the programmer must master all of the subtle ways by which parsing operates; he must think of every dark corner of the language specification. Many programmers think of it in terms of "must escape dangerous characters", and work on the assumption that the few dangerous characters they could think of indeed represent all the "dangerous characters" that could ever exist. This leaves the door open to a lot of issues, in particular when the Web site developer, his programming framework, and the database behind it, do not fully agree on what a "dangerous character" is, and such problems are prone to happen for a lot of reasons, including simple software updates.

See for instance this question for an example of the kind of things which is not captured by the "character escaping" mental framework. Whenever I see the functions mysql_escape_string() and mysql_real_escape_string(), my eyes bleed, and I wonder if there is not somewhere a function called mysql_really_escape_string_this_time_I_said_please().

Nonetheless, there is a proper way to build SQL expressions dynamically without getting into the "data is code" issues: parametrized expressions. This is about working with the abstract representation of the SQL code, the one after parsing. When we use parametrized expressions, we do not try to reverse the parsing step, and security is much enhanced (and it is good for performance, too).

---

So it is not really SQL's fault; the programmer is still to blame, for not using the right solutions which exist and are documented. We may still consider that SQL designers, and, in particular, the people who integrated SQL in PHP, should have made some effort to make dynamic SQL expression building a bit harder when parametrization is not used. For a better language design (with regards to this specific issue), see LINQ: it keeps the syntax where it belongs, i.e. in the source code, not in runtime-interpreted character strings.

Answer 2

SQL has the same inherent weakness as most languages: it will do exactly what you tell it to do.

The problem arises when you don't properly clean input, or the database users has permissions beyond what they need, allowing untrusted third parties to tell it what to do. If they tell it to do something bad... it's going to do something bad.

Various implementations have had various vulnerabilities over the years, including the fairly recent discovery that some MySQL builds would allow you to log on with any password roughly 1 in 256 times: http://nakedsecurity.sophos.com/2012/06/13/anatomy-of-a-bug-the-mysql-authentication-disaster-patch-now/

But SQL injection is generally a flaw in the program between the user and the database (PHP website, etc)

Answer 3

Most of the time SQL injection happens because people do not properly use it in their code. If you use SQL in your code you need to use prepared statements instead of using appending variables to your SQL string.

SQL is just a tool, if you don't use that tool properly you are going to have a bad time.

If you hold the knife by its blade you will get cut. But that's not the blade's fault.