Parameterized Queries: Fix the Sql injection at root.

Sql Injection Is the most common vulnerability found in Web applications. whenever i talk about it’s prevention most common answer comes as input validation which is most cost effective and can be implemented without changing the code (using some third party product like WAF,IPS/IDS,Proxies) or without changing the sql statements by implementing such functions' in web application itself. But this is trivial to bypass ( we are living in the world where of hell lot of character encoding are supported) and many time lead to broken applications.

In this article i will try to explore the fixing the problem in it’s root . Modern application has been build using multi-tier architectures and most common architecture is 3-Tier Architecture.

3-Tier Architecture: This involves three layers Client layer, Server layer and business logic tier, service tier or middle tier (layer). In the client-server solution the client was handling the business logic that makes the client “thick”. A thick client means that it requires heavy traffic with the server, thus making it difficult to use over slower network connections like Internet and Wireless .

In 3-tier Architecture, the client is only handling presentation logic. This means that only little communication is needed between the client and the middle tier making the client “thin” . So a typical view would be Client tier <---> Middle tier<---> Database Tier

Many Developer believe that following Secure coding practice for middle tier only will prevent SQL injection which is not always true that we will see in short while.

not let’s have a look of a typical sql injection flaw

Dynamic Queries: The following (Java) example is would allow an attacker to inject code into the query that would be executed by the database. The unvalidated “userName” parameter that is simply appended to the query allows an attacker to inject any SQL code they want. Unfortunately, this method for accessing databases is all too common. this is also know as Dynamic Queries.

String query = "SELECT EmployeeId FROM Employee WHERE Emp_name = " + request.getParameter("userName");
Statement stmt = connection.createStatement();
ResultSet results = stmt.executeQuery( query );

In sql Community to make an sql statement Sql injection proof use of Parameterized Queries is highly recommended.

Parameterized Queries: They are are simple to write, and easier to understand than dynamic queries. Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.

Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker.For example, if an attacker were to enter the userName of blah' or '1'='1, the parameterized query would not be vulnerable and would instead look for a Emp_name which literally matched the entire string blah' or '1'='1.

String username = request.getParameter("userName");
String query = "SELECT EmployeeId FROM Employee WHERE Emp_name = ? ";
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, username);
ResultSet results = pstmt.executeQuery( );

Another benefit of using Parameterized Queries is to get performance enhancements for details Pleas check http://www.simple-talk.com/sql/t-sql-programming/performance-implications-of-parameterized-queries/

Another method that can be use to make your database access Sql Injection proof is to use Stored procedures .

Stored procedures: They have the same effect as the use of prepared statements when implemented safely i.e Stored procedures must not use any dynamic Sql statements. It require the developer to define the SQL code first, and then pass in the parameters after. The difference between prepared statements and stored procedures is that the sql code for a stored procedure is defined and stored in the database itself, and then called from the application. Using this technique requires great interaction between database developer and middle tier developers.

String username = request.getParameter("username");
CallableStatement cstmt = connection.prepareCall("{call getEmpId(?)}");
cstmt.setString(1, username);
ResultSet results = cstmt.executeQuery();

References:
https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
http://blog.simcrest.com/what-is-3-tier-architecture-and-why-do-you-need-it/
https://www.owasp.org/index.php/Query_Parameterization_Cheat_Sheet
http://www.simple-talk.com/sql/t-sql-programming/performance-implications-of-parameterized-queries/