January 19, 2009 at 2:44 am
Hi ,
Well I'm having a strange situation cropping up about ASP interface and SQL Server 2005 and for over some weeks I'm experiencing some vanishing records from database. There are no deleting statement in any of the related programs that post records in the table in question and still some records are literally deleted leaving no trace and it varies from days to weeks without specific sequence.
This problem occuring in only one table and situation occurs with only records already existed in the database.
First time i thought it was users not properly doing the processing , and I stayed with them and monitor them , all worked fine for sometime I've even generated reports to prove the records were in database but then sundenly this problem appears again and again.
Please let me know if anyone have ever encountered this kind of problem. Any help would be really appreciated.
Nashreen
January 19, 2009 at 3:23 am
Without a delete or truncate query records will not vanish any where...
Are there any triggers defined on that table? May be there is a trigger on some other table that is causing this delete...
Are there any archive job scheduled?
Can you run a trace and check from where the delete is getting fired? And who is issuing that query?
-Vikas Bindra
January 19, 2009 at 3:26 am
Hi
SQL by its own will not delete any data, when u say its from ASP, then you have to look on codes, its so easy when you have narrowed down to a table, do a trace on and see what transactions are been going on in the database, if not if you know its on one table, put a trigger that will let you know from where the delete operation is been performed. 🙂
January 19, 2009 at 3:27 am
Could possible be a SQL injection attack.
How are you acessing your database?
are you using Stored Procedures or Inline SQL?
January 19, 2009 at 4:04 am
Hi all -vikas bindra, CrazyMan, steveb....
Thanks for all your replies ..
Please read on below...
Actually ,all programs related are just posting(inserting) records in the table. there are no scheduling of job. Only routine backups.
I don't think of SQL injection attack... as the users are not into this well.
By the way, CrazyMan can you please explain me what u mean by "put a trigger that will let you know from where the delete operation is been performed."? how i can proceed , give me an example please.. a hint..
Its NOT Stored Procedures but using Inline SQL via program to access database..
well yes I've tried the SQL Profiler and run a trace. I could get hold of the HostName column , but my problem is when i'm connecting via the program interface(ASP) its using the IUSR login and , its only giving me the ServerMachine name, not the client/user hostname.
Is there anyway i get hold of the user machine name please
Nashreen
January 19, 2009 at 4:24 am
nashreen.mosaheb (1/19/2009)
I don't think of SQL injection attack... as the users are not into this well.
You'll be surprised. It's a web app and you're using inline SQL. You are extremely vulnerable to SQL injection there and all it takes is one person whose fooling around or whose machine has some malware that tries SQL injection attacks.
Is this an internal app, or is it available on the internet?
By the way, CrazyMan can you please explain me what u mean by "put a trigger that will let you know from where the delete operation is been performed."? how i can proceed , give me an example please.. a hint..
Look up CREATE TRIGGER in Books Online
Is there anyway i get hold of the user machine name please
only by monitoring the web server. The connections to SQL are coming from the web server. That's the only host that it knows about.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2009 at 4:27 am
How are you so sure that it's not SQL injection....? As you are using inline queries sql injection can be the reason if not enough validations are done at the front end.
Its not possible to get the the client name in SQL profiler when the connecting vai another sever...SQL will know about the immediate server/client from where the request was generated.
There could be possibly a bug in your ASP application from where the DELETE is fired. If you have the code of the application can you search the code for the keyword 'DELETE' and see if some DELETE statement on your table is hanging around somewhere.
-Vikas Bindra
January 19, 2009 at 4:30 am
Thanks for ur 2 prompt answers gilamonster...
Its an internal application.. only use by some users ..
I know its vulnerable .. can a virus be the cause?
January 19, 2009 at 4:33 am
Also, you can try below query to check if any SQL object is there where you have a (unwanted) DELETE statement for your table.
SELECT o.name,c.text from sys.syscomments c
INNER JOIN sys.objects o ON o.id = c.id
WHERE contains(c.text, ' "delete" AND "tablename" ')
-Vikas Bindra
January 19, 2009 at 4:57 am
nashreen.mosaheb (1/19/2009)
I know its vulnerable .. can a virus be the cause?
Maybe. I've seen malware that uses SQL injection. It's not the first place I'd look, but it is a possibility. "The users don't know how" is not a good excuse for not securing against SQL injection.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2009 at 7:43 pm
This problem occuring in only one table and situation occurs with only records already existed in the database.
How do you know the records already existed in the database? And, does this table, by any chance, have an IDENITY column on it?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply