Mysql statement escape and SQL special character escape (SQL anti-injection)

In the SQL anti-injection method, we mean every day we are thinking about various methods. Some time ago, there was a lot of injection code in the company's database, later, I used php mysql_real_escape_string and mysql_escape_string. But why is there a problem? Let's analyze it.

Method

When the SQL where condition is equal to a special string, it is easy to report errors, cut off, or even not run, or cause database risks.
Below is a simple test
Create Test Data

The Code is as follows:	Copy code
Drop table if exists 'user '; Create table 'user '( 'Id' int (10) unsigned not null AUTO_INCREMENT, 'Name' varchar (255) default null, Primary key ('id ') ) ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = utf8; Insert into 'user' VALUES ('1 ',''\\'''\\\'');

If such an SQL statement is used for query, it cannot be found.

The Code is as follows:	Copy code
Select * from user where name = "'\'''\'"

The results can be queried only after escaping.

The Code is as follows:	Copy code
Select * from user where name = "'\\'''\\\'"

Some people may think of fuzzy queries such as like, but it is not a good solution, resulting in inaccurate queries.

We need to escape before executing SQL.
However, transfer functions such as mysql_real_escape_string and mysql_escape_string are not always reliable.
Here I wrote a php function.

The Code is as follows:	Copy code
Function escape ($ SQL _str ){ $ Search = array ('\', '/', '"'," '",' | ','-',';','[', ']'); $ Replace = array ('\', '\/', '\ "'," \ '",' \ | ','\-','\;', '\ [', '\]'); Return str_replace ($ search, $ replace, $ SQL _str ); }

Perform a special escape character before executing the SQL statement.

The Code is as follows:	Copy code
$ SQL = escape ($ SQL );

 
In addition, the example of get_magic_quotes_gpc in the php manual is as follows:

The Code is as follows:	Copy code
If (! Get_magic_quotes_gpc ()){ $ Lastname = addslashes ($ _ POST ['lastname']); } Else { $ Lastname = $ _ POST ['lastname']; }

There are four statements: select, update, delete, and insert. Can we avoid these problems if we filter the data we submit?
Then we construct the following functions using regular expressions:

PHP code

The Code is as follows:

Copy code

<? Php Function inject_check ($ SQL _str) { Return eregi ('select | insert | update | delete | '| Function verify_id ($ id = null) { If (! $ Id) {exit ('no submission parameter! ');} // Determines whether it is null. Elseif (inject_check ($ id) {exit ('the submitted parameter is invalid! ');} // Injection judgment Elseif (! Is_numeric ($ id) {exit ('the submitted parameter is invalid! ');} // Number judgment $ Id = intval ($ id); // integer Return $ id; } ?>

Then we can verify the code, and the code above becomes the following:

PHP code

The Code is as follows:	Copy code
<? Php If (inject_check ($ _ GET ['id']) { Exit ('the data you submitted is invalid. Please check it and submit it again! '); } Else { $ Id = verify_id ($ _ GET ['id']); // Our filter function is referenced here to filter $ id. Echo 'the data submitted is valid. Please continue! '; } ?>

But have we considered the data submitted by post and the large volume of data?

For example, some characters may cause harm to the database, such as '_' and '%'. These characters have special meanings. What if we control them? Another point is our php. when magic_quotes_gpc = off in ini, the submitted data that does not comply with the database rules will not be automatically prefixed with ''. Therefore, we need to control these problems and construct the following functions:
PHP code

The Code is as follows:	Copy code
<? Php Function str_check ($ str) { If (! Get_magic_quotes_gpc () // determines whether magic_quotes_gpc is enabled. { $ Str = addslashes ($ str); // Filter } $ Str = str_replace ("_", "_", $ str); // filter '_' $ Str = str_replace ("%", "%", $ str); // filter '%' Return $ str; } ?>

In this way, you only need to configure the security of php. ini and the server.