PHP MySQL Apostrophe Problem

 
Like Life, software is organized
into layers of interaction. Life
interacts on many levels; software
interacts on many levels.

This brings me to the PHP apostrophe
problem. In PHP apostrophes are
also single quotes. Therein lies
the problem.

Yesterday, I worked on a website with
a custom shopping cart. When I say
custom, I truly mean custom. This
was a lean, clean, shopping cart that
had been programmed from scratch as
far as I could tell.

There was absolutely no code bloat
at all. It was the bare-bones of
what you need and that is it. I
saw no evidence that it was either
an open source shopping cart or
a commercial software cart that
you buy and install. Whoever built
this shopping cart knew what they
were doing.

In any case, the website owner had
migrated the site to a new web hosting
company and a new web server. Ah, yes.
Problems in a new neighborhood. That's
an old problem, isn't it? You move into
a new neighborhood and you have to get
used to new surroundings and new people.

The new surroundings, in this case, was
probably an updated version of PHP. I'm
guessing. You be the judge.

In any case, the website owner found that
he could not update shopping cart items
with new prices. That was his primary
complaint.

In actuality, he could not update anything.
Photos of the item, descriptions of the item,
none of this would update.

Actually, some of it did update and some of
it did not. This was my first clue as to what
the problem might be. None of the fields would
update to the database. That is, none of the fields
would update unless they did. Then they all
updated.

In other words, he had some items that would update
and some that would never update. Curiously, he
had tried to add a fake item and it had updated fine
until he lengthened the description by cutting and
pasting from a description on an item that would
not work, that would not update.

The fake item updated fine until a cut and
paste to the item description introduced
description text from a non-working item.
Changing the description broke the item update.

When he told me this, alarm bells went off in my
head. I wondered if this was the aha! moment.
Would I be able to find a difference in the descriptions
that were successfully updating versus the descriptions
that would not? I decided to investigate.

I now had an image in my head. I imagined that
apostrophes in the description might be being
interpreted by PHP as a language feature. In
other words, PHP might be interpreting a single
apostrophe as an opening single quote.

If a single apostrophe were interpreted as
an opening single quote, this would have the
effect of commenting out everything thereafter.
This could even explain why photos would not
update.

Why would an apostrophe cause an unwanted effect?
Well, PHP often quotes things with single quotes.
When the PHP parser finds an opening single quote,
it goes looking for a closing single quote. If
it does not find a closing single quote subsequently
in the record, the record, right up to the end, is
effectively commented out.

I'm using commented out as an imprecise term
here. I really mean rendered ineffective. The
new fields entered into the item record were as good
as useless because PHP was not recognizing them as
fields. At least, that was my theory.

So, I decided to try my theory out. With the VIM
editor, i started doing copy and paste into the editor.
I was copying and pasting descriptions from the
administrative interface of the shopping cart into
Vim. Shopping cart item descriptions were copied
and pasted into Vim.

I had two categories of items I was examining with
Vim. Category 1 was items that would successfully
update. Category 2 were items that never updated.

I suspected that items that never updated had
apostrophes in the description. I expected to
find a total absence of apostrophes in descriptions
that could successfully be changed.

I tried my first item. I backslashed the apostrophe
and I changed the price of the item from 140 dollars
to 150 dollars. Voila! It worked. It worked
flawlessly!

I immediately called the website owner and told him
I thought I had solved the problem. I had worked
on this problem less than an hour. I immediately
negotiated a price to fix the problem. I told him
I thought he could get things working again by
putting a backslash in front of each and every
apostrophe.

We agreed on a price for the job and I suggested
that he let me try two more items to make sure
it really and truly was a good fix and I'd call
him back.

It turned out that the next two items also updated
flawlessly. So I called him back in 10 minutes
or so and suggested that he try it out over the
next 2 or 3 days. If he also found it to be a
good fix, I told him that he could then pay me.

We joked a bit and I told him how much I love
being paid. He said, "I love being paid too."

So now I'm waiting to hear back from the website
owner. If I don't, I'll call him.

Other website developers had suggested that he
scuttle the whole thing and start all over again.

He was unwilling to do that given that he has
such a beautiful site and it has worked so
well for him. I had suggested to him that he
not scuttle it.

It is the perfect site for a sole proprietor.
Simple, clear and to the point. No clutter.
Just a simple product description and shopping
cart site.

Easy to manage and easy to update. A perfect
site! Whoever did this site originally has
fantastic skills.

The website owner told me that the guy who
developed the site was someone who worked
for a large company but was temporarily out
of work. I'm not surprised he found a job.
He obviously knows what he is doing.

He partnered with a woman to do the site. I
assume that she was the web designer. The
site had a very lovely and very appropriate
color scheme.

It's a rarity when I see a site where you
get the whole idea of what the site is all
about in 5 seconds or less. This was one
of those sites. No need to investigate what
product they are offering. You just simply
know.

I'm so happy how this all turned out. I so
like to see people succeed.

I assume that the new web hosting company has
a different version of PHP running. I'm not
really a PHP expert but this is my assumption.
I assume PHP versions changed as a result of
the migration from the old hosting company to
the new web hosting company.

As I explained to the website owner, software
is organized in layers. Sometimes you have to
backslash a character to penetrate one layer of
software to get to the next.

In this case, the PHP layer needed the apostrophe
backslashed so that the apostrophe could make
it successfully to the MySQL layer with everything
intact.

A backslash removes the special meaning of the
apostrophe. In this case, the special meaning
of the apostrophe is that it is a quoting
mechanism. Apostrophes switchings hats and
becoming single quotes makes a quoting mechanism.
What lies between the single quotes is what is
being quoted.

The lesson here? Problems that at first appear
complex can in fact turn out to be very simple.
This one was very very simple.

The website owner found it hard to believe that
his photos were being quoted out of existence.
None-the-less, I believe this is the case. I
think that over coming days he will find this
to be true. I think he going to get his photo
update capability back.

We'll see.

Ed Abbott