SQL Injection security issues can be eliminated using LINQ

As developers take on more and more security responsibilities, the first Web Application Security vulnerability that many developers know is an extremely dangerous form of Command Injection called "SQL injection. The original form of command injection refers to a vulnerability in which an attacker can change your web application by providing an unexpected input from a normal user.ProgramAllows attackers to run unauthorized commands on the server. Undoubtedly, SQL injection attacks are common and widely used attacks. Fortunately, once we understand this problem, we can easily prevent SQL injection attacks. Even better, Microsoft's data access technology provides. NET developers with the opportunity to completely clear SQL Injection Vulnerabilities, provided that they can be used correctly. This technology is called language-level integrated query (LINQ) and is released along with Visual Studio "orcas" And. NET Framework 3.5. This article will discuss how to enhance data access for Web applications through LINQCodeTo solve the problem of attacks through SQL injection.

SQL injection is a security vulnerability in Web applications. Attackers can exploit this vulnerability to submit malicious data to applications and cheat applications in executing malicious SQL commands on servers. Theoretically, this type of attack is easy to prevent. However, because it allows attackers to directly run database commands targeting users' key data, it becomes a common and harmful form of attack. In extreme cases, attackers can not only freely control user data, but also delete data tables and databases and even control the entire database server.

If this attack is easy to prevent, why is it so dangerous? First, because of the well-known economic reasons, your application database is very attractive and can attract the attention of attackers. If the SQL injection vulnerability may exist in Web applications, an attacker can easily detect it and then exploit it. Obviously, even if SQL Injection errors are not the most common mistakes made by developers, they are easily discovered and used.

 

Analysis of SQL Injection

Here we provide an example of SQL injection to illustrate two problems: first, SQL injection is very easy to make mistakes, and second, as long as strict program design is carried out, such errors are easily prevented.

The Web application used in this example contains a simple customer search page named sqlinjection. aspx, which is vulnerable to SQL injection attacks. This page contains a companyName input server control and a data table control to display the search results from Microsoft's sample database northwind (This database can be found in SQL Server 2005 ). The query executed during the search contains a common error in Application Design: It dynamically generates queries from user input. This is a major error in Web application data access, because it actually believes user input and directly sends it to your server. This query looks like this when the click event from "Search" is started:

Protected void btnsearch_click (Object sender, eventargs e) {string cmd = "select [mermerid], [companyName], [contactname] from [MERs] Where companyName = '" + txtcompanyname. text + "'"; sqldatasource1.selectcommand = cmd; gridview1.visible = true ;}

In this case, if a user enters "Ernst Handel" as the company name and clicks "Search", the company record is displayed as a response screen, this is exactly what we expected. However, an attacker can easily manipulate this dynamic query. For example, an attacker inserts a union clause and terminates the remaining part of the statement with a comment symbol. In other words, the attacker does not enter "Ernst Handel", but enters the following content:

Ernst Handel 'Union select mermerid, shipname, shipaddress from orders --

The result is that the SQL statement is executed on the server because the malicious request is added. It converts the dynamic SQL query to the following:

Select [customerid], [companyName], [contactname] from [Customers] Where companyName = 'ernst Handel 'Union select customerid, shipname, shipaddress from orders --'

This is a fairly legal SQL statement that can be executed on the application database and returns all the customers in the order table who have processed the order through the application.

Typical SQL Protection

We can see how easy it is to create and exploit an SQL injection vulnerability in your application. Fortunately, as mentioned above, only a few simple countermeasures can be taken to prevent SQL injection attacks. The most common and cost-efficient method to prevent SQL injection attacks is to verify all the data input that is ultimately used for data access in the application. Any input sent by the user, whether input through a web application or resident on a data storage device, verify the type, length, format, and range on the server before the server processes your data access commands. Unfortunately, code-based countermeasures are not very secure and may fail, especially when the following situations occur:

Improper verification program design

Verification is only performed at the customer level

In the application, fields (sometimes even one field) are missing during verification ).

Another layer of SQL Injection prevention involves correctly determining all SQL query parameters in the application, whether in dynamic SQL statements or stored procedures. For example, if the code constructs a query like the following, it is safer:

Select [customerid], [companyName], [contactname] from [Customers] Where companyName = @ companyName

When executed as part of an SQL statement, the parameterized query uses the input as a literal value, so the server may use the input with parameters as executable code. Even if you use a stored procedure, you must take another step to determine the input parameters, because the stored procedure does not provide protection for SQL Injection in Embedded queries.

Even with this simple correction, SQL injection is still a big problem for many companies. The challenge to the development team is to educate every developer to treat these types of vulnerabilities with caution and adopt purposeful and effective security standards to prevent attacks and enhance the evaluation of standards and operational security, confirm that there are no omissions. In this way, many variables need to be introduced to ensure application security. Therefore, if you choose a data access technology that makes SQL injection attacks impossible, your efficiency will be higher. This is where the function of LINQ works.

Overview

The standard mode for querying and updating data with any type of data storage is added in LINQ. This is true for both SQL databases, XML documents, and. Net objects. When building a database-driven application, LINQ enables developers to manage relevant data as they manage objects in C # Or VB. This is called "LINQ to SQL" and is considered as ADO. NET data technology system. When it was initially introduced in the form of CTP, LINQ to SQL was considered as dlinq.

With the use of data in applicationsProgramming LanguageTo simplify the complexity of related data management and database connection. In fact, you can display and operate database data through LINQ without writing any SQL statements. During the runtime, LINQ to SQL converts queries embedded or integrated into your code into SQL statements and executes them on the database system. By returning the query results to the application in the form of objects, the interaction between you and the database and SQL is completely transferred. There is no way to clear SQL Injection in Web applications faster than to clear SQL from applications. You can achieve this by stopping to SQL.

Ensures the security of access to the LINQ Database

When using the data access function, you can clear the possibility that SQL Injection exists in your application. The reason is simple: the specific parameters are added to each query you execute. When you build an SQL query from your embedded query statement in LINQ, any input submitted to the query is treated as the literal value no matter where it comes from. In addition, through the intelliisense and syntax check during compilation, the integration of LINQ and Visual Studio orcas can help developers build legal queries. The compiler can capture a large number of query errors. These errors can be used to bring functional defects or other types of vulnerabilities into your application. The difference is that, before you know whether it is correct or not, the SQL statements you write are only parsed on the database system at the runtime. The only attack method against LINQ to SQL is that attackers fool LINQ to form illegal or unconscious SQL statements. Fortunately, the language and compiler are designed to protect this aspect.

After understanding the above basic ideas, we will show you how to use LINQ to SQL to defend against SQL injection attacks and discuss an example of customer search. The first step is to create an object model for data in the database. Visual Studio orcas contains a new object relational designer, which enables you to generate a complete object model. To create an object model for our northwind MERs table, you can select "Add new project ..." Select the "LINQ to SQL file" template (this template is opened in the object link designer), and create a database named "LINQ to SQL" in the application. To automatically create a full object model for the MERs table, select the table in Server Explorer and drag it to the design level of the object relationship designer. In this example, the object relation designer adds a name named MERs. designer. CS file, which defines the class you want to use in the form of code, instead of writing code to directly interact with the database.

After defining the class of the Object Model for the data in the MERs table, you can directly query the data on the customer's data search page in the form of code. LINQ-powered page (linqtosql. aspx. CS) page_load method, specifically shows the customersdatacontext class created by the object relationship designer, and re-uses the preceding sqlinjection. the connection string used in the ASPX page. The following LINQ query re-uses the set of customer objects that match the WHERE clause.

Protected void page_load (Object sender, eventargs e) {string connectionstring = configurationmanager. connectionstrings ["northwndconnectionstring1"]. connectionstring; customersdatacontext DB = new customersdatacontext (connectionstring); gridview1.datasource = from customer in dB. MERs where customer. companyName = txtcompanyname. text orderby customer. companyName select customer; gridview1.databind ();}

After using LINQ to SQL, if we use "Ernst Handel" as the search value, the SQL statements generated at the runtime and executed on the server by LINQ look like the following:

Select [t0]. [customerid], [t0]. [companyName], [t0]. [contactname], [t0]. [contacttitle], [t0]. [address], [t0]. [City], [t0]. [region], [t0]. [postalcode], [t0]. [country], [t0]. [Phone], [t0]. [Fax] from [DBO]. [MERs] as [t0] Where [t0]. [companyName] = @ P0 order by [t0]. [companyName]}

It can be seen that the WHERE clause is automatically added with parameters. Therefore, traditional SQL injection attacks cannot cause damage. No matter what value the user submits as input to the search page, this query is secure and does not allow the user to input and execute commands on the server. If you enter the string used in the preceding example to implement SQL injection attacks, the query will not return any information. In fact, the biggest damage a user can perform with this query is to execute a powerful attack (or brute force attack )), the search function is mainly used to list all company records in the MERs table. The method used is to guess every possible value. However, this only provides the values in the exposed MERs table on that page, and does not give attackers the opportunity to inject commands, the command here refers to the command to access the extra data table in the database.

LINQ and security

As shown in the previous example, it is easy to introduce SQL Injection Vulnerabilities in Web applications, but it is easy to fix these vulnerabilities by using appropriate methods. However, there is no way to prevent developers from making these simple but dangerous mistakes. However, Microsoft's LINQ to SQL technology eliminates the possibility of SQL injection attacks from database applications by allowing developers to directly interact with object models rather than directly with databases. Built in C # and Visual Basic, The LINQ infrastructure correctly expresses valid and secure SQL statements to prevent SQL injection attacks, and enables developers to focus on the most natural programming language for them. No matter whether you use a new version of the SQL to produce a new version.. NET application development is a part of it. It is also a kind of Renovation for existing real application data access. You have made a choice to build a safer application.