On Wed, Jun 10, 2009 at 12:49 PM, Gus Gutoski<shared.entanglement@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
