Rapid Application Development systems tend to promote the writing of bad code. In what follows I’m going to use VS.NET (2003) as an example, simply because it’s probably the most used. I’m also going to take the writing of client database code as the main example, because it is so important and because it represents a large part of development time, if done the right way and hardly no development time if done the VS.NET way.
SQL client design
There are several ways of designing SQL client code. One common way is to have the SQL statements in the client code and send them across the network when you need to execute them. This can be also be done by composing a string with the entire statement or by parameterizing it first, sending the parameters as separate values. It can also be done by precompiling the statements in the source into an intermediate form, as is often done when using IBM’s DB2.
Another common way is to not have any SQL code on the client, but only call predesigned stored procedures on the database server. Practically all current database systems support stored procedures.
Let’s go through a couple of different variations on these themes to see what the advantages and disadvantages are.
Statement assembly on the client
This is probably the most common way of creating SQL clients. The client application assembles an SQL statement using string constants and inputs from the user, then sends the entire SQL statement to the server.
Advantages:
- It’s easy to write and wizards support it well
- Several programming IDE’s produce such code from database diagrams
Disadvantages:
- It’s very vulnerable to SQL injection attacks
- It requires excessively large privileges on the server to execute any possible statement
- It easily causes bugs only found in run-time, since it’s a form of “late binding”
- It causes excessive network traffic
- The server cannot reuse precompiled statements, but has to recompile for every new call, including optimizing, causing excessive server load
Parameterized statements on the client
The only difference with the first kind above is that the SQL statements have placeholders for the variable values.
Advantages:
- It’s more resistant to injection attacks
- Server load is less, since compiled statements are cached and can be reused
- Some development environments support it in wizards, at least partly
Disadvantages:
- Excessive network traffic
- Excessive privileges needed on the server
- If the client application is compromised, injection attacks can be performed, or any kind of malicious SQL can be executed
Stored procedures on the server
In this model, the client has access to a set of stored procedures on the server, but to nothing else. Each type of client has access to its own group of stored procedures. All operations, be they selects, insert, updates or deletes are performed through special purpose stored procedures.
Advantages:
- No operation can be performed by the client unless a stored procedure exists that does it, and that the client has explicit access to
- Even if the client machine is totally compromised, access is still limited to only the provided stored procedures
- Network traffic is as low as it can get.
- The entire data layer, and the integrity guarantees, are on the server, where they should be
Disadvantages:
- Many programming systems don’t support it without manual effort
Except for the advantages already mentioned, we have several more advantages. If you construct the stored procedures right, you can avoid to have any network traffic going on during transactions. What I mean is that the stored procedure you call opens a transaction, performs all the different tasks needed for it, then commits before returning to the client. In heavily used systems, this can mean orders of magnitude better performance and order of magnitude better reliability (since having network slowdowns affect open transactions is a great way to drag down the performance something awful.) My personal motto is: never a transaction over the net. That is, never open a transaction on a client, only in a stored procedure. If your transaction seems too large or complicated to be run only on the server, you should probably go back to the design table and rethink it.
Now for something entirely different
So what has this got to do with VS.NET? Well, to my delight, VS.NET is the first development environment I’ve seen that supports the stored procedures model of writing SQL client code. It actually creates stored procedures for you and installs them into the server, right from the desktop. It writes pug-ugly SQL code in these stored procedures, but I can live with that.
What I can’t live with is that the wizard that does all this doesn’t work! It’s full of bugs. It can’t pick up the parameters to send to the stored procedures right. If it ever writes correct code, it produces an enormous mass of code. If you then correct the code to work right (not easy with the number of lines it produces), then the wizard prompty screws up the code again if you ever open it for edits.
Even worse, if you want to generate the code, then take it manually from there, you’re into fighting a mountain of code. The reasoning at MS was probably that since the code is generated anyway, there’s no need for a decent structure to it, so there you are. The only way out is to throw the whole sordid mess away and start over, coding by hand. And this you can do in a fairly reasonable number of lines, once you’ve found all the relevant documentation, a non-trivial task in its own right.
Programmers write insecure code and it’s MS’s fault!
…at least partly. To write secure code, you really must write client code calling stored procedures on the server. But to do this, you have to create a number of classes by hand. Not only do you need to know how to do this job right, you need to get the development time to do it from your management. But now we’re in trouble.
Microsoft has already shown management how to produce code in no time flat using their tools. What they didn’t show was that this code is either non-secure or non-maintainable. So now you, as a developer, have the choice of either writing good code in three times the time it took the guy from MS to write bad code, and thus seem like a slacker, or to produce the same crappy code Microsoft expects you to produce. And leave the problems to the development management that told you to do it this way.
Now, what would you do?
Conclusion
There is no point in exhorting your developers to write secure code if their development tools don’t support it. If you want secure code, provide the training for your developers and a big helping of extra time to do it. And verify that they don’t fall into the trap of “instant code” generation.