Your message dated Wed, 22 Feb 2006 08:27:13 +0100
with message-id <[EMAIL PROTECTED]>
and subject line Bug#347548: DOMAIN CHECK constraint bypassed
has caused the attached Bug report to be marked as done.
This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.
(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)
--- Begin Message ---
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.
I have refrained from sending this bug upstream because I am a little
short of time to build a vanilla postgresql 8.1 and retest, which is
what they will want. I am not sure if any debian patches may be at fault
or if this is a genuine upstream bug.
-- System Information:
Debian Release: testing/unstable
APT prefers unstable
APT policy: (500, 'unstable')
Architecture: i386 (i686)
Shell: /bin/sh linked to /bin/bash
Kernel: Linux 2.6.12.2-02sws
Locale: LANG=en_GB, LC_CTYPE=en_GB (charmap=ISO-8859-1)
Versions of packages postgresql-8.1 depends on:
ii libc6 2.3.5-11 GNU C Library: Shared
libraries an
ii libcomerr2 1.38+1.39-WIP-2005.12.31-1 common error description library
ii libkrb53 1.4.3-5 MIT Kerberos runtime libraries
ii libpam0g 0.79-3 Pluggable Authentication
Modules l
ii libpq4 8.1.2-1 PostgreSQL C client library
ii libreadline5 5.1-5 GNU readline and history
libraries
ii libssl0.9.8 0.9.8a-5 SSL shared libraries
ii postgresql-cl 8.1.2-1 front-end programs for PostgreSQL
ii postgresql-co 39 manager for PostgreSQL
database cl
ii zlib1g 1:1.2.3-9 compression library - runtime
postgresql-8.1 recommends no packages.
-- no debconf information
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
--- End Message ---
--- Begin Message ---
Version: 8.1.3-1
Hi Tim!
Tim Southerwood [2006-02-21 23:18 +0000]:
> I've checked it with my code and concur that the reported problem is fixed
> in debian/testing/postgresql 8.1.3-1 which is what I happen to have
> installed.
Great, thanks for checking this again. So I guess this bug report can
be closed.
/me sings 'Another one bites the dust'. :)
> To the Debian folks: outstanding - thank you :)
You're welcome. Well, I didn't have anything to do with fixing this
particular bug, though. :)
> To the pgSQL folk: Postgresql rocks, thanks again :)
I agree :)
Martin
--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntulinux.org
Debian Developer http://www.debian.org
signature.asc
Description: Digital signature
--- End Message ---
