Why not just make non-parameterized queries return an error?

Question

SQL injection is a very serious security issue, in large part because it's so easy to get it wrong: the obvious, intuitive way to build a query incorporating user input leaves you vulnerable, and the Right Way to mitigate it requires you to know about parameterized queries and SQL injection first.

Seems to me that the obvious way to fix this would be to shut down the obvious (but wrong) option: fix the database engine so that any query received that uses hard-coded values in its WHERE clause instead of parameters returns a nice, descriptive error message instructing you to use parameters instead. This would obviously need to have an opt-out option so that stuff like ad-hoc queries from administrative tools will still run easily, but it should be enabled by default.

Having this would shut down SQL injection cold, almost overnight, but as far as I know, no RDBMS actually does this. Is there any good reason why not?

Answer 1

There are too many cases where using a literal is the right approach.

From a performance standpoint, there are times that you want literals in your queries. Imagine I have a bug tracker where once it gets big enough to worry about performance I expect that 70% of the bugs in the system will be "closed", 20% will be "open", 5% will be "active" and 5% will be in some other status. I may reasonably want to have the query that returns all active bugs to be

SELECT *
  FROM bug
 WHERE status = 'active'

rather than passing the status as a bind variable. I want a different query plan depending on the value passed in for status-- I'd want to do a table scan to return the closed bugs and an index scan on the status column to return the active loans. Now, different databases and different versions have different approaches to (more or less successfully) allow the same query to use a different query plan depending on the value of the bind variable. But that tends to introduce a decent amount of complexity that needs to be managed to balance out the decision of whether to bother re-parsing a query or whether to reuse an existing plan for a new bind variable value. For a developer, it may make sense to deal with this complexity. Or it may make sense to force a different path when I have more information about what my data is going to look like than the optimizer does.

From a code complexity standpoint, there are also plenty of times that it makes perfect sense to have literals in SQL statements. For example, if you have a zip_code column that has a 5 character zip code and sometimes has an additional 4 digits, it makes perfect sense to do something like

SELECT substr( zip_code, 1, 5 ) zip,
       substr( zip_code, 7, 4 ) plus_four

rather than passing in 4 separate parameters for the numeric values. These aren't things that will ever change so making them bind variables only serves to make the code potentially more difficult to read and to create the potential that someone will bind parameters in the wrong order and end up with a bug.

Answer 2

SQL injection occurs when a query is built by concatenating text from an untrusted and unvalidated source with other portions of a query. While such a thing would most often occur with string literals, that would not be the only way it could occur. A query for numeric values might take a user-entered string (that's supposed to only contain digits) and concatenate with other material to form a query without the quote marks normally associated with string literals; code which is overly trusting of client-side validation might have things like field names come from an HTML query string. There's no way code looking at an SQL query string can see how it was assembled.

What's important is not whether an SQL statement contains string literals, but rather whether a string contains any sequences of characters from untrusted sources, and validation for that would be best handled in the library which builds queries. There's generally no way in C# to write code that will allow a string literal but won't allow other kinds of string expression, but one could have a coding-practices rule that requires that queries be built up using a query-building class rather than string concatenation, and anyone passing a non-literal string to the query builder must justify such action.

Answer 3

SELECT count(ID)
FROM posts
WHERE deleted = false

If you want to put the results of these in the footer of your forum you would need to add a dummy parameter just to say false every time. Or the naive web programmer looks up how to disable that warning and then continues on.

Now you can say you would add an exception for enums but that just opens the hole again (though smaller). Not to mention people first need to be educated to not use varchars for those.

The real problem of injection is programmatically constructing the query string. The solution for that is a stored procedure mechanism and enforcing its use or a whitelist of allowed queries.

Answer 4

TL;DR: You'd have to restrict all literals, not just the ones in WHERE clauses. For reasons why they don't, it allows the database to remain decoupled from other systems.

Firstly, your premise is flawed. You want to restrict only WHERE clauses, but that's not the only place user input can go. For example,

SELECT
    COUNT(CASE WHEN item_type = 'blender' THEN 1 END) as type1_count,
    COUNT(CASE WHEN item_type = 'television' THEN 1 END) AS type2_count)
FROM item

This is equally vulnerable to SQL injection:

SELECT
    COUNT(CASE WHEN item_type = 'blender' THEN 1 END) FROM item; DROP TABLE user_info; SELECT CASE(WHEN item_type = 'blender' THEN 1 END) as type1_count,
    COUNT(CASE WHEN item_type = 'television' THEN 1 END) AS type2_count)
FROM item

So you can't just restrict literals in the WHERE clause. You have to restrict all literals.

Now we're left with the question, "Why allow literals at all?" Keep this in mind: while relational databases are used underneath an application written in another language a large percentage of the time, there is no requirement that you must use application code to use the database. And here we have an answer: you need literals to write code. The only other alternative would be to require all code to be written in some language independent of the database. So having them gives you the ability to write "code" (SQL) directly in the database. This is a valuable decoupling, and it would be impossible without literals. (Try writing in your favorite language sometime without literals. I'm sure you can imagine how difficult this would be.)

As a common example, literals are often used in the population of list-of-value/look-up tables:

CREATE TABLE user_roles (role_id INTEGER, role_name VARCHAR(50));
INSERT INTO user_roles (1, 'normal');
INSERT INTO user_roles (2, 'admin');
INSERT INTO user_roles (3, 'banned');

Without them, you would need to write code in another programming language just to populate this table. The ability to do so directly in SQL is valuable.

We're then left with one more question: why don't programming language client libraries do it then? And here we have a very simple answer: they would have re-implement the entire database parser for each supported version of the database. Why? Because there's no other way to guarantee you've found every literal. Regular expressions aren't enough. For example: this contains 4 separate literals in PostgreSQL:

SELECT $lit1$I'm a literal$lit1$||$lit2$I'm another literal $$ with nested string delimiters$$ $lit2$||'I''m ANOTHER literal'||$$I'm the last literal$$;

Trying to do that would be a maintenance nightmare, especially since valid syntax often changes between major releases of databases.