An explanation of how to prevent SQL injection in PHP

Preventing SQL injection is the best program to write in our daily PHP development website, because it can protect the website we develop. I believe that many junior PHP programmers have not written to prevent SQL injection. Some of them may not be accustomed to it, so today we will talk about how to use PHP to prevent SQL injection.

Cause

On the one hand, I have no awareness of this. Some data have not been strictly verified, and then directly spliced ​​into SQL to query. Leading to vulnerabilities, such as:

$id  = $_GET['id'];
$sql = "SELECT name FROM users WHERE id = $id";

Because there is no data type verification for $_GET['id'], the injector can submit any type of data, such as "and 1= 1 or " and other unsafe data. It is safer if you write it in the following way.

$id  = intval($_GET['id']);
$sql = "SELECT name FROM users WHERE id = $id";

Convert id to int type to remove unsafe things.

Verify data

The first step to prevent injection is to verify the data, which can be strictly verified according to the corresponding type. For example, the int type can be converted directly through intval:

$id =intval( $_GET['id']);

characters are more complicated to process. First, format the output through the sprintf function to ensure that it is a string. Then use some security functions to remove some illegal characters, such as:

$str = addslashes(sprintf("%s",$str));　
//也可以用 mysqli_real_escape_string　函数替代addslashes

This will be safer in the future. Of course, you can further determine the length of the string to prevent "buffer overflow attack" such as:

$str = addslashes(sprintf("%s",$str));
$str = substr($str,0,40); 
//最大长度为40

Parameterized binding

Parameterized binding is another barrier to prevent SQL injection. Both php MySQLi and PDO provide such functionality. For example, MySQLi can query like this:

$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 even more convenient, for example:

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

Most of us use the PHP framework for programming, so it is best not to spell SQL yourself and follow the framework. Parameter bindingQuery. When encountering more complex SQL statements, you must pay attention to strict judgment when spelling them yourself. You can also write a prepared one 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( &#39;|(?<!%)%f|&#39; , &#39;%F&#39; , $query ); 
        // Force floats to be locale unaware
    $query = preg_replace( &#39;|(?<!%)%s|&#39;, "&#39;%s&#39;" , $query ); 
        // quote the strings, avoiding escaped strings like %%s
    array_walk( $args, array( $this, &#39;escape_by_ref&#39; ) );
    return @ vsprintf( $query, $args );
}

Summary

Security is very important. It can also be seen from a person’s basic skills, the project is full of loopholes, the scalability and scalability It doesn't matter how maintainable it is. Pay more attention at ordinary times, establish safety awareness, and develop a habit. Of course, some basic safety will not take up the time of coding. If you develop this habit, even if the project is urgent and the time is short, you can still do it with high quality. Don't wait until all the things you are responsible for in the future, including the database, are taken away and losses are caused before you take it seriously. mutual encouragement!

Related articles:

Detailed explanation of the method of preventing sql injection in php

Introduction to the function of php to prevent sql injection

The above is the detailed content of An explanation of how to prevent SQL injection in PHP. For more information, please follow other related articles on the PHP Chinese website!