I have been using phpGedView to provide some family members who are interested with access to my full family tree data. It has not been without incidents, a couple of years ago, a security flaw in it allowed my website to be hacked and defaced. This was not as bad as it seemed, because it allowed me to reorganise my website, splitting the different areas into their own sub domains. My family tree site became family.ijhedges.com and the on-line database became familydb.ijhedges.com. The theory behind this was that a vulnerability in one piece of software was less likely to impact the whole site again.

Anyway, back to phpGedView, the version I am running is quite old (but patched for security). When I first started using phpGedView, I didn’t have hosting with a database, so I had to configure the software to use index files. The later version, 4.x, no longer supported index files and required a database, so I was unable to upgrade when it was released. Although I have had hosting with database access for about 10 months, I had never revisited this option, content to leave it as it was.

Yesterday, I set up a new domain and configured the new version (4.1.5) to test it out. Installation was straight forward. I did configure the index and GEDCOM files outside of the navigable folder structure for security. After uploading my sanitised GEDCOM file and configuring the options, everything seemed to be going well. However, when I went to the welcome page along with the expected blocks of data, I had 8 error messages above the GEDCOM statistics module.

One of the errors was, the others looked similar:

ERROR:-2 DB Error: syntax error SQL: SELECT d2.d_year, d2.d_type, d2.d_fact, d2.d_gid FROM pgv2_dates AS d2 WHERE d2.d_file=1 AND d2.d_fact IN (‘BIRT’, ‘CHR’, ‘BAPM’) AND d2.d_julianday1=( SELECT MIN(d1.d_julianday1) FROM pgv2_dates AS d1 WHERE d1.d_file=1 AND d1.d_fact IN (‘BIRT’, ‘CHR’, ‘BAPM’) AND d1.d_julianday1!=0 ) ORDER BY d_julianday1 ASC, d_type; [nativecode=1064 ** You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SELECT MIN(d1.d_julianday1) FROM pgv2_dates AS d1 WHERE d1.d_fi]

I tried searching the phpGedView support forums, but the results were less than conclusive. Suggestions to update the version of MySQL were provided to some people asking about the error. This isn’t an option to me, I get what my hosting provider gives me, MySQL 4.0.27, which according to the requirements is sufficient. I met the other requirements too, so I had to take a different approach.

I started modifying the welcome page, turning off each module in turn until I identified which one was causing the problem. It turns out it was the GEDCOM statistics module. Now, this was still displaying underneath the errors, so I took a closer look at the data displayed. There are 4 fields for earliest and latest birth and death dates which didn’t contain any data, all the other fields displayed data. So, I customised the module and turned these fields off and the errors disappeared. Each field added 2 of the 8 errors. Looking closer the first 4 contained ‘BIRT’, ‘CHR’, ‘BAPM’ and the last 4 contained ‘DEAT’, ‘BURI’, ‘CREM’, so that made sense.

I began to wonder if I had some invalid data in the GEDCOM file that was causing the problem, so I used the built in GEDCOM checker in phpGedView to check my uploaded file. I did come across some date fields where I had entered a text comment instead of a date. These were for death dates where, in one case, I knew it occurred between a couple of years, and the other I had the day and month, but not the year. I found one in the death indexes and was able to correct this, but couldn’t find the other so had to estimate it, leaving the information I had as a source. After fixing several other missing source details, I recreated the GEDCOM file and used ResPrivita to sanitise it for living people.

Unfortunately, this didn’t resolve the errors with the birth/death dates, so I have left them turned off at the moment.

I still have to recreate the users/passwords for access to the data, upload and secure the unsanitised data, create a custom theme similar to my exisiting website, and let the user know its all changing, so its a way off of release yet.