From: Mitch Jackson <mitch@freeside.biz>
Date: Sun, 3 Mar 2019 21:35:25 +0000 (-0500)
Subject: RT# 82942 Add FS::DBI, to fix database connection encoding bug
X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=50a717fa7328dfb36d8d3d6c30d616399cda771f

RT# 82942 Add FS::DBI, to fix database connection encoding bug

- Add FS::DBI
  - Drop-in replacement for DBI
  - Ensures client_encoding is set to UTF8 for DBD::Pg
- Implement FS::DBI in FS::UID, where nearly all freeside
  database connections are established
---

diff --git a/FS/FS/DBI.pm b/FS/FS/DBI.pm
new file mode 100644
index 000000000..c6ff12561
--- /dev/null
+++ b/FS/FS/DBI.pm
@@ -0,0 +1,72 @@
+package FS::DBI;
+use strict;
+use warnings;
+use base qw( DBI );
+
+=head1 NAME
+
+FS::DBI - Freeside wrapper for DBI
+
+=head1 SYNOPSIS
+
+  use FS::DBI;
+  
+  $dbh = FS::DBI->connect( @args );
+  $dbh->do(
+    'UPDATE table SET foo = ? WHERE bar = ?',
+    undef,
+    $foo, $bar
+  ) or die $dbh->errstr;
+
+See L<DBI>
+
+=head1 DESCRIPTION
+
+Allow Freeside to manage how DBI is used when necessary
+
+=head2 Legacy databases and DBD::Pg v3.0+
+
+Breaking behavior was introduced in DBD::Pg version 3.0.0
+in regards to L<DBD::Pg/pg_enable_utf8>.
+
+Some freedside databases are legacy databases with older encodings
+and locales. pg_enable_utf8 no longer sets client_encoding to utf8
+on non-utf8 databases, causing crashes and data corruption.
+
+FS::DBI->connect() enforces utf8 client_encoding on all DBD::Pg connections
+
+=head1 METHODS
+
+=head2 connect @connect_args
+
+For usage, see L<DBI/connect>
+
+Force utf8 client_encoding on DBD::Pg connections
+
+=cut
+
+sub connect {
+  my $class = shift;
+  my $dbh = $class->SUPER::connect( @_ );
+
+  if ( $_[0] =~ /^DBI::Pg/ ) {
+    $dbh->do('SET client_encoding TO UTF8;')
+      or die sprintf 'Error setting client_encoding to UTF8: %s', $dbh->errstr;
+
+    # DBD::Pg requires touching this attribute when changing the client_encoding
+    # on an already established connection, to get expected behavior.
+    $dbh->{pg_enable_utf8} = -1;
+  }
+
+  $dbh;
+}
+
+# Stub required to subclass DBI
+package FS::DBI::st;
+use base qw( DBI::st );
+
+# Stub required to subclass DBI
+package FS::DBI::db;
+use base qw( DBI::db );
+
+1;
diff --git a/FS/FS/UID.pm b/FS/FS/UID.pm
index d3ee8d810..3863256f8 100644
--- a/FS/FS/UID.pm
+++ b/FS/FS/UID.pm
@@ -9,7 +9,7 @@ use vars qw(
 );
 use subs qw( getsecrets );
 use Carp qw( carp croak cluck confess );
-use DBI;
+use FS::DBI;
 use IO::File;
 use FS::CurrentUser;
 
@@ -172,14 +172,16 @@ sub callback_setup {
 }
 
 sub myconnect {
-  my $handle = DBI->connect( getsecrets(), { 'AutoCommit'         => 0,
-                                             'ChopBlanks'         => 1,
-                                             'ShowErrorStatement' => 1,
-                                             'pg_enable_utf8'     => 1,
-                                             #'mysql_enable_utf8'  => 1,
-                                           }
-                           )
-    or die "DBI->connect error: $DBI::errstr\n";
+  my $handle = FS::DBI->connect(
+    getsecrets(),
+    {
+      'AutoCommit'         => 0,
+      'ChopBlanks'         => 1,
+      'ShowErrorStatement' => 1,
+      'pg_enable_utf8'     => 1,
+      # 'mysql_enable_utf8'  => 1,
+    }
+  ) or die "FS::DBI->connect error: $FS::DBI::errstr\n";
 
   $FS::Conf::conf_cache = undef;