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 simply
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.
MySQL uses Access Control Lists (ACLs) security for all connections, queries, and other operations
that a user may attempt to perform. There is also some support for SSL-encrypted connections between
MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the
same general ideas apply to almost all applications.
When running MySQL, follow these guidelines whenever possible:
DON'T EVER GIVE ANYONE (EXCEPT THE MySQL ROOT USER) ACCESS TO THE mysql.user
TABLE! The encrypted password is the real password in MySQL. If you know this for one user you
can easily login as him if you have access to his 'host'.
Learn the MySQL access privilege system. The GRANT and REVOKE commands are used for
restricting 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. Any user (not just root) can connect to your MySQL server with full
privileges! Review the MySQL installation instructions, paying particular attention to the item about
setting a root password.
Use the command SHOW GRANTS and check to see who has access to what. Remove those privileges
that are not necessary using the REVOKE command.
Do not keep any plain-text passwords in your database. When your computer becomes compromised,
the intruder can take the full list of passwords and use them. Instead use MD5() or another one-way
hashing function.
Do not use 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 of each word in the sentence ``Mary had a little lamb.'' This is easy to
remember and type, but hard to guess for someone who does not know it.
Invest in a firewall. This protects 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 be inaccessible from untrusted hosts. Another simple way to check whether or not
your MySQL port is open is to type telnet server_host 3306 from some remote machine, where
server_host is the hostname of your MySQL server. 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, everything is OK, the port is blocked.
Do not trust any data entered by your users. 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 may occur as a result of hackers using
similar techniques, if you do not prepare for them. Also remember to check numeric data. A common
mistake is to protect only strings. Sometimes people think that if a database contains only publicly
available data that it need not be protected. This is incorrect. At least denial-of-service type attacks can
be performed on such databases. The simplest way to protect from this type of attack is to use
apostrophes around the numeric constants: SELECT * FROM table WHERE ID='234' instead of
SELECT * FROM table WHERE ID=234. MySQL automatically converts this string to a number and
strips all non-numeric symbols from it. Checklist:
All WWW applications:
Try to modify any dynamic URLs by adding %22 (`"'), %23 (`#'), and %27 (`'') in the URL.
Try to modify datatypes 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 instead of numbers in numeric fields. Your
application should remove them before passing them to MySQL or your application should 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 user name than the one you
use for administrative purposes. Do not give your applications any more access privileges than they
need.
Users of PHP:
Check out the addslashes() function.
Users of MySQL C API:
Check out the mysql_escape() API call.
Users of MySQL++:
Check out the escape and quote modifiers for query streams.
Users of Perl DBI:
Check out the quote() method.
Do not transmit plain (unencrypted) data over the Internet. These data are 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 3.23.9. 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 or not 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 data this doesn't always actually mean that it is encrypted. If you need high security, you
should consult with a security expert.