Setting up Mac OS X Snow Leopard as an Apache, MySQL, PHP (MAMP) web server

When my old Ubuntu on a laptop web and file server died I decided to set up my Mac Mini with Snow Leopard as its replacement rather than finding another old laptop and building a new Linux server on it. I was afraid setting up an Apache, MySQL and PHP stack on Mac OS X would be complicated and messy, but it turned out to be pretty straightforward thanks to some good guides on the web.

My first step was to follow this how-to from the "Coolest Guides on the Planet" website:

Install and configure Apache, MySQL & PHP on OS X Lion 10.7 and 10.6

Then I followed another guide by the same person for setting up phpMyAdmin:

Installing phpMyAdmin on Mac OSX 10.8 Mountain Lion & 10.7, 10.6

I then went ahead and imported the backup of the database for my home-brewed web apps into MySQL using phpMyAdmin, and copied my PHP files into the web server directory and tested my apps.  I quickly ran into a problem where apps that had been working for years starting throwing fatal errors (sorry, I didn't write down what they were).  After poking around on the web I discovered that the MySQL installer you download from Oracle has STRICT_TRANS_TABLES turned on by default, which can cause problems for apps developed on Ubuntu because STRICT_TRANS_TABLES is turned off by default in Ubuntu.  I followed the directions on this blog post to turn off STRICT_TRANS_TABLES and then everything worked:

MySQL Strict Mode on OS X

After that, everything worked great.

How to escape from the View designer in MS SQL Server Management Studio (SSMS)

I started working with SQL Server, and MS SQL Server Management Studio about a year ago, and I quickly got frustrated with the View designer module.  If you click on "New View" in the object explorer, or if you right click on an existing View and choose "Design," it opens up this multi-pane view designer with a graphical representation of the View on top, followed by some table listing the View elements, followed by the SQL for the view formatted as nearly impenetrable blocks of code.  For simple Views the View designer was fine, but for complicated Views I came to hate working with it.  I especially hated the fact that if I tried to format the SQL in a more readable layout, the View designer would throw out my formatting the next time I opened the View in Design mode.

I finally decided that there had to be a better way, and thanks to some discussions on Stack Exchange (natch) I pieced together the following:

• You can edit a View in plain SQL, and have your layout and formatting preserved when you save, by doing the following:
• Right click the View
• Select Tasks -> Script View as -> ALTER To -> New Query Editor Window
• Make your edits in the resulting Query Editor window
• Hit Execute when you are done to save your changes.  The ALTER TO command replaces the current version of the View with your edited version.
• You can quickly clean up the brain dead SSMS formatting of the SQL of existing Views using the free poorsql.com website. You block and copy the ugly SQL into the window, choose from various formatting styles, and then the site instantly prepares a properly formatted version of the SQL.

How to export a table as a SQL script using Microsoft SQL Server Management Studio

I come from the PHP-MySQL world, so I am used to being able to quickly generate a backup of a table as a SQL script (CREATE + INSERT) using the easy to find Export function in phpMyAdmin.  When I had to  use SQL Server for a project I was a bit puzzled when I couldn't find a similar feature in Microsoft SQL Server Management Studio (MS-SSMS).  However, a bit of Googling revealed the (cumbersome) solution:

• In the Object Explorer in MS-SSMS right click the database that has the table you want to export.
• Select Tasks from the right-click menu.
• Select Generate Scripts from the sub menu, which will open a pop-up called Generate and Publish Scripts.
• Click Next on the Introduction screen, which takes you to the Choose Objects screen
• On the Choose Object screen select the "Select specific database objects" option
• Expand the Tables object by clicking the little plus sign
• Select the table(s) you want to export
• Click Next, which takes you to the Set Scripting Options screen.
• Choose where you want the output to go (file, clipboard or new query window)
• Click the Advanced button, which launches a pop-up window called "Advanced Scripting Options" with a list of options
• Scroll down to the "Types of data to script" option, which is at the very bottom of the set of options called  "General."
• Change the "Types of data to script" option from "Schema only" to "Schema and data."  This is what will make the SQL include the data from the table.
•  Click OK to close the Advanced Scripting Options pop-up.
• Click Next on the Set Scripting Options screen.
• Click Next on the Summary screen where it says "Review your selections."  This is what triggers the actual export of the table.
• Click Finish to close the Generate and Publish Scripts pop-up.

There, was that so bad? Well, yes, it was a lot of clicking for something that people probably do relatively frequently, but that is Microsoft for you.

The lineage model for hierarchal data in a SQL database

Recently I decided to make a web app to keep track of a number of policies.  There were a number of different policies, all of which had their provisions numbered and organized in an outline hierarchy that often went three or more layers deep.

The first thing I researched was how to structure SQL databases to handle hierarchical data like a product catalog with the products grouped into categories and subcategories. Or a org chart where employees are grouped into divisions and departments. I quickly learned that most people used either the adjacency list model or the nested set model,  both of which are described in detail in an article on the MySQL website: Managing Hierarchical Data in MySQL.  Another detailed article on both models is Storing Hierarchical Data in a Database.

With the adjacency list model you have a table which gives each record a unique ID number, and then you have a "Parent ID" field where you record the ID number of the record's parent in the hierarchy tree.  If you were doing foods, you would have a record for "apples" and the parent ID for that record would be "fruits."  Then when you need to generate an output of the whole hierarchy tree you do SQL that organizes all the records by connecting the various parent ID values.  Some articles I found on this approach include Tree Drawing with the Adjacency List Model  and Hierarchical SQL.  The thing I didn't like about the adjacency list model was that the SQL required to generate a simple display of the data seemed unduly complicated (lots of recursion) and likely to consume a lot of database server processing power.

I never did really grasp the nested set model, so I won't try and explain it. Needless to say it also involved very complicated SQL that seemed likely to put a big drain on a server.

Then I found an article about a variation on the adjacency list model: More Trees and Hierarchies in SQL.  The author's basic idea was to include a Lineage field in each record where you record the ID numbers of the full path to the record in the hierarchy with each ID number separated by a delimiter character.  For example, if you had a product tree with the iPod in it, the Parent ID field would be "MP3 Players" and the Lineage field would be "Electronics/Audio Equipment/MP3 Players."  This makes the SQL to output the basic tree much easier: you just do a simple SELECT and ORDER BY the Lineage field and you are done.

So here is the structure I ended up with using the lineage field model.  For a hierarchy like this:

• Plants
• Fruits
• Apples
• Bananas
• Peaches
• Vegetables
• Broccoli
• Asparagus
• Animals
• Poultry
• Chicken
• Turkey
• Meat
• Beef
• Pork

The table structure would be like this (except you would use ID numbers instead of the full name of each item as the ID):

Node	Lineage
Plants	/
Animals	/
Fruit	/Plants/
Vegetables	/Plants/
Poultry	/Animals/
Meat	/Animals/
Apples	/Plants/Fruits/
Bananas	/Plants/Fruits/
Peaches	/Plants/Fruits/
Chicken	/Animals/Poultry/
Turkey	/Animals/Poultry/
Beef	/Animals/Meat/
Pork	/Animals/Meat/
Broccoli	/Plants/Vegetables/
Asparagus	/Plants/Vegetables/

Once you put your hierarchical data into a structure like this then working with it is easy-peasy. Want to display the whole tree?

SELECT * FROM table_Foods ORDER BY concat(Lineage, Node)

This will output the data organized by groups and subgroups. Want to see just the items in the group Plants, organized by subgroups?

SELECT * FROM Foods WHERE concat( Lineage, Node ) LIKE '/Plants%' ORDER BY concat( Lineage, Node )

This will return just the Plants, grouped by Fruits and then by Vegetables.

Want to indent each item in your output based on its depth in the hierarchy? In your PHP code just count the number of slashes in the Lineage field using substr_count() for each record and set the indent accordingly.

I know this approach seems too simple but I have been working with it for a while in a web application and I haven't run into any dead ends yet, and figuring out the SQL for various tasks has been very easy.

Escaping square brackets in SQL Server queries from PHP

I have a PHP web app that pulls data from an SQL Server database using the ODBC functions. Earlier I come up with a PHP function to escape single quotes in user input by adding a single quote to each single quote (a single quote is the character you use to escape a single quote) as defense against (inadvertent) SQL injection (the web app is behind a firewall).

The other day I accidentally discovered that including text inside a pair of square brackets [like this] in user input that was added to a LIKE clause of a WHERE clause resulted in a huge data dump being returned by SQL Server. I did some Googling and discovered that characters enclosed in square brackets have some special meaning in SQL Server (I don't remember what it was). I first tried updating my PHP function to escape square brackets with single quotes, but that didn't work for some reason. Then I did some more research and discovered that the way to escape a square bracket in SQL Server is to enclose it in square brackets like this [[]. So I updated my PHP function to do this on user input and it worked to stop the data dumps when a user included something like [fred] in their input.

Writing the PHP to do this was tricky because if you just do a straight str_replace on each square bracket the second replace replaces some of the square brackets you added with the first replace and messes it all up. The way I solved this was to write my function to:

• first replace the left square bracket with an arbitrary three character string that is unlikely to be in user input,
• then replace on the right square bracket with []],
• then do a third replace of my arbitrary three character string with [[].

And yes, I know I should be using stored procedures etc, and hackers can get past any escaping routine, etc, but this app is behind a firewall and I am only concerned about accidental SQL injection.

SQL and sequences

I am working on an application that uses a sequence field to keep records in a specific order.

The first challenge was how to fill in a numbered sequence in the new sequence field without using auto-increment (the data table will contain a number of independent sequences for different sub-sets of records so I need to be able to multiple sequences in the same table).  I found the answer on this blog post:

How to Sequence each Sub-set of Records by David Soussan

I won't repeat that post here, but here is the SQL that I ended up with based on Mr. Soussan's technique.

This first SQL query creates a Temp table with Prov_ID and sequence number.

CREATE TABLE Temp
SELECT
    t1.Prov_ID, COUNT(t1.Prov_Sort_Num) AS sequence,
    t1.Prov_Sort_Num >= t2.Prov_Sort_Num AS flg
FROM
    tbl_Provisions AS t1
INNER JOIN
    tbl_Provisions AS t2 ON t1.Doc_ID = t2.Doc_ID
WHERE
    t1.Doc_ID = 1
GROUP BY
    t1.Doc_ID,
    t1.Prov_Sort_Num, flg
HAVING
    flg = TRUE
ORDER BY
    t1.Prov_Sort_Num

Then this second SQL query updates tbl_Provision using the sequence numbers from the newly created Temp table.

UPDATE
    tbl_Provisions AS t1
JOIN
    Temp AS t2 ON t1.Prov_ID = t2.Prov_ID
SET t1.Prov_Sequence = t2.sequence

The next issue was how to make sure that gaps and duplicates didn't end up in the sequence for each sub-set of records?  I found the solution to the issue of detecting gaps in this blog post:

Sequence gaps in MySQL by Sameer

I still don't fully understand how Sameer's SQL works, but it does indeed work reliably.  Here is the SQL I ended up with based on Sameer's technique:

SELECT
    a.Prov_Sequence + 1 AS start,
    MIN(b.Prov_Sequence) - 1 AS end
FROM
    tbl_Provisions AS a,
    tbl_Provisions AS b
WHERE
    a.Prov_Sequence < b.Prov_Sequence
   AND
      a.Doc_ID=$Doc_ID
   AND
      b.DOC_ID=$Doc_ID
GROUP BY
   a.Prov_Sequence
HAVING
   start < MIN(b.Prov_Sequence)

This returns a two column table with each row giving the beginning of a gap in the Start column and the end of that gap listed in the End column.

Links for articles about securing PHP web applications

Here are some articles I found about securing PHP web applications:

• http://net.tutsplus.com/tutorials/php/5-helpful-tips-for-creating-secure-php-applications/
• http://www.php.net/manual/en/security.php
• http://en.wikipedia.org/wiki/Cross-site_scripting
• http://www.ibm.com/developerworks/opensource/library/os-php-secure-apps/index.html
• http://www.devlounge.net/code/php-security

Transact SQL for including concatenated text fields in WHERE clause

I am working with a database on an SQL Server that carries forward a legacy table structure where the description field for a Purchase Order line item is spread across multiple text columns in the table.  In other words, instead of having one 250 character text field for the item description it has a total of 7 text fields of (I think) about 25 characters each.  I have a web application where users can search for purchase orders using the description fields.  My original solution was to check for the user's search term across each of the seven text fields using OR clauses.  However, this meant that a user's search term would not be found if it happened to straddle the border between two of the seven fields.

To solve this problem I tried concatenating all of the description fields and then testing the concatenated string using LIKE, but that resulted in some POs not being inlcuded in the results when they should have been.  I finally puzzled out that what was happening was that database treated empty description fields as null and Transact SQL gives a null value for the whole concatenation if you try to concatenate a number of valid string values with one null value.  In other words if you concatenate Field1 + Field2 + Field3, and the first two fields have text in them, but Field3 does not, then Transact SQL will give you a null result.  So the solution was to use ISNULL to convert null description fields to zero length strings when concatenating them for the WHERE clause, so that one null value doesn't ruin the whole concatenation, like so:

WHERE Field_1 + ISNULL(Field_2,"") + ISNULL(Field_3,"") + ISNULL(Field_3,"") LIKE %search-term%