'where' syntax problem in opened emails test

Hi Jerry,

I'm coding blind, but try this.

Instead of outputting this: open.php?$recnum&...
output this: open.php?num=$recnum& (It's easier to get at from PHP).

Then replace this:
mysql_query("UPDATE {$TABLE_PREFIX}exhibition_email_test SET opened = '1' ");
mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");

With this:
$numAsInt = int_val( @$_GET['num'] );
$query = "UPDATE {$TABLE_PREFIX}exhibition_email_test SET opened = '1' WHERE num = $numAsInt";
mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");

You need to always escape or sanitize user input to prevent hackers from tricking the script into running their own MySQL. Instead of calling mysql_real_escape_string() or a related function I called int_val() which forces the value to be a number and accomplishes the same thing.

Also, since the record number should be a unique identifier for the record we don't need to check for equality on the title. Which is good because even if you change your record title in the time between sending out the newsletter and when the script is called everything will still work (since the record number doesn't change).

Also, If you want to prevent your script from outputting a broken image tag (will show up in some browsers) you can put this just before the exit;
header('Content-Type: image/gif');
echo base64_decode('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7'); // 1x1 transparent gif
exit;

As I said, I'm coding blind so the code might need a bit of adjustments but should be a lot closer. Let me know how it goes. Cheers!

Hi Dave,

Thanks for taking the time to respond to this.

The ‘opened field’ doesn’t update at all now, but I’m sure it’s really close.

The code that calls the blank image is now <img src='http://www.artistsofpalmbeachcounty.org/open.php?num=$recnum&amp;opened=1&amp;email=$the_to&amp;project_title=$where' height='1' width='1' alt='' />

And the rendered code in the email source that's passed to open.php is now:
<img src='http://www.artistsofpalmbeachcounty.org/open.php?num=1&amp;opened=1&amp;email=jerry@jkwebdesigns.com&amp;project_title=Test Exhibition' height='1' width='1' alt='' />

Here’s the current code in open.php
<?php
//Dave's first suggestion


$numAsInt = int_val( @$_GET['num'] );
$query = "UPDATE {$TABLE_PREFIX}exhibition_email_test SET opened = '1' WHERE num = $numAsInt";
mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();
exit;
?>

The “opened’ field in the exhibition_email_test editor is a checkbox.

I’ve attached both the open.php and the exhibition_mail_test1.php files in case something jumps out.

Thanks,

Jerry Kornbluth

Hi Jerry,

The next step in debugging something like this is to try and reduce the problem area. So for example we can copy and paste the open.php URL into the browser and add some code to output the values from $_GET or the query we're generating. Then we can determine if the problem is with that component or with something else.

When I do that with this url:
http://www.artistsofpalmbeachcounty.org/open.php?num=1&opened=1&email=jerry@jkwebdesigns.com&project_title=Test%20Exhibition

I get this error:
Fatal error: Call to undefined function int_val() in /hsphere/.../open.php on line 12

Which indicates there was a typo in my (blind) coding! Try intval instead of int_val and then reload that page and see if you get any errors. And if needed you can use print_r($_GET); to see what variables are being received by the script.

Hope that helps!

Hi Dave,

Looks like intval was the issue...

As always and again... Thanks for your help.

Jerry Kornbluth

Hi Dave,

I probably should try to get this working myself, but the page is live and I really don't want to mess it up.

I need to reset the updatedDate to reflect the time when the email was opened, which I assume would require inserting:updatedDate = NOW() into the code below, but I'm not sure how to insert it correctly.

Existing Code:<?php
$numAsInt = intval( @$_GET['num'] );
$query = "UPDATE {$TABLE_PREFIX}client_uploads SET opened = '1' WHERE num = $numAsInt";
mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();
exit;
?>

Thanks for your help.

Jerry Kornbluth

Hi Jerry,

Try this:

$numAsInt = intval( @$_GET['num'] );
$query = "UPDATE {$TABLE_PREFIX}client_uploads SET opened = '1', updatedDate = NOW() WHERE num = $numAsInt";
mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();
exit;

Thanks Dave,

I feel much more secure about changing the code now.

Jerry

Hi Jerry,

That's interesting, I haven't heard about that one yet. Sounds like facebook is using it with the bgsound tag:
http://pandodaily.com/2012/03/06/facebook-knows-when-you-open-their-emails-how-creepy-silent-sounds/

In general you want to avoid using <head> content and javascript in emails. Many email clients strip all that out. If email clients allowed javascript then hackers could do all kinds of bad things to the email recipients.
See: http://blog.exacttarget.com/blog/email-design-4/email-design-tip-of-the-week-javascript-in-email

I'd start with a working sound file (not a php) and try sending tags to yourself that actually play the sound. If you get that part working, then just swap it out for the php file that delivers the sound file.

Hope that helps!