Explanation and precaution of SQL injection principle

Original address: http://www.cnblogs.com/rush/archive/2011/12/31/2309203.html1.1.1 Summary

Recently, the country's largest programmer community CSDN website user database was publicly released by hackers, 6 million of the user's login name and password was publicly disclosed, followed by a number of Web site user passwords were circulated in the network, in recent days to trigger a number of users of their own account, password and other Internet information stolen widespread concern.

Network security has become the focus of the Internet now, which has touched every user's nerves, due to the design of the loopholes led to the consequences of the bad, verified a sentence "Out of the mix, sooner or later is to also", so I would like to introduce some common attack technology and prevention strategy through the topic blog.

SQL injection may be known or used by many people, and it doesn't matter if you don't know it or you haven't heard it at all, because next we'll cover SQL injection.

1.1.2 Body

SQL injection: This is done by inserting a SQL command into a Web form to submit or entering a query string for a domain name or page request, eventually reaching a malicious SQL command that deceives the server.

Specifically, it is the ability to inject (malicious) SQL commands into the background database engine execution using existing applications, which can be obtained by entering (malicious) SQL statements in a Web form to a database on a Web site that has a security vulnerability, rather than executing the SQL statement as the designer intended.

First let us know when SQL injection may occur.

Suppose we enter the URL www.sample.com in the browser, because it is just a simple request to the page without dynamic request to the database, so it does not exist in SQL injection, when we enter www.sample.com?testid=23, We pass the variable TestID in the URL and provide a value of 23 because it is a request for a dynamic query to the database (where? testid=23 represents a database query variable), so we can embed a malicious SQL statement in the URL.

Now that we know where SQL injection is applicable, we'll use specific examples to illustrate the application of SQL injection, where we take the pubs database as an example.

We use the Web page to query the job table for recruitment information, the job table is designed as follows:

Figure 1 Jobs table

Then let us implement the Web program, it based on the work ID (job_id) to query the corresponding recruitment information, the schematic code is as follows:

<summary>///Handles The Load event of the page control.///</summary>///<param name= "sender" >the s Ource 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 DepartmentID from HTTP request.        String queryString = request.querystring["DepartmentID"];        if (!string. IsNullOrEmpty (queryString))        {            //Gets data from database.            Gdvdata.datasource = GetData (Querystring.trim ());            Binds data to GridView.            Gdvdata.databind ();}}}    

Now that we have finished the Web program, let's look at the appropriate recruitment information.

Figure 2 Job Table query results

, we want to query work information in the database with a work ID value of 1, and the page shows information such as Id,description,min LVL and Max lvl for that work.

Now that we have the ability to query the corresponding job information based on the job ID, we will soon be able to give a solution, the SQL schematic code is as follows:

SELECT     job_id, Job_desc, MIN_LVL, Max_lvlfrom         jobswhere     (job_id = 1)

Assuming we now ask for all the data in the Department table, and we must keep the where statement, we just need to make sure where is OK, and the SQL schematic code is as follows:

SELECT     job_id, Job_desc, MIN_LVL, Max_lvlfrom         jobswhere     (job_id = 1) OR 1 = 1

We make the where constant, so the where in the query has no effect, and its query results are equivalent to the following SQL statement.

SELECT     job_id, Job_desc, MIN_LVL, Max_lvlfrom         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're going to have the database execute our SQL statement in the form of a page request, we want to embed the malicious expression 1=1 (or 2=2, etc.) in the URL, as shown in the following URL:

Http://localhost:3452/ExcelUsingXSLT/Default.aspx?jobid=1 ' or ' 1 ' = ' 1

The equivalent SQL statement is as follows:

SELECT     job_id, Job_desc, MIN_LVL, Max_lvlfrom         jobswhere     job_id = ' 1 ' OR ' 1 ' = 1 '

Figure 3 Job Table query results

Now that we have all the data in the job table, we can do a simple attack with just one simple, constant-truth expression.

Although we have queried the data of the job table, the data is not of much value, since we have temporarily named the table as the job table, so we are going to find out the real table name of the table.

First we assume that the table name is the 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

When we enter the above URL, the result server returns our error message, which proves that our hypothesis is wrong, should we feel frustrated? No, actually returns a lot of information 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, which also designed a vulnerability to return the error message directly to the user.

Next assume that the table name is jobs, and then 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 that the table name is jobs, this can be a big step towards success, because we know the table name to be able to edit the table, and we can guess more tables to modify them, once the change is successful then it will be a disaster.

Now that we have a preliminary understanding of the SQL injection attack, let's learn how to prevent SQL injection.

In general there are the following points:

1. Never trust the user's input, to verify the user's input, you can use regular expressions, or limit the length, the single quotation mark and the double "-" to convert, and so on.

2. Never use dynamically assembled SQL, either using parameterized SQL or directly using stored procedures for data query access.

3. Never use a database connection with administrator rights, and use a separate limited database connection for each app.

4. do not store confidential information in plaintext, please encrypt or hash out the password and sensitive information.

