Php SQL anti-injection experience

Php SQL anti-injection experience
On the one hand, I am not aware of the cause. Some data has not been strictly verified, and then I directly concatenate an SQL statement to query it. Cause the vulnerability, such as: $ id = $ _ GET ['id']; $ SQL = "SELECT name FROM users WHERE id = $ id "; because no data type verification is performed on $ _ GET ['id'], the injector can submit any type of data, such as insecure data such as "and 1 = 1 or. If you write data as follows, it is safer. $ Id = intval ($ _ GET ['id']); $ SQL = "SELECT name FROM users WHERE id = $ id"; Convert id to int type, you can remove insecure items. The first step to prevent injection of verification data is to verify the data and perform strict verification based on the corresponding type. For example, if the int type is directly converted from the intval type, you can: $ id = intval ($ _ GET ['id']). The character processing is more complex, first, make sure that the sprintf function is a string. Then remove invalid characters through some security functions, such:

$ Str = addslashes (sprintf ("% s", $ str); // You can also use the mysqli_real_escape_string function to replace addslashes. This will make the processing safer. Of course, you can further judge the string length to prevent "buffer overflow attacks" such as: $ str = addslashes (sprintf ("% s", $ str )); $ str = substr ($ str,); // The maximum length is 40.

 

Parameterized binding parameterized binding prevents another barrier of SQL injection. Php MySQLi and PDO provide such functions. For example, you can query MySQLi as follows:

$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");$code = 'DEU';$language = 'Bavarian';$official = "F";$percent = 11.2;$stmt->bind_param('sssd', $code, $language, $official, $percent);

 

PDO is more convenient, such:

/* Execute a prepared statement by passing an array of values */$sql = 'SELECT name, colour, calories    FROM fruit    WHERE calories < :calories AND colour = :colour'; $sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));$sth->execute(array(':calories' => 150, ':colour' => 'red'));$red = $sth->fetchAll();$sth->execute(array(':calories' => 175, ':colour' => 'yellow'));$yellow = $sth->fetchAll();

 

Most of us use php framework for programming, so it is best not to spell the SQL by yourself and bind the query according to the framework given parameters. When encountering complicated SQL statements, you must make strict judgments when spelling them yourself. You can also write a prepared by yourself without using PDO or MySQLi, such as the wordprss db query statement. It can be seen that it has also undergone strict type verification.

function prepare( $query, $args ) {    if ( is_null( $query ) )         return;    // This is not meant to be foolproof --            but it will catch obviously incorrect usage.    if ( strpos( $query, '%' ) === false ) {         _doing_it_wrong( 'wpdb::prepare' ,          sprintf ( __( 'The query argument of %s                 must have a placeholder.' ), 'wpdb::prepare()' ), '3.9' );   }    $args = func_get_args();    array_shift( $args );    // If args were passed as an array (as in vsprintf), move them up    if ( isset( $args[ 0] ) && is_array( $args[0]) )         $args = $args [0];    $query = str_replace( "'%s'", '%s' , $query );         // in case someone mistakenly already singlequoted it    $query = str_replace( '"%s"', '%s' , $query );         // doublequote unquoting    $query = preg_replace( '|(?<!%)%f|' , '%F' , $query );         // Force floats to be locale unaware    $query = preg_replace( '|(?<!%)%s|', "'%s'" , $query );         // quote the strings, avoiding escaped strings like %%s    array_walk( $args, array( $this, 'escape_by_ref' ) );    return @ vsprintf( $query, $args );}

Summary

Security is very important. It can also be seen that a person's basic skills are full of loopholes in the project, and the scalability and maintainability are useless. Pay more attention to it at ordinary times, establish security awareness, and form a habit. Some Basic Security certainly does not take up coding time. This habit can be developed, even if the project is in a short time, it can still be of high quality. Do not pay attention to what you will be responsible for when the database is taken away, resulting in losses. Sharing!