N Layer Architecture Using Sql Injection Computer Science Essay

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Network management usually employs 2- or3-tier, sometimes, n-tier client/server architecture. The nth tier, counting from user side, often is implemented as SNMP agent. Agent developer (like any server developer) has to deal with a suite of generic middleware issues: naming, location, persistency, referential integrity. The sheer number of ATM and network management standards and the rate of changes in this area indicates that some kind of layered architecture, some level of isolation is a requirement.

An SQL injection attack targets interactive web applicationsthat employ database services. These applications accept user inputs and use them to form SQL statements at runtime. During an SQL injection attack, an attacker might provide malicious SQL query segments as user input which could result in a different database request. By using SQL injection attacks, an attacker could thus obtain and/or modify confidential/sensitive information.

An attacker could even use a SQL injection vulnerability as a rudimentary IP/Port scanner of the internal corporate network.

Several papers in literature have proposed ways to prevent SQL injection attacks in the application layer by examining dynamic SQL query semantics at runtime.

However, very little emphasis is laid on securing stored procedures in the database layer which could also suffer from SQL injection attacks. Some papers in literature even refer to stored procedures as a remedy against SQL injection attacks. As stored procedures reside on the database front, the methods proposed by them cannot be applied to secure stored procedures themselves.

In this paper, we propose a novel technique to defend against the attacks targeted at stored procedures. This technique combines static application code analysis with runtime

validation to eliminate the occurrence of such attacks.

In the static part, we design a stored procedure parser, and for any SQL statement which depends on user inputs, we use

this parser to instrument the necessary statements in order to compare the original SQL statement structure to that including user inputs. The deployment of this technique can be automated and used on a need-only basis.

Keywords-N-Layer Architecture, Sql Injection Store Procedure, Information Security


The widely accepted technology web is a instant meaning of information access and lots of other transactions. Those transactions are also financial transactions. Now a time we can not say data its BIG DATA that is rely on only server security and firewall access. Due to sql_injections the one who suffers is the db layer or physical layer. Use of db for banks atms , organization's general and confidential data handling are the key components that made it key component of today's network infrastructure. These applications and their underlying databases often store confidential or even sensitive data. And due to no prevention of sql injections or less prevention of sql injections it gets in the hand of back hats or unauthorized users , they misuse it due to their bad intention. The failure of software increases the downtime and can be cause of damage of data also. Now I a days some times information is that much invaluabe so you can't measure it in millions and billions like national security data of a country , army offices and strategy data, raw departments and other national security agencies and their agents data. It can be cause of lacks of peoples death also due to unauthorized data access and after that its bad impact. Sql injections can be part of cyber war. it is crucial to protect these applications from targeted attacks.

However, the current state of application security leaves much to be desired. The 2002 Computer Security Institute and FBI revealed that, on a yearly basis, over half of all databases experience at least one security breach and an average episode results in close to $4 million in losses [16].

A recent penetration testing study performed by the Imperva Application Defence Center included more than 250 Web applications from e-commerce, online banking, enterprise collaboration and supply chain management sites and their vulnerability assessment concluded that at least 92% of Web applications are vulnerable to some form of malicious intrusions [15]. Recent U.S. industry regulations such as the Sarbanes-Oxley Act pertaining to information security, try to enforce strict security compliance by application vendors [5] and there is an urgent need to find means of satisfying these security requirements.

A. Privileged User Access:

Sensitive data processed outside the enterprise brings with it an inherent level of risk because outsourced services bypass the "physical, logical and personnel controls" IT shops exert over in-house programs.

B. Regulatory Compliance:

Customers are ultimately responsible for the security and integrity of their own data, even when it is held by a service provider . Traditional service providers are subjected to external audits and security certifications.

C. Data Location:

When clients use the distributed data warehouses, they probably won't know exactly where their data are hosted. Distributed data storage is a usual manner of cloud providers that can cause lack of control and this is not good for customers who have their data in local machine before moving from local to


D. Data Segregation:

Data in the present software are typically in a shared environment alongside data from other customers. Encryption is effective but isn't a cure all.

Encryption and decryption is a classic way to cover security issues but heretofore it couldn't ensure to provide perfect solution for it.

E. Recovery:

If a db server broke or some problems cause failure in app sever what will happen to users' data? Can db servers will restore data completely?. This issue can cause an impasse in security.

F. Investigative support:

