PHP and MySQL: SQL injection

First let’s have a look at what SQL injection is about. SQL injection means that an attacker is injecting some pieces of SQL code in a call to a server instead of just sending some text information in order to go around security mechanisms or in order to perform something which shouldn’t be allowed.

Here’s a very simple example. Let’s say you have a very poorly programmed login function which is called with two parameter, a user name and a password. If you take the parameters and built an SQL statement like this:

$query = "SELECT 1 FROM users WHERE user_id='".$user_name."' AND password='".$password"'"

An attacker may send the following:

  • User name: admin' —
  • Password: anything

The generated SQL query would be:

SELECT 1 FROM users WHERE user_id='admin' --' AND password='anything'

The double dash would make the rest of the line a comment and the statement would always return 1 allowing the attacker to login as admin without valid credentials.

An easy fix for this security issue is not to return 1 but to return an MD5 of the password and compare it with the password provided. Unfortunately, it is also trivial to workaround such security fixes. Let’s say you statement now looks like this:

SELECT password FROM user WHERE user_name='xxx'

All the attacker has to do is to use the following username: admin' AND 1=0 UNION SELECT 'known_md5_checksum

But SQL injection is not only used to be able to login without credential. It can be used to perform actions which are not intended to be allowed. This is typically done by using batched queries i.e. closing the first query and having a second query executed which would either return sensitive information or destroying something e.g. using a user name like: admin'; DROP TABLE important_table —

The first statement will be executed normally and the drop table will then be executed additionally. Fortunately, when using PHP and MySQL these kind of batched queries are not supported. The execution will fail since you can only have one statement executed at a time. But it you used PostgreSQL instead of MySQL it would be possible.

Another thing which is often done using SQL injection is getting access to data in the database which should be protected. This is usually done using a kind of UNION injection. The idea behind it is that:

  • the server you are attacking is fetching data using a query and displaying this data in a tabular form
  • you inject a UNION clause to fetch data from another table
  • the data from both table are displayed in the table on the client

Let’s say you have an order table and you can call the server to display all items in a particular order with such a statement:

$statement = "SELECT name, value FROM items WHERE order_id=".$order_id;

If the attacker now sends the following as order_id, he will get a list of all users and their passwords: 1 UNION SELECT name, password FROM users

I do hope the passwords will be at least encrypted but encryption alone is not always enough to protect data. You also need to make sure the encrypted data cannot be accessed that easily.

Of course an attacker will need to know which kind of statements are executed by your software in order to exploit such a security hole. But it might not be as difficult as you think… I’ll post another article about how you can manage to get information about the query being executed later.

Many database engines also provide the functionality to execute commands in the operating system from SQL. The commands are executed using the user running the database engine. It’s sometimes very useful but in an SQL injection scenario it may allow an attacker to not only steal information or damage the database but also the operating system. Fortunately, in our case the xp_cmdshell command used to do this (MSSQL Server and Sybase) does not exist in MySQL.

So what is to be done to protect yourself against SQL injection attacks ?

First if you use MSSQL Server or Sybase: to prevent an attacker from destroying the whole server or prevent him from getting access to any file on the computer, you should disable xp_cmdshell or run the database engine with a user with very limited access rights to the rest of the system.

Now, let’s get back to MySQL and PHP. Here you should use Use prepared statements and parameterized queries using PDO or Mysqli. These statements are sent to the database engine and are parsed independently of anything else. Like this it is not possible to inject SQL code in a parameter.

An example using Mysqli:

$stmt = $db->prepare('SELECT password FROM users WHERE user_name = ?');
$stmt->bind_param('s', $user_name);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // compare the returned value with the MD5 sum of the provided password
}

And using PDO:

$stmt = $pdo->prepare('SELECT password FROM users WHERE user_name = :user_name');
$stmt->execute(array('user_name' => $user_name));
foreach ($stmt as $row) {
    // compare the returned value with the MD5 sum of the provided password
}

Note that PDO is an extension but it is bundled by default since PHP 5.1 and a MySQL driver is also available by default.

This is basically the best way to secure your software. In case you cannot use PDO or Mysqli, there are other techniques to prevent SQL injection attacks. I’ll list them in an update to this post in a few days.

PHP: Cannot connect to MySQL on localhost

I decided some time ago to move on my Mac from MAMP to a native installation of Apache, MySQL and PHP. After installing everything I startet my application and got an error saying the database connection failed. I checked that MySQL was running and that I could connect to it manually. Everything was fine. Running under MAMP with the exact same configuration everything worked. The final deployment server is a Debian machine and there it worked with the exact same settings too. It also worked on a Windows machine using XAMPP.

