1

I have an ASP.NET MVC app I've developed for a client. MVC 5, EF 6.

The web server and db server are separate. All of a sudden a bunch of data was deleted from the db server. Users were performing no delete functions. Is there any scenario that would cause this? The records seem to be random. No stored procs, triggers, etc.... running. The app was working fine for months. Any scenario where SQL Server (2014 version) would delete records in a table? No errors were displayed to the user.

**** UPDATE ****

The only "delete" related code that I rolled out recently was this...

   [Authorize]
    public class WorkResultsController : Controller
    {
        private readonly ABC_WorkingContext db = new ABC_WorkingContext();

        public ActionResult DeleteEvent(int id, bool? performRedirectAfterDelete = true)
        {
            if (!WorkFormServices.IsEditOrDeleteEnabled(id)) return this.HttpNotFound();

            var @event = this.db.Events.Find(id);

            try
            {
                // first remove any Work questions related to this event
                var WorkResultsToDelete = this.db.WorkResults.Where(e => e.EventId == id).ToList();
                foreach (var row in WorkResultsToDelete) this.db.WorkResults.Remove(row);

                this.db.Events.Remove(@event);
                this.db.SaveChanges();

                if (performRedirectAfterDelete == true) return this.RedirectToAction("Index", "WorkScheduler");
                return this.Json(
                    new { success = true, responseText = "Delete success!" },
                    JsonRequestBehavior.AllowGet);
            }
            catch (Exception)
            {
                return this.Json(
                    new { success = false, responseText = "Delete failed!!" },
                    JsonRequestBehavior.AllowGet);
            }
        }

I want to delete only WorkResults records related to the specific ID. So, I believe this is working correctly. Do you see any unintended deletes that could happen?

WebDevGuy2
  • 1,159
  • 1
  • 19
  • 40
  • 1
    Only one scenario... somebody (or something like a service) delete them. Pretty easy to do a FIND ALL on the word "delete" across all your code. See what is going rogue – musefan Feb 09 '18 at 16:15
  • 1
    There are a bunch of companies with a lot of profit riding on SQL Server not deciding to arbitrarily delete data on its own, not the least of which is Microsoft itself, so of all the possible causes, you can put that one on the very bottom of the list. You can do a `DBCC CHECKDB` just to be sure the database has seen no silent corruption, but even that is supremely unlikely. Think in terms of what your application can do, and also in terms of who can log in directly to the server. You may want to set up profiler traces to see if you can catch something in the act. – Jeroen Mostert Feb 09 '18 at 16:19
  • There are several ways to get objects marked as Deleted in Entity Framework, some of which aren't obvious when looking at it. You should try to find the suspicious parts of code where the currently deleted data were possibly touched. Until there's something you can show here this question is unanswerable. – Gert Arnold Feb 09 '18 at 16:21
  • Sorry, can't edit, if using EF you should probably search "remove" too. Even with large applications, these kind of keywords are usually quite infrequent, so it shouldn't take long to review them all – musefan Feb 09 '18 at 16:22
  • Try this answer: https://stackoverflow.com/questions/5299669/how-to-see-query-history-in-sql-server-management-studio It may, or may not, help you locate the queries that executed the deletion. Which could help in locating the source. – user7396598 Feb 09 '18 at 16:24
  • There are a lot of scenarios - the user deleted the records but refuses to acknowledge it. They restored an earlier version of the database and didn't realize. The application may forget to commit a transaction around an `INSERT`. – Panagiotis Kanavos Feb 09 '18 at 16:46
  • Thanks everyone for your insights. I updated my OP with some delete code I recently rolled out. But, it seems to work perfectly, unless you see any holes in it. – WebDevGuy2 Feb 09 '18 at 17:17
  • @GertArnold I posted some code in my OP... does anything look suspicious? – WebDevGuy2 Feb 09 '18 at 19:51
  • Assuming that `db` has the same lifespan as the controller, no. But were the deleted records in this area? It doesn't necessarily have to be code that is *intended* to delete data. For example, you could look at any collection that is cleared. – Gert Arnold Feb 09 '18 at 21:09
  • Why do you assume that this loss is the product of your application? It could have been done manually - an error that no one will likely confess. – SMor Feb 09 '18 at 21:46
  • @GertArnold thanks. This was the only delete related code that was rolled out to production recently. So, that's what caught my attention. But, it seems like I'm doing everything right. I cannot duplicate any errors. So, that makes it tough to solve. – WebDevGuy2 Feb 09 '18 at 22:42

2 Answers2

3

I agree with Min - a DB won't just delete data. This is more than likely a code (app or DB side) issue or a breach of some kind.

I would check:

  • app code - is there a bad SQL call/statement (related to the tables you're missing data from) that could have deleted more than it should
  • Stored Procs, Triggers - same as above - an SQL mistake could wreak havoc
  • Table relationships - are any unwanted cascade deletes set up?
  • EF - are there any unwanted cascades set up in this between entities?
  • Logins - for sanity - change the passwords for the logins your app uses...this could be a breach maybe - hard to tell without seeing the pattern of missing data
scgough
  • 5,099
  • 3
  • 30
  • 48
1

First, no commercial DB deletes random data by itself. If it really deletes its client's data, its maker would be sued by client.
So, there are DELETE queries in somewhere or someone executed DELETE operation on SQL SERVER Studio. You can monitor DB queries. Check your queries and find which query delete your data. And ask DBA or DB Operator if they executed some queries.

In my experience, there is no "THERE IS NO SUCH QUERY".

Min Hyoung Hong
  • 1,102
  • 9
  • 13