How to prevent SQL injection?

1. It was past 11 o’clock last night, and a friend suddenly came to me and told me that a vulnerability in their company’s website had been submitted to wooyun. (Then I briefly learned about the vulnerability with the girl. PS: The girl is a php programmer)

2. Two vulnerabilities were submitted on wooyun, one of which is SQL injection(After understanding, the framework used by their company is an 11-year-old framework, or mysql_query()these old mysql functions) Another problem is the cookie. The girl wrote the user's uid and other sensitive information into the cookie, 2333. Then the uid used by PHP to process business logic is also taken from the cookie, 233333 (resulting in the ability to disguise as any user after modifying the cookie)

3. I told her the solution to SQL injection (the first solution is to treat the symptoms but not the root cause, use regular expressions to match SQL statements, filter dangerous characters, keywords, escape symbols, the second solution is to abandon mysql Old function, use PDO or mysqli) (for the loophole in the cookie, I suggest that she store sensitive information such as uid in the session, and then encrypt the session ID and put it in the cookie)

4. The girl finally said that she probably understood. It was unrealistic to change the mysql driver of the company framework. The company would not let her change it, so she had to use regular matching SQL statements to filter illegal strings. !

5. I went online to find some SQL statement filtering functions. They are known to be very useful SQL filtering functions
   Answers from Zhihu aunties

I would like to discuss with you how to prevent SQL injection. Do you have any useful SQL injection functions to filter SQL statements? Share them. Thank you!

Reply content:

1. It was past 11 o'clock last night, and a friend suddenly came to me and told me that a vulnerability in their company's website had been submitted to wooyun. (Then I briefly learned about the vulnerability with the girl. PS: The girl is a php programmer)

2. Two vulnerabilities were submitted on wooyun, one of which is SQL injection(After understanding, the framework used by their company is an 11-year-old framework, or mysql_query()these old mysql functions) Another problem is the cookie. The girl wrote the user's uid and other sensitive information into the cookie, 2333. Then the uid used by PHP to process business logic is also taken from the cookie, 233333 (resulting in the ability to disguise as any user after modifying the cookie)

3. I told her the solution to SQL injection (the first solution is to treat the symptoms but not the root cause, use regular expressions to match SQL statements, filter dangerous characters, keywords, escape symbols, the second solution is to abandon mysql Old function, use PDO or mysqli) (for the loophole in the cookie, I suggest that she store sensitive information such as uid in the session, and then encrypt the session ID and put it in the cookie)

4. The girl finally said that she probably understood. It was unrealistic to change the mysql driver of the company framework. The company would not let her change it, so she had to use regular matching SQL statements to filter illegal strings. !

5. I went online to find some SQL statement filtering functions. They are known to be very useful SQL filtering functions
   Answers from Zhihu aunties

I would like to discuss with you how to prevent SQL injection. Do you have any useful SQL injection functions to filter SQL statements? Share them. Thank you!

Why not use preprocessing? ? ? ? ? ? ? ? ? ?
Why not use preprocessing? ? ? ? ? ? ? ? ? ?
Why not use preprocessing? ? ? ? ? ? ? ? ? ?
Say important things three times!
SQL injection is already a thing of the last century!
If you don’t want to rewrite, you can use my simply packaged ready-made PHP classes, please choose one of them to use.
(I found that the code had some traces of my project, so I deleted the irrelevant code)
MySQLi version:

<code><?php
class DB
{
    var $Sql;
    var $Fetch;
    var $Param = array();
    
    public function __construct($Sql, $Fetch, $Param = array())
    {
        $this->sql = $Sql;
        $this->fetch = $Fetch;
        $this->param = $Param;
        //数据库信息存放在配置文件里面，请自行修改成正确的路径和值
        require($_SERVER['DOCUMENT_ROOT'] . '/configs/config.inc.php');
        $this->dbhost = & $DBHost;
        $this->dbuser = & $DBUser;
        $this->dbpw = & $DBPassword;
        $this->dbname = & $DBName;
    }
    
    public function Query()
    {
        $Mysqli = new mysqli($this->dbhost, $this->dbuser, $this->dbpw, $this->dbname);
        if ($Mysqli->connect_errno)
        {
            echo '无法连接数据库';
            return false;
        }
        $Mysqli->query('SET NAMES UTF8');
        $Mysqli->begin_transaction(MYSQLI_TRANS_START_READ_ONLY);
        $Stmt = $Mysqli->stmt_init();
        $Stmt->prepare($this->sql);
        if (count($this->param) > 0)
        {
            $Type = '';
            for ($i = 0; $i < count($this->param); $i++)
            {
                if (is_double($this->param[$i]))
                {
                    $Type .= 'd';
                }
                else if (is_int($this->param[$i]))
                {
                    $Type .= 'i';
                }
                else if (is_string($this->param[$i]))
                {
                    $Type .= 's';
                }
                else
                {
                    $Type .= 'b';
                }
            }
            $RefArg = array($Type);
            for ($I = 0; $I < count($this->param); $I++)
            {
                $RefArg[] = & $this->param[$I];
            }
            call_user_func_array(array($Stmt, 'bind_param'), $RefArg);
        }
        if (!$Stmt->execute())
        {
            echo '读取数据库时发生错误：'. $Stmt->error;
            echo $this->sql;
            print_r($this->param);
            $Mysqli->rollback();            
            return false;
        }
        $Mysqli->commit();
        if (strtolower(substr($this->sql, 0, 6)) == 'select')
        {
            $this->res = $Stmt->get_result();
            $Stmt->free_result();
            return $this->GetRes();
        }
        else
        {
            $Stmt->free_result();
            return true;
        }
    }
    
