|
 |
|
| |
5.6.1. General Security Guidelines
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. 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 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.9.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.
-
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 hostname
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 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 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
username 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_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.) 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 the quote() method or
use placeholders.
Ruby DBI: 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. 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.) 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.
|
|
|
