Web attack technology-SQL Injection

1.1.1 Summary
A few days ago, the user database of CSDN, the largest programmer community in China, was publicly released by hackers, And the login names and passwords of 6 million users were publicly leaked, subsequently, user passwords on multiple websites were spread over the Internet, which caused widespread concern among many netizens over the theft of their own accounts, passwords, and other Internet information.
 
Network security has become the focus of the Internet, and it has touched the nerves of every user. Due to the design vulnerabilities, it has resulted in unmanageable consequences. It verifies a phrase "mixed out, sooner or later, "I want to introduce some common attack technologies and defense strategies through the special blog.
 
SQL Injection may be known or used by many people. It doesn't matter if you don't know it or you haven't heard it yet, because we will introduce SQL Injection.
 
 
 
1.1.2 text
SQL Injection: inserts SQL commands into Web forms to submit or enter query strings for domain names or page requests, and finally fool the server to execute malicious SQL commands.
 
Specifically, it uses existing applications to inject (malicious) SQL commands into the background database engine for execution. It can input (malicious) SQL commands in Web forms) SQL statements get a database on a website with security vulnerabilities, instead of executing SQL statements according to the designer's intent.
 
First, let's know when SQL Injection may occur.
 
Suppose we enter the URL www.sample.com in the browser, because it only requires a simple request to the page without dynamic requests to the database, so it does not have SQL Injection. When we enter www.sample.com? When testid = 23, we pass the testid variable in the URL and provide a value of 23, because it is a request for dynamic query of the database (where? Testid = 23 indicates the database query variable), so we can embed malicious SQL statements in the URL.
 
Now we know the application of SQL Injection. Next we will illustrate the application of SQL Injection through specific examples. Here we use the pubs database as an example.
 
We can query the recruitment information in the job table on the Web page. The design of the job table is as follows:
 

 
 
Figure 1 jobs table
 
 
 
Then let's implement the Web program, which queries the corresponding recruitment information based on the job Id (job_id). The Code is as follows:
 
 
 
/// <Summary>
/// Handles the Load event of the Page control.
/// </Summary>
/// <Param name = "sender"> The source of the event. </param>
/// <Param name = "e"> The <see cref = "System. EventArgs"/> instance containing the event data. </param>
Protected void Page_Load (object sender, EventArgs e)
{
If (! IsPostBack)
{
// Gets initialize mentid from http request.
String queryString = Request. QueryString ["inclumentid"];
If (! String. IsNullOrEmpty (queryString ))
{
// Gets data from database.
GdvData. DataSource = GetData (queryString. Trim ());
 
// Binds data to gridview.
GdvData. DataBind ();
}
}
}
 
Now that we have completed the Web application, let's query the corresponding recruitment information.
 
 

 
Figure 2 job Table query results
 
 
 
, We want to query the work information with the work Id value of 1 in the database, and the Id, Description, Min Lvl and Max Lvl of the work are displayed on the page.
 
Now we are required to implement the function of querying the corresponding work information based on the work Id. You may soon be able to provide a solution. The SQL code is as follows:
 
 
 
SELECT job_id, job_desc, min_lvl, max_lvl
FROM jobs
WHERE (job_id = 1)
 
Assume that we want to obtain all the data in the Department table and keep the WHERE statement. Make sure that the WHERE statement is correct. The SQL code is as follows:
 
 
 
SELECT job_id, job_desc, min_lvl, max_lvl
FROM jobs
WHERE (job_id = 1) OR 1 = 1
 
 
In the preceding example, the WHERE statement does not work, and the query result is equivalent to the following SQL statement.
 
 
 
SELECT job_id, job_desc, min_lvl, max_lvl
FROM jobs
 
 
The SQL query code is implemented as follows:
 
 
 
String sql1 = string. Format (
"SELECT job_id, job_desc, min_lvl, max_lvl FROM jobs WHERE job_id = '{0}'", jobId );
 
