2. 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.
- 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.
- 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.
3. 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).
- 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 address 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 quotation marks 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.