07.23.10

AbleCommerce Tax Zone Modification

Posted in Web Development at 12:58 pm by BrianS

One of the features of AbleCommerce is the ability to charge taxes based on address. The feature is nice, but has one major flaw … the tax rate has to managed manually. We actually have only a few states where we are required to charge taxes, but one of those is California which is horribly convoluted. The problem boils down to, essentially, the need to track the tax rate for each zip code individually. You can’t even really use blocks of zip codes because a locality’s zip codes may not be consecutive.

AbleCommerce uses the concept of zones for assigning tax rates. For our purposes a zone can be applied based on state or a combination of state and a list of zip codes. Because of California’s tax rules and AbleCommerce’s tax rule management functionality we have to create a separate zone for each tax rate. The zip code list is just a comma-separated string of zip codes. Maintaining the information for the >1000 zip codes is difficult to do in this format.

I created a simple PHP-based page that keeps track of each zip code and its associated tax rate. While not perfect, this goes a long way towards simplifying maintenance of this information. A link to this page can be located in the AbleCommerce administrative section under Configure->Regions->Zones.

AbleCommerce has one other issue that has to be addressed. The default AbleCommerce installation limits a zone’s zip code filter to 255 characters (or about 40 zip codes) in the database and on the web form. Due to the situation with California taxes we have some zones that have significantly more than 40 zip codes and would require the creation of over 70 zones. To get around this problem I removed the web form limit and modified the database field ac_shipZones@PostalCodeFilter to be varchar(8000).

03.05.10

Rewriting URLs on IIS6

Posted in System Administration, Web Development at 6:27 pm by BrianS

One of the nice things about CakePHP is that it attempts to make a site more friendly to the average web site visitor by creating easier-to-remember URLs. There are a few techniques that CakePHP uses, but for the sake of this conversation we’ll focus on one way in particular: apache’s mod_rewrite functionality. Using mod_rewrite, URLs that would normally include the controller file (index.php) and a querystring can be rewritten as a simple file path.

Microsoft doesn’t include mod_rewrite-style functionality in IIS by default, though it has created an extension for IIS7. Since we are currently using IIS6 the extension isn’t an option for us. Fortunately there are a few other options available, including an open-source project called Ionics Isapi Rewrite Filter (IIRF). IIRF is an ISAPI filter that is very similar to mod_rewrite in terms of functionality. After some testing I’ve found this filter works almost perfectly for enabling CakePHP’s friendly URLs.

Getting Started

Installation is painless and requires only a few steps. The following is based on the IIRF 2.0.1.15 release which does not include an installer:

  1. Extract the files from the IIRF archive to a folder on the server.
  2. Open the properties of the IIS root “Web Sites” folder or the specific site that needs IIRF.
  3. In the “ISAPI Filters” tab create a new entry for IIRF; name the new filter (e.g. “IIRF”); specify the IIRF DLL located with the extracted IIRF files at \bin\IIRF.dll.
  4. Ensure that the IIS user has read/execute access to the IIRF DLL and read/write access to any directories that will be used for logging.
  5. Restart IIRF.

IIRF should now be installed, but I’ve found that sometimes the filter won’t appear to be active in the “ISAPI Filters” tab until it has been used the first time. To enable IIRF all you have to do is create a file called IIRF.ini and place it in the web site root folder or in the root of a virtual directory. This file contains any local configuration directives (such as log directory) and URL rewriting rules.

The obligatory gotcha

I mentioned before that the filter works almost perfectly. The only situation where I’ve had problems up to now is if the URL contains one or more space characters. The IIRF log indicates that a URL with a space is being parsed correctly and returning a valid URL. And yet the web server reports a 404 error.

I’ve only done testing when the final URL references a physical file, but based on a conversation in the support forum I suspect this problem also affects parameters in the querystring. I haven’t yet had a chance to fully investigate the issue, so I don’t have a work-around yet when the issue affects physical files. There does, however, appear to be a work-around for spaces in the querystring.

For now I have decided to ensure that any URLs parsed by IIRF that will point to a physical file/directory does not contain spaces.

References

CakePHP usage:

Space-in-URL problems:

02.26.10