Now we need to use the page Request Method to let the database execute our SQL statement. We need to embed a malicious expression 1 = 1 (or 2 = 2) in the URL, as shown in the following URL:
 
Http://www.bkjia.com/ExcelUsingXSLT/Default. aspx? Jobid = 1 'or '1' = '1
 
 
 
The equivalent SQL statement is as follows:
 
 
 
SELECT job_id, job_desc, min_lvl, max_lvl
FROM jobs
WHERE job_id = '1' OR '1' = 1'
 

 
 
Figure 3 job Table query results
 
 
 
Now we can query all the data in the job table, and use a simple constant expression to launch a simple attack.
 
Although we query all the data in the job table, the data is not of great value, because we temporarily name the table as a job table, so we need to find out the real table name of the table.
 
First, assume that the table name is job, and then enter the following URL:
 
Http: // localhost: 3452/ExcelUsingXSLT/Default. aspx? Jobid = 1' or 1 = (select count (*) from job )--
 
 
 
The equivalent SQL statement is as follows:
 
 
 
SELECT job_id, job_desc, min_lvl, max_lvl
FROM jobs
WHERE job_id = '1' or 1 = (select count (*) from job )--'
 
 

 
 
 
Figure 4 job Table query results
 
 
 
After we enter the above URL, the result server returns our error message, which proves that our assumption is wrong. Should we feel frustrated? No, in fact, a lot of information is returned here. First, it proves that the table name is not a job, and it also tells us that the background database is SQL Server, not MySQL or Oracle, this also designed a vulnerability to directly return the error information to the user.
 
Assume that the table name is jobs and enter the following URL:
 
Http: // localhost: 3452/ExcelUsingXSLT/Default. aspx? Jobid = 1' or1 = (select count (*) from jobs )--
 
 
 
The equivalent SQL statement is as follows:
 
 
 
SELECT job_id, job_desc, min_lvl, max_lvl
FROM jobs
WHERE job_id = '1' or 1 = (select count (*) from jobs )--'
 
 

 
Figure 5 job Table query results
 
 
 
Now it is proved that the table name is "jobs", which is a huge step towards success. As we know the table name, we can add, delete, and modify the table, we can also guess that more tables can be modified. Once the modification is successful, this will be a disaster.
 
Now you have a preliminary understanding of SQL Injection attacks. Next, let's learn how to prevent SQL Injection.
 
In general, there are the following points:
 
1. Never trust your input. to verify your input, you can use a regular expression or limit the length to convert single quotes and double.
 
2. Never use dynamic assembled SQL statements. You can use parameterized SQL statements or directly use stored procedures for data query and access.
 
3. Never use a database connection with administrator permissions. Use a database connection with limited permissions for each application.
 
4. Do not store confidential information in plain text. Encrypt Or hash the password and sensitive information.
 
5. The application exception information should be given as few prompts as possible. It is best to use custom error information to wrap the original error information and store the exception information in an independent table.
 
 
 
Validate user input through regular expression
First, we can use regular expressions to verify that user input data contains characters such as single quotes and double "-" conversion.
 
Then, verify whether the input data contains the reserved words of the SQL statement, such as WHERE, EXEC, and DROP.
 
Now let's write a regular expression to verify user input. The regular expression is defined as follows:
 
 
 
Private static readonly Regex RegSystemThreats =
New Regex (@ "\ s? Or \ s * | \ s ?; \ S? | \ S? Drop \ s | \ s? Grant \ s | ^ '| \ s? -- | \ S? Union \ s | \ s? Delete \ s | \ s? Truncate \ s | "+
@ "\ S? Sysobjects \ s? | \ S? Xp _.*? | \ S? Syslogins \ s? | \ S? Sysremote \ s? | \ S? Sysusers \ s? | \ S? Sysxlogins \ s? | \ S? Sysdatabases \ s? | \ S? Aspnet _.*? | \ S? Exec \ s? ",
RegexOptions. Compiled | RegexOptions. IgnoreCase); we defined a regular expression object RegSystemThreats and passed it a regular expression that verifies user input.
 
Because we have completed the regular expression for user input validation, the next step is to use this regular expression to verify whether the user input is legal, because. NET has helped us determine whether a string matches a regular expression-IsMatch (), so we only need to pass the string to be matched.
 
The sample code is as follows:
 
 
 
/// <Summary>
/// A helper method to attempt to discover [known] SqlInjection attacks.
/// </Summary>
/// <Param name = "whereClause"> string of the whereClause to check </param>
/// <Returns> true if found, false if not found </returns>
Public static bool DetectSqlInjection (string whereClause)
{
Return RegSystemThreats. IsMatch (whereClause );
}
 
/// <Summary>
/// A helper method to attempt to discover [known] SqlInjection attacks.
/// </Summary>
/// <Param name = "whereClause"> string of the whereClause to check </param>
/// <Param name = "orderBy"> string of the orderBy clause to check </param>
/// <Returns> true if found, false if not found </returns>
Public static bool DetectSqlInjection (string whereClause, string orderBy)
{
Return RegSystemThreats. IsMatch (whereClause) | RegSystemThreats. IsMatch (orderBy );
}
 
Now we have completed the regular expression used for verification. Next we need to add the verification function to the page.
 
/// <Summary>
/// Handles the Load event of the Page control.
/// </Summary>
/// <Param name = "sender"> The source of the event. </param>
/// <Param name = "e"> The <see cref = "System. EventArgs"/> instance containing the event data. </param>
Protected void Page_Load (object sender, EventArgs e)
{
If (! IsPostBack)
{
// Gets initialize mentid from http request.
String queryString = Request. QueryString ["jobId"];
If (! String. IsNullOrEmpty (queryString ))
{
If (! DetectSqlInjection (queryString )&&! DetectSqlInjection (queryString, queryString ))
{
// Gets data from database.
GdvData. DataSource = GetData (queryString. Trim ());
 
// Binds data to gridview.
GdvData. DataBind ();
}
Else
{
Throw new Exception ("Please enter correct field ");
}
}
}
}
 
When we execute the following URL again, the embedded malicious statement is verified to prevent SQL Injection to a certain extent.
 
Http: // localhost: 3452/ExcelUsingXSLT/Default. aspx? Jobid = 1 'or '1' = '1
 
 

 
 
 
Figure 6 Add validation query results
 
 
 
However, regular expressions can only prevent some common or known SQL Injection methods, and must be modified whenever a new attack method is found. This is a thankless job.
 
 
 
Query and access data through parameterized stored procedures
First, we define a stored procedure to search for data in the jobs table based on jobId.
 
 
 
-- ===================================================== ======
-- Author: JKhuang
-- Create date: 12/31/2011
-- Description: Get data from jobs table by specified jobId.
-- ===================================================== ======
Alter procedure [dbo]. [GetJobs]
-- Ensure that the id type is int
@ JobId INT
AS
BEGIN
-- Set nocount on;
SELECT job_id, job_desc, min_lvl, max_lvl
FROM dbo.jobs
WHERE job_id = @ jobId
Grant execute on GetJobs TO pubs
END
 
Then modify our Web program to query data using parameterized stored procedures.
 
 
 
Using (var com = new SqlCommand ("GetJobs", con ))
{
// Uses store procedure.
Com. CommandType = CommandType. StoredProcedure;
 
// Pass jobId to store procedure.
Com. Parameters. Add ("@ jobId", SqlDbType. Int). Value = jobId;
Com. Connection. Open ();
GdvData. DataSource = com. ExecuteScalar ();
GdvData. DataBind ();
}
 
Now we query the database through the parameterized stored procedure. Here we comment out the previously added Regular Expression validation.
 
 
 

 
 
Figure 7 Stored Procedure query results
 
 
 
We can see that when we try to embed malicious SQL statements in the URL, the parameterized stored procedure has helped us verify that the variables passed to the database are not shaping, in addition, we can easily control user permissions by using stored procedures. We can assign users read-only or read-write permissions.
 
