MySQL statement escape, SQL Special character escape (SQL anti-injection)

Specific methods

When the SQL where condition equals a string of special symbols, it is easy to make an error, cut off, not even execute, or cause the database to be dangerous.
Let's simply test the following
Creating test data

The code is as follows	Copy Code
DROP TABLE IF EXISTS ' user '; CREATE TABLE ' user ' ( ' ID ' int (a) 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 ', ' \ n ', ' \\\ ');

If we query through such SQL statements, we can't find it.

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

You must be escaped to query before you can query for results

The code is as follows	Copy Code
SELECT * from user where name = ' \ \ ' ' \\\ '

Some people may think of using fuzzy queries like, but not a good solution, resulting in inaccurate query

So we're going to escape before we execute the SQL.
But the transfer functions such as mysql_real_escape_string and mysql_escape_string are not always reliable.
I wrote a PHP function here.

The code is as follows	Copy Code
function Escape ($sql _str) { $search = Array (', ', '/', ' "', '" ', ' | ', '-', '; ', ' [', '] '); $replace = array (' \ \ ', ' \/', ' \ "', ' \ ' ', ' \| ', ' \-', ' \; ', ' \[', ' \] '); Return Str_replace ($search, $replace, $sql _str); }

A special symbol escape can be performed before executing SQL

The code is as follows	Copy Code
$sql = Escape ($sql);

Also for examples of GET_MAGIC_QUOTES_GPC in the PHP manual:

The code is as follows	Copy Code
if (!GET_MAGIC_QUOTES_GPC ()) { $lastname = addslashes ($_post[' LastName ')); } else { $lastname = $_post[' LastName ']; }

The statement has four sentences: Select, update, delete, insert, so if we filter through the data we submit, can we avoid these problems?
So we use the regular to build the following functions:

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 submit parameters! '); }//IS NULL judgment ElseIf (Inject_check ($id)) {exit (' submitted parameter illegal! '); }//Injection judgment ElseIf (!is_numeric ($id)) {exit (' submitted parameter illegal! '); }//Digital judgment $id = Intval ($id); Integral type return $id; } ?>

Well, then we'll be able to verify, so our program code above becomes the following:

PHP code

code is as follows	copy code
<?php         if ( Inject_check ($_get[' id '))          {          exit (' You submit the data illegally, please check and resubmit! ');        }         else          {         $id = verify_id ($_get[' ID ']); Here is a reference to our filter function, $id filter          Echo ' submitted data legal, please continue! ';        }

But have we considered the data submitted by post, the large amount of data?

For example, some characters may cause harm to the database, such as ' _ ', '% ', these characters all have special meaning, then if we control it? Another point is that when our php.ini inside the MAGIC_QUOTES_GPC = off, then the submission of data that does not conform to the database rules is not automatically in front of the "", then we want to control these problems, and then build the following functions:
PHP code

The code is as follows	Copy Code
<?php              function Str_check ($STR)           {         if (!GET_MAGIC_QUOTES_GPC ())// Determine if MAGIC_QUOTES_GPC is open          {          $STR = addslashes ($STR); Filter         }         $str = str_ Replace ("_", "_", $str); Filter ' _ '          $str = str_replace ("%", "%", $str);//filter out '% ' &NBSP;&NBSP;&N bsp;               return $str;         }

So you only have to PHP.ini and server Security configuration.