How to prevent sql injection in mysql

Mysql method to prevent sql injection: 1. The permissions of ordinary users and system administrator users must be strictly separated; 2. Force users to use parameterized statements; 3. Try to use the security parameters that come with the SQL Server database. ; 4. Verify the content entered by the user.

SQL Injection attacks are very harmful. Attackers can use it to read, modify or delete data in the database, and obtain user names and passwords in the database. and other sensitive information, and can even obtain the permissions of the database administrator. If you can reuse SQL Server extended stored procedures and custom extended stored procedures to execute some system commands, the attacker can also gain control of the system.

(Recommended tutorial: mysql video tutorial)

And SQL Injection is also difficult to prevent. Website administrators cannot protect themselves by installing system patches or performing simple security configurations, and general firewalls cannot block SQL Injection attacks.

How does mysql prevent sql injection?

1. The permissions of ordinary users and system administrator users must be strictly distinguished.

If an ordinary user embeds another Drop Table statement in a query statement, is it allowed to be executed?

Since the Drop statement is related to the basic objects of the database, it must be operated The user of this statement must have relevant permissions. In the permission design, for end users, that is, users of application software, there is no need to give them permissions to create and delete database objects.

So even if there is embedded malicious code in the SQL statements they use, these codes will not be executed due to the restrictions on their user permissions.

2. Force the use of parameterized statements.

If when writing a SQL statement, the variables entered by the user are not directly embedded in the SQL statement. If you pass this variable through parameters, you can effectively prevent SQL injection attacks.

In other words, user input must not be directly embedded in SQL statements. In contrast, user input must be filtered, or parameterized statements must be used to pass user input variables. Parameterized statements use parameters instead of embedding user input variables into the SQL statement. Using this measure,

can eliminate most SQL injection attacks. Unfortunately, there are not many database engines that support parameterized statements. However, database engineers should try to use parameterized statements when developing products.

3. Use the security parameters that come with the SQL Server database.

In order to reduce the adverse effects of injection attacks on the SQL Server database, relatively safe SQL parameters are specially designed in the SQLServer database. During the database design process, engineers should try to use these parameters to prevent malicious SQL injection attacks.

For example, the Parameters collection is provided in the SQL Server database. This collection provides type checking and length validation capabilities. If the administrator uses the Parameters collection, user input will be treated as character values ​​rather than executable code. Even if the user input contains executable code, the database will filter it out. Because at this time the database only treats it as an ordinary character. Another advantage of using the Parameters collection is that type and length checks can be enforced, and values ​​outside the range will trigger an exception.

If the value entered by the user does not comply with the specified type and length constraints, an exception will occur and be reported to the administrator. For example, in the above case, if the data type defined by the employee number is string type, the length is 10 characters. Although the content entered by the user is also character type data, its length reaches 20 characters. An exception will be thrown at this time because the length of the user input exceeds the database field length limit.

4. Strengthen the verification of user input.

Generally speaking, two methods can be used to prevent SQL injection attacks.

The first is to strengthen the inspection and verification of user input content; the second is to force the use of parameterized statements to Pass user input.

In the SQLServer database, there are many user input content verification tools that can help administrators deal with SQL injection attacks. Test the contents of a string variable and only accept the required value. Reject input containing binary data, escape sequences, and comment characters. This helps prevent script injection and prevents certain buffer overflow attacks. Test the size and data type of user input and enforce appropriate limits and conversions. This helps prevent intentional buffer overflows and has a significant effect on preventing injection attacks.

For example, you can use stored procedures to verify user input. You can use stored procedures to filter user input variables, such as rejecting some special symbols. For example, in the above malicious code, as long as the stored procedure filters out the semicolon, then the malicious code will be useless.

Before executing the SQL statement, you can use the database's stored procedure to reject some special symbols. Without affecting the database application, the database should be allowed to reject input containing the following characters. Such as the semicolon delimiter, which is the main accomplice of SQL injection attacks. Such as comment separator. Annotations are only used during data design. Generally, there is no necessary comment content in the user's query statement, so he can be rejected directly. Under normal circumstances, this will not cause unexpected losses. If these special symbols are rejected, even if malicious code is embedded in the SQL statement, they will do nothing.

Therefore, always verify user input and filter user input by testing type, length, format, and range. This is a common and effective measure to prevent SQL injection attacks.

How to prevent SQL injection attacks in multi-layer environments?

In a multi-tier application environment, all data entered by users should be verified before being allowed to enter the trusted area.

Data that fails the verification process should be rejected by the database and return an error message to the upper layer. Implement multi-layer verification. Precautions taken against purposeless malicious users may not be effective against determined attackers.

A better approach is to validate input at the user interface and at all subsequent points across trust boundaries. For example, validating data in the client application can prevent simple script injection.

However, if the next layer thinks its input has been validated, any malicious user who can bypass the client can have unrestricted access to the system. Therefore, for multi-layer application environments, when preventing injection attacks, all layers need to work together, and corresponding measures must be taken on the client and database sides to prevent SQL statement injection attacks.

The above is the detailed content of How to prevent sql injection in mysql. For more information, please follow other related articles on the PHP Chinese website!