# Would limiting the number of operations in a transaction prevent SQL injection?

In my understanding all SQL injections need to add another operation to the one intended by the programmer.

For example, a badly written query:

query="Select * from Table where name="+name;


User inputs name as: foo; Delete from Table; and this would be an SQL Injection. But if the programmer would be able to limit the number of operations in a transaction to 1 (the Select in this case) or how much operations he intends to use the SQL Injection would be impossible. Is there a way we can limit the number of operations in most programming languages? Would this be enough to stop SQL Injection?

It could help avoiding some attacks. However, allowing the adversary to inject a part of the SQL WHERE clause is already quite dangerous.

E.g. injection can change this

SELECT * WHERE name="username"


into

SELECT * WHERE name="Bah!" or true or "too easy!"="x"


The sane thing would be to avoid injection completely. Ideally, in a typed language, the type of a SQL query should NOT be a string, so to prevent concatenation with user data. It should be a query template, to be filled with a special function. Something like

query = new Query(...);


Type systems excel for imposing these constraints of the programs. On the other hand, their use require some thoughtful library design. Sometimes it feels simpler to provide a more low-level solution (like using strings for everything), which is also simpler to use, and possibly document the potential security issues "programmers: don't do this", which can then be ignored by busy programmers, which would often take the shortest path to their goals. Types here would simply block the quick path, forcing the programmers to separate the "query template" part from the "user data" part.

Some languages still use strings for templates, but at least encourage programmers to avoid concatenation, and favor some fill-like function.

fill("SELECT * WHERE name=@username", { "username": username })


The above is doable even in an untyped language.

Moral: let the library perform all the string-escaping for you. The library always checks for special characters, and handles them correctly. You likely won't cover all the tricky cases.

Don't try to patch against an attack by imposing restrictions on just that attack. Consider all the variants for that attack which can pass the restriction. If injection is the problem, prevent injection at the root.

It is a totally inappropriate approach to the problem.

The whole premise of an SQL injection is sheer madness. It allows a possibly malicious end user to modify the code that the software developer has created. That is just mad. It is something that should never, ever be allowed to happen. To avoid this, write your SQL code so that the user cannot modify it (for example using parameterized queries).

Allowing the user to modify code and then somehow restricting what the code could do will inevitably fail. And you added the problem for the developer who wants to make his code work for normal situations, the he now must predict precisely how many operations his code will perform.