in multiple layer environment it is especially difficult to investigate, because logging and data for multiple customers may be co-located and may also be spread across an ever-changing set of hosts and data centers.


1-Tier Architecture

We all know software packages like MS Access, MS Excel, QuickBooks and Peachtree just to name a few. They all have the same in common that they access files directly. This means that the file you want to work with must be accessible from a local or shared drive. This is the simplest of all the architectures but also the least secure. Since users have direct access to the files, they could accidentally move, modify or even worse delete the file by accident or on purpose.

There is also usually an issue when multiple users access the same file at the same time: In many cases only one can edit the file while others only have read-only access.

So 1-tier architecture is simple and cheap, but usually unsecured and data can easily be lost if you are not careful.

2-Tier Architecture

This architecture is also called Client-Server architecture because of the two components: The client that runs the application and the server that handles the database back-end. When the client starts it establishes a connection to the server and communicates as needed with the server while running the client. The client computer usually can't see the database directly and can only access the data by starting the client. This means that the data on the server is much more secure. Now users are unable to change or delete data unless they have specific user rights to do so.

The client-server solution also allows multiple users to access the database at the same time as long as they are accessing data in different parts of the database. One other huge benefit is that the server is processing data that allows the client to work on the presentation and business logic only. This mean that the client and the server is sharing the workload and by scaling the server to be more powerful than the client, you are usually able to load many clients to the server allowing more users to work on the system at the same time.


3-Tier Architecture

This involves one more layer called the 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 (3G, Edge or Wi-Fi).

By introducing the middle layer, 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" or "thinner". An example of a thin client is an Internet browser that allows you to see and provide information fast and almost with no delay.

As more users access the system a three-tier solution is more scalable than the other solutions because you can add as many middle tiers (running on each own server) as needed to ensure good performance (N-tier or multiple-tier).

Security is also the best in the three-tier architecture because the middle layer protects the database tier.

There is one major drawback to the N-tier architecture and that is that the additional tiers increase the complexity and cost of the installation.


The reasoning behind using a stored procedure is that the execution plan that is created in order to execute your procedure is cached by SQL Server in an area of memory known as the Plan Cache. When the procedure is then subsequently re-run at a later time, the execution plan has the possibility of being re-used.

A stored procedure will not run any faster than the same query, executed as a batch of T-SQL. It is the execution plans re-use that result in a performance improvement. The query cost will be the same for the actual T-SQL.[14]

Offloading data to a reporting database is a typical pursuit however you may need to review your indexing strategy on the reporting database as it will likely need to be quite different from that of your OLTP platform for example.


In this section, we present a stored Procedure that is vulnerable to a SQL Injection Attacks and explain how an attacker could exploit this vulnerability. We also present various techniques that can be employed to gain illegitimate access to the system as well as the network resources. A stored procedure is an operation set that is stored. Typically, stored procedures are written in SQL.

Since stored procedures are stored on the server side, they are available to all clients. Once the stored procedure is modified, all

clients automatically get the new version.


[EMP].[RetrieveProfile] @Name varchar(50),

@Passwd varchar(50)


3. AS


5. DECLARE @SQL varchar(200);

6. ... - -any user specific code

7. SET @SQL='select PROFILE from EMPLOYEE where ';

8. ... - -any user/DBA specific code

9. IF LEN(@Name) > 0 AND LEN(@Passwd) > 0


11. ... - -any user/ DBA specific code

12. SELECT @[email protected]+'NAME="[email protected]+"' and ';

13. SELECT @[email protected]+'PASSWD="[email protected]+"";

14. ... - - any user/ DBA specific code

15. END

16. ELSE


18. ... - -any user/ DBA specific code

19. SELECT @[email protected]+'NAME="Guest"';

20. ... - -any user specific code

21. END

22. ... - -any user/ DBA specific code

23. EXEC(@SQL)

24. ... - -any user/ DBA specific code

25. END

Code 1. Stored Procedure vulnerable to SQL-Injection A sample stored procedure called with the username and password as user inputs in a variable length string format is shown in Code 1. Notice that, there is an EXEC system function which allow the user to dynamically build a SQL statement in string format and later execute it. This feature is supported in most other business database products also. Dynamically built SQL statements provide great user flexibility but also face a great threat from SQL Injection attacks . The process of building an SQL statement could be used by the attacker to change the original intended semantics of the SQL statement.

