SQL injection principle of explanation, very good!

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

A few days ago, the country's largest program Ape Community CSDN website user Database hacker Bulletin. 600 login name and million user password was publicly leaked, followed by a number of site users password was circulated in the network, in recent days to trigger a lot of netizens to their own account, password and other Internet information stolen widespread concern.

Network security has become the focus of the internet today, which has touched every user's nerves, because the design of the loopholes led to the bad consequences, the verification of a sentence "Out of the mix, sooner or later is to also", so I would like to introduce some of the frequent use of attack technology and prevention strategies.

SQL injection Maybe a lot of people know or use it, and it doesn't matter if you don't know it or you never hear it. Because next we will introduce SQL injection.

1.1.2 Body

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

in detail. It is the ability to use existing applications to inject (malicious) SQL commands into the background database engine to run. It is able to get a database on a site that has a security vulnerability by entering (malicious) SQL statements in a Web form. Instead of running SQL statements according to the designer's intent.

First let us know when SQL injection may occur.

If we enter the URL www.sample.com in the browser, as it is simply a simple request to the page, there is no dynamic request for the database to move. So it doesn't exist in SQL injection. When we enter

Testid=23 ">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 of the database (in which testid=23 represents a database query variable). So we can embed a malicious SQL statement in the URL.

Now we know where SQL injection is applicable. Next we will illustrate the application of SQL injection through a detailed example. Here we take the pubs database as a sample.

We use the Web page to query the job table for recruitment information, the job table design such as the following:

Figure 1 Jobs table

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

// <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 voidPage_Load (ObjectSenderEventArgsE) {if(! IsPostBack) {//Gets DepartmentID from HTTP request. stringqueryString = 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. Next, let's check the corresponding job information.

Figure 2 Job Table query results

What you see. We are querying the work information in the database with a work ID value of 1, and the ID of the job is displayed on the page. Description,min lvl and Max lvl and other information.

Today, we are asked to implement the function based on the work ID to query the corresponding work information, presumably we can give a solution very quickly. SQL schematic codes such as the following:

SELECT      job_idjob_desc      min_lvl         max_lvl from Jobs WHERE (  1)

If we are asked now to get all the data from the Department table. And you have to keep the where statement, then we just have to make sure where the constant is OK. SQL schematic codes such as the following:

SELECT     job_id, Job_desc, MIN_LVL, max_lvlfrom         jobswhere     (Job _id = 1) OR 1 = 1

Above we make the where constant, so the where in the query has not worked. Its query results are identical to the following SQL statement.

SELECT     job_id, Job_desc, MIN_LVL, max_lvlfrom         jobs

SQL query code implementations such as the following:

string. Format (    "Select job_id, Job_desc, MIN_LVL, max_lvl from jobs WHERE job_id= ' {0} '", jobId);

Now we're going to let the database run 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. For example, the following URLs are seen:

Http://localhost:3452/ExcelUsingXSLT/Default.aspx?

Jobid=1 ' or ' 1 ' = ' 1

Equivalent SQL statements such as the following:

SELECT      job_idjob_desc min_lvl max_lvl from JobsWHERE                    1 '  

Figure 3 Job Table query results

Now we are querying all the data in the job table. A simple, constant-truth expression makes it easy to attack.

Although we have queried the data of the job table, the data is not of much value. Because we are temporarily naming the table as the job table. So then we're going to find out the real table name of the table.

First of all, if the table name is job. Then enter the following URL:

Jobid=1 ' or%201= (Select%20count (*)%20from%20job)--">http://localhost:3452/excelusingxslt/default.aspx?"

Jobid=1 ' or 1= (select COUNT (*) from job)--

Equivalent SQL statements such as the following:

SELECT        job_idjob_descmin_lvlfrom WHERE      job_id=' 1 '          1 = (countjob-- ' 

Figure 4 Job Table Query results

When we have entered the above URL. As a result, the server returns our error message, which proves our error. So should we feel frustrated? No, it actually returns a lot of information, 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 designs a vulnerability to return the error message directly to the user.

Then assume that the table name is jobs. Then enter the following URL:

Jobid=1 ' or1= (Select%20count (*)%20from%20jobs)%20--">http://localhost:3452/excelusingxslt/default.aspx?

Jobid=1 ' or1= (SELECT COUNT (*) from jobs)--

Equivalent SQL statements such as the following:

SELECT        job_idjob_descmin_lvlfrom WHERE      job_id=' 1 '          1 = (countjobs-- ' 

Figure 5 Job Table Query results

It is now proved that the name of the table is jobs. This is a big step towards success, because we know the table name to be able to add and revise the table. And we were able to extrapolate a lot of other tables to make changes, and it would be a disaster if the changes were successful.

Now everyone has a preliminary understanding of the SQL injection attack. Then 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. It is possible to convert a single and double "-" by means of a regular table or a limited length.

2. Never use dynamically assembled SQL. Data query access can be done using either SQL or directly using stored procedures.

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

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

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

Validating user input with regular expression

The first thing we can do is to verify the user input data by means of the normal table, including the conversion of single and Double "-" characters.

Then continue verifying that the input data contains a reserved word for the SQL statement. such as: where. Exec,drop and so on.

Now let's write the regular form to verify the user's input. The following is an example of a regular table definition:

Regsystemthreats =        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? ",            regexoptionsregexoptions. IgnoreCase);

Above, we define a regular table-Regsystemthreats object, and pass it a regular table to validate user input.

Because we're done. The normal form of the user input checksum is then validated by the regular table to verify that the user input is legitimate. Because. NET has helped us to infer that the string matches the method--ismatch (), so we just need to pass the string to match to OK.

The schematic codes such as the following:

// <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 BOOLDetectsqlinjection (stringWhereclause) {returnRegsystemthreats.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 BOOLDetectsqlinjection (stringWhereclause,string) {returnRegsystemthreats.ismatch (whereclause) | | Regsystemthreats.ismatch (to be);}

Now that we're done with the regular form of the checksum, the next step is to add a validation 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 voidPage_Load (ObjectSenderEventArgsE) {off(! IsPostBack) {//Gets DepartmentID from HTTP request. stringqueryString = 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 NewException("Please enter correct field"); }        }    }}

When we run the following URL again. The embedded malicious statements are verified. This prevents SQL injection to some extent.

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

Figure 6 Adding validation query results

However, the use of a regular form can only protect against some common or known SQL injection methods, and whenever a new attack mode is found, it is necessary to change the form, which is a thankless task.

Data query and access via parameter stored procedure

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

--=============================================--author:jkhuang--Create date:12/31/2011--Description: Get data from the jobs table by specified jobid.--=============================================ALTER PROCEDURE[dbo].[Getjobs]--Ensure the ID type is int@jobIdIntasbegin--SET NOCOUNT on; SELECTjob_id, Job_desc, Min_lvl, Max_lvl fromDbo.JobsWHEREjob_id= @jobIdGRANT EXECUTE onGetjobs toPubsEND

Then change our web program to use the stored procedures to query data.

SqlCommand ("Getjobs", con)) {    //Uses store procedure.     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 are querying the database by participating in the stored procedure, we stare at the positive table checksum we added earlier.

Figure 7 Stored procedure query results

We see that when we try to embed a malicious SQL statement in a URL, the parameter stored procedure has helped us verify that the variable passed to the database is not shaping. And the advantage of using stored procedures is that we also have the ability to control user permissions very easily, and we are able to assign users only read or write access.

But let's just think it's really necessary. Does each database operation define a stored procedure? And so many stored procedures are not conducive to routine maintenance.

Parameter SQL statements

Or back to the previous dynamic splicing of SQL, we know that once there is malicious SQL code passed over. And is stitched into the SQL statement will be run by the database, then we can be enough to infer 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  (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 The results of the SQL query

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

Join the new schema

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

First, we right-click the schema directory. Then create a new schema.

Figure 9 Adding the Humanresource architecture

We're done up there. Add the Humanresource schema to the pubs database. Then put the jobs table in the Humanresource architecture.

Figure 10 Changing 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 done well before.

job_idjob_descmin_lvlJobs

Figure 11 Query output

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

job_idjob_descmin_lvlhumanresource. Jobs

Why did we run the SQL statement without entering the full table name Dbo.jobs?

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

Because we use ourselves to define the schema, it also reduces the likelihood that database table names will be pushed out.

LINQ to SQL

Stored procedures and query queries are used earlier, both of which are not used frequently. There are also a lot of ORM frameworks for the. NET Framework. For example: Nhibernate,castle and Entity Framework, where we use simpler LINQ to SQL.

Figure 12 Adding a jobs.dbml file

Pubsdatacontext ();  result; //Validates jobId is an int or not.  (intresult)) {    Gdvdata.datasource = dc.jobs.Where (p = = p.job_id = = result);    Gdvdata.databind ();}

Compared to stored procedures and parameter queries. LINQ to SQL We just need to add jobs.dbml and then query the table using LINQ is OK.

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 of the program's source code SQL this attack was carefully analyzed. It makes us SQL injection with an in-depth understanding of the mechanism that is developed as a Web application. Do not blindly believe that user input, the data entered by the user is a serious verification process. Otherwise. SQL injection will be unexpected.

At last. Happy new year. Health. Code with pleasure.

SQL injection principle of explanation, very good!