5. The exception information applied should give as few hints as possible, preferably using a custom error message to wrap the original error message and store the exception information in a separate table.

Validating user input with regular expression

First, we can verify that the user input data through the regular expression is included: the conversion of single quotation marks and double "-" characters.

It then continues to verify that the input data contains reserved words for SQL statements, such as: Where,exec,drop, and so on.

Now let's write a regular expression to verify the user's input, and 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);

Above we define a regular expression object regsystemthreats, and pass it a regular expression that validates the user input.

Since we have completed a regular expression of the user input checksum, the next step is to verify that the user input is legitimate by the regular expression, because. NET has helped us to determine whether a string matches the regular expression method--ismatch (), so we just need to pass the string to match is OK.

The schematic 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) {    Retu RN 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= "by" >string of the clause to check</param>///<returns>true if found, false if not found </returns>public static bool Detectsqlinjection (string Whereclause, string by) {    return Regsystemthreats.ismatch (whereclause) | | Regsystemthreats.ismatch (to be);}

     Now we have a regular expression for the checksum, and then we need to add a check 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 DepartmentID 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 validated to some extent to prevent SQL injection.

Http://localhost:3452/ExcelUsingXSLT/Default.aspx?jobid=1 ' or ' 1 ' = ' 1

Figure 6 Adding a validation query result

But using regular expressions can only protect against some common or known SQL injection, and it is a thankless task to modify regular expressions whenever a new attack pattern is found.

Data query access via parameterized stored procedures

First we define a stored procedure to look up data in the jobs table based on Jobid.

--=============================================--Author:        jkhuang--Create date:12/31/2011--Description:    Get data from the jobs table by specified jobid.--=============================================alter PROCEDURE [dbo]. [Getjobs]    --Ensure that the ID of type is int    @jobId intasbegin--    SET NOCOUNT on;    SELECT job_id, Job_desc, MIN_LVL, max_lvl from    dbo.jobs    WHERE job_id = @jobId    GRANT EXECUTE in getjobs to pubs END

Then modify our web program to use parameterized stored procedures for data query.

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 that we have a database query through a parameterized stored procedure, we comment out the previously added regular expression check.

Figure 7 Stored procedure query results

You see, when we try to embed a malicious SQL statement in a URL, the parameterized stored procedure has helped us verify that the variables passed to the database are not shaping, and the advantage of using stored procedures is that we can also easily control user permissions, and we can assign read-only or read-write permissions to the user.

But let's just think about it. Does each database operation be defined as a stored procedure? And so many stored procedures are not conducive to routine maintenance.

Parameterized SQL statements

Or back to the previous dynamic splicing of SQL, we know that once the malicious SQL code passed over, and is stitched into the SQL statement will be executed by the database, then can we make a judgment before stitching it? --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 (con figurationmanager.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

This allows us to avoid writing stored procedures for each database operation, especially for simple database operations, and to execute the SQL statement when the user has read access to the Jobs table in the database.

Add a new schema

A database schema is a non-repeating namespace that is independent of the database user, and you can treat the schema as a container for objects (similar to. NET namespaces).

First we right-click the schema folder and then create a new schema.

Figure 9 Adding the Humanresource schema

We have finished adding the Humanresource schema to the pubs database, and then put the jobs table in the Humanresource schema.

Figure 10 Modifying the schema that the jobs table belongs to

When we execute the following SQL statement again, SQL Server prompts jobs to be invalid, what is the reason for this? It's good to run before.

SELECT job_id, Job_desc, MIN_LVL, max_lvl from Jobs

Figure 11 Query output

When we enter the full table name "schema name. Object Name" (humanresource.jobs), the SQL statement executes successfully.

SELECT job_id, Job_desc, MIN_LVL, max_lvl from Humanresource.jobs

Why do we not have to enter the full table name Dbo.jobs before we execute the SQL statement?

This is because the default schema is dbo, and when you enter only the table name, SQL Server automatically adds the default schema for the currently logged on user--dbo.

Because we use custom schemas, this also reduces the likelihood that database table names will be guessed.

LINQ to SQL

Stored procedures and parameterized queries are used earlier, both of which are very common, and there are many ORM frameworks for the. NET framework, such as: Nhibernate,castle and Entity Framework, where we use the simpler LINQ to Sql.

Figure 12 Adding a 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 ();}

LINQ to SQL is just a matter of adding jobs.dbml, and then querying the table using LINQ is OK, compared to stored procedures and parameterized queries.

1.1.3 Summary

We've covered the fundamentals of SQL injection in this article, by describing what SQL injection is, how to do SQL injection, and how to protect against SQL injection. Through some program source code to the SQL attack detailed analysis, so that we have a deep understanding of the SQL injection mechanism, as a Web application developer, must not blindly trust the user input, and to the user input data to carry on the strict verification processing, otherwise, SQL The injection will be out of date.

Finally, I wish you a happy New year, 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

Explanation and precaution of SQL injection principle