Re: Bug#347548: DOMAIN CHECK constraint bypassed

This bug was reported to Debian.  Comments?

Tim Southerwood wrote:
> Package: postgresql-8.1
> Version: 8.1.2-1
> Severity: important
>
> DOMAIN CHECK constraint is bypassable when inserting rows using
> perl/DBD::Pg AND prepare/execute semantics AND using bind values.
>
> This is serious as data integrity rules are not consistently
> enforced.
>
> To reproduce:
>
> Create a database, we will use the name "photostore" for this
> example. Run the following SQL through psql:
>
> -- Cut
> CREATE DOMAIN absdirpath AS text
>         CHECK(
>                 VALUE ~ '^[[:print:]]+$' AND
>                 VALUE ~ '^/'
> );
>
> CREATE TABLE image
> (
>         basedir absdirpath NOT NULL
> ) WITH OIDS;
> -- Cut
>
> Now try the following perl program (you will need to adjust
> connection parameters:
>
> # Cut
> #!/usr/bin/perl
>
> use strict;
> use warnings;
>
> use DBI;
>
> my $res;
> # Change to suit your database server
> my $dbh = DBI->connect("dbi:Pg:dbname=photostore", '', '',
>         {AutoCommit => 1, RaiseError => 0, PrintError => 1});
>
> die "Cannot open database connection" unless defined $dbh;
>
> $res = $dbh->do("insert into image (basedir) values ('/tmp')");
> if ($res)
> {
>         print "Insert string was allowed, OK\n";
> }
> else
> {
>         print "Insert string was disallowed, error\n";
> }
>
> $res = $dbh->do("insert into image (basedir) values ('')");
> if ($res)
> {
>         print "Insert empty string was allowed, error\n";
> }
> else
> {
>         print "Insert empty string was disallowed, OK\n";
> }
>
> my $sth=$dbh->prepare("insert into image (basedir) values (?)");
> $res = $sth->execute("");
> if ($res)
> {
>         print "Insert empty string via bind was allowed, error\n";
> }
> else
> {
>         print "Insert empty string via bind was disallowed, OK\n";
> }
>
> $sth=$dbh->prepare("insert into image (basedir) values (?)");
> $res = $sth->execute(undef);
> if ($res)
> {
>         print "Insert NULL via bind was allowed, error\n";
> }
> else
> {
>         print "Insert NULL via bind was disallowed, OK\n";
> }
>
> $dbh->disconnect();
> # Cut
>
> The output I get is:
>
> # Cut
>
> Insert string was allowed, OK
> DBD::Pg::db do failed: ERROR:  value for domain absdirpath violates
> check constraint "absdirpath_check"
> Insert empty string was disallowed, OK
> Insert empty string via bind was allowed, error
> DBD::Pg::st execute failed: ERROR:  null value in column "basedir"
> violates not-null constraint
> Insert NULL via bind was disallowed, OK
>
> # Cut
>
> You can clearly see that inserting the empty string via do("INSERT
> ...") is correctly rejected, but performing the same insert via
> prepare/execute with bind values succeeds.
>
> Further verifcation: Connect to the database via psql and try some
> selects. Here's my example:
>
> -- Cut
>
> photostore=> SELECT basedir from image;
> basedir
> ---------
> /tmp
>
> (2 rows)
>
> photostore=> SELECT length(basedir) from image;
> length
> --------
>       4
>       0
> (2 rows)
>
> -- Cut
>
> We have one row which should be impossible to insert.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/