Message-ID: <64883.63.227.74.41.1097846113.squirrel@63.227.74.41>
In-Reply-To: <87hdow4ld1.fsf@stark.xeocode.com>
References: <56362.63.227.74.41.1097813923.squirrel@63.227.74.41>
	<87hdow4ld1.fsf@stark.xeocode.com>
Date: Fri, 15 Oct 2004 08:14:51 -0500 (CDT)
Subject: Re: Inserting into table only if the row does not already 
From: "C. Bensend" <benny@bennyvision.com>
To: pgsql-sql@postgresql.org
User-Agent: SquirrelMail/1.4.3a
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Importance: Normal


> You just have to put it in the select list as a constant. If you're
> feeling
> generous to the next programmer to read it you could put "AS column1"
> after
> each one, but the column name doesn't actually have to match the column
> you're
> inserting into.

Sweet GOD, I hope no one ever has to read the stuff I'm working on!

This is just a personal curiousity project, not anything for work or
anything released publicly.  I'd be far too embarrassed to ever release
this, as I'm just fumbling along, learning.  :)

> Note that this is going to have some concurrency issues. I think it will
> be
> possible for a second query to execute before the first commits. In that
> case
> it won't see the record the first query inserted and try to insert again.
> You'll just get a primary key violation though which I guess you can just
> ignore.

Concurrency shouldn't be an issue - this is a perl script running from
cron.  The only concurrency that will ever happen is if I'm a bonehead
and I run the script manually right as cron kicks off another copy.

> Which raises a question. Why not forgoe this complicated SQL and try to do
> the
> insert. If you get a primary key violation, well there's your answer... If
> you
> don't care about the failure just ignore it and move on. I would suggest
> checking specifically for a primary key violation and still stopping
> execution
> on unexpected errors though.

Well, I was hoping to minimize the amount of perl needed to get this one
task done, but I think that's going to be the best way to do it.

> Incidentally, if you're putting your parameters directly into your queries
> using $column1 then you've got a potential security problem. Unless you're
> quoting every variable everywhere religiously using postgres's quoting
> functions an attacker can sneak extra SQL into your queries. Potentially
> including whole new statements such as "DELETE FROM table"...

Yes indeed, good catch.  I'll clean that up immediately.

Thanks, Greg!

Benny


-- 
"Even if a man chops off your hand with a sword, you still have two nice,
sharp bones to stick in his eyes."
                                                      -- .sig on Slashdot