PHP security and vulnerabilities to prevent SQL injection

Keywords: PHP Javascript Front-end thinkphp Layui

What is SQL injection

  • The first is the security vulnerability in the database layer of the Web program, which is the simplest vulnerability in the website.
  • The main reason is that the program does not judge the legitimacy of the user input data, so that the attacker can add additional SQL statements to the pre-defined SQL statements in the Web application, so as to deceive the database server to execute SQL statements, which is an illegal operation.

SQL injection instance

For example, the login interface requires a user name and password.
You can enter this to realize account free login:

User name: 'or 1 = 1 –

Click login. If there is no special treatment, the illegal user will log in proudly.
Analyze the cause,
During theoretical login, there will be an authentication program in the background, as shown in the following sql statement:

String sql = "select * from user_table where username=
' "+userName+" ' and password=' "+password+" '";

When the above user name and password are entered, the above SQL statement becomes:

SELECT * FROM user_table WHERE username=
''or 1 = 1 – and password=''

Analyze SQL statements:
After the condition, username = "or 1=1, username equals" or 1=1, then the condition will succeed;
Then add two - which means annotation. It annotates the following statements so that they don't work. In this way, the statements can always be executed correctly. Users can easily deceive the system and obtain legal identity.
This is still relatively gentle. If it is implemented

SELECT * FROM user_table WHERE
username='' ;DROP DATABASE (DB Name) --' and password=''
... the consequences can be imagined


Method 1: open magic from the php.ini configuration file_ quotes_ gpc=on
  • In PHP 4.0 and above, this option is enabled by default.
  • Therefore, in PHP 4.0 and above, even if the parameters in the PHP program are not filtered, the PHP system will automatically convert each variable passed through GET, POST and COOKIE,
  • In other words, all the injected attack codes will be converted, which will bring great difficulties to the attacker.

Nevertheless, attackers still have the opportunity to carry out SQL injection attacks

   Example demonstration:

    Suppose we know that the user name of the administrator is admin,I don't know the password. And has magic_quotes_gpc Enabled.

    SQL sentence: sql="select∗fromuserswhereusername=sql="select∗fromuserswhereusername=name and password='pwd′";Note: Variables pwd′";Note: Variables name No quotation marks

    At this point, enter in the address field username=admin%23,After synthesis sql The statement is:

  select * from users where username='admin\' #' and password='';

  By this time url Single quotation mark entered in the address field(')Will be backslashed, the sql The statement will be invalidated.

  admin convert to ASCII After is char(97,100,109,105,110)

  Enter in the address field username=char(97,100,109,105,110)%23

  SQL The statement becomes:

  select * from users where username=char(97,100,109,105,110)#' and password='';

  If the execution result is true, you can enter the background smoothly.

  For digital injection attacks, it must be used before any digital parameters are put into the database intval()The parameters are forcibly converted to numbers, which can cut off the generation of digital injection vulnerabilities.

  For example: id=intval(id=intval(_GET['id']);

  select * from articles where id='$id';

  Enter in the address field: id=5' or 1=1%23

  SQL The statement becomes: select * from articles where id='5';

  instead of select * from articles where id='5' or 1=1#;
Method 2: cast type

We often use URL s like xxx.php?id=xxx. Generally speaking i d all yes whole type change amount , by Yes Guard against Model attack hit person hold IDs are all integer variables. In order to prevent attackers from IDs are all integer variables. In order to prevent attackers from tampering IDS into attack statements, we should try our best to force variables. The code is as follows:


Of course, there are other variable types. Try to force the format if necessary.

Method 3: input content verification

We can filter sensitive characters such as single quotation mark ', backslash \, character or, or escape some parameters, so as to destroy the constructed sql statement and achieve our goal.


// Enter user name and password authentication using whitelist
$whitepattern="/^[a-z\d]*$/i";         // The whitelist regular expression constructed only allows the input content to be a combination of strings and numbers
// Constructed blacklist regular expression
if(preg_match($blackpattern, $name)){ // preg_match: regular matching of strings using regular expressions
	die('illegal input! User name contains sensitive words!');
if(!preg_match($whitepattern, $passwd)){
	die('illegal input! Password contains sensitive words!');

You can write this method in the common file, and it is better to call it multiple times.

Posted by vund0 on Tue, 23 Nov 2021 03:10:06 -0800