Translating Geek Into English

In this edition…

• About The Previous Newsletter
• About This Newsletter
• On the Web => Typenator, Text Expander, and Breevy
• On the Blog => E-Commerce Stored Procedures, Continued
• Q&A => How do I Secure Sensitive Information on Shared Hosts?
• Q&A => When Should I Create MySQL Indexes?
• What is Larry Thinking? => Proxy Scripts
• Book Giveaway => Results
• Larry Ullman’s Book News => “Effortless E-Commerce”, “PHP for the Web”, and More!

About the Previous Newsletter

First, I should point out that I’m not going to start every newsletter with a discussion of the previous one. But because the last newsletter used a new system (mandated, in part, by problems with the previous newsletter), quality issues are still on my mind. I think the previous newsletter was relatively glitch-free, so I feel comfortable moving ahead with this new system (it’s Campaign Monitor). If you have any problems with this or future newsletters, please do let me know. Thanks!

About This Newsletter

A new year and a new newsletter. Hurrah! After the last newsletter of epic length (Tolstoy thought it a bit wordy), I’m striving to keep this newsletter down to a tidy 14 or 15 pages (it turns out that it’s only, oh, about 400 words shorter than the last one—after cutting a section).

In this newsletter you’ll find answers to some questions and a good deal of code. There’s a slight theme on how to secure and provide site content. As always, questions, comments, and all feedback are much appreciated. And thanks for your interest in what I have to say and do!

On the Web => Typenator, Text Expander, and Breevy

One reader, Josée, in response to the previous newsletter, had recommended to me a program named Typinator. Typinator is one of many word/abbreviation replacement utilities, although this particular one is for a Mac. Typinator runs in the background all the time and “listens” to what you type. When you type a significant series of letters, Typinator will automatically replace that sequence with something else. For example, in my “PHP for the Web: Visual QuickStart Guide” book (I’m currently writing the fourth edition), I use this code to test for form submissions:

