SQL injection and defense with Php+mysql as an example

With the rapid development of Web applications and the continuous maturation of technology, the demand for web development-related jobs is increasing, and more and more people are joining the ranks of web development. However, due to the uneven level of programmers or the low security awareness, many programmers only consider the implementation of the function when writing code, little or no consideration of the security of the application. This has led to a number of applications with varying degrees of security vulnerabilities. SQL injection is one of them.

As a popular attack method, SQL injection has been widely concerned by network security researchers and hackers. So what is SQL injection? SQL injection is an attack technique in which an attacker can attack a malicious SQL command by inserting a malicious SQL command into a Web form's input domain or a query string in a page request to spoof the server.

1.SQL Injection (SQL injection) definition

SQL injection is an attacker who exploits a malicious SQL command into a query string in the input domain or page request of a Web form to trick the server into executing a malicious SQL command.

2.SQL injection Attack Hazard

With SQL injection vulnerability, an attacker can manipulate data in a database (such as obtaining confidential data from a database, arbitrarily changing data in a database, deleting a database, and so on), and can also hang a horse after getting certain permissions, or even gain administrator privileges for the entire server. Because SQL injection submits malicious SQL statements through the normal port of the website (typically port 80), there is no difference on the surface from normal access sites, which is very high if you do not look closely at the Web logs to find such attacks. Once the program has a SQL injection vulnerability, the harm is quite large, so we should pay enough attention to this.

3.SQL Injection Vulnerability principle

The essence of SQL injection is that a malicious attacker inserts or adds the SQL code to the program's parameters, and the program does not properly process the incoming parameters, causing the data in the parameter to be executed as code and eventually returning the execution result to the attacker.
Let's take a look at an example, when accessing http://www.a.com/cms/new.php?id=3, an article with an ID number of 3 is displayed on the page, and the server side actually executes the following piece of code, as shown in 1:

Figure 1

The SQL statements actually executed in the above procedure are as follows:

Select * FROM news where id=3

Now we add "and 1=1" after the URL (http://www.a.com/cms/new.php?id=3), at which point the actual SQL statement executed is:

Select * from news where id=3 and 1=1

Since this condition is always true, the returned page is the same as the normal page.

When "and 1=2" is added, the following SQL statement is executed:

Select * from news where id=3 and 1=2

Since this condition is never true, the returned page is different from the normal page.

Now we can control the value of the parameter ID to affect the return result of the program. Let's analyze the code in 11, and get the value of the parameter ID by taking it directly to construct the dynamic SQL statement and execute the SQL query. The entire process does not handle the value of the variable ID, resulting in a SQL injection vulnerability.

4.SQL Injection Typical process

1. Determine the scripting language used by the web system, discover the injection point, and determine if there is a SQL injection vulnerability

2. Determine the database type of the web system

3. Determine the structure of tables and corresponding fields in the database

4. Construct the injection statement to get the data content in the table

5. Locate the webmaster and log in with the Administrator account and password you obtained

6. Combine other loopholes and try to upload a Webshell

7. Further power to obtain the system privileges of the server

(Note: The above is the general process, according to the actual situation, the circumstances may be different.) ）

5.php+mysql Injection Instance

In order to facilitate testing, we built a Web system environment locally to penetrate the website in an attacker's perspective, allowing the reader to understand the complete process of php+mysql injection. The URL is http://www.a.com/cms/index.php, which is shown in page 2 of the website:

Figure 2

(1) Manual injection of the article

1. Find the injection point to determine if there is a SQL injection vulnerability in the site

First open the site, select the "Enterprise News" link, casually choose a news, 3 shows. With this URL (http://www.a.com/cms/new.php?id=3), we can determine that there is a parameter here that is ID, and its value is equal to 3.

Figure 3

Now we add a single quotation mark to the back of the http://www.a.com/cms/new.php?id=3, we will find a page that is different from the normal page, 4, indicating that the single quotation marks we added affect the running results of the program. There is a good chance there is an injection point, but sometimes it is not accurate to judge whether an injection point exists by simply adding single quotes. Because some programmers think that simple filtering of single quotes can avoid SQL injection, so in the program parameters are simply filtered single quotation marks, and no other processing. It is not detectable when a single quotation mark is used to detect whether a program has a SQL injection vulnerability, but there is still a SQL injection vulnerability that can be detected in other ways.

Figure 4

Then we add "and 1=1" and "and 1=2" behind the http://www.a.com/cms/new.php?id=3, and we find that the page returned when the and 1=1 are added is consistent with the normal page, as shown in 5.

Figure 5

When adding and 1=2, the page returned is inconsistent with the normal page, as shown in 6.

Figure 6

Now we can be sure that this is a SQL injection point, and the program does not take any processing of the parameters that are brought in, directly into the query statement of the database. It can be inferred that the SQL statements executed in the database when accessing http://www.a.com/cms/new.php?id=3 are probably:

Select * from [table name] where id=3

SQL statement after adding and 1=1:

Select * from [table name] where id=3 and 1=1 because the condition and 1=1 are always true, the returned page is consistent with the normal page

SQL statement after adding and 1=2:

Select * from [table name] where id=3 and 1=2 the returned page is inconsistent with the normal page because the condition 1=2 is always false

2. Get the information we are interested in via SQL injection

Above we have identified the SQL injection vulnerability of this system, let's experience the powerful power of SQL injection below. Let's start by judging the database type and version, and construct the following statement:

Http://www.a.com/cms/new.php?id=3 and Ord (Mid (Version (), >51))

Found to return to the normal page, stating that the database is MySQL, and the version is greater than 4.0, Support union query, the reverse is 4.0 or other types of database.

We then construct the following statement to list the fields in the table:

A. http://www.a.com/cms/new.php?id=3 ORDER by 10 returns an error page stating that the field is less than 10

B. http://www.a.com/cms/new.php?id=3 ORDER BY 5 returns to the normal page, indicating that the field is between 5 and 10

C. http://www.a.com/cms/new.php?id=3 ORDER BY 7 returns the error page, stating that the field is greater than 5 less than 7, you can determine the number of fields is 6. Let's check it out again.

D. http://www.a.com/cms/new.php?id=3 ORDER BY 6 returns to the normal page, stating that the field is indeed 6
This uses the "binary search Method", which can reduce the number of judgments, save time. If you use the order by 1 to increase the number of methods to judge, it takes 7 times to determine the number of fields, the use of "binary search" only need 4 times enough. When the number of fields is large, the advantage of the binary search method is more obvious and more efficient.

Below we construct the following statement to determine which fields can be used to display the data:

Http://www.a.com/cms/new.php?id=0 Union Select 1,2,3,4,5,6

Figure 7

Based on the return information, we can determine that the field 3,4,5,6 can be used to display data, as shown in 7. So let's construct the following statement to get some database information:

Http://www.a.com/cms/new.php?id=0 Union Select 1,2,database (), version (), User (), 6

Figure 8

Based on the page return information shown in 8, we can get the following information:

Database name: CMS

Database version: 5.1.51-community

User name: [email protected], and the web system and database on the same server

We can also construct other statements to get other information, such as operating system and database path, and so on, which is free to play. According to the above information, we know that the database is more than 5.0 versions. In mysql5.0 above version added a system library, called Information_schema, using it we can directly Bauku, table, field. In versions under 5.0, you can only get table names and field names by brute-force guessing. Let's construct the SQL statement for the table name and the field name:

Name of the Mob table

Http://www.a.com/cms/new.php?id=0 Union Select 1,2,table_name,4,5,6 from Information_schema.tables where Table_schema =0x636d73 Limit 0,1

Note: table_schema=[library name], the library name is converted to 16 binary. The 0 in Limit 0,1 represents the first table in the query library, and so on.

Until the 12th table, a table named root is found, and the administrator table is suspected. Then it bursts out the fields in this table and constructs the following SQL statement:

Http://www.a.com/cms/new.php?id=0 Union Select 1,2,column_name,4,5,6 from INFORMATION_SCHEMA.COLUMNS where table_name =0x726f6f74 Limit 0,1

Finally confirm that this table has three fields, namely root_id, Root_name, and Root_pass. The initial judgment of this table should be to save the administrator account and password. Now we've got the root table and its corresponding field names.

What's in the mob table

Construct the following SQL statement:

Http://www.a.com/cms/new.php?id=0 Union select 1,2,root_id,root_name,root_pass,6 from Cms.root

Figure 9

9, we get the administrator's account number and password, the password is MD5 encrypted, after decryption for 123456. We now have the administrator's account and password, just find the Administrator login page, you can login into the background. Soon we'll find the site. Back office Address: http://www.a.com/cms/isadmin/login.php

Let's log in and see below, 10.

Figure 10

Now we have the permissions of the webmaster, you can change the site information (such as add, delete articles, etc.), can also be combined with other vulnerabilities to upload a webshell, further to gain access to the server system permissions. You can use your imagination to the fullest. Because this article is mainly about SQL injection, please refer to the other information for the question of right to raise.

(2) Tool injection

Through the above introduction and manual injection of examples, we have a complete understanding of the principle of SQL injection vulnerability and exploit process. Readers may also find manual injections cumbersome, inefficient, and prone to error. But manual injection can deepen the understanding of the vulnerability principle and exploit process. When we have a good understanding of these, we can use tools to improve efficiency. Now there are a lot of great tools for us to choose from, and here's a demonstration of using tools for SQL injection. Here we use the industry's most famous Havij to do the demonstration. First, open the Havij software, which is shown in interface 11:

Figure 11

When all is set up, select "Analyze" and the results are returned soon, as shown in 12 and figure 13.

Figure 12

Figure 13

6.SQL injection Attack Defense

Above, we explain the principle and harm of SQL injection, and demonstrate the exploitation of SQL injection vulnerability from the point of view of attackers. Below we as the manager of the identity, from the perspective of defense to talk about SQL anti-injection.
Through the principle of SQL injection we learned that to successfully exploit SQL injection vulnerabilities, we need to meet two conditions, one is that the attacker can control the user's input, and the injected code is executed successfully. The following content is mainly about these two aspects to unfold.
First, we need to handle the parameters passed from other places correctly before entering the database. There are several main aspects:

1. Use a precompiled statement to bind a variable.

2. Validate the incoming parameters to ensure compliance with the standards defined in the application. There are mainly white list and blacklist two methods to achieve. In theory, the whitelist is more secure than the blacklist because it allows only the data defined in the whitelist to pass, and all other data is filtered out. The blacklist only filters data that is defined in the blacklist (such as some dangerous characters in SQL injection) and is usually implemented using regular expressions. However, it is important to note that because the blacklist cannot contain all of the dangerous characters, there may be situations where the blacklist is bypassed. For example, in MySQL injection, when the white space characters are filtered in the blacklist, we can use "/* (note in MySQL)" and "+" instead of spaces, and bypass the blacklist restrictions continue to inject, so we should try to use the whitelist as much as possible.

In addition to validating the user's input, there are times when the programmer's security awareness and technical problems may be just a small omission that could create a SQL injection vulnerability. In another case, we found a SQL injection vulnerability, but the vulnerability could not be fixed from the code layer due to conditions or other reasons. For example, in an enterprise a set of Web systems was developed by a programmer, after a period of time a quit. Later, there was a SQL injection vulnerability found in this system, and it is almost impossible for a programmer to come back to fix the vulnerability. Other programmers are not familiar with the system or are incapable of repairing the vulnerability because of technical problems. This situation is more prevalent in SMEs. While we cannot fix the vulnerability from the code layer, we can use some other means to prevent the exploit from succeeding and minimizing the risk. If a WAF (Web application firewall) can be used to block SQL injection attacks, although some attackers can bypass the WAF limit, there are a handful of them. For the vast majority of attacks, WAF can be detected and blocked. Even a high-level attacker, after the deployment of WAF, can make exploitation of the exploit significantly more difficult.

Finally, on the database side, you should use the "least privilege principle" to avoid web applications connecting directly to the database using a high-privileged account. If you have multiple different apps that use the same database, you should assign a different account to each app and give only the necessary permissions.

7.PHP Preventing SQL Injection principle Introduction

Although many PHP programmers in the country still rely on addslashes to prevent SQL injection, or to strengthen the Chinese to prevent SQL injection check, addslashes the problem is that hackers can use 0xbf27 to replace single quotes, Addslashes simply modifies 0xbf27 to 0xbf5c27 as a valid multibyte character, where the 0xbf5c is still considered a single quote, so addslashes cannot intercept successfully.

Of course addslashes is not useless, it is used for single-byte string processing, multibyte characters or mysql_real_escape_string bar.

Open MAGIC_QUOTES_GPC to prevent SQL injection, there is a setting in php.ini: MAGIC_QUOTES_GPC = off, this is off by default, if it is opened will automatically send the user to the SQL query to convert, such as the ' turn ' and so on, has a significant effect on preventing SQL injections.

If Magic_quotes_gpc=off, use the addslashes () function, and for example GET_MAGIC_QUOTES_GPC in the PHP manual, the code is as follows:

if (! GET_MAGIC_QUOTES_GPC ()) {  　　$lastnameaddslashes($_post[' LastName ']);   Else {  　　$lastname$_post[' LastName '];  

It is better to MAGIC_QUOTES_GPC already open case, or to $_post[' LastName '] to check, say Mysql_real_escape_string and Mysql_escape_ The difference between the 2 functions of string.

Mysql_real_escape_string must be in (PHP 4 >= 4.3.0, PHP 5) in the case to use, or only with mysql_escape_string, the difference is: mysql_real_escape_ String takes into account the current character set of the connection, and mysql_escape_string is not considered.

(1) mysql_real_escape_string--escapes special characters in the string used in the SQL statement, taking into account the current character set of the connection.

Using the method, the code is as follows:

$sql = "SELECT count (*) as Ctr from users where username02.= '". Mysql_real_escape_string ($username). "' and 03.password= '". Mysql_real_escape_string ($PW). "' Limit 1";

PHP Custom Function, the code is as follows:

functionInject_check ($sql _str) {      return Eregi(' select|insert|and|or|update|delete| ' |/*|*|.. /|. /|union|into|load_file|outfile ', $sql _str); } function verify_id ($id =null) {if (! $id) {exit (' no submit parameter!)}      ‘); } elseif (Inject_check ($id)) {exit (' argument submitted is illegal!     ‘); } elseif (!is_numeric ($id)) {exit (' argument submitted is illegal!      ‘);            } $id = Intval ($id);  return $id;      } function Str_check ($STR) {if (!GET_MAGIC_QUOTES_GPC ()) {$str = Addslashes ($STR);//filter}      $str = Str_replace ("_", "_", $str);           $str = str_replace ("%", "%", $str);  return $str;     } function Post_check ($post) {if (!GET_MAGIC_QUOTES_GPC ()) {$post = Addslashes ($post);      }//Open source code phpfensi.com $post = Str_replace ("_", "_", $post);      $post = str_replace ("%", "%", $post);      $post = NL2BR ($post);            $post = Htmlspecialchars ($post);  return $post; } 

To summarize:

* Addslashes () is forcibly added;

* Mysql_real_escape_string () will determine the character set, but the PHP version is required;

* Mysql_escape_string does not consider the current character set of the connection.

8.php Preventing SQL Injection principle Introduction

The prevention of SQL injection in DZ is to use the Addslashes function, while there are some substitutions in dthmlspecialchars this function $string = Preg_replace (/& ]{4}));)/, &1, this substitution solves the problem of injection, but also solves some problems of Chinese garbled.

Prevent SQL injection These details are usually in the main idea of the programmer or novice programmers, they did not submit the data submitted by some very filtering so as to give you a test on the breach of your database, the following I come to a simple user login without security configuration may occur SQL injection method, Let's take a look below.

For example, the following login code:

if($l= @mysql_connect(' localhost ', ' root ', ' 123 ') Or die(' Database connection failed '); mysql_select_db(' Test '); Mysql_set_charset (' UTF8 '); $sql= ' SELECT * FROM test where username = "$username" and password = "$password" '; $res=mysql_query($sql); if(mysql_num_rows($res)){  Header(' location:./home.php '); //Open Source Code phpfensi.com}Else{   die(' Incorrect input '); } 

Note that the above SQL statement, there is a great security risk, if the use of the following universal password and universal user name, then you can easily access the page, the code is as follows:

　　1. $sql = ' SELECT * FROM test where username = "* * *" and password = "* * *" or 1 = "1";

Obviously, the universal password for this SQL statement is: * * * "or 1 =" 1

2. $sql = ' SELECT * FROM test where username = "* * *" UNION SELECT * FROM users/* and password = "* * *";

Forward slash * indicates that the following does not execute, MySQL support Union union query, so directly query out all the data; So the universal user name for this SQL statement is: * * * "UNION SELECT * FROM users/*

However, this injection is only for SQL statements in code, if the code is as follows:

$sql = "SELECT * FROM Test where username = $username and password = $password";

The above injection is at least useless, but the method is the same;

After using PDO, SQL injection can be completely avoided, and in this fast-developing era, the framework is rampant, without much consideration for SQL injection.

The following is a collation of two to prevent SQL registration functions, the code is as follows:

/*filter all get over variables*/ foreach($_get  as $get _key=>$get _var) { if(Is_numeric($get _var)) { $get[Strtolower($get _key)] = Get_int ($get _var); } Else { $get[Strtolower($get _key)] = Get_str ($get _var); } } /*Filter all the post-over variables*/ foreach($_post  as $post _key=>$post _var) { if(Is_numeric($post _var)) { $post[Strtolower($post _key)] = Get_int ($post _var); } Else { $post[Strtolower($post _key)] = Get_str ($post _var); } } /*Filter Function*/ //Integer filter functionfunctionGet_int ($number) { return intval($number); } //string-type filter functionfunctionGET_STR ($string) {//Open Source Code phpfensi.comif(!GET_MAGIC_QUOTES_GPC()) { return addslashes($string); } return $string; } 

There are some blogs that write this code as follows:

<?PHPfunctionPost_check ($post)  {  if(!GET_MAGIC_QUOTES_GPC())//determine if the MAGIC_QUOTES_GPC is open{  $post=addslashes($post);//To filter the submission data without opening the MAGIC_QUOTES_GPC}  $post=Str_replace("_", "＼_",$post);//filter out the ' _ '$post=Str_replace("%", "＼%",$post);//filter out the '% '$post=NL2BR($post);//Carriage return Conversion$post=Htmlspecialchars($post);//HTML Markup Conversionsreturn $post; }  ?>

9.MySQL Comments

Many times annotations are also part of SQL injection.

There are three types of MySQL annotations: 1, # ... (single line) 2, "--..." (Single line, note:-followed by a space or tab or other whitespace) 3,/*...*/(multiline) 10. My Summary      In its normal project, if we use a variety of frameworks, we do not need to do anti-SQL injection work, because the framework has been packaged for us, but once we use the original way to get the user input values, we must pay attention to prevent SQL injection. SQL injection attack and defense principle is must understand, and always remember in the heart, safety nothing small, must be in every aspect of the protection. Reference:

Web Defense Series Tutorial PHP + MySQL Injection defense combat

PHP Prevent SQL Injection principle Introduction

Analysis of preventing SQL injection method in PHP login

SQL injection and defense with Php+mysql as an example