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.
MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also 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:
Do not ever give anyone (except MySQL
root
accounts) access to the
user
table in the mysql
database! This is critical.
Learn the MySQL access privilege system. The
GRANT
and
REVOKE
statements are used for
controlling access to MySQL. Do not grant 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, 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. See
Section 2.11.3, “Securing the Initial MySQL Accounts”.
Use the SHOW GRANTS
statement to check which accounts have 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 and store the hash value.
Do not choose passwords from dictionaries. Special programs exist to break passwords. 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 a password that is taken from the first characters of each word in a sentence (for example, “Mary had a little lamb” results in a password of “Mhall”). The password is easy to remember and type, but difficult to guess for someone who does not know the sentence.
MySQL Enterprise. MySQL Enterprise subscribers can find an example of a function that checks password security in the Knowledge Base article, Checking Password Complexity. To subscribe to MySQL Enterprise see http://www.mysql.com/products/enterprise/advisors.html.
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 name or
IP number of 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 hangs or the connection is refused, the port is blocked, which is how you want it to be.
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 row
in the table. This exposes every row and causes excessive
server load. The simplest way to protect from this type of
attack is to use single quotes around the numeric constants:
SELECT * FROM table WHERE ID='234'
. If the
user enters extra information, it all becomes part of the
string. In a numeric context, MySQL automatically converts
this string to a number and strips any trailing nonnumeric
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 row 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 single and double quote marks
(“'
” and
“"
”) in all of your Web
forms. If you get any kind of MySQL error, investigate the
problem right away.
Try to modify dynamic URLs by adding
%22
(“"
”),
%23
(“#
”), and
%27
(“'
”) to them.
Try to modify data types in dynamic URLs from numeric to character types using the characters shown in the previous examples. Your application should be safe against these 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 the size of data before passing it to MySQL.
Have your application connect to the database using a user name different from 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_real_escape_string()
function
(available as of PHP 4.3.0, prior to that PHP version use
mysql_escape_string()
, and prior to
PHP 4.0.3, use addslashes()
). Note
that only mysql_real_escape_string()
is character set-aware; the other functions can be
“bypassed” when using (invalid) multi-byte
character sets. In PHP 5, you can use the
mysqli
extension, which supports the
improved MySQL authentication protocol and passwords, as
well as prepared statements with placeholders.
Perl DBI: Use placeholders or the
quote()
method.
Ruby DBI: Use placeholders or the
quote()
method.
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. Another technique is to use SSH port-forwarding to create an encrypted (and compressed) tunnel for the communication.
Learn to use the tcpdump and strings utilities. In 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.
If you do not see plaintext data, this does not always mean that the information actually is encrypted. If you need high security, you should consult with a security expert.
User Comments
Add your own comment.