From: C. Bensend <benny@bennyvision.com>
To: pgsql-sql@postgresql.org
Subject: Re: Inserting into table only if the row does not already
Date: Fri, 15 Oct 2004 08:14:51 -0500 (CDT)
Message-ID: <64883.63.227.74.41.1097846113.squirrel@63.227.74.41> (raw)
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>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: benny@bennyvision.com
Subject: Re: Inserting into table only if the row does not already
In-Reply-To: <64883.63.227.74.41.1097846113.squirrel@63.227.74.41>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox