• notice
  • Congratulations on the launch of the Sought Tech site

MySQL database configuration skills

Starting a remote service with the root user has always been a security no-no, because if there is a problem with the service program, a remote attacker is very likely to gain full control of the host.MySQL has made small changes since version 3.23.15.After installation by default, the service should be accessed by the user mysql Start, root user is not allowed to start.If you have to use the root user to start, you must add the--user=root parameter (./safe_mysql d--user=root &).Because there are SQL statements of LOAD DATA INFILE and SELECT...INTO OUTFILE in MySQL, if the root user starts the MySQL server, then the database user has the write permission of the root user.However, MySQL still has some restrictions.For example, LOAD DATA INFILE can only read files that are globally readable, and SELECT...INTO OUTFILE cannot overwrite existing files.

Local log files cannot be ignored either, including shell logs and MySQL's own logs.When some users log in locally or back up the database for convenience, sometimes they directly bring the database password in the command line parameters, such as:

shell>/usr/local/mysql/bin/mysqldump-uroot-ptest test>test.sql
shell>/usr/local/mysql/bin/mysql-uroot-ptest

These commands will be recorded in the history file by the shell.For example, bash will write the.bash_history file in the user directory.If these files are accidentally read, the password of the database will be leaked.The SQL commands executed after the user logs in to the database will also be recorded by MySQL in the.mysql_history file in the user directory.If the database user modifies the database password with SQL statements, it will also be leaked due to the.mysql_history file.So we do not directly add the password after-p when logging in and backing up the shell, but enter the database password after the prompt.

The other two files we should also not let it record our operations, just in case.

shell>rm.bash_history.mysql_history
shell>ln-s/dev/null.bash_history
shell>ln-s/dev/null.mysql_history

The two commands on the door link these two files to/dev/null, so our operations will not be recorded in these two files.Some issues that need to be paid attention to in programming: No matter what programming language is used to write a program to connect to a MySQL database, there is a rule that never trust the data submitted by the user!

For numeric fields, we need to use a query statement: SELECT * FROM table WHERE ID='234', do not use a query statement such as SELECT * FROM table WHERE ID=234.MySQL automatically converts strings to numeric characters and removes non-numeric characters.If the data submitted by the user is processed by mysql_escape_string, we can completely eliminate the sql inject attack.For the sql inject attack, please refer to the article linked below:

http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
http://www.ngssoftware.com/papers/advanced_sql_injection.pdf

Problems that should be paid attention to in various programming languages:

1) All web programs:

a) Try entering single and double quotes in the web form to test for possible errors and find out why.

b) Modify URL parameters with %22 ('"'), %23 ('#'), and %27 (''').

c) For variables of numeric fields, our application must perform strict checks, otherwise it is very dangerous.

d) Check whether the data submitted by the user exceeds the length of the field.

e) Do not give too many access rights to users whose programs connect to the database.

2)PHP:

a) Check whether the data submitted by the user has been processed by addslashes before querying, after PHP 4.0.3 Provides the function mysql_escape_string() based on the MySQL C API.

3)MySQL C API:

a) Check if the query string is called using the mysql_escape_string() API.

4)MySQL++:

a) Check whether the query string is treated with escape and quote.

5) Perl DBI:

a) Check if the query string uses the quote() method.

6)Java JDBC:

a) Check whether the query string uses a PreparedStatement object.


Some tips

1) If you accidentally forget the root password of MySQL, we can add the parameter--skip-grant-tables when starting the MySQL server to skip the verification of the authorization table (./safe_mysqld--skip-grant-tables &), so that we can directly log in to the MySQL server, then modify the password of the root user, restart MySQL, and log in with the new password.

2) Add--skip-show-database when starting the MySQL server so that general database users cannot browse other databases.

3) Add the--chroot=path parameter when starting the MySQL server, so that the mysqld daemon runs in the chroot environment.In this way, the SQL statements LOAD DATA INFILE and SELECT...INTO OUTFILE are limited to reading and writing files under chroot_path.One thing to note here is that after MySQL starts, a mysql.sock file will be created, which is in the/tmp directory by default.After using chroot, MySQL will create the mysql.sock file in chroot_path/tmp.If there is no chroot_path/tmp directory or the user who starts MySQL does not have write permission to this directory, the mysql.sock file cannot be created, and MySQL will fail to start.For example, if we add the--chroot=/usr/local/mysql/startup parameter, then it is best to create a/usr/local/mysql/tmp directory that the user who starts MySQL can write.Of course, we can also use--socket=path To specify the path of the mysql.sock file, but this path must be in the chroot_path.

4) Add the--log-slow-queries[=file] parameter when starting the MySQL server, so that mysqld will write the SQL command execution time longer than long_query_time to the file file.If=file is not specified, mysqld will write to hostname-slow.log in the data directory by default.If only filename is specified and no path is specified, then mysqld will also write filename to the data directory.Through this log file, we can find out the query statement that takes too long to execute, and then optimize it as much as possible to reduce the burden on the MySQL server.

5) If we only need to use the MySQL service locally, then we can also add the--skip-networking startup parameter so that MySQL does not monitor any TCP/IP connections, increasing security

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+