CakePHP, MySQL, PHP, Web Development

CakePHP and CURRENT_TIMESTAMP

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:

CakePHP, Web Development

Authentication & Authorization with Scaffolding

1 Comment

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.

Web Development

Updates for IE8

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.

Web Development

Excel drops empty columns on CSV export

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.

Web Development

Internet Explorer blocks Firefox downloads

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

CakePHP, PHP, Web Development

CakePHP, Apache, and Ampersands

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

Web Development

Your float is showing

Float styling is a useful aspect of CSS … until it’s not. When a floated element occurs inside a container and you want it to be wholly in that container you have to resort to hacks to do so. Or do you? Never content to assume that a question has been resolved, I decided to see if anyone had come up with a new method of clearing a float. Lo and behold a solution has emerged, and the solution is so simple as to defy logic: apply overflow: auto to the container element. I’ll leave the full explanation (with examples, alternatives, and warnings) to the experts.

Of course, there can be advantages to the default float rendering. Most notably I found it useful for creating some nice formatting around blockquotes, such as in our textbook analysis report.

Adobe Illustrator, Web Development

CSS Quirk: opacity

Elements with opacity styling are stacked independent of their containing object. As a result, any positioned elements that do not have a z-index declaration and that appear in the HTML code prior to an opacity-styled element will appear underneath the opacity-styled element (see example). This is the defined behavior in the CSS specification, though currently only gecko- and WebKit-based browsers (Firefox, Safari, Chrome) exhibit the “quirk” in question. From the CSS3 Working Draft:

… implementations must create a new stacking context for any element with opacity less than 1.

CSS Color Module Level 3: Working Draft 21 July 2008

MySQL

SQL Tricks: WHERE column IN (SELECT …)

I was trying to find a way to update some rows in a MySQL table where the condition for performing the update was based on a column in another table. Normally this isn’t an issue, just run UPDATE table ... WHERE key_column IN (SELECT key_column FROM ...).

I was having some problems, however, because the table that needed to be updated uses a mutli-column primary key. Normally I would select the relevant rows using a JOIN. Unfortunately, you can’t update the results of a JOIN. How then to select the relevant rows?

Turns out it’s easier than I thought. You can still use the WHERE ... IN clause, you just specify all of the columns that satisfy the condition: UPDATE table ... WHERE (key_column1, key_column2) IN (SELECT key_column1, key_column2 FROM ...).

I did not see any information about this functionality in the MySQL Reference Manual (1, 2).