It was getting late and I just couldn’t understand what the problem was, I read each character of the configured username, password and database name like 100 times to make sure I had everything right. Then I just changed the host from localhost to 127.0.0.1 and didn’t expect anything to change but there it was, my application could connect to the database !

On that evening I just went to bed making a mental note I had to understand what was the difference. The next day I did some research and could figure out what was the problem:

Just looking at the mysql_connect page in the PHP online manual brought the answer. There is a note saying:

Whenever you specify “localhost” or “localhost:port” as server, the MySQL client library will override this and try to connect to a local socket (named pipe on Windows). If you want to use TCP/IP, use “127.0.0.1” instead of “localhost”.

Of course, when I searched for an answer online before I found the solution I never saw this manual page. But now it’s clear.

It’s rather confusing since it effectively means that MySQL has redefined localhost to mean “connect to a unix domain socket”. And when it uses unix domain socket, it will ignore whatever you define as a port. This is of course kind of an issue if you want to have multiple instances of MySQL running.

It also looks like the default behavior on Windows is to use TCP-IP. But on Unix-like operating systems, it depends on whether you use localhost or 127.0.0.1.

If you need to use localhost and cannot configure 127.0.0.1, you’ll have to use socat to establish a relay between a unix domain socket and the MySQL tcp port.

One of the reason why it works with some localhost on a machine and not on the other might also be that the path to the unix domain socket is not the one you expect. Usually the path would be /tmp/mysql.sock. But if your mysql instance uses a different one, you should adapt the mysql.default_socket setting in php.ini and point it to the right path (e.g. /opt/local/var/run/mysql5/mysqld.sock, /var/mysql/mysql.sock, /private/tmp/mysql.sock or /usr/local/mysql/run/mysql_socket). If you’re using PDO, the setting you need to change is probably pdo_mysql.default_socket. You should be able to find the right path using the following:

mysqladmin variables | grep socket

or this:

mysqld --verbose --help | grep "^socket"

or this:

mysql_config.pl --socket

You can read the location where PHP looks for the MySQL socket in php.ini (mysql.default_socket, mysqli.default_socket and pdo_mysql.default_socket). If you have no php.ini file yet copy php.ini.default or rename it:

sudo cp /etc/php.ini.default /etc/php.ini

You can then change the path there. After changing the path, you need to restart the Apache web server e.g.:

sudo apachectl restart

If you do not want to change php.ini, you can also create a link:

mkdir /var/mysql
ln -s /tmp/mysql.sock /var/mysql/mysql.sock

You might have to use sudo to have the permissions to perform the above actions. Also make sure that the permission of the /var/mysql directory are appropriate.

Note that if you see the socket at the right location but disappearing for unknown reasons, you my want to trying deleting the /etc/my.cnf global options file. I saw this somewhere during my search on the internet but do not quite remember where.

On the other hand instead of changing the path in php.ini you may want to change the path in the MySQL configuration (my.cnf):

[mysqld]
socket=/var/mysql/mysql.sock

And restart the MySQL server.

Note that using the following hostname for the connection should also work although I haven’t tried it myself:

localhost:/tmp/mysql.sock

(change /tmp/mysql.sock to the path to the actual MySQL socket).

If you are on Mac OS X, please read the General Notes on Installing MySQL on Mac OS X. It states that the default location for the MySQL Unix socket is different on Mac OS X and Mac OS X Server depending on the installation type you chose and provides a table with a list of location depending on the installation type.

In MAMP, the location should be /Applications/MAMP/tmp/mysql/mysql.sock. So if you are using an external PHP installation with MySQL from MAMP, you’ll probably need to run the following:

sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock

MySQL: Access denied for user ‘root’@’localhost’ to database ‘information_schema’

When trying to import a MySQL database, it might stop with following error and fail to load all the dumped database:

Access denied for user 'root'@'localhost' to database 'information_schema'

INFORMATION_SCHEMA is a virtual database where information about all the other databases that the MySQL server maintains are stored. The tables in this schema are actually views not real tables and there is no database directory with that name.

Since it only contains data about the other databases on the server, you shouldn’t actually be importing it. So you shouldn’t have been dumping it in the first place. If you used mysqldump to dump all the databases, you are either using an old version (like 4.1) and should switch to a more current version of mysqldump. Or you use MySQL 5.5 or newer and actually went out of your way to dump INFORMATION_SCHEMA by naming it explicitly using the –skip-lock-tables option.

Anyway, there three solutions for this problem:

  1. Do not dump INFORMATION_SCHEMA.
  2. Remove it from the dump before importing it.
  3. Use the –force option when importing it.

Not dumping INFORMATION_SCHEMA if you use a version of mysqldump which dumps it if you use the -A or –all-databases option, means you’ll need to use the -B or –databases option and provide a list of databases to be dumped.

