Your message dated Sat, 28 Jan 2006 20:17:17 +0100
with message-id <[EMAIL PROTECTED]>
has caused the Debian Bug report #347548,
regarding DOMAIN CHECK constraint bypassed
to be marked as having been forwarded to the upstream software
author(s) [EMAIL PROTECTED]
(NB: If you are a system administrator and have no idea what I am
talking about this indicates a serious mail system misconfiguration
somewhere. Please contact me immediately.)
Debian bug tracking system administrator
(administrator, Debian Bugs database)
---------------------------------------
Received: (at 347548-forwarded) by bugs.debian.org; 28 Jan 2006 19:17:51 +0000
>From [EMAIL PROTECTED] Sat Jan 28 11:17:51 2006
Return-path: <[EMAIL PROTECTED]>
Received: from mail.gmx.net ([213.165.64.21])
by spohr.debian.org with smtp (Exim 4.50)
id 1F2va3-00036S-3r
for [EMAIL PROTECTED]; Sat, 28 Jan 2006 11:17:51 -0800
Received: (qmail invoked by alias); 28 Jan 2006 19:17:19 -0000
Received: from dslb-084-063-024-043.pools.arcor-ip.net (EHLO colt.pezone.net)
[84.63.24.43]
by mail.gmx.net (mp001) with SMTP; 28 Jan 2006 20:17:19 +0100
X-Authenticated: #495269
From: Peter Eisentraut <[EMAIL PROTECTED]>
To: [email protected]
Subject: Re: Bug#347548: DOMAIN CHECK constraint bypassed
Date: Sat, 28 Jan 2006 20:17:17 +0100
User-Agent: KMail/1.8.3
Cc: [EMAIL PROTECTED]
References: <[EMAIL PROTECTED]>
In-Reply-To: <[EMAIL PROTECTED]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <[EMAIL PROTECTED]>
X-Y-GMX-Trusted: 0
X-Spam-Checker-Version: SpamAssassin 2.60-bugs.debian.org_2005_01_02
(1.212-2003-09-23-exp) on spohr.debian.org
X-Spam-Level:
X-Spam-Status: No, hits=-4.5 required=4.0 tests=BAYES_10,HAS_BUG_NUMBER
autolearn=no version=2.60-bugs.debian.org_2005_01_02
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/
--
To UNSUBSCRIBE, email to [EMAIL PROTECTED]
with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
