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 »

MySQL Master-Master Replication, Heartbeat, DRBD, Apache, PHP, Varnish MegaHOWTO

October 8th, 2014

I created this HOWTO while building a new development environment today. The intention is to take a single Apache2/Varnish/MySQL environment and scale it to two servers, with one effectively a “hot-standby” – increase redundancy and continuity whilst maintaining current performance. This HOWTO is based on Linux Debian-76-wheezy-64-minimal 3.2.0-4-amd64 #1 SMP Debian 3.2.60-1+deb7u3 x86_64

Our current server has IP and our new server has IP

Section #1: Set up MySQL Master/Master Replication

First, we’ll set up MySQL master to master replication. In this configuration, data can be written and read from either host. Bear in mind that issues may exist with autoincrement fields when written to at the same time. There are other caveats with replication so ensure to research them along with how to deal with corruption and repair before considering this setup for a live application. Also be sure to be using the same version of MySQL on both servers – this may not always be necessary, however unless you are very familiar with any changes between versions, not doing so could spell disaster.

Read the rest of this entry »

SQL injection with sqlmap

December 10th, 2013

sqlmap is web application & database penetration testing tool that automates detecting and exploiting many types of SQL injection flaw, and then taking over the database server. It’s able to detect a huge range of injection types.

Let’s take the following code –

        $link = mysql_connect("localhost", "twl", "XXXX");

        echo "This is a page\n";
        $sql = "SELECT * FROM wp_posts WHERE ID='" . $_GET['id'] . "';";
        $res = mysql_query($sql);
        echo "This is some text\n";


Read the rest of this entry »

mysql_real_escape_string won’t magically solve your SQL Injection problems

August 18th, 2013

Edited: 5th Oct 2014 after bug fixing and reader feedback
Edited: 6th Oct 2014 after reader feedback

I was engaged by an online retailer to test their custom web application CMS and store. I attended their premises and sat down with the tech manager and his lead developer to discuss with them from both a business management and a technical perspective some of the vulnerabilities that should be tested for, as well as to gain a solid understanding of the business needs and logic.

When I came on to SQL injection, I was assured by the lead developer that owing to their secure coding practices, SQL injection is completely impossible. All expected user entered integers are cast as integers, and all expected user entered strings are run through mysql_real_escape_string before being passed back to the database. Once code is committed by a developer to the development Subversion server, the lead developer then manually reviews it before deciding to push it live. Great, I thought, it’s certainly a good start. I did point out that this might not always work, but he didn’t seem too phased, and I didn’t want to get too much into a discussion about why or when that might not always work at that stage.
Read the rest of this entry »

MySQL Root to System Root with lib_mysqludf_sys for Windows and Linux

August 13th, 2013

Once a MySQL database server has been compromised at root level, it’s often possible to escalate this access to full system level access using User Defined Functions (UDFs). We may have MySQL root access but not system root access for a number of reasons including having a shell account on the target whilst MySQL’s root user has been left unpassworded by default, or alternatively gaining access via SQL injection through a web application connecting to the database as root, which is something I see far too often.

Firstly, you’ll want to check out a copy of sqlmap. For this attack you’ll want to browse to the ‘udf’ directory and select the appropriate library depending on your target platform:

  1. udf/mysql/linux/32/
  2. udf/mysql/linux/64/
  3. udf/mysql/windows/32/lib_mysqludf_sys.dll
  4. udf/mysql/windows/64/lib_mysqludf_sys.dll

The steps for escalation on both Windows and Linux are the same. Firstly, we need to get a copy of the correct library on to the target machine in a known location – this could be by uploading to a user account we have access to, or uploading via a website image/file upload, or anonymous FTP account. The second step is issuing a SQL query to load this file in to a newly created table row.

Third, we then want to dump that table row out to a new file in either the ‘/usr/lib’ directory or the ‘c:\windows\system32’ directory depending on whether we are on Linux or Windows respectively. The reason we need to do this, is that our regular web application or user account does not have permission to create files in these directories, however the MySQL root user does. Next, we want to instruct MySQL to create a new function to point to the code in our malicious library. Lastly, we execute this new function with arbitrary system commands that we wish to run.
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 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 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 =

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.

MySQL – Find Duplicates Only

June 25th, 2010

Within MySQL, we may want to select duplicate records, instead of just selecting unique records. Assuming a table name of ‘table’ and the field to check on being ‘field’;

To select UNIQUE rows only:

To select DUPLICATE rows only:
SELECT field FROM table GROUP BY field HAVING ( COUNT(field) = 2 )

To select DUPLICATE, TRIPLICATE or more rows only:
SELECT field FROM table GROUP BY field HAVING ( COUNT(field) > 1 )

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 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;’
| Id | User | Host | db | Command | Time | State | Info |
| 66041116 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 66042322 | sql | | 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:

Access denied for user ‘debian-sys-maint’@’localhost’

April 20th, 2010

Exporting all databases from a MySQL installation on Debian using –all-databases, and then importing them back into a new installation will overwrite your privileges table. Whilst this may be what you want, each MySQL installation on debian generates a unique /etc/mysql/debian.cnf which contains logon details for the ‘debian-sys-maint’ system account. This account is used by the custom Debian scripts to deal with things such as checking for crashed tables.

After your export and import, you will likely end up with an error on your new installation:

‘Access denied for user ‘debian-sys-maint’@’localhost’

The way to fix this, is to log in to MySQL as root, and issue:

GRANT ALL PRIVILEGES ON *.* TO ‘debian-sys-maint’@’localhost’ IDENTIFIED BY ‘xxxxx’ WITH GRANT OPTION

Where ‘xxxxx’ is the password found in your debian.cnf file.

Then issue: FLUSH PRIVILEGES; and restart MySQL

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 Security

January 14th, 2010

As a PHP programmer, there are a couple of things you can do quickly and easily to increase the security of your PHP code installation.

Look into PHP’s “safe mode” feature, ESPECIALLY if you’re running a webserver that takes the general public can upload scripts to. Here you’ll find a list of the functions disabled or restricted by safe mode. It is not strictly PHP’s job to restrict these types of functions, however unless you really know what you’re doing, the list of functions restricted by safemode is a good starting point for building secure applications. These are generally functions that allow file and directory manipulation, and socket manipulation. If it’s not possible within your environment to disable them all, disable as many of these functions as possible.

Although not that common, if I’m writing an application that heavily relies on functions that manipulate directories or sockets, I’ll prefer to create a C daemon or similar to handle this side of things and simply use PHP to communicate with it. Read the rest of this entry »

PHP Developer – Loops in General

November 15th, 2009

There are 3 types of loop in PHP:

while (condition)
{ code_goes_here; }

{ code_goes_here; }
while (condition);

for(expr1, expr2, expr3)
{ code_goes_here; }

In terms of the ‘for’ loop above, ‘expr1’ being the starting expression, i.e. $i=0. expr2 being the condition that must be satisfied to keep the loop running, i.e. $i < 100. expr3 being the expression evaluated each time the loop runs, i.e. $i++. Each loop type has it’s uses.
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..

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 »