5.3.1 Guide de sécurité

Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.

In discussing security, we emphasize the necessity of fully protecting the entire server host (not just the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.

When running MySQL, follow these guidelines whenever possible:

• Do not ever give anyone (except MySQL root accounts) access to the user table in the mysql database! This is critical. The encrypted password is the real password in MySQL. Anyone who knows the password that is listed in the user table and has access to the host listed for the account can easily log in as that user .
• Learn the MySQL access privilege system. The GRANT and REVOKE statements are used for controlling access to MySQL. Do not grant any more privileges than necessary. Never grant privileges to all hosts.Checklist:
  • Try mysql -u root . If you are able to connect successfully to the server without being asked for a password, you have problems. Anyone can connect to your MySQL server as the MySQL root user with full privileges! Review the MySQL installation instructions, paying particular attention to the information about setting a root password. Mise en place des privilèges initiaux de MySQL .
  • Use the SHOW GRANTS statement and check to see who has access to what. Then use the REVOKE statement to remove those privileges that are not necessary.
• Do not store any plain-text passwords in your database. If your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead, use MD5() , SHA1() , or some other one-way hashing function.
• Do not choose passwords from dictionaries. There are special programs to break them. Even passwords like ``xfish98'' are very bad. Much better is ``duag98'' which contains the same word ``fish'' but typed one key to the left on a standard QWERTY keyboard. Another method is to use ``Mhall'' which is taken from the first characters of each word in the sentence ``Mary had a little lamb.'' This is easy to remember and type, but difficult to guess for someone who does not know it.
• Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarized zone (DMZ).Checklist:
  • Try to scan your ports from the Internet using a tool such as nmap . MySQL uses port 3306 by default. This port should not be accessible from untrusted hosts. Another simple way to check whether or not your MySQL port is open is to try the following command from some remote machine, where server_host is the host on which your MySQL server runs:

    shell> telnet server_host 3306

    If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router, unless you really have a good reason to keep it open. If telnet just hangs or the connection is refused, everything is OK; the port is blocked.
• Do not trust any data entered by users of your applications. They can try to trick your code by entering special or escaped character sequences in Web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user enters something like `` ; DROP DATABASE mysql; ''. This is an extreme example, but large security leaks and data loss might occur as a result of hackers using similar techniques, if you do not prepare for them.

  A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234 , the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1 . As a result, the server retrieves every record in the table. This exposes every record and causes excessive server load. The simplest way to protect from this type of attack is to use apostrophes around the numeric constants: SELECT * FROM table WHERE ID='234' . If the user enters extra information, it all becomes part of the string. In numeric context, MySQL automatically converts this string to a number and strips any trailing non-numeric characters from it.

  Sometimes people think that if a database contains only publicly available data, it need not be protected. This is incorrect. Even if it is allowable to display any record in the database, you should still protect against denial of service attacks (for example, those that are based on the technique in the preceding paragraph that causes the server to waste resources). Otherwise, your server becomes unresponsive to legitimate users.Checklist:
  • Try to enter ''' and '"' in all your Web forms. If you get any kind of MySQL error, investigate the problem right away.
  • Try to modify any dynamic URLs by adding %22 ( '"' ), %23 ( '#' ), and %27 ( ''' ) in the URL.
  • Try to modify data types in dynamic URLs from numeric ones to character ones containing characters from previous examples. Your application should be safe against this and similar attacks.
  • Try to enter characters, spaces, and special symbols rather than numbers in numeric fields. Your application should remove them before passing them to MySQL or else generate an error. Passing unchecked values to MySQL is very dangerous!
  • Check data sizes before passing them to MySQL.
  • Consider having your application connect to the database using a different username than the one you use for administrative purposes. Do not give your applications any access privileges they do not need.
• Many application programming interfaces provide a means of escaping special characters in data values. Properly used, this prevents application users from entering values that cause the application to generate statements that have a different effect than you intend:
  • MySQL C API: Use the mysql_real_escape_string() API call.
  • MySQL++: Use the escape and quote modifiers for query streams.
  • PHP: Use the mysql_escape_string() function, which is based on the function of the same name in the MySQL C API. Prior to PHP 4.0.3, use addslashes() instead.
  • Perl DBI: Use the quote() method or use placeholders.
  • Java JDBC: Use a PreparedStatement object and placeholders.
  Other programming interfaces might have similar capabilities.
• Do not transmit plain (unencrypted) data over the Internet. This information is accessible to everyone who has the time and ability to intercept it and use it for their own purposes. Instead, use an encrypted protocol such as SSL or SSH. MySQL supports internal SSL connections as of Version 4.0.0. SSH port-forwarding can be used to create an encrypted (and compressed) tunnel for the communication.
• Learn to use the tcpdump and strings utilities. For most cases, you can check whether MySQL data streams are unencrypted by issuing a command like the following:

  shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings

  (This works under Linux and should work with small modifications under other systems.) Warning: If you do not see plaintext data, this doesn't always mean that the information actually is encrypted. If you need high security, you should consult with a security expert.