From c5f28366936af76b66095de8120c8f08ec7e9b0d Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Wed, 10 Jun 2015 14:17:37 -0400 Subject: [PATCH] LP#1463973: Use dollar-quoting for report literals When inserting string literal into SQL for Clark, we quote apostrophes with a backslash. That worked fine until Postgres changed its default for the standard_conforming_strings GUC to "on" after which it no longer worked fine. So, we will now use "dollar quoting" for string literals instead in much the same way we do for our search queries. Test plan --------- Pre-patch: Try to run a report where a string value is required as a filter parameter, and include an apostrophe in that string. The report will fail with an SQL error. Post-patch: Do the same, it will not fail. Signed-off-by: Mike Rylander Signed-off-by: Angela Kilsdonk Signed-off-by: Ben Shum --- .../lib/OpenILS/Reporter/SQLBuilder.pm | 76 +++++-------------- 1 file changed, 19 insertions(+), 57 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm b/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm index 41d76ba95f..f3cef565d0 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm @@ -318,9 +318,9 @@ sub toSQL { my @params; @params = @{ $self->{params} } if ($self->{params}); - my $sql = $func . '(\''; - $sql .= join("','", @params) if (@params); - $sql .= '\')'; + my $sql = $func . "(\$_$$\$"; + $sql .= join("\$_$$\$,\$_$$\$", @params) if (@params); + $sql .= "\$_$$\$)"; return $sql; } @@ -343,10 +343,7 @@ sub toSQL { my $val = $self->{params}; $val = $$val[0] if (ref($val)); - $val =~ s/\\/\\\\/go; - $val =~ s/'/\\'/go; - - return "'$val'"; + return "\$_$$\$$val\$_$$\$"; } @@ -359,10 +356,7 @@ sub toSQL { my $val = $self->{params}; $val = $$val[0] if (ref($val)); - $val =~ s/\\/\\\\/go; - $val =~ s/'/\\'/go; - - return "AGE(NOW(),'" . $val . "'::TIMESTAMPTZ)"; + return "AGE(NOW(),\$_$$\$$val\$_$$\$::TIMESTAMPTZ)"; } sub is_aggregate { return 0 } @@ -375,17 +369,10 @@ sub toSQL { my $self = shift; my $rtime = $self->relative_time || 'now'; - - $rtime =~ s/\\/\\\\/go; - $rtime =~ s/'/\\'/go; - my $val = $self->{params}; $val = $$val[0] if (ref($val)); - $val =~ s/\\/\\\\/go; - $val =~ s/'/\\'/go; - - return "EXTRACT(YEAR FROM '$rtime'::TIMESTAMPTZ + '$val years')"; + return "EXTRACT(YEAR FROM \$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val years\$_$$\$)"; } @@ -396,18 +383,11 @@ sub toSQL { my $self = shift; my $rtime = $self->relative_time || 'now'; - - $rtime =~ s/\\/\\\\/go; - $rtime =~ s/'/\\'/go; - my $val = $self->{params}; $val = $$val[0] if (ref($val)); - $val =~ s/\\/\\\\/go; - $val =~ s/'/\\'/go; - - return "EXTRACT(YEAR FROM '$rtime'::TIMESTAMPTZ + '$val months')" . - " || '-' || LPAD(EXTRACT(MONTH FROM '$rtime'::TIMESTAMPTZ + '$val months')::text,2,'0')"; + return "EXTRACT(YEAR FROM \$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val months\$_$$\$)" . + " || \$_$$\$-\$_$$\$ || LPAD(EXTRACT(MONTH FROM \$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val months\$_$$\$)::text,2,\$_$$\$0\$_$$\$)"; } @@ -418,17 +398,10 @@ sub toSQL { my $self = shift; my $rtime = $self->relative_time || 'now'; - - $rtime =~ s/\\/\\\\/go; - $rtime =~ s/'/\\'/go; - my $val = $self->{params}; $val = $$val[0] if (ref($val)); - $val =~ s/\\/\\\\/go; - $val =~ s/'/\\'/go; - - return "DATE('$rtime'::TIMESTAMPTZ + '$val days')"; + return "DATE(\$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val days\$_$$\$)"; } @@ -439,17 +412,10 @@ sub toSQL { my $self = shift; my $rtime = $self->relative_time || 'now'; - - $rtime =~ s/\\/\\\\/go; - $rtime =~ s/'/\\'/go; - my $val = $self->{params}; $val = $$val[0] if (ref($val)); - $val =~ s/\\/\\\\/go; - $val =~ s/'/\\'/go; - - return "EXTRACT(WEEK FROM '$rtime'::TIMESTAMPTZ + '$val weeks')"; + return "EXTRACT(WEEK FROM \$_$$\$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$val weeks\$_$$\$)"; } @@ -568,7 +534,7 @@ sub toSQL { @params = @{ $self->resolve_param( $self->{_column}->{params} ) } if ($self->{_column}->{params}); my $sql = $func . '("' . $self->{_relation} . '"."' . $self->name . '"'; - $sql .= ",'" . join("','", @params) . "'" if (@params); + $sql .= ",\$_$$\$" . join("\$_$$\$,\$_$$\$", @params) . "\$_$$\$" if (@params); $sql .= ')'; return $sql; @@ -592,8 +558,6 @@ package OpenILS::Reporter::SQLBuilder::Column::Transform::upper; sub toSQL { my $self = shift; my $params = $self->resolve_param( $self->{_column}->{params} ); - my $start = $$params[0]; - my $len = $$params[1]; return 'UPPER("' . $self->{_relation} . '"."' . $self->name . '")'; } @@ -606,8 +570,6 @@ package OpenILS::Reporter::SQLBuilder::Column::Transform::lower; sub toSQL { my $self = shift; my $params = $self->resolve_param( $self->{_column}->{params} ); - my $start = $$params[0]; - my $len = $$params[1]; return 'evergreen.lowercase("' . $self->{_relation} . '"."' . $self->name . '")'; } @@ -1000,18 +962,18 @@ sub toSQL { } elsif (lc($op) eq 'like') { $val = $$val[0] if (ref($val) eq 'ARRAY'); $val = $val->toSQL; - $val =~ s/^'(.*)'$/$1/o; - $val =~ s/%/\\\\%/o; - $val =~ s/_/\\\\_/o; - $sql .= " LIKE '\%$val\%'"; + $val =~ s/\$_$$\$//g; + $val =~ s/%/\\%/o; + $val =~ s/_/\\_/o; + $sql .= " LIKE \$_$$\$\%$val\%\$_$$\$"; } elsif (lc($op) eq 'ilike') { $val = $$val[0] if (ref($val) eq 'ARRAY'); $val = $val->toSQL; - $val =~ s/^'(.*)'$/$1/o; - $val =~ s/%/\\\\%/o; - $val =~ s/_/\\\\_/o; - $sql .= " ILIKE '\%$val\%'"; + $val =~ s/\$_$$\$//g; + $val =~ s/%/\\%/o; + $val =~ s/_/\\_/o; + $sql .= " ILIKE \$_$$\$\%$val\%\$_$$\$"; } else { $val = $$val[0] if (ref($val) eq 'ARRAY'); -- 2.43.2