Moving from Microsoft Access to MySQL

Posted in IERI, MySQL, Web Development at 12:58 pm by BrianS

We recently decided to make a public release of an old web-based application coded in ASP (classic, using VBScript) and using Microsoft Access. In order to make this application public we need to make a few modifications, not the least of which is moving from Microsoft Access to MySQL. Using Microsoft Access on the back-end would significantly hamper the ability of the application to support concurrent users, among other issues.

The majority of the coding modifications have yet to be made, but the database switch has already occurred. In the process of moving from MS Access to MySQL I discovered a few settings that would be helpful should this action need to be performed for other applications. These settings should enable similar applications to be moved with minimal modification to the programming.

First, let’s review some settings related to the MySQL ODBC driver. The settings are relevant to all versions of the driver, but the name of the setting may be different on different versions (I’m using 5.1.6). Here are the options which should be selected:

  • Return matched rows instead of affected rows
  • Treat BIGINT columns as INT columns
  • Enable safe options

The following information relates more generally to changes that may have to be made in the code:

  • MySQL doesn’t really support server-side cursors so the ODBC drivers fakes it. This is, mostly, fine except that some properties of the Recordset object are not available (namely RecordCount). In order to get full cursor support you should change the location from the server to the client (adUseClient; 3).
  • ASP doesn’t understand non-signed integers. This causes problems when performing operations using these values unless you manually type the value in your script, e.g. scriptvar = CInt(objrs(“dbcol”)). The other solution is to make all integers signed. Otherwise you will see the error: Variable uses an Automation type not supported in VBScript.
  • Finally, check your SQL statements for any VBA function calls. These will either have to be modified into MySQL-compatible function calls or removed from the SQL code altogether.

There are a number of issues that may be encountered when attempting to convert an ASP-based application from MS Access to MySQL. The issues addressed here are only those relevant to this particular application. Other applications may require additional or different solutions and settings.

Resources:

05.08.09

Subversion and Third-Party Code

Posted in System Administration, Web Development at 5:28 pm by BrianS

Often in the course of developing a project it is desirable to use code from a  third party. The main benefit, of course, is being able to add functionality without developing that functionality from scratch. Some third-party projects we have used in the past include FCKEditor (now CKEditor), the Yahoo! User Interface library, and, more recently, the CakePHP framework.

When our code is maintained in a subversion repository there are two options for including third-party code: externals and vendor branches. Read the rest of this entry »

04.17.09

CakePHP and CURRENT_TIMESTAMP

Posted in CakePHP, MySQL, PHP, Web Development at 3:15 pm by BrianS

As of cakePHP 1.2.1.8004 you can’t use CURRENT_TIMESTAMP as the default for a column. With MySQL when the default is set to CURRENT_TIMESTAMP the current date+time is inserted for a new row that doesn’t specifically define the value of the column.

From what I can tell, when CakePHP specifies the values for all columns when it creates a new record. A column with a defined default that is not specifically set by the user is manually set by CakePHP (rather than let MySQL handle defaults upon record insertion). But CakePHP doesn’t understand the CURRENT_TIMESTAMP keyword and so treats it as a string and wraps it in quotes. This breaks the resulting INSERT statement and you receive an error:

Incorrect datetime value: ‘CURRENT_TIMESTAMP’

Interestingly, columns that are named “created” and “modified” receive special handling by CakePHP. These columns are treated like auto-update columns by CakePHP and it sets them as expected. With the special handling of these columns in mind it is possible to get around the CURRENT_TIMESTAMP bug by following the recommended settings for created/modified columns, i.e. specifying the field as DATETIME with a default of NULL. CakePHP will automatically update the columns when inserting/updating records.

References:

Authentication & Authorization with Scaffolding

Posted in CakePHP, Web Development at 1:45 pm by BrianS

Though scaffolding is not recommended for production sites, I’ve found it quite handy when just getting started. Unfortunately, it doesn’t appear that the authentication/authorization (auth^2) mechanism works with scaffolding. You can, however, get auth^2 working manually with just a few lines of code.

First, follow the steps of the Simple Acl controlled Application tutorial from the CakePHP cookbook up to the section on logging in.