    public function GetRes()
    {
        switch(strtolower($this->fetch))
        {
            case 'all':
                $row = $this->res->fetch_all();
                break;
            case 'array':
                $row = $this->res->fetch_array();
                break;
            case 'assoc':
                $row = $this->res->fetch_assoc();
                break;
            case 'field':
                $row = $this->res->fetch_field();
                break;
            case 'row':
                $row = $this->res->fetch_row();
                break;
            default:
                echo 'Please select a row return mode.';
                exit;
        }
        return $row;
    }
    
    public function NumRow()
    {
        if (isset($this->res))
        {
            return $this->res->num_rows;
        }
        else
        {
            return false;
        }
    }
}
?></code>

PDO_MYSQL version:

<code><?php
class DB
{
    var $SQL;
    var $Fetch;
    var $Param = array();
    
    public function __construct($SQL, $Fetch, $Param)
    {
        //数据库信息存放在配置文件里面，请自行修改成正确的路径和值
        require($_SERVER['DOCUMENT_ROOT'] . '/configs/config.inc.php');
        $this->DBHost = & $DBHost;
        $this->DBUser = & $DBUser;
        $this->DBPW = & $DBPassword;
        $this->DBName = & $DBName;
        $this->SQL = $SQL;
        $this->Fetch = $Fetch;
        $this->Param = $Param;
    }
    
    public function Query()
    {
        try
        {
            $Pdo = new PDO('mysql:host=' . $this->DBHost . ';dbname=' . $this->DBName, $this->DBUser, $this->DBPW);
            $Pdo->query('SET NAMES UTF8');
            $Pdo->beginTransaction();
            $Stmt = $Pdo->prepare($this->SQL);
            if (count($this->Param) > 0)
            {
                for ($I = 0; $I < count($this->Param); $I++)
                {
                    $Stmt->bindParam($I + 1, $this->Param[$I]);
                }
            }
            if (!$Stmt->execute())
            {
                echo '读取数据库时发生错误：' . $Stmt->errorinfo()[2];
                $Pdo->rollback();
                return false;
            }
            $Pdo->commit();
            if (strtolower(substr($this->SQL, 0, 6)) == 'select' || strtolower(substr($this->SQL, 0, 4)) == 'desc')
            {
                $this->Res = $Stmt;
                return $this->GetRes();
            }
            else
            {
                return true;
            }
        }
        catch (PDOException $e)
        {
            echo '无法连接数据库';
            $Pdo->rollback();
            return false;
        }
    }
    
    public function GetRes()
    {
        switch(strtolower($this->Fetch))
        {
            case 'all':
                $Row = $this->Res->fetchAll(PDO::FETCH_ASSOC);
                break;
            case 'array':
                $Row = $this->Res->fetch(PDO::FETCH_BOTH);
                break;
            case 'assoc':
            case 'field':
                $Row = $this->Res->fetch(PDO::FETCH_ASSOC);
                break;
            case 'row':
                $Row = $this->Res->fetch(PDO::FETCH_NUM);
                break;
            default:
                echo 'Please select a row return mode.';
                exit;
        }
        return $Row;
    }
    
    public function NumRow()
    {
        if (isset($this->Res))
        {
            return $this->Res->num_rows;
        }
        else
        {
            return false;
        }
    }
}
?></code>

How to use:

<code>$DB = new DB(SQL语句, 结果集方式, array(要绑定的参数);
$DB->Query();</code>

Attention! ! ! SQL statements use ? to replace the parameters to be queried! ! ! SQL injection vulnerabilities are caused by splicing variables into SQL statements! ! !
PS: If you can’t change the framework, then just go to bed. Time will only eliminate people who stick to the rules and outdated technologies.
Don’t expect regular filtering SQL statements to completely eliminate SQL injection. The reason why PHP7 abandoned the MySQL extension is because this extension has security holes!

I have written a similar summary before, so I won’t copy it. Commonly used Web security prevention suggestions are included (I will continue to add some omissions later), you can refer to: PHP Security Coding

A simple understanding is that if you can use PDO for preprocessing, use PDO. If you can't use PDO, use addslashes on top.

PHP system function has this addslashes(), you can try it

If you don’t use regular filtering, you can consider escaping.

In fact, the essence of preventing SQL injection is to escape or intercept sensitive characters in GPC foreign variables. GPC can be preprocessed in the framework entry file or route analysis class and then used for subsequent business use. This is relatively easy to implement and easy to implement. As for cookies, make a big change and use some encryption methods to check cookies. The encryption key can be placed in the session.

Well, the posture of this thing should be like this:

1. Add some security services outside, such as Accelerator (we use Accelerator, the response is generally 100 milliseconds longer, there are occasional fluctuations, security is only basic, some posts and some XSS cannot be intercepted) )

2. The server is equipped with web application firewall software

3. Evaluation time, slowly migrate to pdo, and queries are bound with parameters (I personally think it is not that the change is not allowed, but that it takes time to arrange this)

Since PHP>=5.5 has abolished mysql_*, so for the sake of compatibility, we should take the time to reconstruct the framework.

Try www.oneasp.com