How to prevent SQL injection in php

【1. Configuration on the server side】

Security, PHP code writing is one aspect, and PHP configuration is very critical.

We installed PHP manually. The default configuration file of PHP is in /usr/local/apache2/conf/php.ini. Our most important thing is to configure the content in php.ini so that we can execute PHP more safely. The security settings in the entire PHP are mainly to prevent attacks from phpshell and SQL Injection. Let’s discuss it slowly. We first use any editing tool to open /etc/local/apache2/conf/php.ini. If you install it in other ways, the configuration file may not be in this directory.

(1) Turn on php’s safe mode

php’s safe mode is a very important built-in security mechanism that can control some functions in php, such as system(),

at the same time grant permissions to many file operation functions Control, and do not allow certain key files, such as /etc/passwd,

But the default php.ini does not open safe mode, we turn it on:

safe_mode = on

(2) User Group security

When safe_mode is turned on and safe_mode_gid is turned off, the php script can access the file, and users in the same

group can also access the file.

It is recommended to set it to:

safe_mode_gid = off

If it is not set, we may not be able to operate the files in the directory of our server website, for example, when we need to

operate files.

(3) Home directory for executing programs in safe mode

If safe mode is turned on, but you want to execute certain programs, you can specify the home directory for executing programs:

safe_mode_exec_dir = D:/usr/bin

Generally, there is no need to execute any program, so it is recommended not to execute the system program directory. You can point to a directory,

Then copy the program that needs to be executed, for example:

safe_mode_exec_dir = D:/tmp/cmd

However, I recommend not to execute any program, then you can point to our web page directory:

safe_mode_exec_dir = D:/usr/www

(4) Include files in safe mode

If you want to include certain files in safe mode Public files, then modify the options:

safe_mode_include_dir = D:/usr/www/include/

In fact, generally the files included in php scripts have been written in the program itself. This can be set according to specific needs.

(5) Control the directories that php scripts can access

Use the open_basedir option to control PHP scripts to only access specified directories. This can prevent PHP scripts from accessing files that

should not access, and limits the harm of phpshell to a certain extent. , we can generally set it to only access the website directory:

open_basedir = D:/usr/www

(6) Turn off dangerous functions

If the safe mode is turned on, then function ban is not necessary, but for our Safety should be considered. For example,

we feel that we do not want to execute php functions including system() that can execute commands, or

phpinfo() and other functions that can view php information, then we can disable them:

disable_functions = system ,passthru,exec,shell_exec,popen,phpinfo

If you want to prohibit any file and directory operations, you can turn off many file operations

disable_functions = chdir,chroot,dir,getcwd,opendir,readdir,scandir,fopen,unlink ,delete,copy,mkdir, rmdir,rename,file,file_get_contents,fputs,fwrite,chgrp,chmod,chown

The above are just some of the commonly used file processing functions. You can also combine the above execution command function with this function Combined,

can resist most phpshells.

(7) Turn off the leakage of PHP version information in the http header

In order to prevent hackers from obtaining the PHP version information in the server, we can turn off the leakage of the information in the http header:

expose_php = Off

For example, hackers use telnet www.12345.com 80, then you will not be able to see the PHP information.

(8) Turn off registration of global variables

Variables submitted in PHP, including variables submitted using POST or GET, will be automatically registered as global variables and can be accessed directly.

This is very unsafe for the server. So we can't let it be registered as a global variable, so we turn off the register global variable option:

register_globals = Off

Of course, if it is set like this, then we must use a reasonable method when getting the corresponding variables, such as getting the variables submitted by GET var,

Then you must use $_GET['var'] to obtain it. PHP programmers should pay attention to this.

(9) Turn on magic_quotes_gpc to prevent SQL injection

SQL injection is a very dangerous problem. It can cause the website backend to be invaded, or the entire server to fall.

So be careful. There is a setting in php.ini:

magic_quotes_gpc = Off

This is off by default. If it is turned on, it will automatically convert user-submitted sql queries,

for example, convert ' to ', etc., which will prevent SQL injection plays a major role. So we recommend setting it to:

magic_quotes_gpc = On

(10) Error message control

Generally, PHP will prompt an error when it is not connected to the database or under other circumstances. Generally, the error message will contain the current path information of the PHP script or the SQL statement of the query. This kind of information is not safe if it is provided to hackers. , so it is generally recommended that servers disable error prompts:

display_errors = Off

If you want to display error messages, be sure to set the level of display errors, such as only displaying information above warnings:

error_reporting = E_WARNING & E_ERROR

Of course, I still recommend turning off error prompts.

(11) Error log

It is recommended to record the error information after turning off display_errors to facilitate finding the reason for the server operation:

log_errors = On

At the same time, the directory where the error log is stored should also be set. It is recommended to root the apache log. Exists together:

error_log = D:/usr/local/apache2/logs/php_error.log

Note: The file must allow the apache user and group to have write permissions.

MYSQL running with reduced privileges

Create a new user such as mysqlstart

net user mysqlstart fuckmicrosoft /add

net localgroup users mysqlstart /del

does not belong to any group

If MYSQL is installed in d:mysql, then, give mysqlstart Full control permission

Then set the MYSQL service properties in the system service. In the login properties, select this user mysqlstart and enter the password and confirm.

Restart the MYSQL service, and then MYSQL will run with low privileges.

If the apache is built on the windos platform, we need to pay attention to one thing. By default, apache runs with system permissions.

This is scary and makes people feel very uncomfortable. Then let’s lower the permissions of apache.

net user apache fuckmicrosoft /add

net localgroup users apache /del

ok. We created a user apche that does not belong to any group.

We open the computer manager, select services, click on the properties of the apache service, we select log on, select this account, we fill in the account and password created above,

Restart the apache service, ok, apache runs with low privileges Got off.

In fact, we can also set the permissions of each folder so that the apache user can only perform what we want it to do, and create a separate read-write user for each directory.

This is also a popular configuration method used by many virtual host providers. However, this method is overkill when used to prevent this.

【2. Writing in PHP Code】

Although many domestic PHP programmers still rely on addslashes to prevent SQL injection, it is still recommended that everyone strengthen checks to prevent SQL injection in Chinese. The problem with addslashes is that hackers can use 0xbf27 to replace single quotes, while addslashes only changes 0xbf27 to 0xbf5c27, which becomes a valid multi-byte character. 0xbf5c is still regarded as a single quote, so addslashes cannot successfully intercept.

         Of course, addslashes is not useless, it is used for processing single-byte strings. For multi-byte characters, use mysql_real_escape_string.

        In addition, for the example of get_magic_quotes_gpc in the php manual:
if (!get_magic_quotes_gpc()) {
$lastname = addslashes($_POST['lastname']);
} else {
$lastname = $_POST['lastname'];
}

It is best to check $_POST['lastname'] when magic_quotes_gpc is already open.

Let’s talk about the difference between the two functions mysql_real_escape_string and mysql_escape_string:

