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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
