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)
OS is Windows XP (I think -- possibly Vista. I'll check next time I'm
at that machine).
Yours in need,
Gus