if ($_SERVER['REQUEST_METHOD'] == 'POST') { …

That code checks if the page has been requested via POST, which would be the case when a form that uses the POST method is submitted. Anyway, that’s kind of wordy to repeatedly type, so I’ve created the keystroke shortcut “srm”. When I type those three letters in any application Typinator swaps in the full text. Similarly, I’ve assigned “phpvqs” to be “PHP for the Web: Visual QuickStart Guide” and “eec” to “Effortless E-Commerce with PHP and MySQL”. You can take this idea as far as you want, including longer blocks of text, formatted text, and text with placeholders (i.e., for values to be entered into boilerplate copy).

Typinator also comes with “sets”: defined groups of abbreviations. Two correction-based sets define over 3,000 replacements for common spelling errors. Now, whether in Scrivener or my email application, if I accidentally enter “wtih”, it will be replaced automatically with “with”. Sweet!

The idea behind a text replacement utility is fantastic and well worth your consideration. Typinator itself may not be the best choice, though (around the time Josée mentioned it to me, I coincidentally ended up buying Typinator as part of a large bundle). I’ve heard very good things about TextExpander, although have not used it personally. For Windows, there is Breevy, among others.

People often wonder how I’m able to do everything I do. Part of the reason is that I no longer have hobbies or down time. A bigger reason is that I try to make the most of what my computer can do, including automating everything I can. Not writing 20 characters may not seem like much, but in time it adds up. Plus there’s a lot to be said for guaranteeing that things will be spelled correctly!

On the Blog => E-Commerce Stored Procedures, Continued

In the second e-commerce example in my “Effortless E-Commerce with PHP and MySQL” book, stored procedures are used for all of the public-side database-related functionality. This includes: the displaying of product categories, specific items, and sale items; cart and wish list management (adding items, updating quantities, removing items); and order submissions. However, not everyone can use stored procedures, in particular those on some shared hosting environments.

One reader recently said (in the forums) that they couldn’t use stored procedures with their host, so I volunteered to rewrite some of the stored procedures as standard PHP and MySQL. In the first post towards that end, I rewrote the procedures, and corresponding PHP scripts, from Chapter 8, “Creating a Catalog”. In the second post, I rewrote the procedures from Chapter 9, “Building a Shopping Cart.” In the third post, I rewrote the procedures from Chapter 10, “Checking Out”. That covers all the stored procedures for the book, but I have more e-commerce-related extras on the way.

Q&A => How do I Secure Sensitive Information on Shared Hosts?

John had asked this question, which is one well worth discussing. Some information placed on a Web server is of a more sensitive nature than others: for example, the configuration file, a script that defines the database connectivity parameters, and files or directories with “open” permissions (so that PHP can write to them). Ideally, such sensitive items should be stored outside of the Web root directory. The “Web root directory”, if you’re not familiar with the phrase, is the folder on the server where your domain points. In other words, if you go to www.example.com in your Web browser, from what directory on the file system will files be served?

A Web server, by its very nature, is intended to make content available. By default, anything within the Web root directory is accessible via HTTP by anyone on the Internet. Sensitive files and folders, like those already listed, shouldn’t be available to anyone and, in fact, don’t need to be. A script that contains the database connectivity parameters is not intended to be invoked directly; it’s intended to be included by the scripts that are directly called. This is why I suggest placing such things outside of the Web root directory: doing so limits access and these items don’t need to be available over HTTP anyway.

If you cannot place files and folders outside of the Web root directory, which is common enough among shared hosts (particularly cheap ones), what can you do to protect yourself? The answer is to try to limit access however you can. For example, you could create a folder named private and place all sensitive files and folders there. Ideally, you would then use the Web server’s .htaccess feature to block all access to that folder:

# Disable directory browsing:
Options All -Indexes  
# Prevent folder listing:
IndexIgnore *  
# Prevent access to any file:
<FilesMatch "^.*$">
Order Allow,Deny  
Deny from all 
</FilesMatch>

That code, in a file named .htaccess, residing in a directory would deny access to that directory’s contents. (This is Apache-specific; other Web servers use different code.) Also, to clarify a point of confusion, this approach does not prevent a PHP script on the server from accessing that content through include() and require(), as those requests go through the file system, not through the Web server.

I will also point out that placing database connectivity information or a writable file or directory within the Web browser, even if unprotected, is not a huge security risk. A PHP script that defines some variables or constants, even if run directly, wouldn’t show anything at all (unless the script actually printed those variables and constants, which makes no sense). The only way the PHP code in such files would be readable would be if you didn’t use the .php extension or if there was a problem with the Web server so that it wasn’t properly processing PHP scripts. In either rare case, you would be vulnerable, which is another reason why placing sensitive items outside of the Web root directory is more secure: those items are protected even if there’s a problem with the Web server application itself.

Finally, understand that a writable file or directory at worst is only writable by users on the server; such things aren’t writable by anyone on the Internet.

Q&A => When to Create MySQL Indexes?

Although I’ve written about the subject of MySQL indexes in various books of mine, it’s a topic always worth rehashing and Steve recently asked about them as well. Simply put, creating the proper indexes for your tables improves the performance of your database (well, for SELECT queries anyway, which are the most important).

To start, you should index columns that are:

• The primary key
• Frequently used in WHERE clauses
• Frequently used in ORDER BY clauses
• Frequently used as the basis for a JOIN

You should not index columns that:

• Allow NULL values
• Have a very limited range of values (such as Y/N or 1/0)

The other thing to keep in mind is how the index is defined. For example, if queries might sort by last name, you could index just the first few characters of the last name column. By doing so, you get the performance benefit of treating the column as if it were of fixed-length. You can also create indexes on more than one column at once. For example, you might create an index on the combination of the email address and password (for logging in purposes). If the index was created with the columns named in that order, you would also effectively be creating an index on the email address by itself, which could be beneficial. The same goes for indexing last_name, first_name: that creates an index applicable to ORDER BY last_name, first_name as well as one that applies to just ORDER BY last_name.

To confirm that you’ve got the right indexes for your database, use the EXPLAIN command to verify how well MySQL is using the indexes you’ve created for the queries your site executes.

What is Larry Thinking? => Proxy Scripts

Proxy scripts are an extremely useful tool for dynamic Web sites but many beginning developers aren’t that familiar with them. Arlene thought it might be useful for me to write a bit about the subject, and so I am…

A proxy is something used in place of something else. You can, for example, access Web sites through proxy servers. By doing so, your identity is kept hidden from the destination Web site. Through a proxy you can also access sites that might otherwise be blocked (e.g., your company might have a firewall that denies access to www.example.com, but you could get to www.example.com by going through Free Proxy Server).

Similarly, dynamic Web sites use proxy scripts as an alternative way to provide access to content. Whereas a proxy server might be used for privacy, a proxy script generally adds security. How about some specifics…

Say you have somefile.pdf on your server. You could make this page directly available to the public at large by linking it: www.example.com/files/somefile.pdf. But what if you wanted to restrict access to that file to only registered and logged in users? In that case, you’d need to block access to the files directory (by using the .htaccess file discussed under “Q&A => How do I Secure Sensitive Information on Shared Hosts?”) so that no one can access anything in that directory. Then you would use a proxy script to provide the file when appropriate. That script would first validate that the user has permission to access the file, presumably by checking a session or cookie. If the user doesn’t have permission, the proxy script would display an entire HTML page, indicating their access is denied. If the user does have permission, instead of sending HTML, the proxy script sends the actual file. Doing so is a multi-step process (this code is taken from the first example site in my “Effortless E-Commerce with PHP and MySQL” book):

1. Send a Content-Type header:
   header('Content-Type:application/pdf');
2. Send a Content-Disposition header:
   header('Content-Disposition:inline;filename="somefile.pdf"');
3. Send a Content-Length header:
   $fs = filesize(‘files/somefile.pdf');
header ("Content-Length:$fs\n");
4. Send the actual file:
   readfile('files/somefile.pdf');

Note that when the proxy script sends the file, it sends no other data at all: no HTML, no blank spaces or lines, nothing. Just some headers and the file.

That code, as written, is a proxy for a specific file. With some simple modifications, the same proxy script can be used to send any file. The script would normally receive an argument in the URL indicating what file to serve. This could be a filename—<a href="proxy.php?name=somefile.pdf">Download the File</a>—or an ID value for the associated database record: <a href="proxy.php?id=23">Download the File</a>. In either case, the proxy script must confirm that the file exists and is appropriate to be sent to the user. By that I mean the script must ensure that the file is in the appropriate directory or is of the appropriate type. The script should prevent a malicious user from downloading other files on your server using the proxy script.

In describing this scenario, I wrote of serving files otherwise found in the Web root directory, but another common use of proxy scripts is to serve files securely stored outside of the Web root directory. For example, if you create a writable directory where file uploads get stored, it’d be best to place that directory outside of the Web root (so that someone couldn’t upload a dangerous file and then execute it). When files are stored outside of the Web root, you must use a proxy script to make them available, as anything outside of the Web root is outside the reach of the Web server by definition.

So that’s the basic idea behind a proxy script. If done correctly, it’s very simple. And keep in mind that you’re not limited to just serving PDFs this way: you could proxy any kind of file, so long as you change the Content-Type header to match the file’s proper MIME type. And because this is a PHP script, you could incorporate other logic or functionality into it, such as counting each download of a file (presumably by updating a field in the associated database record), adding output compression, and so forth.

Book Giveway => Results

I had a wonderful response to the December book giveaway, with most of the interest leaning towards my “Effortless E-Commerce with PHP and MySQL” book (not unexpected). Thanks to everyone for their interest in my books and for participating. I’ll probably do my next book giveaway in a couple of months, when the fourth edition of my “PHP for the Web: Visual QuickStart Guide” is printed.

If you wanted a free book but didn’t get one (which may also mean that you’d like to read the book without paying for it), I always recommend you check with your local library. If they don’t carry the book, you can request that they acquire it or perhaps get one through an inter-library loan.

Larry Ullman’s Books News => “Effortless E-Commerce…”, “PHP for the Web”, and More!

As mentioned in my previous newsletter and earlier in this one, I’ve been writing some extra code and blog posts in support of my “Effortless E-Commerce with PHP and MySQL” book. You can find all of this and any other extra that comes up specifically for that book through www.LarryUllman.com/tag/ecom/. I’ve also been writing some articles and blog posts for Peachpit Press’s Web site, all supporting the “Effortless E-Commerce with PHP and MySQL” book. The blog posts will discuss the five most important security criteria for e-commerce sites, similar to a question that I answered in a blog comment. The four articles will explain how to implement extra features in an e-commerce site. The specific features are: adding customer reviews, product recommendations, an Ajax-enabled shopping cart, and an Ajax-enabled product rating system. I’ve written a couple already, but they need to go through an editorial process. I’ll post links to them on my Web site as they go live.

I’m just about done with the entire first draft of the fourth edition of “PHP for the Web: Visual QuickStart Guide”. Along with fixing any minor problems, updating the code for the latest version of PHP, and adding a “Review and Pursue” section to the end of each chapter, I’ve been able to add a new chapter. That chapter will replace the original Chapter 13, “Regular Expressions,” as the ereg() function has been deprecated in PHP 5.3. The new chapter will be titled “Putting It All Together” and will show how to use everything covered in the book to assemble a mini-Web application. The chapter will be similar to the three popular example chapters in my “PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide” book, although using a different example.

My self-published JavaScript book is still on the docket and should be actively pursued starting in a couple of weeks. I have a couple of months before I’ll need to work on the fourth edition of my “PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide,” so I’ll try to do as much as possible on the JavaScript book in that interim. As I doubt I’ll be able to complete and publish the book in the next two months, I’ll probably post what I have online in HTML format as I go. I’ll continue to post status updates on this project in the newsletters. Thanks to everyone for their continued support and interest on this new book!