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.
--
To UNSUBSCRIBE, email to [EMAIL PROTECTED]
with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
