Monthly Archives: September 2011

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?

The technology war

I recently attended some Google demo days and two interesting little facts were buried in the piles of other data.

JSON versus XML

Seems that Google is changing many of its APIs to no longer return data in XML fragments but to use JSON (Javascript Object Notation) to exchange data. Google claims its about 34% lighter in terms of bytes served to return JSON results than XML.

SOAP versus REST

Another technology change seems to be the large-scale move to prefer REST (Representational State Transfer) rather than SOAP for web services. I wasn’t aware there was a huge debate here. But looking online afterwards, there are endless flamewars, logs etc about this. But in a nutshell, if you don’t know about REST then its time to climb on the wagon as the Google machine is now all about REST architectures.


Google Maps Javascript API – An introduction with a focus on handling InfoWindows

Using the Google Maps Javascript API

Having final updated some map examples I had to version 3 of the Google Maps Javascript API, I ran into a few problems with the way InfoWindows had changed and as I searched for solutions, it seems many people are having problems with this part of the API.

In version 3 there was a move towards simplification, reducing in some instances what Google Maps did for you; most of these changes were seem to be around simplifying the API to make it lightweight for mobile devices. However, because of these changes you now need to do a little extra work yourself.

On the other hand, you no longer need an API key to use the scripts, which is a really nice change.

Below, I generated a few simple examples showing how to use the API and especially how to handle markers and their infowindows (the little popup quote bubbles common on Google Maps).

A Simple Marker Example