Next, we need to insert the code that updates the ARO when a user is added or edited. Normally you would place this code in your add/edit action in the users controller, but for scaffolded actions we’ll use another callback function. Add the following code to your users controller:

function _afterScaffoldSave($action) {
  $aro =& $this->Acl->Aro;
  $user = $aro->findByForeignKeyAndModel($this->data['User']['id'], 'User');
  $group = $aro->findByForeignKeyAndModel($this->data['User']['group_id'], 'Group');
  $aro->id = $user['Aro']['id'];
  $aro->save(array('parent_id' => $group['Aro']['id']));
  return TRUE;
}

Note: for scaffold callbacks you must return TRUE; or the scaffold will not finish building the page.

The above code has been modified from the original in the tutorial, which included a conditional that checked for a change in the user’s group. As far as I can tell scaffolding causes CakePHP to return only the updated record (even when using the _beforeScaffold method) so you’re unable to compare the old and new values. As a result you have to update the ARO with every update, even if the user’s group is not updated.

Finally, for scaffolded actions we need a way to determine if the user is authorized. The AuthComponent has all the functionality we need. Add the following function to any controller using scaffolding that needs auth^2:

function _beforeScaffold($action) {
  if ($this->Auth->user() == NULL && !in_array('*', $this->Auth->allowedActions) && !in_array($action, $this->Auth->allowedActions)) {
    $this->Session->write('Auth.redirect', '/' . $this->name . '/' . $action);
    $this->Auth->loginRedirect = array('controller' => $this->name, 'action' => $action);
    $this->redirect($this->Auth->loginAction, NULL, TRUE);
    return FALSE;
  } else if (!in_array('*', $this->Auth->allowedActions) && !in_array($action, $this->Auth->allowedActions) && $this->Auth->user() !== NULL && !$this->Acl->check($this->Auth->user(),$this->Auth->action())) {
    $url = '/' . implode('/',$this->Auth->loginAction) == $this->referer() ? '/' : $this->referer();
    $this->Session->setFlash('You do not have permission to perform that action.');
    $this->redirect($url, NULL, TRUE);
  }
}

This function checks to see if the user is logged in when accessing restricted actions. If not, the user is redirected to the login page. If so, and if the user is attempting to access a page for which he has no permissions, then the user is bounced back to the referring page.

Of course, you can skip all this if you build a skeleton CRUD using cake bake and specify not to use scaffolding.

02.20.09

Updates for IE8

Posted in Web Development at 4:00 pm by BrianS

With the release of IE8 getting closer I took a moment to check out our web site and Benchmarks Online in the new browser. As I suspected, the incompatibilities on our web site were of the same ilk as when IE7 was released and required minor updates. The updates to Benchmarks were also fairly minor, though did require a bit of effort to track down. Luckily IE8 includes developer tools right in the browser, making debugging web content much easier.

While I was at it, I decided to see how the JS+CSS was working in Safari (latest version on Windows and Mac) and Opera (latest versions on Windows). There were some minor issues with these browsers that I fixed as well. Quickly, Safari and Opera both were having trouble displaying the bullets in my ordered list when the pseudo multi-column-styling was applied. The odd thing is that the numbers were there, just not visible until an element was forced on top of them.

Finally, I decided to update the multi-column list code on the web site to that used in Benchmarks (which is more robust). I did run across one problem in the update that I may have to investigate further. Essentially, the script was not correctly positioning the second+ columns if a margin was specified on the container OL/UL.

02.06.09

Excel drops empty columns on CSV export

Posted in Web Development at 4:58 pm by BrianS

I’m often tasked with getting data from a flat format (Excel, CSV, etc.) into a database. When the format is Excel  and I have only a few files to work with I find it easiest to export the Excel file to CSV or TSV format. This makes it much easier to script the data conversion using PHP. There’s only one problem, Excel doesn’t always represent the total number of columns in each row. If there are blank values in the columns at the end of a row in the spreadsheet then the exported data may have fewer value delimeters than expected.

This bug has been documented by Microsoft for Excel 2003 and earlier. The solution given by Microsoft is poorly worded, but basically it says to make sure the cells at the end of a row always contain data. Not always a realistic proposition. Luckily I found a step-by-step solution that works perfectly:

Put a formula that evaluates to empty (=”" ) in the last column of  the rows that are empty:

  1. select the range in the last column: edit->goto, click on the “Special…” button, Choose “Blanks” and click OK.
  2. type the formula: =”"
  3. hit ctrl-enter

Now when you save the document the appropriate number of delimeters should be present, even in rows with empty cells.

01.16.09

Internet Explorer blocks Firefox downloads

Posted in Web Development at 12:16 pm by BrianS

When designing the school reports I decided to use a GET request on form submission so that it would be easier to bookmark or share a report. I knew at the time that using GET might cause problems down the road, but the utility/programming trade-off seemed worthwhile.

Long URLs

The main problem with GET is that the total size a URL is allowed to be (and thus the amount of data passed) is fairly limited when compared to POST. The exact limit varies by browser and web server, but a practical limit is the lowest limit across platforms. In this case that is 2083 characters in Internet Explorer.

Since each report has to pass the list of included packets and items, the URL could easily surpass this limit. Recently one user generated a report that went over the IE limit. Figuring this out, though, proved to be somewhat difficult.

Held up at security

The user in question was using Firefox to access the utility. The report in question was generating and displaying just fine. However, when the user tried to download the report an error would result. It seemed odd that the browser would display the report on screen without issue then error out when attempting to download the same report.

I tried many options, but no matter what I did I was unable to download the report in Firefox. Next I tried the same report in IE and the situation started to clarify. IE would not even display the report. So now I was beginning to think that maybe the GET request was causing problems. Still it was difficult to fathom Firefox displaying the report on screen but refusing to download it.

A little web research finally clarified everything. Firefox attempts to honor the Windows Internet security zone settings for downloaded files. In order to apply the security zone settings Windows has to pass the URL to the IE engine for analysis. Firefox allows URLs of significantly larger size than IE, so the same URL that displays correctly on Firefox will thus cause IE to error out. Since Firefox attempts to honors the security settings, when IE coughs up a hairball due to the URL length the download is aborted.

Solving the problem

Initially I thought to just shorten the request by modifying the variable names on the form. This worked well enough for the problem at hand, but I could easily see having to address the problem again in the future. In the end I decided to modify the code behind the page so that the report parameters are submitted by POST instead of GET. It required more work up front, but will prevent this problem from cropping up in the future.

Plus, the utility has an option to save reports, so bookmarking/sharing is not as much of an issue.

References

01.15.09

CakePHP, Apache, and Ampersands

Posted in CakePHP, PHP, Web Development at 2:44 pm by BrianS

I’m still learning CakePHP. My most recent experimentation is with forms and URL redirecting. In the process I noticed that if the URL includes an ampersand, even in URL-encoded form, CakePHP will read in the URL incorrectly.

In my case, I was taking a form submission and redirecting it to a new page, appending the submitted data as URL parameters (e.g. of the form /controller/action/name:value). CakePHP handles ampersand in POST-submitted forms just fine, but during the redirect I noticed that the value of the parameter was cut off at the ampersand. For example, I’m creating a search form for a list of items (located at /items/index). When you submit the form with a value of “ball & chain” the controller sees it just fine. The form is submitted to the search action (/items/search) which takes the values, constructs a new URL, and redirects back to the item list (/items/index/Search.keywords:ball+%26+chain). However, the index action only sees “ball ” … the rest of the value is assumed to be additional key/value pairs in the querystring.

This problem seems like a pretty major issue to me. A bug report has already been filed, but I don’t believe it will be addressed anytime soon. And for good reason, the bug is actually in Apache’s mod_rewrite module rather than in CakePHP. When mod_rewrite processes a URL it unescapes any escaped characters (newer releases unescape to two levels). What this means for CakePHP is that any escaped characters in the URL become normal characters in the querystring (%26 becomes &).

The best overview of the problem has a good work-around, but it requires modification of the core CakePHP scripts. I’ll leave that to the experts. On our install I’ve found that a triple-escaped value will only be unescaped twice, resulting in the proper escaping after being parsed by mod_rewrite.

References

« Previous entries Next Page » Next Page »