This is the second installation of a 3 part article on LAMP (Linux Apache MySQL PHP). In order to safeguard a MySQL server to the basic level, one has to abide by the following guidelines.

Securing MySQL:

MySQL uses security based on Access Control Lists (ACLs) 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.

For the most part, MySQL has the following default security parameters:

By default mysqld runs as root. It allows external network connections and has a known port number which is easily detectable. It has no passwords whatsoever and allows "File_Priv". This means anyone from anywhere on the internet can misuse the privileges by logging as root and meddle with the contents of the database and even drop the database with one SQL command.

In order to safeguard a MySQL server to the basic level, one has to follow the guidelines below:

  • Do not forget to set a password for the root user

  • Be careful with the access privileges given to users

  • Never give anyone (except the mysql root user) access to the user table in the mysql database! This is one of the most important measures to be taken while secu ring your database

  • The encrypted password is the real password in MySQL. Anyone who knows the password which is listed in the user table and has access to the host listed for the account can easily log in as that user

Be aware of the privilege system in MySQL like the GRANT and REVOKE commands are used for controlling access to MySQL. Do not grant any privileges to any user more than what is required.By giving all privileges to a particular user you will be at a high security risk as the user may simply break into the system and damage the database .

To Ensure what permissions are given to whom execute the following commands: 

 bash# mysql -u root -p 
 mysql> use mysql; 
 mysql> show tables; 
 mysql> select * from user; 
 mysql> select * from db; 
 mysql> show grants;

Here you will get an overview of which user is granted what privileges. Using the command REVOKE one can remove the privileges granted for a particular user. To be on the safer side, make sure you delete the users who have empty passwords set. Use the following command: 

mysql> DELETE FROM user WHERE Password='';

Cameron Green contributed this to the discussion:

In any case I would recommend setting the passwords the password to something else. There could be databases, permissions in the table and database privilege tables that will be orphaned by simply deleting all users with blank passwords. Even if you are assuming that this is a fresh installation, I think you will find that the test user has entries in the DB table which will be orphaned by the above command. Changing the password leaves the account as is, and if for some reason it is need later on then a "SET PASSWORD" will reactivate it.

Also FLUSH PRIVILEGES should be run after manual changes to the permissions tables such as above, not after any other changes, ie those using grant and revoke, set password etc. As set out in your article, after removing the users via a delete query, the accounts will remain active until flush privileges is done. For your grant and revoke statements, it does nothing.

Try mysql -u root. If you can connect successfully to the server without being asked for a password, you are at a risk. Now anyone can connect to your MySQL server as the MySQL root user with full privileges! So set the password for the mysql root user like this: 

mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';

Make sure that the MySQL passwords are different from the OS passwords (MySQL "root" has nothing to do with system "root").

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(), SHA1() or another one-way hashing function. Do not choose passwords from dictionaries. There are special programs to break them. Do not keep passwords like your name,date of birth or the place of birth because it becomes easier for the hacker to guess and crack.Keep your password vague so that is easy to remember and type, but difficult to guess for someone who does not know it.Make sure that your passwords are not too short It has to be atleast more than 8 characters. Do not grant privileges to all hosts as it will avoid unwanted and unexpected attacks from the hosts If you're not running "mit-pthreads" you can use: 

mysql> GRANT USAGE ON *.* TO bugs@localhost;
mysql> GRANT ALL ON bugs.* TO bugs@localhost;
mysql> REVOKE DROP ON bugs.* FROM bugs@localhost;
mysql> FLUSH PRIVILEGES;

To activate the changes done one has to run flush privileges or may restart the mysql server 

mysql> FLUSH PRIVILEGES;

With "mit-pthreads" you'll need to modify the "globals.pl" Mysql->Connect line to specify a specific host name instead of "localhost", and accept external connections: 

mysql> GRANT USAGE ON *.* TO bugs@bounce.hop.com;
mysql> GRANT ALL ON bugs.* TO bugs@bounce.hop.com;
mysql> REVOKE DROP ON bugs.* FROM bugs@bounce.hop.com;
mysql> FLUSH PRIVILEGES;

As we know, a firewall is a mechnaism to prevent intruders from attacking a system. Therefore, it's better to put MySQL behind the firewall or in a demilitarised zone (DMZ). This reduces at least 50% of the risks that prevail.

MySQL uses port 3306 by default, hence its very important to scan your ports from the Internet using 'nmap'. This port should not be accessible from untrusted hosts. In order to check whether the MySQL port is open, execute the following command from some remote machine, where server_host is the hostname of your MySQL server: 

 shell> telnet server_host 3306

If you get a connection and some garbage characters it means the port is open, and should be closed on your firewall or router, unless you really want to keep it open.

If telnet just hangs or the connection is refused, then everything is fine and the port is blocked.

Securing the data that is entered either from a web form, a URL, or any application that is built is very much essential. Since the users may just pass some special or escaped character sequences which can cause some security damage. Hence one should not trust any data entered by the users. If you find something like ``; DROP DATABASE mysql;'' in the data entered by user then you must know that data loss is going to occur. Also remember to check numeric data. We normally protect against only strings which is a common blunder.

Generally people think that if a database contains data which is publically available then it need not be protected which is very much wrong. DOS attacks can still be performed on such databases.

Therefore, use apostrophes around the numeric constants: 

mysql> SELECT * FROM table WHERE age ='23';

instead of 

mysql> SELECT * FROM table WHERE age=23;

MySQL automatically converts this string to a number and strips all non-numeric symbols from it.

All Web Applications