If the stored procedure in Code 1 is called with no values for @Name and @Passwd variables, the following query would get executed:

select PROFILE from EMPLOYEE where NAME='Guest'

When user inputs are provided for @Name and @Passwd,

the following query would get executed:

select PROFILE from EMPLOYEE where NAME='name' and


In this scenario, suppose a user gives input for variable

@Name as "' OR 1=1 −−" and any string, say "null", for

the variable @Passwd the query would take the form:

select PROFILE from EMPLOYEE where NAME=" or 1=1 −−' and


The characters "−−" mark the beginning of a comment in SQL, and everything after that is ignored. The query as interpreted by the database is a tautology and hence will always be satisfied, and the database would return information about all users. Thus an attacker can bypass all authentication modules in place and gain unrestricted access to critical data on the web server.

An SQL-Injection Attack is a subset of the unverified/unsanitized input vulnerability and occurs when an attacker attempts to change the logic, semantics or syntax of a legitimate SQL statement by inserting new SQL keywords or operators into the statement. This definition includes, but is not limited, to attacks based on tautologies, injected additional statements, exploiting untyped parameters, stored procedures, overly descriptive error messages, alternate encodings, length limits, second-order injections and injection of "UNION SELECT", "ORDER BY" and "HAVING" clauses. A detailed explanation of the different types and forms of SQL-INJECTION ATTACK s and the ways in which they can be exploited are available in the public domain. [1] [2] [12] [7].

The widely deployed defense today is to train the programmers and web-developers about the security implications of their code and to teach them corrective measures and good programming practices[17] . However, rewriting or revising the entire lot of existing legacy code is not an easy process and is not a financially viable option for many organizations.

Even this does not guarantee any foolproof defense and hence we need automated processes to detect the vulnerability and eliminate them. Various other techniques like escaping the quotes and limiting the length of user inputs are employed as a quick fix solution. Unfortunately, even these security measures are only inadequate against highly sophisticated attacks .[18] It is of even greater concern that well known database vendor products like Microsoft SQL Server etc. provide attackers direct access to the command line shell and registry using methods like xp cmdshell, xp regread etc. Some of the very recent incidents only highlight the magnitude of this problem and hence the urgent need to address it in an appropriate manner. [19] [20] [3] [21].

A simple example is to check for single quotes and dashes, and escape them manually. This could be easily beaten by using ASCII representation of these characters such as CHAR(0x27) for single quotes. Safe Query Objects [9] and SQLDOM [8] use encapsulation of database queries to provide a safe and reliable way to access databases but they require developers to learn and use a new programming paradigm. SQLrand [6] provided a radical shift in the way this problem can be approached using query randomization [20]. However, it could be circumvented if the key used for randomization were to be exposed. The use of a machine learning technique trained using a set of typical application queries to detect malicious query models at runtime was proposed by F. Valeur, D. Mutz and G. Vigna [4]. However, like most other learning algorithms, it can generate a large number of false positives in the absence of an optimal query set for training.


SQL injection attacks have been the bread and butter of system crackers since the first SQL database became Web-enabled. Why is that? Simply put, if you can break through the authorization challenge presented at log-on, you can access the data stored in the SQL database. In other words, all of a customer's data can be exposed to the wrong individuals. That may not be so important when talking about a database that stores model numbers and color codes, but when the data changes to credit card or social security numbers, the game changes.

Recently, researchers have been exploring the use of static analysis in conjunction with runtime validation [11] to detect instances of SQLIAs. In [10], Buehrer and Weide have proposed the use of parse trees to detect malicious user input, which requires a developer to manually modify new and existing code.

Armed with an understanding of how SQL injection attacks work, administrators can take steps to prevent those attacks. In many cases administrators will have to work with their software and Web developers to implement some basics rules that offer additional protection.

1. Employ filters that prevent characters like single or double quotes, backslashes, colons and so on from being passed from a web form into the SQL Server.

2. Only allow numeric values that are integers to be passed to the SQL Server, that can be handled by simply using the ISNUMERIC command to validate the input.

3. Delete stored procedures from the SQL database that are not needed. Examples are xp_sendmail or xp_cmdshell, which are not normally needed, but can be used by hackers to send information or gain access.

4. Check privileges behind SQL commands, such as Startup and RUN, on the SQL Server Security TAB (for Microsoft (NSDQ:MSFT) SQL Server) and make sure the appropriate privileges are assigned for your environment.