Removing it from the dump just involves using a text editor and deleting the parts about INFORMATION_SCHEMA since the dump is just a text file containing a list of SQL commands.

–force tells the MySQL client to continue even if an SQL error occurs. So it will produce errors while importing INFORMATION_SCHEMA but will then import the databases dumped after it.

MySQL: Compare two similar tables to find unmatched records

Sometimes you have two tables with similar structures and want to compare them. One could be the table in an older schema on an old server and the other one the newer schema on a new server. Let’s say you move data from one server to the other and want to check the data.

If you just want to compare two tables only once, you can go for a non-generic approach. Let’s assume that you have two tables (table A and table B) with a primary key called primary_key and two other columns (column1 and column2). What you want to get are:

  1. keys present in A but not in B
  2. keys present in B but not in A
  3. keys present in both A and B but where the other columns differ

The first part can be fetched like this:

SELECT A.primary_key FROM A LEFT JOIN B ON A.primary_key=B.primary_key WHERE B.primary_key IS NULL

The second part is basically the same but switching A and B:

SELECT B.primary_key FROM B LEFT JOIN A ON B.primary_key=A.primary_key WHERE A.primary_key IS NULL

The third part just involves an inner join and checking the other columns:

SELECT A.primary_key FROM A INNER JOIN B ON A.primary_key=B.primary_key WHERE A.column1 <> B.column1 OR A.column2 <> B.column2

An alternative is to get the following:

  1. entries in A which have no entry in B exactly matching
  2. keys present in B but not in A

The second part uses the same statement as the second part above. The first part can be fetched like this:

SELECT A.primary_key FROM A WHERE A.primary_key NOT IN (SELECT B.primary_key FROM B WHERE A.primary_key=B.primary_key AND A.column1=B.column1 and A.column2=B.column2)

This step basically combines the steps 1 and 3 in the first list.

Note that you can always replace a left join with a not in e.g. the following:

SELECT A.primary_key FROM A LEFT JOIN B ON A.primary_key=B.primary_key WHERE B.primary_key IS NULL

By:

SELECT A.primary_key FROM A WHERE A.primary_key NOT IN (SELECT B.primary_key FROM B WHERE A.primary_key=B.primary_key)

You just have to check what’s best from a performance point of view in you particular case.

If you do not have a primary key, you’ll have to do it using the algorithm described further down in the generic solution.

But if you often need to compare tables or need to compare many tables at once, then the following generic approach is probably better:

First since the two tables do not have the exact same columns, we’ll need to figure out what are the columns they have in common. To do this, we’ll simply read from INFORMATION_SCHEMA.COLUMNS.

A function to get the list of columns would look like this:

DELIMITER $$
DROP FUNCTION IF EXISTS getColumnList$$
CREATE FUNCTION getColumnList(schema1 VARCHAR(64), table1 VARCHAR(64)) RETURNS text
BEGIN 
	DECLARE column_name VARCHAR(64);
	DECLARE column_list TEXT DEFAULT '';
	DECLARE done INT DEFAULT 0;
	DECLARE cur CURSOR FOR SELECT c1.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c1 WHERE c1.TABLE_SCHEMA=schema1 AND c1.TABLE_NAME=table1;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	OPEN cur;
	read_loop: LOOP
    	FETCH cur INTO column_name;
    	IF done THEN
			LEAVE read_loop;
		END IF;
		IF column_list <> '' THEN
			SET column_list = CONCAT(column_list, ', ');
		END IF;
		SET column_list = CONCAT(column_list, '`', column_name, '`');
	END LOOP;
	CLOSE cur;
	RETURN column_list; 
END $$
DELIMITER ;

You can all the function like this:

SELECT getColumnList('phpBugTracker', 'comment');

Now we do not only want the columns of one table but of the two tables. It’s just a matter of joining INFORMATION_SCHEMA.COLUMNS with itself:

DELIMITER $$
DROP FUNCTION IF EXISTS getCommonColumnList$$
CREATE FUNCTION getCommonColumnList(schema1 VARCHAR(64), table1 VARCHAR(64), schema2 VARCHAR(64), table2 VARCHAR(64)) RETURNS text
BEGIN 
	DECLARE column_name VARCHAR(64);
	DECLARE column_list TEXT DEFAULT '';
	DECLARE done INT DEFAULT 0;
	DECLARE cur CURSOR FOR SELECT c1.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c1 INNER JOIN INFORMATION_SCHEMA.COLUMNS c2 ON c1.COLUMN_NAME=c2.COLUMN_NAME WHERE c1.TABLE_SCHEMA=schema1 AND c1.TABLE_NAME=table1 AND c2.TABLE_SCHEMA=schema1 AND c2.TABLE_NAME=table2;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	OPEN cur;
	read_loop: LOOP
    	FETCH cur INTO column_name;
    	IF done THEN
			LEAVE read_loop;
		END IF;
		IF column_list <> '' THEN
			SET column_list = CONCAT(column_list, ', ');
		END IF;
		SET column_list = CONCAT(column_list, '`', column_name, '`');
	END LOOP;
	CLOSE cur;
	RETURN column_list; 
