From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Gus Gutoski <shared(dot)entanglement(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: help with data recovery from injected UPDATE |
Date: | 2009-06-11 14:26:46 |
Message-ID: | b42b73150906110726q321b2886m3443aaa4e49ddbc9@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Jun 10, 2009 at 12:49 PM, Gus
Gutoski<shared(dot)entanglement(at)gmail(dot)com> wrote:
> Hi,
>
> I'm a noob who failed to properly sanitize incoming data from the
> front end. As a result, a poor hapless user managed to smuggle in a
> malicious UPDATE statement that corrupted every single record in a
> 70000+ table. Only 3 fields were corrupted and of those only one is
> vital. But it's REALLY vital.
>
> I don't expect there's anything anyone can do, but I've been advised
> that some subscribers to this list are miracle-workers, so it's worth
> a shot.
>
> Here's how it happened. A typical update statement from the front end
> has the form
> UPDATE collections SET foreign_id=2, coin=50-30, bills=10+20 WHERE
> entry_date='2009-09-09';
>
> The hapless user accidentally included TWO minus signs in one entry,
> so the statement looked like this:
> UPDATE collections SET foreign_id=2, coin=50--30, bills=10+20 WHERE
> entry_date='2009-09-09';
>
> (These examples are simplified for the sake of brevity in this message.)
>
> Of course, the double minus sign comments out the rest of the line and
> the statement is left dangling, looking for a terminating semicolon.
>
> Now, my front-end happens to be Visual Basic 6.0 (yeah, I know) via
> ActiveX Data Objects (ADO). In particular, the SQL statement is
> invoked via the ADO Recordset object's Open() method. It appears that
> this Open() method automagically terminates unfinished statements,
> because the above statement *actually executes* in postgres when
> invoked form the VB front end.
>
> Naturally then, *every* record in the database has its "foreign_id"
> field set to 2 and its "coin" field set to 50. I *really* need to
> recover that "foreign_id" field. (As its name suggests, that field is
> a foreign key into a different table.)
>
> Here's some more info. As I'm a noob, I don't know what all to
> include here -- please ask for more info if you need it.
>
> psql version() returns
> PostgreSQL 8.1.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
> 3.4.2 (mingw-special)
If you have not done so already, immediately shut down the database,
and make a full filesystem copy of it (two better). On windows, iirc
this in postgresql/$pgversion/data by default. Following that you are
in for a tough slog, depending on how proficient you are with manually
setting up the database...
does postgresql 8.1 have last_vacuum time in the pg_stat_all_tables
coiumn? if so, I'd check that to see if vacuum was fired since the
'incident'.
plus, there is no way you are escaping the obligatory 'where are your
backups?'. :-). postgresql 8.1 supports pitr archiving. you can
do continuous backups and restore the database to just before the bad
data.
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2009-06-11 14:50:21 | Re: [pgsql-general] Daily digest v1.9081 (14 messages) |
Previous Message | rodeored | 2009-06-11 14:24:42 | Re: interval is ignored |