In cases where there is an attempt to enter `'' and `"' into web forms and you get any kind of MySQL error, find out the problem right away. Check for modification of any dynamic URLs by adding %22 (`"'), %23 (`#'), and %27 (`'') in the URL. Look for changes to datatypes in dynamic URLs from numeric ones to character ones containing characters from previous examples. You should ensure that the application is safe against this and similar attacks. Entry of characters, spaces, and special symbols instead of numbers in numeric fields will fetch you erroneaus results. It should be removed from the aplication before it is passed to MySQL. Passing unchecked values to MySQL is very dangerous! Data sizes must be checked before passing them to MySQL.

PHP users must notice the addslashes() function. As of PHP 4.0.3, a mysql_escape_string() function is available that is based on the function of the same name in t he MySQL C API.

  • In MySQL C API: Note mysql_real_escape_string() API call.

  • In MySQL++: Check out the escape and quote modifiers for query streams.

  • For Users of Perl DBI: Check out the quote() method or use placeholders.

  • Users of Java JDBC: Make sure you use PreparedStatement object and placeholders.

Never allow the data to be transmitted in plain text(unencrypted) across the network. This 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 4.0.0. SSH port-forwarding can be used to create an encrypted (and compressed) tunnel for the communication.

Try tcpdump and strings utilities. For 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 changes under other systems) You'll notice the following things: 

Kernel filter, protocol ALL, datagram packet socket
tcpdump: listening on eth0

Warning: Just because you cannot see the data, does not always mean that it is encrypted.

Please take care of these things as well:

  • Turning off external networking with "--skip-networking", unless you have "mit-pthreads", in which case you can't. Without networking, MySQL connects with a Unix domain socket.

  • Using the --user= option to mysqld to run it as an unprivileged user.

  • Be aware of the special command in linux called 'chroot'. chroot means running a command or interactive shell with a special root directory. For more information see the man pages on chroot

Running MySQL in a chroot jail, running httpd in a chroot jail, running MySQL on a separate untrusted machine, does not free your system of risks, but it certainly helps.

PHP Security

PHP is a widely-used general-purpose HTML embedded scripting language that is especially suited for Web development. With PHP, one can build dynamic web pages with ease and comfort. It is the most popular scripting language that is used because of its ease and flexibility compared to other scripting languages.

Securing PHP is one of the major duties of a developer or an administrator. When we talk about security we must concentrate on 2 things:

  1. What platform is PHP going to be installed on?
    • PHP supports a variety of OS of which linux and windows are most common

  2. The way PHP is going to be configured
    • An apache module or

    • A CGI interpreter

Building as a dynamic shared object will mean that you can compile apache separately, and don't have to recompile everything as you add to, or change PHP. Building P HP into apache (static method) means that PHP will load and run faster. Servers can be extended with other modules even after installation. This also allows for easie r module development and testing, since recompiling the apache source is not required each time the module is changed.

  • DSO is not supported on all platforms and Startup of the server is 20% slower due to symbol resolving.

  • DSO can produce a slightly slower server depending on platform and address resolution.

  • DSO modules cannot be linked with other DSO modules. For example a.out-based platforms usually don't provide this functionality while ELF-based platforms do.

  • You cannot use the DSO mechanism for all types of modules. This requires either the code be referenced directly through the Apache core, or compile Apache with chaining available.

Building as a CGI Binary means efficiency could be improved by having only a single Perl interpreter running in memory, and passing it the Perl scripts. This is whe re mod_perl comes in to the picture. mod_perl has its own merits, like it allows customized processing of URI to filename translation, authentication, response generat ion, and logging with practically no run-time overhead and improved performance of 200% - 2000% apparently obtained. It also provides a single embedded Perl interprete r within the Apache web server. This can be either statically linked, or as a DSO module. But the main drawback of CGI interpreter is that when PHP is compiled as a cg i, there occurs a lack of effieciency in handling high traffic applications.

Let us look at the parameters that are to be set in the configuration file (php.ini). Although there are a huge number of them let us discuss atleast the required o nes which must be followed.

Firstly safe_mode on

Safe mode is required for nearly all of the following options, safe mode allows PHP to impose more security restrictions than a normal configuration.

Safe_mode_exec_dir

Set this variable for it helps you in forceing PHP to only execute scripts from a specified directory.

Open_basedir

This parameter allows you to control which directories PHP scripts are allowed to access files from. By default PHP will allow a script to access a file from anywhere s o it is recommended that is option be set. By predefining valid directories, data can be protected.

Max_execution_time

This variable enables you to set a maximum execution time that a script can have. If a script runs longer than the allocated execution time, it will be terminated. This option will allow you to prevent attackers from tying up your web server with malicious scripts that could cause denial of service.

Memory_limit

You can control the maximum amount of memory that a script can use. Using this will help to prevent buffer overflows which may lead to more serious threats.

Upload_tmp_dir

This designates where PHP will place files that are being uploaded

Error_Reporting on

This helps in debugging.The various errors,notices and warnings that one may encounter while programming can be seen by turning on this parameter

Summary

Here we have discussed about securing LAMP and by following the guidelines given one can reduce the security threats and hence increase the difficulty for the intruders to perform any malicious activity.

Although we cannot completely be successful in reaching 100% security but all that we can do is increase the degree of difficulty for intruders to perform harmful activities.

I hope this article will shed some light on those who are unaware of the vulnerabilities and security holes.

This email address is being protected from spambots. You need JavaScript enabled to view it.

Roopa has been in the IT field in Karnataka, India for about three plus years. Her interests include Linux Security and Networking and she has been at them for a while.