END $$
DELIMITER ;

This one takes 4 parameters instead of 2:

SELECT getCommonColumnList('phpBugTracker', 'comment', 'phpBugTracker', 'comment_copy');

Now we know the columns, we just need an algorithm to compare these columns in the two tables. We’ll use the following:

SELECT MIN (TableName) AS TableName, column_list
FROM
 (
  SELECT 'source_table ' as TableName, column_list
  FROM source_table
  UNION ALL
  SELECT 'destination_table' as TableName, column_list
  FROM destination_table
)  AS tmp
GROUP BY column_list
HAVING COUNT (*) = 1

Now we need to create a function with parameters, calling our previous function to get the relevant column names, creating this statement and executing it:

DELIMITER $$
DROP PROCEDURE IF EXISTS compareTables$$
CREATE PROCEDURE compareTables(schema1 VARCHAR(64), table1 VARCHAR(64), schema2 VARCHAR(64), table2 VARCHAR(64))
BEGIN 
	SET @columns=getCommonColumnList(schema1, table1, schema2, table2);
	SET @st = CONCAT("SELECT MIN(TableName) as TableName, ",@columns," FROM ( SELECT '", schema1,".", table1,"' as TableName, ",@columns," FROM ", schema1,".", table1," UNION ALL SELECT '", schema2,".", table2,"' as TableName, ",@columns," FROM ", schema2,".", table2," ) tmp GROUP BY ",@columns," HAVING COUNT(*) = 1");
	PREPARE stmt FROM @st;
	EXECUTE stmt;
END $$
DELIMITER ;

And you call it like this:

CALL compareTables('phpBugTracker', 'comment', 'phpBugTracker', 'comment_copy');

Of course you could also adapt it to store the results in a temporary table…

MySQLBuddy 1.0 released

Since I’m not only using Sybase at work but also MySQL, I finally took the time to create a clone of SybaseBuddy for MySQL.

This new tool is called MySQLBuddy. Since both tools mainly provide the same functionality, they share most of their code. Of course at some point in time, I’ll put my software architect hat back on and will refactor the tools so that I have one tool supporting multiple databases. Unfortunately, I currently lack the time for this and will thus for the time being maintain both tools separately.

mysql: Access denied to information_schema when using LOCK TABLES

We’re backing up all our mysql databases with a script similar to this script. But we do backup also the information_schema database.

When doing so we got the following error:

mysqldump: Got error: 1044: Access denied for user 'admin'@'%' to database 'information_schema' when using LOCK TABLES

Note: on a system with Plesk, the admin user is just like the root user. If not using Plesk, you might also get the following message:

access denied for user 'root'@'localhost' to database 'information_schema'

The problem is basically that this user doesn’t have the right to LOCK TABLES.

So one solution is to grant this user the required rights:

mysql> GRANT SELECT,LOCK TABLES ON information_schema.* TO 'admin'@'localhost';

Another solution is to use the –skip-lock-tables option which will perform the dump without using LOCK TABLES (but tables may be dumped in completely different states):

#mysqldump -u admin -h localhost --skip-lock-tables -pxxxxxx information_schema

Or you can also use the –single-transaction so that a START TRANSACTION SQL statement is executed at the beginning and a consistent state is achieved without LOCK TABLES:

#mysqldump -u admin -h localhost --single-transaction -pxxxxxx information_schema

mysql: Backup all databases in separate files

In order to backup all databases on a mysql server at once, you can use the following command:

# mysqldump --all-databases -u xxx --password="xxx" | gzip > full.backup.sql.gz

This will create an sql file with a dump of all databases and compress it. It’s nice, fast and easy. But it’s then not so easy to work with this one single file (especially if only one database needs to be restored).
But this can also be done with a one-liner (though a long one…):

# echo 'show databases' | mysql -u xxx --password="xxx" --skip-column-names | grep -v information_schema | xargs -I {} -t bash -c 'mysqldump -u xxx --password="xxx" {} | gzip > /backup/mysqldump-$(hostname)-{}-$(date +%Y-%m-%d-%H.%M.%S).sql.gz'

This first gets all databases in the local mysql server, dumps each of them and stores them compressed.