The Franciscan Report

Ways to prevent SQL Injection Attacks

A few ways to avoid SQL Injection attacks.

In .Net

Instead of

using( SqlConnection con = (acquire connection) ) {
con.Open();
using( SqlCommand cmd = new SqlCommand("SELECT * FROM users WHERE name = '" + userName + "'", con) ) {
using( SqlDataReader rdr = cmd.ExecuteReader() ){
...
}
}
}


Use the following

using( SqlConnection con = (acquire connection) ) {
con.Open();
using( SqlCommand cmd = new SqlCommand("SELECT * FROM users WHERE name = @userName", con) ) {

cmd.Parameters.Add("@userName", userName);

using( SqlDataReader rdr = cmd.ExecuteReader() ){
...
}
}
}

In Java.

Instead of

Connection con = (acquire Connection)
Statement stmt = con.createStatement();
ResultSet rset = stmt.executeQuery("SELECT * FROM users WHERE name = '" + userName + "';");


Use the following

Connection con = (acquire Connection)
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM users WHERE name = ?");
pstmt.setString(1, userName);
ResultSet rset = pstmt.executeQuery();

Data Driven Design is generated based on the need to provide users of the system with a set of data.

The common flow of the system will be as follows;
1. A data access object or a business entity object will retrieve the particular data from a database either through stored procedures or dynamic sql statements.
2. The resulting query result will then be mapped to a collection of data objects (usually an anemic model, ie. model with data only, no behavior) representing the information requested by the user.
3. Logics can be embedded to the business entity object (usually contain just behavior with no data member) or the data access object if necessary to act on the data object.
4. This data object will then be passed along to the user to be displayed in the presentation tier. If it needs to cross multiple physical tiers, then this data object is usually called a Data Transfer Object.
5. If the user can resubmit the data back to the server, then the aforementioned business entity object or the data access object can be used to validate user entries.

Benefits of this approach are:
1. It's a more procedural approach in software development, thus doesn't require deep domain expertise.
2. It's easier to implement since data objects are created based on user requirements.

Disadvantages of this approach are:
1. Business logics could be scattered around the system, thus could potentially create accidental duplications (root of all evil).
2. No ubiquitous language and understanding between development team and the business folks, since nobody speaks in term of the actual business domain.
3. No logical boundary exists between inter-related objects, thus could potentially lead to a highly coupled systems.
4. Most of the cases the data objects will be closely mapped to tables in the database, thus it might not take advantages of what Object Oriented Design principles give, ie. polymorphism, inheritance, composition, etc.

Compare this approach to a Domain Driven approach which value system as a set of domain objects and how they are inter-related with each other, which can be defined/contained within their own boundary.

Enterprise application team has a constant battle of whether to use stored procedure or OR mapper.

Despite some common myths about O/R mapping framework such as performances, sql injection attacks, one to one mapping between tables and classes, which are not true of course.

Here are my takes on the subject

Benefits of using Stored Procedure
- SQL codes are precompiled thus can improve performance
- Prevent SQL injections.
- Works very well with data intensive operations that return huge datasets.

Disadvantages of using Stored Procedures
- Developers need to know the language, thus becoming language dependent.
- Need to implement complex mapping layers between the returned data and domain objects
- Need to maintain the SQL codes
- Business logics could leak into the stored procedures.
- No tool supports for debugging, refactoring, code coverage.

Benefits of using OR Mapper
- Developers deal with domain objects directly instead of having to write their own SQL codes.
- Eliminate repetitive database access codes
- Most OR Mappers (NHibernate, iBatis, etc.) provide caching, lazy loading, optimistic locking, dirty saves, business transaction mechanisms.
- SQL injection is never an issue, since most of the underlying frameworks prevent it
- Support for inheritance, composition, one-one and many-one, many-many associations
- Support transaction updates across tables/rows/objects.
- Objects dependency ordering during DB operations.
- Paging supports
- Cascading updates/deletes are supported out of the box.
- SQL statement batching, sending multiple statements to the database in one shot.

Disadvantage of using OR Mapper
- Have to maintain the mapper schemas
- More performance impacts compares to using Stored Procedure, in particular, if the application is a data intensive operations, ie. batch processes, projection types queries, etc.

I believe both methods have their places, if you want complex logic in a user interaction then using domain objects with O/R mapper is the way to go. On the other hand, use stored procedures if you need to perform large dataset queries, or batch jobs.

Common .Net practice is to write software in a solution file consists of multiple project files. A project file corresponding to a dll.

There are two options where one could structure his layered-architecture concept in .Net projects.

One is to structure the layering to corresponds to individual project, thus one layer per project. The benefits of this approach are:
- It allows flexibility in swapping dlls in and out of an application.
- It's quite easy to separate the work between development group, particularly in an organization where developers are segregated to different area, ie. DB developer, Service developer, domain developer, thus each group can develop their own module independently.
- Each individual layer can potentially be deployed in different physical tiers.
- It allows creation of patches to fix defects that only affect certain area of the applications, thus it promotes faster build time and "minimal" risk.

The disadvantages of such approach are:
- We lost the advantage of encapsulation, for example, as different layers are package to different dll, there's no way to limit other projects from referencing dll that they are not supposed to.
- It's easier to adopt the concept of SOA, where the only way to gain "business access" is through external service call, instead of allowing direct access to a particular business objects or data access layers in the worst case.
- The effort to maintain dll's dependencies as the application grows larger could create maintenance nightmare.

The other approach is to use namespace to produce the logical layering. This technique has some benefits, such as:
- It allows the use of assembly level access to limit direct access to layers that other dlls are not supposed to have.
- It allows the use of tools such as NDepend to set up dependency structure between different layers.

In conclusion both approaches have their places in an application development.

