Nginx, SSL & php5-fpm on Debian Wheezy

October 11th, 2014

I decided to take a break from my love affair with Apache and set up a recent development project on Nginx. I’ve seen nothing but good things in terms of speed and performance from Nginx. I decided to set up a LEMP server (Linux, Nginx, MySQL, PHP), minus the MySQL as it’s already installed on my VM host server, and plus SSL. Here’s the full setup tutorial on Debian Wheezy:

Step #1 – Installing the packages

apt-get install nginx-extras mysql-client
apt-get install php5-fpm php5-gd php5-mysql php-apc php-pear php5-cli php5-common php5-curl php5-mcrypt php5-cgi php5-memcached

MySQL can be installed into the mix with a simple:

apt-get install mysql-server

Read the rest of this entry »

Securing PHPMyAdmin and SQL

July 14th, 2010

PHP is of course a valuable tool, and PHPMyAdmin is an equally valuable asset for those that don’t like command line administration. The problem is that because it’s a valuable tool, it’s a security exposure. As a website security consultant, I see the problem often: people don’t secure the one thing that, if accessed by a malicious party, can give carte blanche for destruction.

One simple way to secure your installation is to slightly modify your config.inc.php file:

Look for this line:

$cfg[‘Servers’][$i][‘auth_type’] = ‘config’;

Change “config” to “http”. By doing this, you will require that the database information (username and password) be entered prior to accessing PHPMyAdmin. Of course, this only addresses attacks over the web. If someone tries to remotely connect to your database and knows the root password, or the credentials for any of your database, then you’re still vulnerable.

One way to address the security of your config.inc.php file is to secure the directory that it’s stored in. This is especially important if you should be on a shared server.

Of course, there is still the matter of your SQL port, 3306, being open to remote attacks. The solution to this problem can be found in the /etc/my.cnf file.

You need to add this line to make it so that only your server can connect to the SQL server.

Ensure that it’s under the “[mysqld]” section:

bind-address = 127.0.0.1

This sets it so that the SQL daemon only listens for connections locally, i.e. on your server. Anyone who tries to connect remotely will be denied. Now, the argument could be made that you could also try to add “skip-networking” to your my.cnf file, and then specify the path to your socket file, but you still need a way to administer your SQL, preferably via SSH. By adding the “bind-address” command, you can do just that.

The name of the game is security, and assumption. You have to assume that everyone’s out to attack you. If you think like that, you’ll narrow down all the ports that are exposed, and secure your server. Your SQL server is, like your DNS server, vital. It most likely powers your site. If the database is attacked, the damage can be considerable. Do understand that if a hacker is intent enough, they will find a way in, but by making it as difficult as possible, you reduce the chances of that happening.

PHP, MySQL and memcached

June 24th, 2010

According to memcached is a distributed object memory caching system. It can be used to set and get data by keys by any application that supports sockets.

As a website security consultant I advise you to ensure that your memcache server runs on 127.0.0.1 only and that you secure your server. Anyone with access to the server can telnet to the server’s local interface and get/set your memcache data.

I’ve used memcached for a number of PHP/MySQL projects, where I want greater cache control on database queries, than just relying on MySQL’s inbuilt caching abilities.

Now, whilst memcached should not be used to mask bad database design and optimization, or badly written SQL queries, it can help dramatically with queries that simply take a long time and have already been optimized as far as possible.

Assume that you had a simple database query wrapper:
Read the rest of this entry »

MySQL – Running Processes

June 6th, 2010

Showing running processes is easy, just log in to the MySQL command line and issue ‘SHOW PROCESSLIST;’
mysql> SHOW PROCESSLIST;
+———-+——+————————-+————+———+——+———-+———————————————————————————————–+
| Id | User | Host | db | Command | Time | State | Info |
+———-+——+————————-+————+———+——+———-+———————————————————————————————–+
| 66041116 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 66042322 | sql | www.adampalmer.me/iodigitalsec:57281 | websonline | Query | 1 | Updating | UPDATE `video_tags` SET `quantity` = ’27’ WHERE CONVERT( `tag` USING utf8 ) = ‘sport’ LIMIT 1 |
+———-+——+————————-+————+———+——+———-+———————————————————————————————–+
2 rows in set (0.00 sec)

You can also use ‘SHOW’ to display a wide range of information: http://dev.mysql.com/doc/refman/5.0/en/show.html

Website Security Scan

January 19th, 2010

Websites get hacked every day, customers details taken, and it’s usually REALLY EASY to do. As a security consultant,  I often get a call after a Google search turns up with my details as the guy to contact when this happens.

Shameless plug over, why not consider some of the things that can be done to help prevent a website breach..
Read the rest of this entry »

PHP Programmer – Logical Operators

December 17th, 2009

PHP allows the use of boolean operators.

AND, OR, XOR and NOT. We can combine NOT with AND and OR to form the NAND and NOR operators respectively.

$a = ($b and $c); will return TRUE if both $b AND $c are TRUE, otherwise, it will return FALSE. This can also be specified as $a = ($b && $c)

$a = ($b or $c); will return TRUE if $b OR $c are TRUE, otherwise, it will return FALSE. This can also be specified as $a = ($b || $c);

$a = ($b xor $c); will return TRUE if $b OR $c are TRUE, but not if they are both TRUE, otherwise, it will return FALSE.

$a = (! $b); will return TRUE if $b is NOT TRUE.

