SQL injection principle explanation, very good!

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

Recently, the country's largest program ape Community CSDN site user database was hacked public, 6 million users of the login name and password was publicly leaked, and then there are many sites of users password was circulated in the network, in recent days to trigger many netizens to their own account, Password and other Internet information are widely feared for theft.

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 may be known or used by a lot of people, it doesn't matter if you don't know it or you haven't heard it at all, because we'll cover SQL injection next.

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 inject (malicious) SQL commands into the background database engine to run with existing applications, and it is possible to get a database on a security-vulnerable site by entering (malicious) SQL statements in a Web form, rather than running SQL statements as the designer intended.

First let us know when SQL injection may occur.

If we enter the URL www.sample.com in the browser, because it is simply a simple request to the page without dynamic request to the database, so it does not exist 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 against the database (in which 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 will illustrate the application of SQL injection with 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, let's look at the corresponding job information.

Figure 2 Job Table query results

As you can see, 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 requires us to implement the function based on the work ID query corresponding work information, presumably everyone very quickly to give a solution, SQL schematic code such as the following:

SELECT      job_idjob_desc      min_lvl         max_lvl from Jobs WHERE (  1)

If we now ask us to get all the data in the Department table, and we have to keep the where statement, we just have to make sure where is OK, and the SQL schematic code, for example:

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

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 get the database to run our SQL statements in the form of a page request, we want to embed the malicious expression 1=1 (or 2=2, etc.) in the URL, as seen in the following URL:

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 that we have all the data in the job table, we can do a simple attack with a simple constant-truth expression.

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 we will then find out the real table name of the table.

First, if the table name is the job, then enter the following URL:

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 enter the above URL, the result server returns our error message, which proves that if we are wrong, then 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.

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)--

Equivalent SQL statements such as the following:

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

Figure 5 Job Table Query results

Now that the table name is jobs, this is a big step towards success because we know that table names can be modified and manipulated, and we can also extrapolate a lot of other tables to make changes, and it would be a disaster if the changes were successful.

Now that we have a preliminary understanding of SQL injection attacks, 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, through the normal table, or limit the length of the single and Double "-" to convert.

2. Never use dynamically assembled SQL, be able to use the SQL to be counted or use stored procedures directly for data query access.

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 proceed to verify that the input data includes the reserved words of the SQL statement, such as Where,exec,drop.

Now let's write the regular form to verify the user's input, and the normal table definition is as follows:

Regsystemthreats =        Regex(+            @ "\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 followed 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, we need to add the 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 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 statement is validated to some extent to prevent SQL injection.

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

Figure 6 Adding? verifying 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, here we stare at the positive table checksum that was previously added.

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 variables passed to the database are not shaped, and that the advantage of using stored procedures is that we have the ability to control user permissions very conveniently, and that we can assign only read or write access to the user.

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 the malicious SQL code passed over, and was stitched into the SQL statement will be run by the database, then we can be enough to make inferences 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 in? New architecture

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 and then create a new schema.

Figure 9 Join? Humanresource Architecture

We're finished. Join in the pubs database? Humanresource architecture, and 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 non-trivial, and there are many ORM frameworks for the. NET framework, such as: Nhibernate,castle and Entity Framework, where we use a 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 ();}

LINQ to SQL we just need to join the JOBS.DBML, and then use LINQ to query the table when compared to stored procedures and query 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 has carried on the careful analysis, causes us to the SQL injection mechanism to have a thorough understanding, as a Web application developer, must not blindly believe the user input, but to the user input data carries 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.