22

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?

  • 22
    bad_ideas_sql = 'SELECT title FROM idea WHERE idea.status == "bad" AND idea.user == :mwheeler' would have both hard-coded and parameterized values in a single query – try to catch that! I think there are valid use cases for such mixed queries. – amon Aug 11 '15 at 15:50
  • 6
    How about selecting records from today SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY score DESC; – Jaydee Aug 11 '15 at 15:55
  • 10
    @MasonWheeler sorry, I meant “try to allow that”. Note that it is perfectly parameterized and does not suffer from SQL injection. However, the database driver cannot tell whether the literal "bad" is truly literal or resulted from string concatenation. The two solutions I see are either getting rid of SQL and other string-embedded DSLs (yes please), or promoting languages where string concatenation is more annoying than using parameterized queries (umm, no). – amon Aug 11 '15 at 16:06
  • 4
    and how would the RDBMS detect whether to do this? It would overnight make it impossible to access the RDBMS using an interactive SQL prompt... You'd no longer be able to enter DDL or DML commands using any tool at all. – jwenting Aug 11 '15 at 17:14
  • 8
    In a sense you can do this: don't construct SQL queries at runtime at all, instead use an ORM or some other abstraction layer that avoids you needing to construct SQL queries. ORM doesn't have the features you need? Then SQL is a language intended for people who want to write SQL, which is why on the whole it lets them write SQL. The fundamental issue is that dynamically generating code is harder than it looks, but people want to do it anyway and will be unsatisfied with products that don't let them. – Steve Jessop Aug 12 '15 at 0:20
45

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.

12

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.

  • 1
    As an approximation for "is it a literal" you can check if the string is interned. – CodesInChaos Aug 11 '15 at 20:24
  • 1
    @CodesInChaos: True, and such a test might be accurate enough for this purpose, provided that anyone who had a reason for generating a string at runtime used a method which accepted a non-literal string rather than interning the runtime-generated string and using that (giving the non-literal-string method a different name would make it easy for code reviewers to inspect all uses of it). – supercat Aug 11 '15 at 20:30
  • Note that while there's no way of doing this in C#, some other languages do have facilities that make it possible (e.g. Perl's tainted string module). – Jules Aug 12 '15 at 16:29
  • More succinctly, this is a client problem, not a server problem. – Blrfl Aug 13 '15 at 0:03
7
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.

  • 2
    If your solution for "it's all too easy to forget--or not know in the first place--to use parametrized queries" is "make everyone remember--and know in the first place--to use stored procs", then you're missing the entire point of the question. – Mason Wheeler Aug 11 '15 at 16:09
  • 5
    I've seen SQL injection via stored procedures at my work. It turns out mandating stored procedures for everything is BAD. There's always that 0.5% that are true dynamic queries (you can't parametrize an entire where clause, let alone a table join). – Joshua Aug 11 '15 at 19:37
  • In the example in this answer you could replace deleted = false with NOT deleted, which avoids the literal. But the point is valid in general. – psmears Aug 13 '15 at 9:50
5

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.

protected by gnat Aug 12 '15 at 19:51

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.