From b41f35ea6ed4bef7cb15ffb665c08d1987adcc69 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Michael=20T=C3=A4nzer?= Date: Wed, 18 Apr 2012 18:28:40 +0200 Subject: bug 922: take care of missing domlink entries or wrongly set domids in expiration reminder script MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Signed-off-by: Michael Tänzer --- scripts/cron/warning.php | 33 ++++++++++++++++++++++++--------- 1 file changed, 24 insertions(+), 9 deletions(-) diff --git a/scripts/cron/warning.php b/scripts/cron/warning.php index 18e89da..8d59ddf 100755 --- a/scripts/cron/warning.php +++ b/scripts/cron/warning.php @@ -68,16 +68,31 @@ echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['memid foreach($days as $day => $warning) { - $query = "SELECT `domaincerts`.`id`, `users`.`fname`, `users`.`lname`, `users`.`email`, - `domains`.`memid`, `domaincerts`.`subject`, `domaincerts`.`crt_name`, - `domaincerts`.`CN`, - (UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft` + $query = + "SELECT `domaincerts`.`id`, + `users`.`fname`, `users`.`lname`, `users`.`email`, + `domains`.`memid`, + `domaincerts`.`subject`, `domaincerts`.`crt_name`, + `domaincerts`.`CN`, + (UNIX_TIMESTAMP(`domaincerts`.`expire`) - + UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft` + FROM `users`, `domaincerts`, `domlink`, `domains` - WHERE UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 AND - UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW()) < $day * 86400 AND - `domaincerts`.`renewed`=0 AND `domaincerts`.`warning` <= '$warning' AND - `domaincerts`.`revoked`=0 AND `users`.`id` = `domains`.`memid` AND - `domlink`.`certid` = `domaincerts`.`id` AND `domains`.`id` = `domlink`.`domid`"; + WHERE UNIX_TIMESTAMP(`domaincerts`.`expire`) - + UNIX_TIMESTAMP(NOW()) > -7 * 86400 + AND UNIX_TIMESTAMP(`domaincerts`.`expire`) - + UNIX_TIMESTAMP(NOW()) < $day * 86400 + AND `domaincerts`.`renewed` = 0 + AND `domaincerts`.`warning` <= '$warning' + AND `domaincerts`.`revoked` = 0 + AND ( + `domaincerts`.`domid` = `domains`.`id` + OR ( + `domaincerts`.`id` = `domlink`.`certid` + AND `domlink`.`domid` = `domains`.id` + ) + ) + AND `domains`.`memid` = `users`.`id`"; $res = mysql_query($query); while($row = mysql_fetch_assoc($res)) { -- cgit v1.2.1 From 61b9d45e70358f0f79c21e6449ee8a0d7d18f035 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Michael=20T=C3=A4nzer?= Date: Wed, 18 Apr 2012 19:01:49 +0200 Subject: bug 922: also fix domlink handling in domain disputes MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Signed-off-by: Michael Tänzer --- includes/account.php | 26 +++++++++++++++++++++++--- 1 file changed, 23 insertions(+), 3 deletions(-) diff --git a/includes/account.php b/includes/account.php index 554713e..5e87605 100644 --- a/includes/account.php +++ b/includes/account.php @@ -620,10 +620,30 @@ { $row = mysql_fetch_assoc($res); echo $row['domain']."
\n"; - mysql_query("update `domains` set `deleted`=NOW() where `id`='$id'"); - $dres = mysql_query("select * from `domlink` where `domid`='$id'"); + + $dres = mysql_query( + "select `domaincerts`.`id` + from `domaincerts`, `domlink` + where `domaincerts`.`domid` = '$id' + or ( + `domaincerts`.`id` = `domlink`.`certid` + and `domlink`.`domid` = '$id' + )"); while($drow = mysql_fetch_assoc($dres)) - mysql_query("update `domaincerts` set `revoked`='1970-01-01 10:00:01' where `id`='".$drow['certid']."' and `revoked`=0 and UNIX_TIMESTAMP(`expire`)-UNIX_TIMESTAMP() > 0"); + { + mysql_query( + "update `domaincerts` + set `revoked`='1970-01-01 10:00:01' + where `id` = '".$drow['id']."' + and `revoked` = 0 + and UNIX_TIMESTAMP(`expire`) - + UNIX_TIMESTAMP() > 0"); + } + + mysql_query( + "update `domains` + set `deleted`=NOW() + where `id` = '$id'"); } } } -- cgit v1.2.1 From ded64e3bc978fa332777cf4fffa84c756dd65cb3 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Michael=20T=C3=A4nzer?= Date: Wed, 18 Apr 2012 19:11:14 +0200 Subject: bug 922: Doh, typo MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Signed-off-by: Michael Tänzer --- scripts/cron/warning.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/scripts/cron/warning.php b/scripts/cron/warning.php index 8d59ddf..afa9996 100755 --- a/scripts/cron/warning.php +++ b/scripts/cron/warning.php @@ -89,7 +89,7 @@ echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['memid `domaincerts`.`domid` = `domains`.`id` OR ( `domaincerts`.`id` = `domlink`.`certid` - AND `domlink`.`domid` = `domains`.id` + AND `domlink`.`domid` = `domains`.`id` ) ) AND `domains`.`memid` = `users`.`id`"; -- cgit v1.2.1 From b316652307b171588e813b7586efa028f38b6e92 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Michael=20T=C3=A4nzer?= Date: Wed, 15 Aug 2012 00:14:31 +0200 Subject: bug 922: only select distinct server serts to avoid processing the same cert multiple times MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Signed-off-by: Michael Tänzer --- includes/account.php | 2 +- scripts/cron/warning.php | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/includes/account.php b/includes/account.php index 5e87605..4f8ed03 100644 --- a/includes/account.php +++ b/includes/account.php @@ -622,7 +622,7 @@ echo $row['domain']."
\n"; $dres = mysql_query( - "select `domaincerts`.`id` + "select distinct `domaincerts`.`id` from `domaincerts`, `domlink` where `domaincerts`.`domid` = '$id' or ( diff --git a/scripts/cron/warning.php b/scripts/cron/warning.php index afa9996..ef570fd 100755 --- a/scripts/cron/warning.php +++ b/scripts/cron/warning.php @@ -69,7 +69,7 @@ echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['memid foreach($days as $day => $warning) { $query = - "SELECT `domaincerts`.`id`, + "SELECT DISTINCT `domaincerts`.`id`, `users`.`fname`, `users`.`lname`, `users`.`email`, `domains`.`memid`, `domaincerts`.`subject`, `domaincerts`.`crt_name`, -- cgit v1.2.1 From 6dd2854e1d61800daa287f4f8139ade9c52a0a58 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Michael=20T=C3=A4nzer?= Date: Wed, 3 Oct 2012 01:21:20 +0200 Subject: bug 922: Include serial number in the expiration reminder MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Signed-off-by: Michael Tänzer --- scripts/cron/warning.php | 15 ++++++++++++--- 1 file changed, 12 insertions(+), 3 deletions(-) diff --git a/scripts/cron/warning.php b/scripts/cron/warning.php index ef570fd..5cf7c31 100755 --- a/scripts/cron/warning.php +++ b/scripts/cron/warning.php @@ -24,7 +24,7 @@ foreach($days as $day => $warning) { $query = "SELECT `emailcerts`.`id`,`users`.`fname`,`users`.`lname`,`users`.`email`,`emailcerts`.`memid`, - `emailcerts`.`subject`, `emailcerts`.`crt_name`,`emailcerts`.`CN`, + `emailcerts`.`subject`, `emailcerts`.`crt_name`,`emailcerts`.`CN`, `emailcerts`.`serial`, (UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 as `daysleft` FROM `users`,`emailcerts` WHERE UNIX_TIMESTAMP(`emailcerts`.`expire`) - UNIX_TIMESTAMP(NOW()) > -7 * 86400 and @@ -56,7 +56,11 @@ $body = sprintf(_("Hi %s"), $row['fname']).",\n\n"; $body .= _("You are receiving this email as you are the listed contact for:")."\n\n"; $body .= $row['subject']."\n\n"; - $body .= sprintf(_("Your certificate is set to expire in approximately %s days time, you can renew this by going to the following URL:"), $row['daysleft'])."\n\n"; + $body .= sprintf(_("Your certificate with the serial number %s is ". + "set to expire in approximately %s days time. You can ". + "renew it by going to the following URL:"), + $row['serial'], + $row['daysleft'])."\n\n"; $body .= "https://www.cacert.org/account.php?id=5\n\n"; $body .= _("Best Regards")."\n"._("CAcert Support"); sendmail($row['email'], "[CAcert.org] "._("Your Certificate is about to expire"), $body, "support@cacert.org", "", "", "CAcert Support"); @@ -74,6 +78,7 @@ echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['memid `domains`.`memid`, `domaincerts`.`subject`, `domaincerts`.`crt_name`, `domaincerts`.`CN`, + `domaincerts`.`serial`, (UNIX_TIMESTAMP(`domaincerts`.`expire`) - UNIX_TIMESTAMP(NOW())) / 86400 AS `daysleft` @@ -103,7 +108,11 @@ echo $row['fname']." ".$row['lname']." <".$row['email']."> (memid: ".$row['memid $body = sprintf(_("Hi %s"), $row['fname']).",\n\n"; $body .= _("You are receiving this email as you are the listed contact for:")."\n\n"; $body .= $row['subject']."\n\n"; - $body .= sprintf(_("Your certificate is set to expire in approximately %s days time, you can renew this by going to the following URL:"), $row['daysleft'])."\n\n"; + $body .= sprintf(_("Your certificate with the serial number %s is ". + "set to expire in approximately %s days time. You can ". + "renew it by going to the following URL:"), + $row['serial'], + $row['daysleft'])."\n\n"; $body .= "https://www.cacert.org/account.php?id=12\n\n"; $body .= _("Best Regards")."\n"._("CAcert Support"); sendmail($row['email'], "[CAcert.org] "._("Your Certificate is about to expire"), $body, "support@cacert.org", "", "", "CAcert Support"); -- cgit v1.2.1