Separating module to different dll works well for a module that's being shared across physical tiers, for example the domain objects module.

Common components could probably be separated into their-own dlls, to some degree, since applications that use web component probably doesn't need to include windows component. The rule of thumb is "things that work/used together stay together".

As far as some of the benefits listed above for separting layers to different dlls, one must ask the following questions:
- How often have we ever swapped dlls in and out of applications in practice?
I'm a big supporter of Simplicity and Minimalist concepts, therefore we do things the simplest way now, and when we need to use separate modules in the future we can still do it with ease, since we have separated those layers into different namespaces.
- We have bigger issues on hand if an organization is more accustomed to specialist developer rather than generalist developer in my opinion.
- Deploying different modules in different tiers only make sense in some cases, for example, presentation tier and server tier, but in other cases we have to question the benefit and the performance impact of doing so.
- Fixing defects rarely only touch one logical layer. Also, most of the time spent when promoting patches is not in the build process but in the QA testing (manual or scripted). Continous integration and TDD practices can minimize any risk associated with "patching" a portion of codebase vs. the whole codebase.

Brad Appleton's ACME Blog: Simple ain't Easy: Myths and Misunderstandings about Simplicity

My favorite quotes from the blog.

Everything should be made as simple as possible, but not simpler. -- Albert Einstein

Three Rules of Work: Out of clutter find simplicity; From discord find harmony; In the middle of difficulty lies opportunity. -- Albert Einstein

For every problem there is a solution which is simple, clean and wrong. -- Henry Louis Mencken

Think simple as my old master used to say - meaning reduce the whole of its parts into the simplest terms, getting back to first principles. -- Frank Lloyd Wright

Beauty of style and harmony and grace and good rhythm depend on simplicity. -- Plato

The ability to simplify means to eliminate the unnecessary so that the necessary may speak. -- Hans Hofmann

Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that's creativity. -- Charles Mingus

Everything is both simpler than we can imagine, and more complicated that we can conceive. -- Goethe

The whole is simpler than the sum of its parts. -- Willard Gibbs

The pure and simple truth is rarely pure, and never simple. -- Oscar Wilde

Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius--and a lot of courage--to move in the opposite direction. -- E. F. Schumacker

Besides the noble art of getting things done, there is the noble art of leaving things undone. The wisdom of life consists in the elimination of nonessentials. -- Lin Yu Tang

Very often, people confuse simple with simplistic. The nuance is lost on most. -- Clement Mok

You can't force simplicity; but you can invite it in by finding as much richness as possible in the few things at hand. Simplicity doesn't mean meagerness but rather a certain kind of richness, the fullness that appears when we stop stuffing the world with things. -- Thomas Moore

The point of philosophy is to start with something so simple as not to seem worth stating, and to end with something so paradoxical that no one will believe it. -- Bertrand Russell

The aspects of things that are most important to us are hidden because of their simplicity and familiarity. -- Ludwig Wittgenstein

Manifest plainness, Embrace simplicity, Reduce selfishness, Have few desires. -- Lao-Tzu, Tao Te Ching

Simple things should be simple and complex things should be possible. -- Alan Kay

The key to performance is elegance, not battalions of special cases. The terrible temptation to tweak should be resisted unless the payoff is really noticeable. -- Jon Bentley and Doug McIlroy

... the purpose of abstraction is not to be vague, but to create a new semantic level in which one can be absolutely precise. -- Edsger W. Dijkstra

Simplicity and elegance are unpopular because they require hard work and discipline to achieve and education to be appreciated. -- Edsger W. Dijkstra

Beauty is more important in computing than anywhere else in technology because software is so complicated. Beauty is the ultimate defense against complexity. -- David Gelernter

Fools ignore complexity; pragmatists suffer it; experts avoid it; geniuses remove it. -- Alan Perlis

Technical skill is mastery of complexity, while creativity is mastery of simplicity. -- E. Christopher Zeeman

Architect: Someone who knows the difference between that which could be done and that which should be done. -- Larry McVoy

One of the great enemies of design is when systems or objects become more complex than a person - or even a team of people - can keep in their heads. This is why software is generally beneath contempt. -- Bran Ferren

A complex system that works is invariably found to have evolved from a simple system that worked. -- John Gall

The most powerful designs are always the result of a continuous process of simplification and refinement. -- Kevin Mullet

There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies and the other way is to make it so complicated that there are no obvious deficiencies. The first method is far more difficult. -- C.A.R. Hoare

Perfection (in design) is achieved not when there is nothing more to add, but rather when there is nothing more to take away. -- Antoine de Saint-Exupéry

Simple, clear purpose and principles give rise to complex intelligent behavior. Complex rules and regulations give rise to simple stupid behavior. -- Dee Hock

This Brad's assessment of what "simplicity" means,

I think that true simplicity is about minimizing and managing overall complexity. Complexity in software design and development comes from the sheer size and complexity of the problem we are being asked to solve, and the richness and vastness of the interconnected maze of interactions within the system and between the system and its environment. The overall complexity of the system is dominated far more by the interactions between the parts that makeup the whole than it is by the parts alone. For any non-trivial system, simplicity often has less to do with the number and kind of different things involved and more to do with the number and kind of interdependencies between them. So achieving simplicity is less about managing "parts" of "things" or individual point-solutions, and is more about managing rules and relationships between the parts and things and solution-sets.

When dealing with large or complex systems (like most software, and software processes) the number of things (scale) and different types of things (diversity) that need to be managed is overwhelming. If we can come up with a modicum of modest, simple rules & principles to govern our design decisions in ways that help us minimize and manage interdependencies, eliminate constraints, and remove waste, then we are on the path to solving the real problem and meeting stakeholder needs in a way that is both simple and sustainable.