In-Depth Example—Server-Side Database

In 2004, I received an email that appeared to come from a well-known, legitimate company inviting me to click on various links and look at their current offers and promotions. It caught my eye in part because a friend was working for that company at the time and because the links on the page pointed to a totally different domain. More than that, the links had an unusual format. Here are five examples of that:

Several features emerge when these URLs are compared. The first component of each hostname is different but other components are identical. Running dig on each of these showed that they mapped to two IP addresses, also used by the name http://track.soak-up-the-sun.com. The most likely explanation for the use of multiple hostnames is to prevent anti-spam software from recognizing the hosts.

The server-side script has a very distinctive name, _c.jpegg, which the casual observer might take for a JPEG file. Two other URLs in the email message had scripts called _o.jpegg and _r.jpegg. There would seem to be no good reason why a legitimate site would name its scripts in this cryptic way.

This script has four arguments: cid, ln, kin, and urlid. The first three have the same values in each of the example URLs, whereas the value of the fourth increases by one with each instance. I figured that cid and kin represent a customer ID and mail campaign, or message, ID. The urlid parameter presumably specifies a unique item or catalog record about which this link will retrieve information. The choice of the parameter name does, however, suggest that it might identify a unique URL. But in that case, why not just use that URL in place of the server-side script?

Clicking on any of these links would return a page from the legitimate web site. So http://soak-up-the-sun.com was not capturing any account or credit card information, as would a phishing site. But acting as an intermediary between the original spam and the target site, it could record my email address, in the form of one of those parameters, as well as the particular URL in which I was interested.

Two possibilities come to mind in a case like this. The site could have been providing a customer tracking service for the legitimate site, allowing it to record the addresses of people that were interested in its various products. But the target site here is a substantial company and would most likely have in-house resources that could provide the same service. The alternative would be that the intermediary site was using spam and subterfuge to build a custom mailing list of people interested in the target company. This could be very valuable information for any of its competitors. Subterfuge was certainly being employed to some degree, in the form of the cryptic script name and the prefix on the server hostname.

Given that the parameters passed to the script in the URLs appeared to contain identifying information, the first step in probing its function was to enter the URL with no arguments at all. In most cases, this would trigger a basic error message from the script itself or perhaps a response from the web server of 500 Internal Server Error. In this case, however, the response was considerably more detailed:

    Fatal Error: Could not insert -1.
    Date/Time: March 12, 2004, 12:04 pm
    A fatal error has occurred in the procedure: AddStat()
    All script execution has halted.

    MySQL 
 Error(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
    '-1_Table (ListNameID,EmailID,TimeStamp,IP,TypeID) VALUES (-1,-1

    MySQL Query: INSERT INTO Campaign_-1_Table (ListNameID,EmailID,
    TimeStamp,IP,TypeID) VALUES (-1,-1,'1079111058','66.134.177.170',1)

    Please contact the system administrator Admin.
    You may return to the previous page by clicking here [ Go Back ].
    - admin is : admin@opmtrack.com
      XSmarterTrack V2.0

There is so much information in this message that it’s hard to know where to start. First of all it tells me that the script was not doing much in the way of validating its input parameters. The fact that none were specified should not cause a script to fail like this.

I cannot tell whether this is a Perl or PHP script. I might have been able to do so if I had logged the HTTP headers associated with the error message. But it is clear that the script is interacting with a MySQL relational database. That alone suggests that the site is recording information about the users that click on the links in their emails.

The lack of input parameters has been ignored by the script, but the corresponding internal variables have been passed to MySQL. One of these has triggered an error at the level of a SQL statement when that is executed by the database. The script has reported the error from within the function AddStat(), the name of which suggests that it might add a statistic to the database.

The specific error was that it could not insert -1, suggesting that a negative integer is not valid for one of the columns in this database table. This must be the default value assigned to a variable in the script because nowhere have I supplied this as an input parameter.

The most informative part of the message is the SQL statement that caused the database error. Here it is reformatted to make it easier to dissect:

    INSERT INTO Campaign_-1_Table
    (ListNameID, EmailID, TimeStamp, IP, TypeID)
    VALUES
    (-1, -1, '1079111058', '66.134.177.170', 1)
    ;

If you are familiar with relational databases, you will recognize this as a standard SQL INSERT statement that adds a record to a specific table. The syntax of this is INSERT INTO < table name > ( <columns> ) VALUES (< values >);, where < columns > and < values > are comma-separated lists with matching numbers of elements in both.

In this example, the table name is Campaign_-1_Table. The appearance of -1 in the name suggests that the value of one of the input parameters would normally be used to define the database table name. The parameter cid is a likely suspect as its name might represent campaign ID. If so, the correct table would be named Campaign_7848608_Table. The use of the word “campaign” implies some involvement in marketing or sales, with the database perhaps being used to track the success of a marketing campaign.

The SQL statement inserts five values into columns in this table:

ListNameID

Value: -1

The name of this column does not immediately suggest its role. But comparing these columns with the script parameters, it most likely stores the values supplied in the urlid parameter. This would likely be the unique identifier for a record in another table that stores the actual URL to which the user will be redirected.

EmailID

Value: -1

The name might suggest that it contains an email address for the person invoking the script. But none of the regular parameters passed to the script contain an address. Instead, one of the parameters—probably kin—might specify a database record in another table that contains my email address.

TimeStamp

Value: 1079111058

This denotes when the script was executed, in the form of the number of seconds since a defined point in time, called the epoch.

IP

Value: 66.134.177.170

This is the IP address of the computer I was using when I triggered this error.

TypeID

Value: 1

This role of this column is unclear. Perhaps it is used to specify certain classes of links that the system is able to track. It may refer to the ln script parameter, which had the same value as this.

At the bottom of the error message is what appears to be a reference to a software package called XSmarterTrack V2.0. Google’s indexes contain no reference to this package, so perhaps it is the name of an internal system used by this company.

The level of sophistication suggests to me that this intermediary web site and database are part of a commercial operation that runs bulk email campaigns on behalf of its clients. The company maintains a database of email addresses and generates email messages with URLs that can identify the user that clicked on them. By storing all responses in a database, they have the potential to report detailed statistics to the client regarding the most popular products, services, and so forth. They are able to identify who was interested in the products and report the email addresses of that audience. Using the IP address information, they have the potential to identify where these users are located, at least in general terms. If they chose to do so, they could report the specific products in which a single individual expressed an interest.

Many online businesses, such as Amazon or eBay, operate sophisticated customer tracking systems like this. What I find disturbing about this example is that the tracking is happening outside of the client company. Nowhere on the initial email or the company web site was there any statement that a third party was involved. That lack of disclosure, their effort to obfuscate host and script names in the email, plus the fact that the original email was not solicited, cast doubt on the legitimacy of the tracking operation.

In this case, I was able to follow up with the target company, and they confirmed that they were indeed using a third-party company to monitor the response to an email campaign. But the association with spam and the seemingly covert gathering of customer data were viewed as potentially damaging to the reputation of the company. As result, they no longer use this type of service.