Lets look at the simplest example of adding a marker to a map (this is based on

First we include the new Javascript API in the header (no API key required which is great):

<script type="text/javascript" src=""></script&gt

Then we set our HTML to call a Javascript function, initialize to handle the map, along with setting up a div element into which we want to draw our map. In this example, we setting the div id to be map_canvas. Note the CSS styling of height and width is just there to constrain the size of the map we’re drawing.

<body onload="initialize()">
<div id="map_canvas" style="width: 450px; height: 450px;"></div>

And now the fun part, here is the initialize function which we use to set-up our map:

function initialize() {
    var myLatlng = new google.maps.LatLng(-0.09516,34.74733);

    var myOptions = {
        zoom: 10,
        center: myLatlng,
        mapTypeId: google.maps.MapTypeId.TERRAIN

    var myMap = new google.maps.Map(document.getElementById("map_canvas"), myOptions);

    var myMarker = new google.maps.Marker({
        position: myLatlng,
        map: myMap,
        title:"Hello there Kisumu!"

This is broken down into four key components:

  • A geographical data point (myLatLng), which is a LatLng object.
    • We want to point to a given place on the globe, in this case Kisumu, Kenya
  • Options for the map we want to display (myOptions), which is based on the MapOptions object.
    • There are plenty of options to set, but these are the required options. Zoom controls the zoom level of the map, the larger the number the closer in the map is zoomed. Center sets the center point of the map, in this case we’re using the latlng point we created. MapTypeId sets the map type, which can be one of: HYBRID (overlay of streets onto satelite images), ROADMAP (the more common street map view), SATELLITE (the Google Earth view) or TERRAIN (shows terrain and vegetation).
  • The Map itself (myMap), a Map object.
    • We create a map object, telling it where to draw itself via the id we set (map_canvas) in the div in the HTML and we give the map the options we set-up.
  • A display marker (myMarker), a Marker object.
    • Finally we create a simple marker telling it to point at our LatLng point on our map and with a given tooltip title. If you place the cursor over the point, you can see the title appear.

This will create a map like this:

You can check out the map here: examples/maps/simple_map.html. Show the source in your browser to see how it ties together. Feel free to make a copy and start playing around.

Adding an InfoWindow

In the previous example we have a map but this map is not very exciting. Typically, people want to be able to click on the points we’ve marked and show some extra information. This is done via Maps’ InfoWindow object, which handles the pop-up quote window common on Google Maps.

First, lets create a new InfoWindow object, like so:

    var myContentString = "<div id='pop_up'><h2>Kisumu, Kenya</h2><p>Kisumu is Kenya's 3rd largest city</p></div>";

    var myInfowindow = new google.maps.InfoWindow({
        content: myContentString;

Notice that the content of an InfoWindow can be HTML so you can style it, format it, include images, provide links etc to really have some rich data in there. Note: there is a max size for this content, so don’t include too much.

Next, we attach a listener to our marker (myMarker) to open this infowindow when the marker is clicked:

google.maps.event.addListener(marker, 'click', function() {,myMarker);

This gives a map with a nice InfoWindow:

You can check this out here: examples/maps/simple_map2.html

You may noticed that clicking on the marker brings up the InfoWindow popup but you have to click on the InfoWindow close box to close it. We could also change our click handler to automatically close the window if we click on the marker, using this code:

if (myInfowindow.getMap()) {
else {,myMarker);

That call to getMap will only succeed if the InfoWindow is open. There may be a better way to do this but so far this is the most reliable method I’ve found.

Adding Multiple Markers with InfoWindows

There is some confusion about how to handle multiple markers and InfoWindows. In the old API there was only ever one InfoWindow and you had a global array of markers to hand to query. In the new v3 API things are little more complex.

So there seems to be two ways to do this: multiple markers & multiple infowindows or multiple markers & a single infowindow.

Multiple Markers & Multiple InfoWindows

We’re going to load our markers out of a static array to keep things simple. However, you can easily load out of an XML, KML or other file. Theres examples of loading out of an XML file here:

First we’ll define some markers, giving the latitude and longitude, a title string and some info window contents.

var locations = [
    [-0.09516,34.74733, 'Point 1', '<p>This is Point 1</p>'],
    [-0.09526,34.75733, 'Point 2', '<p>This is Point 2</p>'],
    [-0.09506,34.73733, 'Point 3', '<p>This is Point 3</p>']

Then we’ll iterate over the markers and add them to our map, using a new setmarker function:

function setmarker(myMap, lat, lng, title, content) {
    var markerLatlng = new google.maps.LatLng(lat, lng);

    var myMarker = new google.maps.Marker({
        position: markerLatlng,
        map: myMap,
        title: title

    var myInfowindow = new google.maps.InfoWindow({
        content: content

    google.maps.event.addListener(myMarker, 'click', function() {
        if (myInfowindow.getMap()) {
        } else {
  , myMarker);

Theres not a lot new here, we turn our lat & lng information into a LatLng object, create a marker for it, we then create a new info window object and use it in the click listener for the marker.

However, because we have multiple markers we are also creating multiple info windows; one on each call to the setmarker function. Each marker is then associated with each infowindow when we create the callback click listener.

You can see this in the example as InfoWindows will remain open until you close them:

You can play with this example here: examples/maps/simple_map3.html

Multiple Markers and a Single Info Window

Finally, we’ll look at how to achieve the old style of InfoWindows, where you had just a single window, which involves a little more effort.

First we move creation of the InfoWindow out of the setMarker function and just create a single empty one:

var myInfowindow = new google.maps.InfoWindow();

Next we change the setmarker function as follows:

function setmarker(myMap, myInfowindow, locationId, lat, lng, title) {
    var markerLatlng = new google.maps.LatLng(lat, lng);

    var myMarker = new google.maps.Marker({
        position: markerLatlng,
        map: myMap,
        title: title

    google.maps.event.addListener(myMarker, 'click', (function(myMarker, locationId) {
        return function() {
  , myMarker);
    })(myMarker, locationId));

This code was quickly written so please allow me some flunkiness here (we could obviously clean up that parameter list for a start).

Basically we now pass our sole InfoWindow (myInfowindow) into the setmarker function along with the id (locationid) in the locations array. For simplicity we’ve moved the locations array outside of the initalize function so it can be found in the click callback, obviously that could be cleaned up to.

We now create a slightly different click callback, bound to our marker and the id of the location in the locations array. When the click is called, we associate the content of the correct marker (via the locationId) to the infowindow and then call to open the infowindow on the map associated with our marker.


You can play with this example here examples/maps/simple_map4.html

What Next?

  • Find map examples you like and look at the Javascript via ShowSource in your browser. YOu can quickly figure out how people are using the API this way.
  • Try changing the default pin marker icon to something else. There are lots of options here (see MarkerImage and the Marker options).
  • Use Google Earth to make polygon shapes for your map (to show an area rather than a point)and more 3D displays on maps.
  • Use Google Fusion Tables to mash-up data to overlay geo data and other data.

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: