[Code implementation] prevents SQL injection solution and SQL Injection

[Code implementation] prevents SQL injection solution and SQL Injection

Source: PHP development learning portal

Address: http://www.phpthinking.com/archives/494

SQL injection is a common issue during program development. It is a security vulnerability at the database layer of the application. It is passed into the Web application by constructing special input as parameters, these inputs are mostly a combination of SQL syntax. The main reason is that the program does not carefully filter user input data by executing SQL statements to execute the operations required by attackers, this causes illegal data to intrude into the system.
In short, the SQL command is injected into the input string, ignoring the check in poorly designed programs, then these injected commands will be run by the database server for a normal SQL command and thus be damaged.

This is a simple data table:
Create table 'user '(
'Id' int (10) not null AUTO_INCREMENT,
'Name' varchar (50) not null,
'Password' varchar (50) not null,
'Age' smallint (3) not null,
'Is _ admin' tinyint (1) not null,
Primary key ('id ')
) ENGINE = InnoDB AUTO_INCREMENT = 4 default charset = utf8;
Insert into 'user' VALUES ('1', 'Tom ', '2313sdf', '10', '0 ′);
Insert into 'user' VALUES ('2', 'Lucy ', 'sdff234', '5', '0 ′);
Insert into 'user' VALUES ('3', 'Teacher wang ', 'salfdjlkvjaldf', '24', '1 ′);

 

See the following SQL statements:
$ SQL = "SELECT * from user WHERE name = '$ name' and password =' $ password ';";
Scenario 1:
If the information submitted through the form is:
$ Name = "tom 'or '1' = '1 ″;
$ Password = "test ";
When the data is not processed, the original SQL string is parsed as follows:
SELECT * from user WHERE name = 'Tom 'or '1' = '1' and password = 'test ';
SQL Execution result:
Without outputting the correct password, we got the "tom" Information and logged on to the website as "tom.
Scenario 2:
If the information submitted through the form is:
$ Name = "'or name! = "And is_admin = 1 or '1' = '1 ″;
$ Password = "test ";
When the data is not processed, the original SQL string is parsed as follows:
SELECT * from user WHERE name = "or name! = "And is_admin = 1 or '1' = '1' and password = 'test ';
SQL Execution result:
Without outputting the correct user name and password, we obtained the "teacher wang" information, that is, we logged on to the website as "teacher wang.
Case 3:

If the information submitted through the form is:
$ Name = "'; delete from user ;'";
$ Password = "test ";
When the data is not processed, the original SQL string is parsed as follows:
SELECT * from user WHERE name = "; delete from user;" and password = 'test ';
SQL Execution result:
Tragedy: the user table data is cleared.
How to Prevent SQL injection attacks:

• When combining SQL strings, escape the input parameters
• If you develop a web application using PHP, you can also enable the Magic quote function of PHP to automatically input parameters for all web pages, replacing single quotes with two consecutive single quotes.
• If possible, filter the following characters: Semicolon ";", two minus signs "-", single quotation marks "'", comment "/*… */".
• Replace dangerous characters. For example, in PHP, use the addslashes () function to protect SQL injection.
• Restrict the length of user input and the value range of user input.
• Create a database user with lower permissions for the current application, so that the database administrator will not be lost.

 

Php code implementation:
Function mysql_prepare_for_request ($ value, $ type = "string "){
$ Return = $ value;
Switch ($ type ){
Case "string ":
// Remove the slash
If (get_magic_quotes_gpc ()){
$ Return = stripslashes ($ return );
}
// Configure the connection to a valid database
$ Con = mysql_connect ('localhost', 'root', 'root'); // enter the correct user name and password.
If (! $ Con ){
Die ('could not connect: '. mysql_error ());
}

$ Return = mysql_real_escape_string ($ return );
Mysql_close ($ con );
Break;
Case "number ":
If (! Is_numeric ($ return )){
$ Return = 0;
}
Break;
Default:
$ Return = "";
Break;
}

Return $ return;

}

 

// Scenario 1: user input
$ Name = "tom 'or '1' = '1 ″;
$ Password = "test ";
$ SQL = "SELECT * from user WHERE name = '$ name' and password =' $ password ';";
Echo "Danger:". $ SQL. "<br/> ";

// Data Escape filtering
$ Name = mysql_prepare_for_request ($ name, 'string ');
$ Password = mysql_prepare_for_request ($ password, 'string ');
$ SQL = "SELECT * from user WHERE name = '$ name' and password =' $ password ';";
Echo "Security:". $ SQL. "<br/> ";
// Scenario 2: user input
$ Name = "'or name! = "And is_admin = 1 or '1' = '1 ″;
$ Password = "test ";
$ SQL = "SELECT * from user WHERE name = '$ name' and password =' $ password ';";
Echo "Danger:". $ SQL. "<br/> ";

// Data Escape filtering
$ Name = mysql_prepare_for_request ($ name, 'string ');
$ Password = mysql_prepare_for_request ($ password, 'string ');
$ SQL = "SELECT * from user WHERE name = '$ name' and password =' $ password ';";
Echo "Security:". $ SQL. "<br/> ";
// Case 3: user input
$ Name = "'; delete from user ;'";
$ Password = "test ";
$ SQL = "SELECT * from user WHERE name = '$ name' and password =' $ password ';";
Echo "Danger:". $ SQL. "<br/> ";

// Data Escape filtering
$ Name = mysql_prepare_for_request ($ name, 'string ');
$ Password = mysql_prepare_for_request ($ password, 'string ');
$ SQL = "SELECT * from user WHERE name = '$ name' and password =' $ password ';";
Echo "Security:". $ SQL. "<br/> ";

 

Output result:

Dangerous: SELECT * from user WHERE name = 'xiaoming 'or '1' = '1' and password = 'test ';
Security: SELECT * from user WHERE name = 'xiaoming \ 'or \ '1 \' = \ '1' and password = 'test ';

Dangerous: SELECT * from user WHERE name = "or name! = "And is_admin = 1 or '1' = '1' and password = 'test ';
Security: SELECT * from user WHERE name = '\' or name! = \ 'And is_admin = 1 or \ '1 \' = \ '1' and password = 'test ';

Dangerous: SELECT * from user WHERE name = "; delete from user;" and password = 'test ';
Security: SELECT * from user WHERE name = '\'; delete from user; \ "and password = 'test ';

 

Download the source code (click at the bottom of the URL)

How to use SQL anti-injection code

The best way to prevent SQL injection is to use stored procedures.
 
How to completely prevent SQL injection?

1. Yes. user input restrictions must be valid.
2. It should also be possible, but regular expressions are not an efficient method. Using HtmlEncode can effectively prevent space and other interpretations by DBMS, but be careful not to reverse encoding and decoding; A stored procedure is a program executed by a DBMS. Instead of submitting SQL statements, you can effectively prevent SQL injection.
3. for SQL attacks in the address bar, I have referenced a piece of information to explain it. He has made it clear about the mechanism. For solutions, he only considers them from the client, in fact, stored procedures can be used to prevent such attacks.
MATERIALS:
First, the intruder will determine whether a website can be injected. Suppose the address of an article is www.naohou.cn/show.asp? Id = 325 is usually tested by submitting two addresses, for example:
Www.naohou.cn/show.asp? Id = 325 and 1 = 1
Www.naohou.cn/show.asp? Id = 325 and 1 = 2
The first address is followed by and 1 = 1, and the SQL statement is changed: the Select * from form name where id = 1 and 1 = 1 must be set up before and after the statement is set up. The address of the previous article can be accessed, and the value 1 = 1 is also objective, so the first address can be displayed normally. On the contrary, 1 = 2 is obviously not true, the key is to take a look at this step. If the page and 1 = 2 is submitted normally, it means that it has not written and 1 = 2 into the SQL statement, and the website does not have the injection vulnerability; however, if an error page is returned after and 1 = 2 is submitted, it means that the site has brought the following statements into the SQL statement and executed it, which means that it can perform SQL injection. (Note: if the address is followed by news. asp? Id = '1' must be changed to news. asp? Id = 1 'and '1' = '1 to enclose the quotation marks)
So what can intruders do after they can be injected?
Here is a simple example, such as submitting an address:
Www.naohou.cn/show.asp? Id = 325 and exists (select * from table name where column name = data)
Check whether the table name and column name are correct based on the returned correct or error page. The specific implementation is to first guess the table name and then guess the column name. After you have guessed the table name and column name, you can use the ASC and MID functions to guess the data of each column. The MID function is in the format of mid (variable name, number of characters starting to read and reading), such as mid (pwd) you can read two characters from the first place in the variable pwd. The ASC function is in the format of ASC ("string"). For example, asc ("a") can read the ASCII code of the letter. Then, in actual application, the full text will be included.>