Category Archives: PHP

Drupal Commerce Product Feeds – Problems Importing Prices

I’ve been playing around with Drupal to create an internal ordering system. However I was getting stumped setting up a feed, which contained price information.

I had a CSV field with price data which was just ‘0’ or empty in places, and I was trying to import it into a product field of type Price, which caused the Feeds Importer to die with following cryptic statement:

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column
'commerce_price_amount' at row 1

Minor Unit

This is issue occurs because Drupal Commerce uses a Minor Unit format (prices without decimals) for its pricing data. For example, if you had a price of 1.50, you would write it as 150. It also seems that depending on how your data is quoted, or if it was missing in my caase, Drupal might struggle to bring in the price data correctly.

Solving the problem

Check that the data looks good.

Before fixing the actual problem, its best to first look at the logs button on the feeds import page. Check that parsed data matches up to your CSV. You may have inadvertent line breaks or other issues (such as the file not being in UTF-8 encoding) that is causing the data to import badly and you might need to simply fix up your feed.

Feeds Tamper

If you’re still having problem then the solution is Drupal’s Feeds Tamper module, which allows you to modify feeds data before its get written into Drupal.

Feeds Tamper comes with 10s of plugins that can alter data (setting a default value, doing string substition) as well as providing a path to write custom plugins.

Install the Feeds Tamper module

Don’t forget to set the Permissions for Feeds Tamper. Go to Modules->Feeds Tamper and activate the module, along with the Feed Tamper Admin UI.

Then set permissions Modules->Feeds Tamper->Permissions and give the right permissions by role to each existing importer.

Tamper with Your Feed

If everything is set-up correctly, you should be able to head to Structure->Feeds Importers and you should see Tamper now as option on the importers you enabled it for. Clicking this takes you to a list of all your feed mappings.


Hit add plugin on a mapping to start tampering with that field. The nice thing is that you can add multiple feed tampers and order them as appropriate to get some powerful data manipulation.

Writing your own Feeds Tamper

Returning to our problem, we can now easily fix the feed issue. First we’re going to set-up a custom module, which we’ll call pricefixer.

First create a file and save it in the appropriate sites module directory (such as Sites/all/modules).

This should contain something like this:

name = PriceFixer
description = "Fixes price importation issues"
package = Feeds
dependencies[] = commerce
dependencies[] = feeds_tamper
core = 7.x

files[] = pricefixer.module

Then create the corresponding module file, in our case pricefixer.module, which contains a single hook based on MYMODULE_ctools_plugin_directory, which lets us tell Feed Tamper (via CTools) where our plugins are going to be. In this case they will be in a sub-folder of the module, PriceFixer/Plugins.

Finally create the feed tamper plugin iteself (which we will call and save it into the plugins directory under our module:

 * @file
 * Filter to convert prices to Drupals minor unit format

$plugin = array(
  'form' => 'price_fixer_form',
  'callback' => 'price_fixer_callback',
  'validate' => 'wbr_price_filter_validate',
  'name' => 'Price Fixer',
  'multi' => 'skip',
  'category' => 'Filter',

function price_fixer_form($importer, $element_key, $settings) {
  $form = array();

  $form['convert_minor'] = array(
    '#type' => 'checkbox',
    '#title' => t('Convert to Minor Units'),
    '#default_value' => isset($settings['convert_minor']) ? $settings['convert_minor'] : TRUE,
    '#description' => t('If checked, then will convert to minor units (Drupals default value system). So 1.50 becomes 150.')
  return $form;

 * Called validate user settings in our ui element set up in price_fixer_form
function price_fixer_validate (&$settings) {
  /* validate our settings in here */

 * Called by the feed (via Feed Tamper) to modify data.
function price_fixer_callback ($result, $item_key, $element_key, &$field, $settings) {

	if ( empty($field) ) {
		$field = 0.0;
	else if ( $settings['strip_quotes'] ) {
		$field = strip_quotes($field);

	// strip thousands separator if required
	if ( $settings['convert_minor'] ) {
		// convert to minor units
		$field = commerce_currency_decimal_to_amount($field, $settings['currency_code'], TRUE);

And volia. Reload the Feed Tamper UI and you should be able to plugin this price fixer like any other feed tamper plugin. Obviously the code is incomplete because everyones feed is slightly different. I’ve include the call to commerce_currency_decimal_to_amount as it seems like a simple way to get into the Minor Unit value.

You can obviously do a lot more here, such as strip quotes, check field is numeric etc etc, but once you’ve written one feed tamper, a second or third is very easy.

The easiest way to see how things are set-up is to look at the installed Feed Tamper plugins, you’ll quickly see how to change the categories a plugin is listed under, add complex UI elements and really work that data.

Want to know more?

Improved MySQL and PHP login security using salts

Obviously in the age of Drupal and other CMS systems, you generally don’t need to write your own user login/password schemes, but just in case you do, heres some code I fiddled around with recently to

  • Provide a reasonable safe password storage mechanism that was safe from rainbow dictionary attacks.
  • Implement a simple ‘I forgot my password scheme’, which sends an email to confirm the request to the users email address with a clickable link.
    • This could be combined with a Captcha scheme or similar to prevent an automation attacks.


The first thing to realize with passwords, is that storing them as plaintext in a database is just asking for trouble. You always need to encode, encrypt or in someway protect passwords.

Typically, this is done via a one-way cryptographic hash function such as the Secure Hash Algorithm 1 (SHA-1), which returns a pseudo-random 40 character string. For example, calling sha1(‘biteme’) returns:


There are some weaknesses in the SHA-1 algorithm that make it a less than ideal choice for an encoding scheme these days. But there is also a general weakness of such a hash based encodings. Because the hashing function always returns the same hash for the same key, you can create rainbow tables of keys and their hashes. If you can access to the DB, it then becomes relatively easily to start matching user passwords to the rainbow table.

Obviously the longer a user password is and the more non-alphabetic characters it includes all help make the generation of a sufficient rainbow table pretty hard.

The solution to preventing such attacks is to use a stronger encoding scheme and to ‘salt’ the password to make the key to hash mapping unique.

Sadly a lot of PHP/MySQL books(*) use SHA-1 only in their examples to encode passwords, such as ‘PHP and MySQL WebDevelopment’ by Luke Welling and Laura Thompson, probably for clarity and also as they’re trying to cover a lot of ground.

Just remember SHA-1 is not your friend any longer.

Our Data

We’re going to use a few sample MySQL tables to discuss better ways of encoding passwords and handling password resets.

First is our user table which stores an email address, a password field, the date and time of account creation and some password ‘seasoning':

| Field      | Type             | Null | Key | Default | Extra |
| email      | varchar(60)      | NO   | PRI |         |       |
| password   | varchar(64)      | YES  |     | NULL    |       |
| created    | datetime         | NO   |     | NULL    |       |
| pepper     | varchar(10)      | YES  |     | NULL    |       |

And a password reset table, which holds an email address, a password reset token and a date time of the password request. We will uses this when a user forgets a password.

| Field     | Type        | Null | Key | Default | Extra |
| email     | varchar(60) | NO   | PRI | NULL    |       |
| token     | varchar(60) | NO   |     | NULL    |       |
| requested | datetime    | NO   |     | NULL    |       |

Creating an account

You would use a standard webform to capture user details such as name and password. Setting this up is beyond this posting, but you probably want to do this via an SSL session to prevent anyone sniffing user passwords as they’re sent from the browser to the server.

Say that the user was and the password was ‘biteme’.

Conventional examples would have you insert this into your MySQL database by doing something similar to  this:


But as we saw the weakness of SHA-1 and rainbow table attacks, we need to do something a little more complex. We will use the following approach to ‘season’ the password:

  • Use a generic ‘salt’ phrase. That is a somewhat random salt phrase which we will apply to the password in some hidden way to provide a salted-hash.
  • A user specific ‘pepper’ password phrase. Randomly generated at the time of a password change/creation and stored alongside the user account.
  • We then combine these with the password phrase and the SHA-256 function to create a strongly encoded password which is resistant to rainbow table attacks.

The Code

We first generate our unique global salt value (either using the above code or by creating a nice password string by hand) and store it away in a place that is accessible, for example in a config file which is in a protected directory, ie:

 * Global salt value
 $cfg_salt = 'GFGFB-8ba72a57ff'; 

Really this global salt can be anything and the larger the value, the more secure things will be.

We then take then generate a unique salt for each user, the ‘pepper’ in our seasoning using the generate_salt function again. This pepper value will be saved in the DB with the user record.

$pepper = generate_salt(10);

Finally, we season the user’s password by applying both the salt and pepper and use the much more secure SHA-256 variant:

$db_password = hash('sha256', $cfg_salt . $user_password . $pepper);

Obviously you can combine this in many different ways, and some people hash the hash to really randomize things. Its this combination of salts that makes the mapping between the users actually password and the hash keys generated almost impossible to discover.

sha256 is a reference to a specific version of the SHA-2 algorithm and returns a hash of 64 characters. But you could use sha512, md5, bcrypt etc here. See this thread for more details and the pros and cons.

Checking a provided password

The user returns the site and wants to login in. So we once again capture their password using some suitable means and simple:

  • Extract their unique salt ($pepper) from the db.
  • Season the supplied password in the same as before, using the $pepper and $salt
  • Compare against the database for the given email record.
 * @param string $email
 *     User supplied email address
 * @param string $password
 *     User supplied password
function is_authorised_user ( $email, $password )
	global $db_users;
	$result = null;

	// connect to our db
	$db = open_db();
	if ( !mysqli_connect_errno() )
		// clean input before using in sql
		$name = clean_input_for_sql_use($db, $name);

		// load our user specific seasoning from the users database
		$pepper = get_seasoning($db, $email);
		// generate a password by applying global salt + local pepper		
		$password = season_password($password, $pepper);

		$query = "SELECT * FROM ". $db_users. " WHERE email=" .$email. " and password = '" .$password. "'";

		$db_response = $db->query($query);

		if ($db_response) {
			$result = $db_response->fetch_assoc();
	else {
		throw new Exception('Connect Error: ' . mysqli_connect_error());
	return $result;
If the passwords match, then the user can be logged in.
As a final measure, when the user changes their password, I re-generate a new $pepper value for them just to keep things fresh.
Note: If you change the global salt, or the way you combine the seasoning for passwords, you will have to change any encoded passwords. There is plenty of really useful articles on the web about the best approaches to combine techniques to strongly encode passwords (ie double hashing, performance of bcrypt vs sha512 etc).
Obviously the best thing you can is to ensure your users give strong passwords to begin with:
In the next post, we’ll look at reseting user passwords. In the meantime check out these threads:

Working code will be available in the next post.

Preview: A very simple php & javascript image viewer

I wanted to create a very basic (and I mean basic) image gallery to use on a webpage. It didn’t have to be flash or with fantastic animation. Its purpose was just a little extra page flair and so simplicity was the main requirement.

I looked at a number of existing libraries, searched for examples, read a few blogs and just didn’t see anything particularly simple or lightweight (Lightbox2 for example was pulling 100Kbs of js data in; granted its lovely to see in action but a little overkill for what I wanted).

So I decided to create something as minimal as possible but that was still flexible using PHP to construct the HTML, Javascript to swap images and some simple CSS styling. The solution, nominally called Preview is nothing special, however it does provide a nice simple solution that you might find educational.

The basic features:

  • Somewhat flexible on image sizes.
  • Use a simple folder based structure, making it easy to auto-generate gallery images (not covered here but easy to do with PHP) or for someone to do it quickly by hand (under 5mins).
  • Displays clickable thumbnails;  with the number displayed being configurable.
  • Displays a single main image, that can be swapped out when the thumbnails are clicked.
  • Provide support for titles and alt text association with images.
  • Uses as little browser-side code as possible.
  • Supports CSS styling.

Javascript and OnClick

The dynamic swapping of images is handled using a really simple Javascript function, which is small enough to be embedded in the header of a page and a little CSS


This function is attached to an onClick action placed on an image of our choice. In Preview, when the user clicks on a thumbnail image, this swapImage function is triggered. We change the main image being displayed by updating where its src should point to (the image is simply identified by the CSS id image-main). We then attach the attributes from our thumbnail image to the new main image.

The page then shows a new main image with the correct alt and title attributes. You could obviously pass different alt, title and other attributes into this function as well as trigger animations and other visual wizardry. Thats up to you.

Preview places Javascript onClick events on each thumbnail images being displayed, so when the user clicks on one then the imageSwap function is called:

<img class='thumbnail' src='gallery/ke_75.jpg' alt='[Photo:Lake Victoria]' title='Lake Victoria' height='75' width='75' onclick='imageSwap(event, "gallery/ke.jpg")'>

The final trick is to change the cursor when rolling over a thumbnail, so that the user will ‘see’ the thumbnail as a clickable link. This requires a only tiny piece of CSS via the cursor property ie:

#preview img.thumbnail {
	cursor: pointer;	/* Show 'link' cursor */

Really thats the core of the solution in a nutshell.

Putting it together with PHP

Now we have the basic mechanism ready for use, we can use PHP to tie everything together so one simple call will show a nice looking gallery, such as this one:

Preview provides a single PHP call, which takes the location of the gallery folder and how many images to show:

show_preview_gallery('gallery', 6);

First we need to decide on some basic image size rules, which really helps to constrain the design requirements. You will need to set different image sizes in the preview.php and preview.css files if you want to use image sizes different than this example.

Image Sizes

The first thing to decide is on a size for our Preview main image, in this example I’m going to use images which are 240px by 300px (again sticking with idea of small page decoration) so it can be easily be floated into a sidebar or margin.

Next lets decide on thumbnail sizes, in this example thumnail previews are going to be 75px by 75px.

I use PHP to crop, resize and prepare uploaded images for galleries like this, but we can look at that on another day. We need for each image to display:

  • The main image version, 240×300 (ie image.jpg)
  • A thumbnail preview version, 75×75 (ie image_75.jpg)
  • A piece of description text, that will be used for the alt text and tooltip title

The pair of images are placed flat in our gallery folder and the description text is written into a very simple descriptions.txt file at this level, which is simply a tab separated format.

Folder Structure

All the image files and description file reside in a single folder, like so:

Each thumbnail file has the same name as its parent (thumbnails are identified by a suffix showing their image dimensions – so in this case xxxx_75.jpg). The Preview code should support .gif, .png files as well.

Using PHP to scan a folder

We use PHP to scan the given gallery folder to find all the thumbnail images of the size we want (in this case all 75×75), using the glob function, which finds pathnames matching a simple pattern:

You could use nice directory iterators to do the same, but as we have a flat filesystem currently then glob works fine. To add a bit of spice we use shuffle to randomize things and then we slice off how many images we want.

At this stage we now have an array of all the thumbnail images.

The rest of the code is pretty simple. We strip off the thumbnail size designation to get the name of the main image file its related to. We construct our IMGs and hook in the imageSwap onClick event and we attach the long descriptions from our flat description text file.

Use CSS to style Preview

Preview is set-up to use id based selection for its main view. Unfortunately theres no simple way of passing our thumbnail and image sizes in, so if you using something bigger/smaller than adjust the widths accordingly in the CSS (remembering to account for any padding and margins being set).

The css file is there mostly to provide a very simple starting point for experimentation by the key components. The only real constraint is that the imageSwap function relies on the CSS id of the main image.


You can see a very basic example of Preview here. Things are pretty basic to support experiementation.


You can download all the code and a working example here. Feel free to modify, hack, lambast and destroy this code. Its deliberately simple and limited so you can see how things work. There is no support provided for this code, though am happy to answer questions and the code is obviously provided as is, with no warranty or guarantee of its functionality.

Too Simple?

There are literally thousands of Javascript image galleries and cool lightboxes out there. Preview is just here to show how to begin to construct your own, its not meant to be used seriously.

If you want something to make really good-looking images displays then start with one of these fine libraries: