[DBD::Pg 3/3] First pass at placeholder escpaing. In other words, allowing literal question marks to be passed into queries by adding a backslash to them. Affects other placeholders as well (dollarsign and colon) Add tests for ? and ?? by creating custom operators Adjust cleanup routines in the tests for the new operators Actual rewrite code in dbdimp.c modified from a proof of concept by Tim Bunce.

Committed by Greg Sabino Mullane <[email protected]>

Subject: [DBD::Pg 3/3] First pass at placeholder escpaing. In other words,
allowing literal question marks to be passed into queries by adding a
backslash to them. Affects other placeholders as well (dollarsign and colon)
Add tests for ? and ?? by creating custom operators Adjust cleanup routines
in the tests for the new operators Actual rewrite code in dbdimp.c modified
from a proof of concept by Tim Bunce.

---
 dbdimp.c              | 17 ++++++++++++++++-
 t/12placeholders.t    | 31 +++++++++++++++++++++++++++++--
 t/dbdpg_test_setup.pl | 26 ++++++++++++++++++++++++++
 3 files changed, 71 insertions(+), 3 deletions(-)

diff --git a/dbdimp.c b/dbdimp.c
index d437d4d..c90d561 100644
--- a/dbdimp.c
+++ b/dbdimp.c
@@ -1733,7 +1733,7 @@ static void pg_st_split_statement (pTHX_ imp_sth_t * imp_sth, int version, char
 
 	if (TSTART_slow) TRC(DBILOGFP, "%sBegin pg_st_split_statement\n", THEADER_slow);
 	if (TRACE6_slow) TRC(DBILOGFP, "%spg_st_split_statement: (%s)\n", THEADER_slow, statement);
-	
+
 	/*
 	  If the pg_direct flag is set (or the string has no length), we do not split at all,
 	  but simply put everything verbatim into a single segment and return.
@@ -1979,6 +1979,21 @@ static void pg_st_split_statement (pTHX_ imp_sth_t * imp_sth, int version, char
 			continue;
 		}
 
+		/* If this placeholder is escaped, we rewrite the string to remove the
+		   backslash, and move on as if there is no placeholder */
+		if ('\\' == oldch) {
+			/* copy the placeholder-like character but ignore the backslash */
+			unsigned char *p = statement-2;
+			while(*p++) {
+				*(p-1) = *p;
+			}
+			/* We need to adjust these items because we just rewrote statement! */
+			statement--;
+			currpos--;
+			ch = *statement;
+			continue;
+		}
+
 		/* We might slurp in a placeholder, so mark the character before the current one */
 		/* In other words, inside of "ABC?", set sectionstop to point to "C" */
 		sectionstop=currpos-1;
diff --git a/t/12placeholders.t b/t/12placeholders.t
index eb4f3d2..cfd7164 100644
--- a/t/12placeholders.t
+++ b/t/12placeholders.t
@@ -17,7 +17,7 @@ my $dbh = connect_database();
 if (! $dbh) {
 	plan skip_all => 'Connection to database failed, cannot continue testing';
 }
-plan tests => 251;
+plan tests => 253;
 
 my $t='Connect to database for placeholder testing';
 isnt ($dbh, undef, $t);
@@ -841,10 +841,37 @@ while (my ($name,$res) = each %booltest) {
 	}
 }
 
+## Test of placeholder escaping. Enabled by default, so let's jump right in
+$t = q{Basic placeholder escaping works via backslash-question mark for \?};
+
+## But first, we need some operators
+$dbh->do('create operator ? (leftarg=int,rightarg=int,procedure=int4eq)');
+$dbh->commit();
+$dbh->do('create operator ?? (leftarg=text,rightarg=text,procedure=texteq)');
+$dbh->commit();
+
+$SQL = qq{SELECT count(*) FROM dbd_pg_test WHERE id \\? ?};
+$sth = $dbh->prepare($SQL);
+eval {
+	$count = $sth->execute(123);
+};
+is($@, '', $t);
+$sth->finish();
+
+$t = q{Basic placeholder escaping works via backslash-question mark for \?\?};
+$SQL = qq{SELECT count(*) FROM dbd_pg_test WHERE pname \\?\\? ?};
+$sth = $dbh->prepare($SQL);
+eval {
+	$count = $sth->execute('foobar');
+};
+is($@, '', $t);
+$sth->finish();
+
+## pg_placeholder_escaping = 1;
+
 ## Begin custom type testing
 
 $dbh->rollback();
 
 cleanup_database($dbh,'test');
 $dbh->disconnect();
-
diff --git a/t/dbdpg_test_setup.pl b/t/dbdpg_test_setup.pl
index 01fc98c..bebd833 100644
--- a/t/dbdpg_test_setup.pl
+++ b/t/dbdpg_test_setup.pl
@@ -22,6 +22,12 @@ my @matviews =
 	 'dbd_pg_matview',
      );
 
+my @operators =
+	(
+		'?.integer.integer',
+		'??.text.text',
+    );
+
 my @schemas =
 	(
 	 'dbd_pg_testschema',
@@ -792,6 +798,20 @@ sub relation_exists {
 } ## end of relation_exists
 
 
+sub operator_exists {
+
+	my ($dbh,$opname,$leftarg,$rightarg) = @_;
+
+	my $SQL = 'SELECT 1 FROM pg_catalog.pg_operator '.
+		'WHERE oprname=? AND oprleft::regtype::text = ? AND oprright::regtype::text = ?';
+	my $sth = $dbh->prepare_cached($SQL);
+	my $count = $sth->execute($opname,$leftarg,$rightarg);
+	$sth->finish();
+	return $count < 1 ? 0 : 1;
+
+} ## end of operator_exists
+
+
 sub cleanup_database {
 
 	## Clear out any testing objects in the current database
@@ -811,6 +831,12 @@ sub cleanup_database {
 		$dbh->do("DROP MATERIALIZED VIEW $schema.$name");
 	}
 
+	for my $name (@operators) {
+		my ($opname,$leftarg,$rightarg) = split /\./ => $name;
+		next if ! operator_exists($dbh,$opname,$leftarg,$rightarg);
+		$dbh->do("DROP OPERATOR $opname($leftarg,$rightarg)");
+	}
+
 	for my $name (@tables) {
 		my $schema = ($name =~ s/(.+)\.(.+)/$2/) ? $1 : $S;
 		next if ! relation_exists($dbh,$schema,$name);
-- 
1.8.4