Re: help with data recovery from injected UPDATE

On Wed, Jun 10, 2009 at 12:49 PM, Gus
Gutoski<shared.entanglement@gmail.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