|
| Monday, March 26, 2007 |
| MySQL Security Overview |
When should you start to worry about MySQL security? When you start to use the MySQL server over an Internet connection. Why? Because that is when your MySQL server is going to be the most vulnerable to all kinds of attacks, such as alterations and denial of service.
The security issues that we will be discussing here will relate only to the MYSQL Server, but must be taken in the context of securing the entire server host against the above mentioned attacks and others.
How does MYSQL Security Work?: MYSQL security is based on the Access Control List (or ACL) for all connections, queries and many other operations that a user can perform. What does this mean? Well, it basically means that different users will have varying levels of access to certain databases and tables and that they will be limited to performing only certain operations. For example, a user with full privileges to a database will be able to perform a range of operations such as SELECT, DELETE, UPDATE and INSERT, while a user with limited privileges would only be able to use the SELECT operation and so on.
General Guidelines for MYSQL Security: On the machine running MYSQL, go into the bin directory where mysql is installed. For example if the installation directory is "c:mysql" then go into the "c:mysqlbin" directory and type the following:
Mysql -u root
Now press enter. If you get a "Welcome to MYSQL server " message, then it means that you have successfully connected to the server WITHOUT being asked for a password. This means that anyone can access your MYSQL server as the "root" user. This status gives this user complete control over your MYSQL server. Now, I don't have to tell you what it would mean if a hacker should gain this kind of access when you are hosting databases for companies. It is THE ultimate nightmare situation for a Database Administrator.
So to avoid this scenario:
- Check your MYSQL installation manual for setting a password for your root account. It is absolutely critical that you do this if you are going to use MYSQL over the Internet.
- Make sure that you understand the MYSQL access privilege system. Use the GRANT and REVOKE statements to control access to your MYSQL server.
- Use the SHOW GRANT statement to check who has access to what. And use the REVOKE statement to remove those privileges that you deem not necessary.
Passwords: When storing passwords in your database, take care not to store your passwords in plain text. If your system becomes compromised, the intruder will have a field day with your list of passwords. Encrypt them with MD5() or SHA1() or any other one way hash function, to make it hard for any intruder to get any passwords they can do anything with.
Also, make sure that you combine numbers and letters when generating a password. There are sophisticated password crackers being developed as we speak and although any password that you create can eventually be broken, you can make it difficult for intruders to do so by just making this small effort. Another method of creating memorable but difficult passwords is to think of a sentence such as "Mary had a little lamb" and take the first letter of each word in the sentence. This will give you something like this: "mhall." You can then add a memorable number to the word, and to make this even more difficult you can capitalize some of the letters. Arrange the numbers around the letters, so that it is easy to remember, but difficult to guess for someone who does not know the password.
Firewalls: You are probably sick of hearing this, but proper firewall protection is absolutely essential if you have an Internet connection. It will help keep a significant number of exploits and other attacks off your system and therefore away from your MySQL installation. So if at all possible try to install a firewall and make sure to put MySQL behind it.
Also most attacks are made possible by a port being open. A firewall blocks this kind of access, if configured to do so. After installing a firewall you can test whether the port at which MySQL listens is open or closed. You can do this with a program like Telnet. Just type:
telnet mysql 3306
If you get a reply with garbage, then the port is open and should be closed. If you do not get any reply, then the port is closed, which is what we want.
Data transmission: Do not transmit plain text (basically unencrypted) data over the Internet, because anyone who has the time and know-how will be able to intercept your communications and use them for their own purposes. A protocol such as SSL or SSH would go a long way to secure your communication over the Internet. As of version 4.0.0 MySQL supports internal SSL connections. SSH port-forwarding can be used to create an encrypted (and compressed) tunnel for the communication.
To check whether your MYSQL data streams are encrypted, use a utility like windump or tcpdump (for UNIX). Both are available at http://www.winpcap.org/ Once you have installed all the relevant DLLs and drivers, simply execute windump.exe and you should be able to see all network communications. Another good network traffic analyzer is ethereal (available at www.ethereal.com), which you can also use to see whether or not your MYSQL server data stream is encrypted.
Web Form Data: It is advisable to never trust data entered by users in your applications. Just recently I had the unfortunate experience of dealing with a security breach that was caused by a user entering "DROP DATABASE mysql;" into a form. Now, the script that was written to handle this form data was not written with security in mind, so of course the intruder succeeded.
This is but one example of why you should not trust user input to be safe. Intruders can use many other ways to trick your code into revealing more about your database. A common mistake that we programmers make is to protect only the string data when processing form data. While it is good to do this, it would be even better to check numeric or integer data as well. PHP has an is_numeric() function that you can use to make sure that numeric data is used properly. Other programming languages will have different functions to verify data. For example, if your application uses a query like
SELECT * FROM tblname WHERE ID=456
where the number 456 is entered by the user in a form, then the server will just go and retrieve that record. But a intruder can very easily fool your code by entering:
"456 OR 1=1"
This will change your query to:
SELECT * FROM tblname WHERE ID=456 OR 1=1
This will cause your query to retrieve all the records in your table and expose data that should not be exposed. In addition this kind of security breach can also cause excessive server load, making your server unresponsive or slow. The solution to this kind of attack is to use apostrophes around the numeric values. So the query would look something like this:
SELECT * FROM tblname WHERE ID='456'
So if any additional data is added to the number it will become part of the string. In a numeric context (meaning that we assume the ID field in the query above is a integer), MySQL will automatically convert this string to a number and strip any trailing non-numeric characters from it.
Testing Web Forms: Make sure that your scripts have all the safeguards in place when dealing with user input no matter how trivial the data in your database might seem. To test whether your web forms are properly protected, enter ''' into your web form. If you get any kind of MySQL error, then you should look at your scripts and try to work it out, because it may be a vulnerability that you cannot afford to have when using MySQL over the Internet.
Enter random characters, special symbols and spaces in fields that are meant to be numeric. If you see a MYSQL error, it means that your script passes unchecked values to your MySQL query. This is very dangerous and should not happen.
Data escaping and data filtering is very important when dealing with databases, and different scripting languages offer different functions to help escape form data. Some of the functions offered by PHP include mysql_escape_string(), addslashes() and stripslashes(). Other programming languages will have their own like Perl's "quote()".
Conclusion: This article talked about the general guidelines that you need to follow in order to make MySQL as safe as possible. This is by no means a exhaustive list of guidelines, but it will make your MYSQL server more secure. In future articles we will discuss the MySQL Privilege System.
DISCLAIMER: The content provided in this article is not warranted or guaranteed by Web Technologies (http://www.webteks.blogspot.com) The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.Labels: MySQL, Security |
posted by WebTeks @ 5:04 AM  |
|
|
|
|
|
|
|
|
|
|
|
|
| Previous Post |
|
| Archives |
|
| Links |
|
| Template by |
 |
|
