Converting a script to work with a database.

[rik72]

Hi, i'm basically having problems with this code, its for a newsletter script which added the email address into a file, i'm trying to convert it to work with mysql but having a few problems;

 

I've edited the last bit, the code just basically doesn't add it to the database even though i thought i had done it right...

 

any help is very much appreciated!

 

If email is not valid the script is letting me know; if email is valid it says "already added to the list" no matter what.

 

old script

<?php
/** BY WebResourcesDepot - http://www.webresourcesdepot.com*/
/** YOU CAN EDIT HERE*/
$newsletterFileName = "file.txt";

/** IMPORTANT: EDIT BELOW UNLESS YOU KNOW WHAT YOU ARE DOING*/
function GetField($input) {
    $input=strip_tags($input);
    $input=str_replace("<","<",$input);
    $input=str_replace(">",">",$input);
    $input=str_replace("#","%23",$input);
    $input=str_replace("'","`",$input);
    $input=str_replace(";","%3B",$input);
    $input=str_replace("script","",$input);
    $input=str_replace("%3c","",$input);
    $input=str_replace("%3e","",$input);
    $input=trim($input);
    return $input;
} 



/**Validate an email address.
Provide email address (raw input)
Returns true if the email address has the email 
address format and the domain exists.
*/
function validEmail($email)
{
   $isValid = true;
   $atIndex = strrpos($email, "@");
   if (is_bool($atIndex) && !$atIndex)
   {
      $isValid = false;
   }
   else
   {
      $domain = substr($email, $atIndex+1);
      $local = substr($email, 0, $atIndex);
      $localLen = strlen($local);
      $domainLen = strlen($domain);
      if ($localLen < 1 || $localLen > 64)
      {
         // local part length exceeded
         $isValid = false;
      }
      else if ($domainLen < 1 || $domainLen > 255)
      {
         // domain part length exceeded
         $isValid = false;
      }
      else if ($local[0] == '.' || $local[$localLen-1] == '.')
      {
         // local part starts or ends with '.'
         $isValid = false;
      }
      else if (preg_match('/\\.\\./', $local))
      {
         // local part has two consecutive dots
         $isValid = false;
      }
      else if (!preg_match('/^[A-Za-z0-9\\-\\.]+$/', $domain))
      {
         // character not valid in domain part
         $isValid = false;
      }
      else if (preg_match('/\\.\\./', $domain))
      {
         // domain part has two consecutive dots
         $isValid = false;
      }
      else if
(!preg_match('/^(\\\\.|[A-Za-z0-9!#%&`_=\\/$\'*+?^{}|~.-])+$/',
                 str_replace("\\\\","",$local)))
      {
         // character not valid in local part unless 
         // local part is quoted
         if (!preg_match('/^"(\\\\"|[^"])+"$/',
             str_replace("\\\\","",$local)))
         {
            $isValid = false;
         }
      }
   }
   return $isValid;
}


$email 	= GetField($_GET['email']);
$pass 	= validEmail($email);

if ($pass) {
$f = fopen($newsletterFileName, 'a+');
$read = fread($f,filesize($newsletterFileName));
If (strstr($read,"@")) {
	$delimiter = ";";
}
if (strstr($read,$email)) { 
	echo 3;
} else {
	fwrite($f, $delimiter . $email);
	echo 1;
}
fclose($f);
} else {
echo 2;
}
?>

 

edited script

<?php
/** BY WebResourcesDepot - http://www.webresourcesdepot.com*/
/** YOU CAN EDIT HERE*/
$newsletterFileName = "file.txt";

/** IMPORTANT: EDIT BELOW UNLESS YOU KNOW WHAT YOU ARE DOING*/
function GetField($input) {
    $input=strip_tags($input);
    $input=str_replace("<","<",$input);
    $input=str_replace(">",">",$input);
    $input=str_replace("#","%23",$input);
    $input=str_replace("'","`",$input);
    $input=str_replace(";","%3B",$input);
    $input=str_replace("script","",$input);
    $input=str_replace("%3c","",$input);
    $input=str_replace("%3e","",$input);
    $input=trim($input);
    return $input;
} 



/**Validate an email address.
Provide email address (raw input)
Returns true if the email address has the email 
address format and the domain exists.
*/
function validEmail($email)
{
   $isValid = true;
   $atIndex = strrpos($email, "@");
   if (is_bool($atIndex) && !$atIndex)
   {
      $isValid = false;
   }
   else
   {
      $domain = substr($email, $atIndex+1);
      $local = substr($email, 0, $atIndex);
      $localLen = strlen($local);
      $domainLen = strlen($domain);
      if ($localLen < 1 || $localLen > 64)
      {
         // local part length exceeded
         $isValid = false;
      }
      else if ($domainLen < 1 || $domainLen > 255)
      {
         // domain part length exceeded
         $isValid = false;
      }
      else if ($local[0] == '.' || $local[$localLen-1] == '.')
      {
         // local part starts or ends with '.'
         $isValid = false;
      }
      else if (preg_match('/\\.\\./', $local))
      {
         // local part has two consecutive dots
         $isValid = false;
      }
      else if (!preg_match('/^[A-Za-z0-9\\-\\.]+$/', $domain))
      {
         // character not valid in domain part
         $isValid = false;
      }
      else if (preg_match('/\\.\\./', $domain))
      {
         // domain part has two consecutive dots
         $isValid = false;
      }
      else if
(!preg_match('/^(\\\\.|[A-Za-z0-9!#%&`_=\\/$\'*+?^{}|~.-])+$/',
                 str_replace("\\\\","",$local)))
      {
         // character not valid in local part unless 
         // local part is quoted
         if (!preg_match('/^"(\\\\"|[^"])+"$/',
             str_replace("\\\\","",$local)))
         {
            $isValid = false;
         }
      }
   }
   return $isValid;
}