mysql_real_escape_string can only be used when (PHP 4 >= 4.3.0, PHP 5). Otherwise, you can only use mysql_escape_string. The difference between the two is: mysql_real_escape_string takes into account the current character set of the connection, while mysql_escape_string does not.
To summarize:
* addslashes() is a forced addition;
* mysql_real_escape_string() will determine the character set, but there are requirements for the PHP version;
* mysql_escape_string does not consider the current character set of the connection.
------------------------------------------------- ----------------------------------------
In When coding in PHP, if you consider some basic security issues, first of all:
1. Initialize your variables
Why do you say this? Let’s look at the following code:
PHP code

 <?php     
    if ($admin)     
    {     
    echo &#39;登陆成功！&#39;;     
    include(&#39;admin.php&#39;);     
    }     
    else     
    {     
    echo &#39;你不是管理员，无法进行管理！&#39;;     
    }     
    ?>

Okay, let’s see that the above code seems to be running normally and there is no problem. Then let me submit an illegal parameter to it. What will be the effect? For example, our page is http://daybook.diandian.com/login.php, then we submit: http://daybook.diandian.com/login.php?admin=1, haha, think about it, we are Either you are an administrator directly, you manage it directly.
Of course, maybe we won’t make such a simple mistake, and some very secret mistakes may also cause this problem. For example, there is a loophole in the phpwind forum, which allows us to directly obtain administrator privileges because there is no $skin variable. Initialization leads to a series of problems later. So how do we avoid the above problems? First, start with php.ini and set register_global =off in php.ini, which means that not all registered variables are global, so this can be avoided. However, we are not server administrators and can only improve it from the code. So how do we improve the above code? We rewrite it as follows:
PHP code

 <?php     
    $admin = 0; // 初始化变量     
    if ($_POST[&#39;admin_user&#39;] && $_POST[&#39;admin_pass&#39;])     
    {     
    // 判断提交的管理员用户名和密码是不是对的相应的处理代码     
    // ...     
    $admin = 1;     
    }     
    else     
    {     
    $admin = 0;     
    }     
    if ($admin)     
    {     
    echo &#39;登陆成功！&#39;;     
    include(&#39;admin.php&#39;);     
    }     
    else     
    {     
    echo &#39;你不是管理员，无法进行管理！&#39;;     
    }     
    ?>

Then it won’t work if you submit http://daybook.diandian.com/login.php?admin=1 at this time, because we initialized the variable to $ at the beginning admin = 0, then you cannot obtain administrator privileges through this vulnerability.
2. Prevent SQL Injection (sql injection)
SQL injection should be the most harmful program at present, including the earliest from asp to php, which are basically popular technologies in the country in the past two years. The basic principle is to pass through the inaccuracy of submitted variables. Filtering forms an injection point and then allows malicious users to submit some SQL query statements, resulting in important data being stolen, data lost or damaged, or being invaded into the backend management.
So now that we understand the basic methods of injection invasion, how can we prevent it? We should start with the code.
We know that there are two ways to submit data on the Web, one is get and the other is post, so many common sql injections start from the get method, and the injection statements must contain some sql statements, because there is no SQL statement, then how to proceed? There are four major sentences in SQL statement: select, update, delete, and insert. So if we filter the data we submit, can we avoid these problems?
So we use regular expressions to build the following function:
PHP code

<?php          
    function inject_check($sql_str)     
    {     
    return eregi(&#39;select|insert|update|delete|&#39;|     
    function verify_id($id=null)     
    {     
    if (!$id) { exit(&#39;没有提交参数！&#39;); } // 是否为空判断     
    elseif (inject_check($id)) { exit(&#39;提交的参数非法！&#39;); } // 注射判断     
    elseif (!is_numeric($id)) { exit(&#39;提交的参数非法！&#39;); } // 数字判断     
    $id = intval($id); // 整型化         
    return $id;     
    }     
    ?>
     呵呵，那么我们就能够进行校验了，于是我们上面的程序代码就变成了下面的：
PHP代码     
    <?php     
    if (inject_check($_GET[&#39;id&#39;]))     
    {     
    exit(&#39;你提交的数据非法，请检查后重新提交！&#39;);     
    }     
    else     
    {     
    $id = verify_id($_GET[&#39;id&#39;]); // 这里引用了我们的过滤函数，对$id进行过滤     
    echo &#39;提交的数据合法，请继续！&#39;;     
    }     
    ?>

Okay, the problem seems to be solved here, but have we considered the data submitted by post and the large batch of data?
For example, some characters may cause harm to the database, such as '_', '%'. These characters have special meanings, so what if we control them? Another point is that when magic_quotes_gpc = off in our php.ini, the submitted data that does not comply with the database rules will not automatically add ' ' in front. Then we need to control these problems, so we build it as follows Function:
PHP code

 <?php        
    function str_check( $str )     
    {     
    if (!get_magic_quotes_gpc()) // 判断magic_quotes_gpc是否打开     
    {     
    $str = addslashes($str); // 进行过滤     
    }     
    $str = str_replace("_", "\_", $str); // 把 &#39;_&#39;过滤掉     
    $str = str_replace("%", "\%", $str); // 把&#39; % &#39;过滤掉     
         
    return $str;     
    }     
    ?>

We once again avoided the danger of the server being compromised.
Finally, consider submitting some large batches of data, such as posting, or writing articles or news. We need some functions to help us filter and convert. Based on the above functions, we build the following functions:
PHP code

    <?php      
    function post_check($post)     
    {     
    if (!get_magic_quotes_gpc()) // 判断magic_quotes_gpc是否为打开     
    {     
    $post = addslashes($post); // 进行magic_quotes_gpc没有打开的情况对提交数据的过滤     
    }     
    $post = str_replace("_", "\_", $post); // 把 &#39;_&#39;过滤掉     
    $post = str_replace("%", "\%", $post); // 把&#39; % &#39;过滤掉     
    $post = nl2br($post); // 回车转换     
    $post= htmlspecialchars($post); // html标记转换        
    return $post;     
    }     
    ?>

Haha, basically at this point, we have talked about some situations. In fact, I feel that I have talked about very little. At least I have only talked about two aspects, and there is very little content in the whole security. Think about talking more next time, including PHP security configuration, Apache security, etc., so that our security can be integrated as a whole and be the safest.
 Finally, let me tell you what is expressed above: 1. Initialize your variables 2. Remember to filter your variables