Fix quote escaping in SQL, allow template for problem confirmed.

3 files changed, 29 insertions, 24 deletions

diff --git a/db/alert_types.sql b/db/alert_types.sql
index bcb42b4e9..d605d8e65 100644
--- a/db/alert_types.sql
+++ b/db/alert_types.sql
@@ -6,7 +6,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('new_updates', 'select * from problem where id=?', 'problem',
     'Updates on {{title}}', '/', 'Updates on {{title}}',
-    'comment', 'comment.state=\'confirmed\'', 'created desc',
+    'comment', 'comment.state=''confirmed''', 'created desc',
     'Update by {{name}}', '/report/{{problem_id}}#comment_{{id}}', '{{text}}', 'alert-update');
 
 -- New problems anywhere on the site
@@ -17,7 +17,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('new_problems', '', '',
     'New problems on FixMyStreet', '/', 'The latest problems reported by users',
-    'problem', 'problem.state in (\'confirmed\', \'fixed\')', 'created desc',
+    'problem', 'problem.state in (''confirmed'', ''fixed'')', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
 
 -- New fixed problems anywhere on the site
@@ -28,7 +28,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('new_fixed_problems', '', '',
     'Problems recently reported fixed on FixMyStreet', '/', 'The latest problems reported fixed by users',
-    'problem', 'problem.state in (\'fixed\')', 'lastupdate desc',
+    'problem', 'problem.state in (''fixed'')', 'lastupdate desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
 
 -- New problems around a location
@@ -39,7 +39,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('local_problems', '', '',
     'New local problems on FixMyStreet', '/', 'The latest local problems reported by users',
-    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (\'confirmed\', \'fixed\')', 'created desc',
+    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (''confirmed'', ''fixed'')', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
 
 -- New problems sent to a particular council
@@ -50,8 +50,8 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('council_problems', '', '',
     'New problems to {{COUNCIL}} on FixMyStreet', '/reports', 'The latest problems for {{COUNCIL}} reported by users',
-    'problem', 'problem.state in (\'confirmed\', \'fixed\') and (council like \'%\'||?||\'%\'
-        or council is null) and areas like \'%,\'||?||\',%\'', 'created desc',
+    'problem', 'problem.state in (''confirmed'', ''fixed'') and (council like ''%''||?||''%''
+        or council is null) and areas like ''%,''||?||'',%''', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council'
 );
 
@@ -64,8 +64,8 @@ insert into alert_type
 values ('ward_problems', '', '',
     'New problems for {{COUNCIL}} within {{WARD}} ward on FixMyStreet', '/reports',
     'The latest problems for {{COUNCIL}} within {{WARD}} ward reported by users',
-    'problem', 'problem.state in (\'confirmed\', \'fixed\') and (council like \'%\'||?||\'%\'
-        or council is null) and areas like \'%,\'||?||\',%\'', 'created desc',
+    'problem', 'problem.state in (''confirmed'', ''fixed'') and (council like ''%''||?||''%''
+        or council is null) and areas like ''%,''||?||'',%''', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward'
 );
 
@@ -76,9 +76,9 @@ insert into alert_type
     item_table, item_where, item_order,
     item_title, item_link, item_description, template)
 values ('area_problems', '', '',
-    'New problems within {{NAME}}\'s boundary on FixMyStreet', '/reports',
-    'The latest problems within {{NAME}}\'s boundary reported by users', 'problem',
-    'problem.state in (\'confirmed\', \'fixed\') and areas like \'%,\'||?||\',%\'', 'created desc',
+    'New problems within {{NAME}}''s boundary on FixMyStreet', '/reports',
+    'The latest problems within {{NAME}}''s boundary reported by users', 'problem',
+    'problem.state in (''confirmed'', ''fixed'') and areas like ''%,''||?||'',%''', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area'
 );
 
diff --git a/db/alert_types_eha.sql b/db/alert_types_eha.sql
index 4ccd51cef..c949ea2f8 100644
--- a/db/alert_types_eha.sql
+++ b/db/alert_types_eha.sql
@@ -6,7 +6,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('new_updates', 'select * from problem where id=?', 'problem',
     'Updates on {{title}}', '/', 'Updates on {{title}}',
-    'comment', 'comment.state=\'confirmed\'', 'created desc',
+    'comment', 'comment.state=''confirmed''', 'created desc',
     'Update by {{name}}', '/report/{{problem_id}}#comment_{{id}}', '{{text}}', 'alert-update');
 
 -- New problems anywhere on the site
@@ -17,7 +17,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('new_problems', '', '',
     'New reports on reportemptyhomes.com', '/', 'The latest empty properties reported by users',
-    'problem', 'problem.state in (\'confirmed\', \'fixed\')', 'created desc',
+    'problem', 'problem.state in (''confirmed'', ''fixed'')', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
 
 -- New fixed problems anywhere on the site
@@ -28,7 +28,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('new_fixed_problems', '', '',
     'Properties recently reported as put back to use on reportemptyhomes.com', '/', 'The latest properties reported back to use by users',
-    'problem', 'problem.state in (\'fixed\')', 'lastupdate desc',
+    'problem', 'problem.state in (''fixed'')', 'lastupdate desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
 
 -- New problems around a location
@@ -39,7 +39,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('local_problems', '', '',
     'New local reports on reportemptyhomes.com', '/', 'The latest local reports reported by users',
-    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (\'confirmed\', \'fixed\')', 'created desc',
+    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (''confirmed'', ''fixed'')', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
 
 -- New problems sent to a particular council
@@ -50,8 +50,8 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('council_problems', '', '',
     'New reports to {{COUNCIL}} on reportemptyhomes.com', '/reports', 'The latest reports for {{COUNCIL}} reported by users',
-    'problem', 'problem.state in (\'confirmed\', \'fixed\') and (council like \'%\'||?||\'%\'
-        or council is null) and areas like \'%,\'||?||\',%\'', 'created desc',
+    'problem', 'problem.state in (''confirmed'', ''fixed'') and (council like ''%''||?||''%''
+        or council is null) and areas like ''%,''||?||'',%''', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council'
 );
 
@@ -64,8 +64,8 @@ insert into alert_type
 values ('ward_problems', '', '',
     'New reports for {{COUNCIL}} within {{WARD}} ward on reportemptyhomes.com', '/reports',
     'The latest reports for {{COUNCIL}} within {{WARD}} ward reported by users',
-    'problem', 'problem.state in (\'confirmed\', \'fixed\') and (council like \'%\'||?||\'%\'
-        or council is null) and areas like \'%,\'||?||\',%\'', 'created desc',
+    'problem', 'problem.state in (''confirmed'', ''fixed'') and (council like ''%''||?||''%''
+        or council is null) and areas like ''%,''||?||'',%''', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward'
 );
 
@@ -76,9 +76,9 @@ insert into alert_type
     item_table, item_where, item_order,
     item_title, item_link, item_description, template)
 values ('area_problems', '', '',
-    'New reports within {{NAME}}\'s boundary on reportemptyhomes.com', '/reports',
-    'The latest reports within {{NAME}}\'s boundary reported by users', 'problem',
-    'problem.state in (\'confirmed\', \'fixed\') and areas like \'%,\'||?||\',%\'', 'created desc',
+    'New reports within {{NAME}}''s boundary on reportemptyhomes.com', '/reports',
+    'The latest reports within {{NAME}}''s boundary reported by users', 'problem',
+    'problem.state in (''confirmed'', ''fixed'') and areas like ''%,''||?||'',%''', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area'
 );
 
diff --git a/web/confirm.cgi b/web/confirm.cgi
index 10655c14c..59382df57 100755
--- a/web/confirm.cgi
+++ b/web/confirm.cgi
@@ -6,7 +6,7 @@
 # Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org
 #
-# $Id: confirm.cgi,v 1.58 2009-10-21 15:10:08 louise Exp $
+# $Id: confirm.cgi,v 1.59 2009-11-04 16:28:16 matthew Exp $
 
 use strict;
 use Standard;
@@ -152,10 +152,15 @@ $q->p('<a href="/report/' . $id . '">' . _('View your report') . '</a>.');
             . sprintf(_('. You can <a href="%s">view the problem on this site</a>.'), $report_url)
         );
         $out .= CrossSell::display_advert($q, $email, $name);
+        my %vars = (
+            url_report => $report_url,
+            url_home => Cobrand::url($cobrand, '/', $q),
+        );
+        my $cobrand_page = Page::template_include('confirmed-problem', $q, Page::template_root($q), %vars);
+        $out = $cobrand_page if $cobrand_page;
     }
 
     # Subscribe problem reporter to email updates
-    my $cobrand = Page::get_cobrand($q);
     my $alert_id = mySociety::Alert::create($email, 'new_updates', $cobrand, $cobrand_data, $id);
     mySociety::Alert::confirm($alert_id);