But do we really need to define every database operation as a stored procedure? In addition, so many stored procedures are not conducive to daily maintenance.
 
 
 
Parameterized SQL statement
Back to the previous dynamic concatenation of SQL statements, we know that once malicious SQL code is passed and spliced into SQL statements, it will be executed by the database, can we determine before splicing? -- Name the SQL parameter.
 
 
 
String sql1 = string. Format ("SELECT job_id, job_desc, min_lvl, max_lvl FROM jobs WHERE job_id = @ jobId ");
Using (var con = new SqlConnection (ConfigurationManager. ConnectionStrings ["SQLCONN1"]. ToString ()))
Using (var com = new SqlCommand (sql1, con ))
{
// Pass jobId to SQL statement.
Com. Parameters. Add ("@ jobId", SqlDbType. Int). Value = jobId;
Com. Connection. Open ();
GdvData. DataSource = com. ExecuteReader ();
GdvData. DataBind ();
}
 

Figure 8 parameterized SQL query results
 
In this way, you can avoid writing stored procedures for each database operation (especially some simple database operations). You can execute this SQL statement only when you have the read permission for the jobs table in the database.
 
 
 
Add new architecture
The database architecture is a non-repeated namespace independent of database users. You can regard the architecture as an object container (similar to the namespace in. NET ).
 
First, right-click the architecture folder and create a new architecture.
 
 

 
 
 
 
 
Figure 9 Add a HumanResource Architecture
 
 
 
We have completed adding the HumanResource architecture to the pubs database, and then put the jobs table in the HumanResource architecture.
 
 
 
 
 

 
 
Figure 10 modify the architecture of the jobs table
 
 
 
When we execute the following SQL statement again, SQL Server prompts that jobs are invalid. Why? It was still running well.
 
 
 
SELECT job_id, job_desc, min_lvl, max_lvl FROM jobs
 

Figure 11 query output
 
 
 
When we enter the complete table name "Architecture name. Object Name" (HumanResource.jobs), the SQL statement is successfully executed.
 
SELECT job_id, job_desc, min_lvl, max_lvl FROM HumanResource.jobs
 
 
 
 

 
Why can we execute SQL statements without entering the full table name dbo.jobs?
 
This is because the default schema is dbo. When only the table name is entered, SQL Server automatically adds the default schema-dbo of the currently logged-on user.
 
Since we use a custom architecture, this also reduces the possibility of database table names being guessed.
 
 
 
LINQ to SQL
The stored procedure and parameterized query are used in the preceding steps. there are also many ORM frameworks in the NET Framework, such as nhib.pdf, Castle, and Entity Framework. Here we use relatively simple LINQ to SQL.
 
 
 

Figure 12 add the jobs. dbml File
 
 
 
Var dc = new pubsDataContext ();
Int result;
 
// Validates jobId is int or not.
If (int. TryParse (jobId, out result ))
{
GdvData. DataSource = dc.jobs. Where (p => p. job_id = result );
GdvData. DataBind ();
}
 
Compared with stored procedures and parameterized queries, we only need to add jobs. dbml to the linq to SQL statements, and then query the tables using the LINQ statements.
 
 
 
1.1.3 Summary
This article introduces the basic principles of SQL Injection. It introduces what is SQL Injection, how to implement SQL Injection, and how to prevent SQL Injection. Through the detailed analysis of SQL attacks through some program source code, we have a deep understanding of the SQL Injection mechanism. As a Web application developer, do not blindly trust the user input, but strictly verify the user input data. Otherwise, SQL Injection will arrive unexpectedly.
 
Finally, I wish you a happy New Year and good health. Code with pleasure.
 
 
 
Reference
 
Http://en.wikipedia.org/wiki/ SQL _injection
 
Http://msdn.microsoft.com/zh-cn/library/bb153640%28v= SQL .90%29.aspx
 
 
 
Updated on 02/29/2012