$email 	= GetField($_GET['email']);
$pass 	= validEmail($email);

if ($pass) {

	$user_name = "_db";
	$password = "";
	$database = "_db";
	$server = "localhost";
	$db_handle = mysql_connect($server, $user_name, $password);
	$db_found = mysql_select_db($database, 		$db_handle);
	if ($db_found) {
	$SQL2 = "SELECT * FROM newsletter WHERE email = '$_GET[email]'";
	$result = mysql_query($SQL2);
	mysql_close($db_handle); }

if (strstr($SQL2,$email)) { 
	echo 3;
} else {
	$user_name = "_db";
	$password = "";
	$database = "_db";
	$server = "localhost";
	$db_handle = mysql_connect($server, $user_name, $password);
	$db_found = mysql_select_db($database, 		$db_handle);
	if ($db_found) {
	$SQL = "INSERT INTO newsletter (email,subscribed) VALUES ('$_GET[email]',1)";
	$result = mysql_query($SQL);
	mysql_close($db_handle);}
	echo 1;
} } else {
echo 2;
}
?>

 

[Pikachu2000]

I don't see anything in the code that should echo "already added to the list" under any circumstances.

[rik72]

I don't see anything in the code that should echo "already added to the list" under any circumstances.

 

Echo 1, 2, 3 is changed when it comes to the page...

 

if (theResponse == 1) {
				$(".successBalloon").fadeIn("slow");
				$(".successBalloon").animate({opacity: 1.0}, 3000);
				$(".successBalloon").fadeOut(1500);
				$(".resultText").html(successMessage);
			}
			if (theResponse == 2) {
				$(".errorBalloon").fadeIn("slow");
				$(".errorBalloon").animate({opacity: 1.0}, 3000);
				$(".errorBalloon").fadeOut(1500);
				$(".resultText").html(invalidMailError);
			}
			if (theResponse == 3) {
				$(".errorBalloon").fadeIn("slow");
				$(".errorBalloon").animate({opacity: 1.0}, 3000);
				$(".errorBalloon").fadeOut(1500);
				$(".resultText").html(duplicateMailError);
			}

[Pikachu2000]

So then which one is it getting to, 1, 2 or 3?

[rik72]

It's always going to 3, it's not adding to database.

 

I changed the code to this since then (to try and self-troubleshoot it);

$email 	= GetField($_GET['email']);
$pass 	= validEmail($email);

if ($pass) {

	$user_name = "db";
	$password = "";
	$database = "_db";
	$server = "localhost";
	$db_handle = mysql_connect($server, $user_name, $password);
	$db_found = mysql_select_db($database, 		$db_handle);
	if ($db_found) {
	$SQL2 = "SELECT * FROM newsletter'";
	$result = mysql_query($SQL2);
	mysql_close($db_handle); }

if ('$SQL2' == '$_GET[email]') { 
	echo 3;
} else {
	$db_handle = mysql_connect($server, $user_name, $password);
	$db_found = mysql_select_db($database, 		$db_handle);
	if ($db_found) {
	$SQL = "INSERT INTO newsletter (email,subscribed) VALUES ('$_GET[email]',1)";
	$result = mysql_query($SQL);
	mysql_close($db_handle);}
	echo 1;
} } else {
echo 2;
}

 

Now it's getting stuck at 1 (success), but it's not checking if the email address is already in the table, which is 3. Although it is doing error check 2 successfully.

 

[DavidAM]

This:

if (strstr($SQL2,$email)) { 
	echo 3;
} else {

is checking to see if the email address is in the SELECT statement. It is in the SELECT statement because you put it there with:

$SQL2 = "SELECT * FROM newsletter WHERE email = '$_GET[email]'";

 

You need to check the result of the execution of that query to see if any rows were found. Something like:

		$SQL2 = "SELECT * FROM newsletter WHERE email = '$_GET['email']'";
	$result = mysql_query($SQL2);
	$emailCount = mysql_num_rows($result);
	mysql_close($db_handle); }
if ($emailCount > 0) { 
	echo 3;
} else {

[Pikachu2000]

One thing I notice is this comparison: if ('$SQL2' == '$_GET'). To me, it looks like you want to see if the user entered the literal string SELECT * FROM newsletter' in the form, because that's the only way that will evaluate to TRUE.

[rik72]

This:

if (strstr($SQL2,$email)) { 
	echo 3;
} else {

is checking to see if the email address is in the SELECT statement. It is in the SELECT statement because you put it there with:

$SQL2 = "SELECT * FROM newsletter WHERE email = '$_GET[email]'";

 

You need to check the result of the execution of that query to see if any rows were found. Something like:

		$SQL2 = "SELECT * FROM newsletter WHERE email = '$_GET['email']'";
	$result = mysql_query($SQL2);
	$emailCount = mysql_num_rows($result);
	mysql_close($db_handle); }
if ($emailCount > 0) { 
	echo 3;
} else {

 

Thanks a lot, i've sorted the issue, the only thing i had to do is put mysql_close($db_handle); after the $emailCount function and it worked.