PHP Prevents SQL injection

Q: If you put the user input without any changes in the SQL query statement, it is likely to lead to SQL injection, such as the following example:

$unsafe _variable $_post [' User_input 'mysql_query(' INSERT into ' table ' (' column ') VALUES ('$unsafe _variable') ');

Why is there an injection bug? Because the user can enter value‘); DROP TABLE table;-- and then query the statement becomes this

INSERT into ' table ' (' column ') VALUES (' value '); DROP TABLE table;--')

A: by using pre-compiled statements (prepared statements) and parameterized queries (parameterized queries).

There are two ways to accomplish this:

1. Use the PDO object (useful for any database driver)$stmt$pdo->prepare (' SELECT * FROM employees WHERE name =: Name ' ); $stmt->execute (array$name));

2. use mysqli$stmt$dbConnection->prepare (' SELECT * FROM employees WHERE name =? ' ); $stmt $name ); $stmt

================

There are usually several ways to do this:

(1) Input validation and filtering
(2) Preprocessing SQL statements
(3) using Stored procedures
(4) Enter white list
(5) General simple filtering, directly with the PHP addslashes function can be.

Full anti-injection:

functionInject_check ($sql _str) { return Eregi(' select|insert|update|delete|\ ' |\/\*|\*|\.\.\/|\.\/|union|into|load_file|outfile ',$sql _str);//to filter}functionVERIFY_ID ($id=NULL) { if(!$id) {Exit(' No Submit parameters! ‘); }//is null-judged ElseIf(Inject_check ($id)) {Exit(' The arguments submitted are illegal! ‘); }//Injection Judgment ElseIf(!Is_numeric($id)) {Exit(' The arguments submitted are illegal! ‘); }//Digital Judgment $id=intval($id);//The whole type of return $id;}

functionStr_check ($str ) { if(!GET_MAGIC_QUOTES_GPC()) {//determine if MAGIC_QUOTES_GPC is open  $str=addslashes($str);//to filter } $str=Str_replace("_", "\_",$str);//filter out the ' _ ' $str=Str_replace("%", "\%",$str);//filter out the '% ' return $str;}

functionPost_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 Conversions return $post;}

    /** * Escape field values that need to be inserted or updated * * All queries and updated field variables need to call this method to process data * * @param mixed $STR The variables to be processed * @return mixed Returns the result after escaping*/     Public functionEscape$str) {        if(Is_array($str)) {            foreach($str  as $key=$value) {                $str[$key] =$this->escape ($value); }        } Else {            return addslashes($str); }        return $str; }

Usage examples:

 Public function_savewithwhere ($tableName,$row,$where,$sync=false) {        //Generate SQL string for fields to insert/update        $values= ' '; foreach($row  as $searchKey=$val) {            $values.= "`{$searchKey} ' = ' {Escape ($val)}‘,"; }        $values=Trim($values, ","); //If you have a itemid , you can't update it. Insert        if(Trim($where)) {            $sql= "UPDATE {$tableName} SET {$values} WHERE {$where} "; }Else {            $sql= "INSERT into {$tableName} SET {$values}"; }          $this->savelog ($sql); //Lib_db->update only returns a Boolean when insert is required to get last_id.        return $this->_update ($sql,$sync); }

Other Important:

1. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php

PHP Prevents SQL injection