$a = (!($b && $c)); will form NAND (NOT + AND)
$a = (!$b || $c)); will form NOR (NOT+AND);
Read the rest of this entry »

PHP Developer – Looping through database results

November 5th, 2009

As a PHP Programmer, a very routine PHP/MySQL procedure is fetching a set of records from the result of a query.

$sql = "SELECT ...";
$result_set = mysql_query($sql);
for ($ctr = 0; $ctr < mysql_numrows($result_set); $ctr++)
{
    $my_object = mysql_fetch_object($result_set);
    //do something with $my_object
}

Now as tidy as the above code is, what’s the big problem? The number of rows returned by the query remains the same throughout. Why are we calling the mysql_numrows function on the same result set, to return the same answer over and over, possibly thousands of millions of times depending on the size of the result set? On a larger web application with a larger result set, things like this will dramatically increase unnecessary overhead. This is one of the most basic optimizations to make:

$sql = "SELECT ...";
$result_set = mysql_query($sql);
$result_num = mysql_numrows($result_set);
for ($ctr = 0; $ctr < $result_num; $ctr++)
{
    $my_object = mysql_fetch_object($result_set);
    //do something with $my_object
}

Now, there’s a couple of different methods you can use to achieve the same purpose, some of which may actually be more appropriate, such as a simple while loop, but the purpose of this article was to illustrate the issue above solely. More on optimization later..

Copy/Export MySQL User Priviledges

October 13th, 2009

I’m often asked how to copy or export MySQL Users from one machine to another. The following SQL query will show your users:

SELECT DISTINCT CONCAT (‘show grants for `’, user, ‘`@`’, host, ‘`;’) AS query FROM mysql.user;

In my case on my test server, this shows:

SHOW GRANTS FOR ‘root’@’127.0.0.1’;
SHOW GRANTS FOR ‘debian-sys-maint’@’localhost’;
SHOW GRANTS FOR ‘root’@’localhost’;

Now, I’ll need to execute each one of these as separate statements. The output of SHOW GRANTS FOR ‘root’@’localhost’; is:

GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY PASSWORD ‘*XXX…XXX’ WITH GRANT OPTION;

Copy and paste each ‘GRANT’ statement to your new SQL server, with the hashed password intact and you should be ready to go.

PHP MySQL Developer – Using MySQLi Prepared Statements to Avoid SQL Injection

September 25th, 2009

I’m going to demonstrate a very short and simple method of avoiding SQL Injection at the SQL query level. You’ll need MySQLi support, on Debian you can apt-get install php5-mysql will contain everything that you need, and would be installed by default with your LAMP Installation.
Read the rest of this entry »

How to Find and Replace data in MySQL

September 17th, 2009

It’s really easy!

UPDATE mytable SET myfield = REPLACE(myfield, ‘replace this’, ‘with this’);

Take a backup of your database first!

Security Consultant – PHP Developer – SQL Injection Attacks

September 6th, 2009

One of the most common form of attacks against web applications is SQL Injection. In the most part, the language that the web application is written in is irrelevant, be that PHP, ASP, Python, Perl, C, etc. As long as the back end database uses something SQL based, be that MySQL, MSSQL, etc, again, we’re in business. This probably covers over 99% of web applications out there. Both the security consultant and the php developer or web application developer in general has to be aware of the implications of SQL Injection. Here’s how it works:
Read the rest of this entry »

Simple MySQL Developer Intro

March 11th, 2009

MySQL is one of the most powerful and widely used databases available. Here is a really quick guide to creating a database, creating a table, inserting, selecting and deleting the data, then table, then database. This will not go into too much depth as there are plenty of resources out there already that can provide more information on each step.

I’ll be using the MySQL command line tool on a Linux (Debian!) platform. Assuming I already have mysql running and a passworded root user account:

ns3:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 168753
Server version: 5.0.51a-24-log (Debian)
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql&gt;

Read the rest of this entry »

How to reset forgotton MySQL root password

January 20th, 2009

As long as you have root access to your debian machine, you can do this as follows:

/etc/init.d/mysql stop #stop MySQL
/usr/bin/mysqld_safe –skip-grant-tables & #start MySQL with --skip-grant-tables
/usr/bin/mysql -u root mysql #connect to mysql as root, straight into the 'mysql' database. No password is required
UPDATE user SET password=PASSWORD(’newrootpassword’) WHERE user=’root’; #Do replace 'newrootpassword' with something that you'll remember.
FLUSH PRIVILEGES;
q #to quit
/etc/init.d/mysql stop #stop MySQL
/etc/init.d/mysql start #start MySQL

You can now test with mysql -u root -p you’ll be prompted for your password and your ‘newrootpassword’ should now work!

PHP, MySQL, Apache2 install HOWTO on Debian

October 7th, 2008

Setting up a PHP/MySQL/Apache2 environment on Debian is really easy, and as a PHP MySQL Developer, it’s kinda important! I’ll walk through a quick Debian lamp install howto and optimization process. I’ve optimized it for a 1.5Gb to 2GB RAM machine with reasonable load.

apt-get install apache2 php5 mysql-server-5.0 mysql-client-5.0 libapache2-mod-php5 php5-mysql php5-curl php5-cli php5-dev make gcc libc6-dev automakemysqladmin password 'NEWPASSWORD'

Now download eaccelerator from http://eaccelerator.net
Read the rest of this entry »