Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Access control

The security foundation of MySQL servers is that users should have adequate access to the data they need, no more or no less. In other words, users cannot have too much access to too much data.

Consider the following:

  • Most users only need to read and write tables, but a few users even need to be able to create and delete tables.
  • Some users may need to read tables, but may not need to update tables;
  • You might want to allow users to add data, but not delete it;
  • Some users (administrators) may need access to user accounts, but most do not;
  • You might want to give users access to data through stored procedures, but not directly;
  • You may want to limit access to certain features based on where the user is logged in.

These are just examples, but they help illustrate the important fact that you need to give users the access they need, and only the access they need. This is called access control, and administrative access control requires the creation and management of user accounts.

MySQL- Admin user

To perform database operations, you need to log in to MySQL. MySQL creates a user account named root, which has full control over the MySQL server. You’ve probably logged in with root in your studies, which is great when you’re experimenting with MySQL against a non-real database. However, root should never be used in real-world day-to-day work. You should create a series of accounts, some for administration, some for users, some for developers, and so on.

-- The root user is the super administrator of mysql and has the highest permission
Create a user and assign permissions to it
Create zhangsan user 234567 log in to mysql
create user zhangsan identified by '234567';

Assign select permission to zhangsan for all tables in itXDL
grant select on itxdl.* to zhangsan;

-- Check user permissions
show grants for zhangsan;

-- Deletes the specified permission of the user
revoke delete on itxdl.stu from zhangsan;

-- Short form
grant select on itxdl.* to lisi@The '%' identified by '111';
Copy the code

SQL injection

The so-called SQL injection is to trick the server into executing malicious SQL commands by inserting SQL commands into Web form submission or query string for entering domain names or page requests. Specifically, it takes advantage of an existing application’s ability to inject (malicious) SQL commands into the back-end database engine to execute them. It can get a database on a vulnerable website by typing (malicious) SQL statements into a Web form, rather than executing the SQL statements intended by the designer.

protective

To sum up, there are mainly the following points:

1. Never trust user input. Verify user input, either through regular expressions or by limiting the length;

2. Never use dynamic assembled SQL, use parameterized SQL or directly use stored procedures for data query access.

3. Never use database connections with administrator privileges. Use separate database connections with limited privileges for each application.

4. Don’t store confidential information directly, encrypt or hash passwords and sensitive information.

5. The exception information of the application should provide as little information as possible. It is better to use custom error information to wrap the original error information

6. The best way to preprocess SQL is to separate SQL from data parameters

1. Use % S or %(key)s placeholder 2 in the prepared SQL statement. Execute SQL and parameters (list, tuple, dictionary) using execute method.Copy the code

Finally, welcome to pay attention to my personal wechat public account “Little Ape Ruochen”, get more IT technology, dry goods knowledge, hot news