all records deleted from table
Does variable and CFQueryParam checking also apply to Access databases? Is it needed when using MS Access 2000 as a backend? ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
all records deleted from table
Does val() and CfQueryParam checking also apply if you're using Access as your backend database? ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
[EMAIL PROTECTED] wrote: Does variable and CFQueryParam checking also apply to Access databases? Is it needed when using MS Access 2000 as a backend? Yes. I am not sure whether you get the performance improvements with Access, but cfqueryparam is still a protection against SQL Injection Attacks. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
all records deleted from table
A long time ago, I made a really stupid mistake that results in deletion of all data from a table... A tip I picked up from somewhere - it might even have been this list - is to not ever actually delete records using code. Instead, I have a dtDeleted field, which is set to the current date when I want to delete the record. When doing selects, I add WHERE dtDeleted IS NULL to get only non-deleted records. I had an admin section which was secured using session variables. Records kept being deleted in big batches. It took a long time to track down, but it turned out the client was using the Alexa toolbar in his browser - the program managed to capture his username and password as well as urls he visited, and use that to spider the site for the internet wayback machine. In that case all took to stop it was a robots.txt file. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kay Smoljak wrote: | A long time ago, I made a really stupid mistake that results in | deletion of all data from a table... | | | A tip I picked up from somewhere - it might even have been this list | - is to not ever actually delete records using code. Instead, I have | a dtDeleted field, which is set to the current date when I want to | delete the record. When doing selects, I add WHERE dtDeleted IS | NULL to get only non-deleted records. | good idea, badly implemented. a select using a null criteria cannot use a index and means the database must do a full table scan which means ~ bad performancetry changing your logic round so you can use an index... z -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+QSAM1lRHdy8ASusRAnb4AJoDt66u5fWbgibWEoFPmqmMu5AElwCeMmsK pSKubEkErq+ymszYSb/7QRo= =lz66 -END PGP SIGNATURE- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
all records deleted from table
| Kay Smoljak wrote: | A tip I picked up from somewhere - it might even have been this list | - is to not ever actually delete records using code. Instead, I have | a dtDeleted field, which is set to the current date when I want to | delete the record. When doing selects, I add WHERE dtDeleted IS | NULL to get only non-deleted records. | Zac Spitzer wrote: good idea, badly implemented. a select using a null criteria cannot use a index and means the database must do a full table scan which means ~ bad performancetry changing your logic round so you can use an index... Hey, thanks for the tip. Will definitely look into that... ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
Zac Spitzer wrote: Kay Smoljak wrote: | | A tip I picked up from somewhere - it might even have been this list | - is to not ever actually delete records using code. Instead, I have | a dtDeleted field, which is set to the current date when I want to | delete the record. When doing selects, I add WHERE dtDeleted IS | NULL to get only non-deleted records. | good idea, badly implemented. a select using a null criteria cannot use a index and means the database must do a full table scan which means ~ bad performancetry changing your logic round so you can use an index... That is just a limitation of your specific dbms. Others can use much fancier indexing schemes. (If your database supports it, look into partial indexes for this type of problem.) Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: all records deleted from table
I add a IsDeleted flag to perform soft deletes and use DateChanged field to record the last date that the record was updated. -Original Message- From: Kay Smoljak [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 7:47 AM To: CF-Talk Subject: all records deleted from table | Kay Smoljak wrote: | A tip I picked up from somewhere - it might even have been this list | - is to not ever actually delete records using code. Instead, I have | a dtDeleted field, which is set to the current date when I want to | delete the record. When doing selects, I add WHERE dtDeleted IS | NULL to get only non-deleted records. | Zac Spitzer wrote: good idea, badly implemented. a select using a null criteria cannot use a index and means the database must do a full table scan which means ~ bad performancetry changing your logic round so you can use an index... Hey, thanks for the tip. Will definitely look into that... ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jochem van Dieten wrote: | Zac Spitzer wrote: | |Kay Smoljak wrote: || || A tip I picked up from somewhere - it might even have been this list || - is to not ever actually delete records using code. Instead, I have || a dtDeleted field, which is set to the current date when I want to || delete the record. When doing selects, I add WHERE dtDeleted IS || NULL to get only non-deleted records. || | |good idea, badly implemented. a select using a null criteria cannot |use a index and means the database must do a full table scan which means |~ bad performancetry changing your logic round so you can use an |index... | | | That is just a limitation of your specific dbms. Others can use much | fancier indexing schemes. (If your database supports it, look into | partial indexes for this type of problem.) interesting i think oracle (at least in the 8i world) ms-sql don't support it, which dbms *DO* support it? z -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+QSql1lRHdy8ASusRAtArAJwLPMHtUOvCWV9KR5Ronyoi9NljbQCgqla/ LwScJAm+7C2MZthJd2ZhqCA= =y9Ph -END PGP SIGNATURE- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
Zac Spitzer wrote: Jochem van Dieten wrote: | | That is just a limitation of your specific dbms. Others can use much | fancier indexing schemes. (If your database supports it, look into | partial indexes for this type of problem.) interesting i think oracle (at least in the 8i world) ms-sql don't support it, which dbms *DO* support it? PostgreSQL has them. I would expect DB2 to have them as wll, since IBM funded some research into this: http://simon.cs.cornell.edu/home/praveen/papers/partindex.de95.ps.Z (If you already know a little about indexing concepts it is interesting, but it is not a beginners guide.) Typical indexing would have a unique index over the primary key field without any predicate, and all other indexes only over the current records. In this case that would be: CREATE INDEX currentRecords_idx ON table (field) WHERE dtDeleted IS NOT NULL; Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: all records deleted from table
-Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 10:32 AM To: CF-Talk Subject: Re: all records deleted from table PostgreSQL has them. I would expect DB2 to have them as wll, since IBM funded some research into this: http://simon.cs.cornell.edu/home/praveen/papers/partindex.de95 .ps.Z (If you already know a little about indexing concepts it is interesting, but it is not a beginners guide.) Typical indexing would have a unique index over the primary key field without any predicate, and all other indexes only over the current records. In this case that would be: CREATE INDEX currentRecords_idx ON table (field) WHERE dtDeleted IS NOT NULL; I may be reading this wrong, but I think that you'd accomplish nearly the same thing (albeit in two steps) in SQL Server by indexing a view. You'd have to take the view into account on the select however... So many this doesn't do the same thing at all. But it's one way of getting close at least. Jim Davis ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
Where do you perform updates to these tables? What does your data look like and how much data do you have? The code you have posted below looks sound, perhaps somehow all your data was updated by an update query which then would meet the delete criteria below. It sounds highly unlikely, but you have to look at all angles when trying to solve a problem that happens only once in a while. I would start by finding the delete query which caused the problem in log files, if you are logging each query executed, then work backwards to see where an odd update may have occurred. CC Tim Do TDo @lahd.lacity.org To: CF-Talk [EMAIL PROTECTED] cc: Subject: all records deleted from table 02/04/03 04:30 PM Please respond to cf-talk Hi all, I ran into a big problem this morning when I found out that all the data from my table were somehow deleted. I wanted to make sure that it wasn't my code so I did an extended search for delete from tablename and delete tablename in my code and I'm positive that there is only one instance that I do a delete from this table. I had this happen a long while ago to the same exact table. Here is my delete page, any ideas on how this could've happened would be greatly appreciated. CFQUERY NAME=getTenantCoding DATASOURCE=#rentDSN# SELECT CItem FROM CodeOfUnits WHERE CItem=#CItem# AND UnitNo='#UnitNo#' /CFQUERY CFIF getTenantCoding.recordCount GT 1 CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# DELETE CodeOfUnits WHERE UnitNo='#UnitNo#' AND CItem=#CItem# /CFQUERY CFSET notice=Tenant info has successfully been deleted! CFELSE CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# DELETE CodeOfUnits WHERE UnitNo='#UnitNo#' AND CItem=#CItem# /CFQUERY CFQUERY NAME=deleteTenant DATASOURCE=#rentDSN# DELETE Tenant WHERE CImproveNo=#caseNo# AND UnitNo='#UnitNo#' /CFQUERY CFSET notice=Tenant info has successfully been deleted! /CFIF ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
Jim Davis wrote: -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 10:32 AM To: CF-Talk Subject: Re: all records deleted from table http://simon.cs.cornell.edu/home/praveen/papers/partindex.de95.ps.Z Typical indexing would have a unique index over the primary key field without any predicate, and all other indexes only over the current records. In this case that would be: CREATE INDEX currentRecords_idx ON table (field) WHERE dtDeleted IS NOT NULL; I may be reading this wrong, but I think that you'd accomplish nearly the same thing (albeit in two steps) in SQL Server by indexing a view. You'd have to take the view into account on the select however... So many this doesn't do the same thing at all. But it's one way of getting close at least. MS SQL Server indexed views (a.k.a. materialized views a.k.a. materialized query tables) are quite different. They depend on a part of the data being stored in another table and that part being maintained by triggers. If you compare that to the scenario from the paper you will notice some differences. For one type of query they are/can be much faster (depends on the presence of joins/aggregates), but they lack the flexibility of partial indexes. And they come at a hefty price once you need more as 1 indexed view, because not only the system that issues the queries has to keep track of more and more tables which are only usefull for specific queries, but each table has to be stored and eats disk space. For heavily indexed tables it is not uncommon to have the indexes take more space as the data itself, now imagine storing that data X times and the having all those indexes. BTW, materialized views can be implemented in any database that can use triggers. I don't really understand why many database vendors insist on naming them views when they are just extra tables. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
I also now put a confirmation page between the link to delete and the actual delete page.. this confirmation page has a form where you have to actually type the word DELETE into a form field and hit the submit button to do the deletion, so a web crawler can't trigger it. At 09:30 AM 2/5/2003, you wrote: | A long time ago, I made a really stupid mistake that results in | deletion of all data from a table... | | | A tip I picked up from somewhere - it might even have been this list | - is to not ever actually delete records using code. Instead, I have | a dtDeleted field, which is set to the current date when I want to | delete the record. When doing selects, I add WHERE dtDeleted IS | NULL to get only non-deleted records. | ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: all records deleted from table
BTW, materialized views can be implemented in any database that can use triggers. I don't really understand why many database vendors insist on naming them views when they are just extra tables. Because putting Now, more tables! into the sales material just doesn't have the same effect as Now, with materialized views! Jochem, you're a smart guy, but you'll never make it in marketing. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
Dave Watts wrote: BTW, materialized views can be implemented in any database that can use triggers. I don't really understand why many database vendors insist on naming them views when they are just extra tables. Because putting Now, more tables! into the sales material just doesn't have the same effect as Now, with materialized views! Maybe I should have said I don't understand why people ever started to use the marketing term for this feature instead of a sound technical term like 'Calculated Ready Access Partial'-table.. Jochem, you're a smart guy, but you'll never make it in marketing. Thanx for the compliment ;-) Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: all records deleted from table
-Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 12:50 PM To: CF-Talk Subject: RE: all records deleted from table BTW, materialized views can be implemented in any database that can use triggers. I don't really understand why many database vendors insist on naming them views when they are just extra tables. Because putting Now, more tables! into the sales material just doesn't have the same effect as Now, with materialized views! True - but there is something to be said for abstracting this. You can build them in any DB that offers triggers, sure. But if the DB offrs support for them then you can just Create index and be done with it. The DB does all the work, hides all the triggers and tables, and let's you ignore the processes. You can maintain them easier, manage them easier, etc. It may not be a big feature, but come on - it IS a feature. ;^) Jochem, you're a smart guy, but you'll never make it in marketing. Agree... and agree. ;^) Jim Davis ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
all records deleted from table
Jochem van Dieten wrote: PostgreSQL has them. I would expect DB2 to have them as wll, since IBM funded some research into this: snip Interesting... I mostly use SQL Server, sometimes Interbase (not with CF yet though) - I don't suppose you have any idea whether Interbase/Firebird supports partial indexes? Although, the other idea posted in this thread (using a flag and a dtModified field) is probably a better way to do it - not quite so clean though. K. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
Kay Smoljak wrote: Interesting... I mostly use SQL Server, sometimes Interbase (not with CF yet though) - I don't suppose you have any idea whether Interbase/Firebird supports partial indexes? I never heard it does. Although, the other idea posted in this thread (using a flag and a dtModified field) is probably a better way to do it - not quite so clean though. It is the same method, only in the first case a NULL date is the key and in the other case a boolean set to false. My point was merely that whether this matters for indexability depends on your DBMS. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
all records deleted from table
Hi all, I ran into a big problem this morning when I found out that all the data from my table were somehow deleted. I wanted to make sure that it wasn't my code so I did an extended search for delete from tablename and delete tablename in my code and I'm positive that there is only one instance that I do a delete from this table. I had this happen a long while ago to the same exact table. Here is my delete page, any ideas on how this could've happened would be greatly appreciated. CFQUERY NAME=getTenantCoding DATASOURCE=#rentDSN# SELECT CItem FROM CodeOfUnits WHERE CItem=#CItem# AND UnitNo='#UnitNo#' /CFQUERY CFIF getTenantCoding.recordCount GT 1 CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# DELETE CodeOfUnits WHERE UnitNo='#UnitNo#' AND CItem=#CItem# /CFQUERY CFSET notice=Tenant info has successfully been deleted! CFELSE CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# DELETE CodeOfUnits WHERE UnitNo='#UnitNo#' AND CItem=#CItem# /CFQUERY CFQUERY NAME=deleteTenant DATASOURCE=#rentDSN# DELETE Tenant WHERE CImproveNo=#caseNo# AND UnitNo='#UnitNo#' /CFQUERY CFSET notice=Tenant info has successfully been deleted! /CFIF ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: all records deleted from table
Are you type checking the variables that get passed into your queries? If those variables came from a form submission or a URL parameter it would be simple for a malicious user to hack some code into those queries that can delete all the data.. Could this have been caused by a naughty hacker? -Original Message- From: Tim Do [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 5 February 2003 9:31 AM To: CF-Talk Subject: all records deleted from table Hi all, I ran into a big problem this morning when I found out that all the data from my table were somehow deleted. I wanted to make sure that it wasn't my code so I did an extended search for delete from tablename and delete tablename in my code and I'm positive that there is only one instance that I do a delete from this table. I had this happen a long while ago to the same exact table. Here is my delete page, any ideas on how this could've happened would be greatly appreciated. CFQUERY NAME=getTenantCoding DATASOURCE=#rentDSN# SELECT CItem FROM CodeOfUnits WHERE CItem=#CItem# AND UnitNo='#UnitNo#' /CFQUERY CFIF getTenantCoding.recordCount GT 1 CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# DELETE CodeOfUnits WHERE UnitNo='#UnitNo#' AND CItem=#CItem# /CFQUERY CFSET notice=Tenant info has successfully been deleted! CFELSE CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# DELETE CodeOfUnits WHERE UnitNo='#UnitNo#' AND CItem=#CItem# /CFQUERY CFQUERY NAME=deleteTenant DATASOURCE=#rentDSN# DELETE Tenant WHERE CImproveNo=#caseNo# AND UnitNo='#UnitNo#' /CFQUERY CFSET notice=Tenant info has successfully been deleted! /CFIF ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: all records deleted from table
yes.. I also check for some info first before you can even hit the delete file -Original Message- From: Ryan Sabir [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 2:30 PM To: CF-Talk Subject: RE: all records deleted from table Are you type checking the variables that get passed into your queries? If those variables came from a form submission or a URL parameter it would be simple for a malicious user to hack some code into those queries that can delete all the data.. Could this have been caused by a naughty hacker? -Original Message- From: Tim Do [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 5 February 2003 9:31 AM To: CF-Talk Subject: all records deleted from table Hi all, I ran into a big problem this morning when I found out that all the data from my table were somehow deleted. I wanted to make sure that it wasn't my code so I did an extended search for delete from tablename and delete tablename in my code and I'm positive that there is only one instance that I do a delete from this table. I had this happen a long while ago to the same exact table. Here is my delete page, any ideas on how this could've happened would be greatly appreciated. CFQUERY NAME=getTenantCoding DATASOURCE=#rentDSN# SELECT CItem FROM CodeOfUnits WHERE CItem=#CItem# AND UnitNo='#UnitNo#' /CFQUERY CFIF getTenantCoding.recordCount GT 1 CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# DELETE CodeOfUnits WHERE UnitNo='#UnitNo#' AND CItem=#CItem# /CFQUERY CFSET notice=Tenant info has successfully been deleted! CFELSE CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# DELETE CodeOfUnits WHERE UnitNo='#UnitNo#' AND CItem=#CItem# /CFQUERY CFQUERY NAME=deleteTenant DATASOURCE=#rentDSN# DELETE Tenant WHERE CImproveNo=#caseNo# AND UnitNo='#UnitNo#' /CFQUERY CFSET notice=Tenant info has successfully been deleted! /CFIF ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
Yeah that's my thought as well, take a look at your web logs and see if someone was playing around with putting SQL in the URL... -- jon mailto:[EMAIL PROTECTED] Tuesday, February 4, 2003, 5:30:27 PM, you wrote: RS Are you type checking the variables that get passed into your queries? RS If those variables came from a form submission or a URL parameter it would RS be simple for a malicious user to hack some code into those queries that can RS delete all the data.. RS Could this have been caused by a naughty hacker? RS -Original Message- RS From: Tim Do [mailto:[EMAIL PROTECTED]] RS Sent: Wednesday, 5 February 2003 9:31 AM RS To: CF-Talk RS Subject: all records deleted from table RS Hi all, RS I ran into a big problem this morning when I found out that all the data RS from my table were somehow deleted. I wanted to make sure that it wasn't my RS code so I did an extended search for delete from tablename and delete RS tablename in my code and I'm positive that there is only one instance that RS I do a delete from this table. I had this happen a long while ago to the RS same exact table. Here is my delete page, any ideas on how this could've RS happened would be greatly appreciated. RS CFQUERY NAME=getTenantCoding DATASOURCE=#rentDSN# RS SELECT CItem RS FROM CodeOfUnits RS WHERE CItem=#CItem# AND UnitNo='#UnitNo#' RS /CFQUERY RS CFIF getTenantCoding.recordCount GT 1 RS CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# RS DELETE CodeOfUnits RS WHERE UnitNo='#UnitNo#' AND CItem=#CItem# RS /CFQUERY RS CFSET notice=Tenant info has successfully been deleted! RS CFELSE RS CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# RS DELETE CodeOfUnits RS WHERE UnitNo='#UnitNo#' AND CItem=#CItem# RS /CFQUERY RS CFQUERY NAME=deleteTenant DATASOURCE=#rentDSN# RS DELETE Tenant RS WHERE CImproveNo=#caseNo# AND UnitNo='#UnitNo#' RS /CFQUERY RS CFSET notice=Tenant info has successfully been deleted! RS /CFIF RS ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
The items in red is where your problem lies! a simple sql statement via the url could delete all data from your db. Have a look at your webserver log file, do a search for DELETE, if its anywhere in the log file then see if its a SQL statement, if so, thats the hacker, trace its IP address... To solve your probs in the future use #val(yourVariable)# for any integer used in your SQL clause, i.e. ## without the single quotes around it... Taco Fleur PS. there are many sites out there that do not know about this, I used to work for a company called RP Data who I told about this problem, but they don't care (or did not believe me). You can completely take over servers with this hole... www.massiveauctions.com is another one with the hole, I also told them but they don't care! Funny, people are playing with other peoples personal information and they just don't care Hi all, I ran into a big problem this morning when I found out that all the data from my table were somehow deleted. I wanted to make sure that it wasn't my code so I did an extended search for delete from tablename and delete tablename in my code and I'm positive that there is only one instance that I do a delete from this table. I had this happen a long while ago to the same exact table. Here is my delete page, any ideas on how this could've happened would be greatly appreciated. CFQUERY NAME=getTenantCoding DATASOURCE=#rentDSN# SELECT CItem FROM CodeOfUnits WHERE CItem=#CItem# AND UnitNo='#UnitNo#' /CFQUERY CFIF getTenantCoding.recordCount GT 1 CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# DELETE CodeOfUnits WHERE UnitNo='#UnitNo#' AND CItem=#CItem# /CFQUERY CFSET notice=Tenant info has successfully been deleted! CFELSE CFQUERY NAME=deleteTenatCoding DATASOURCE=#rentDSN# DELETE CodeOfUnits WHERE UnitNo='#UnitNo#' AND CItem=#CItem# /CFQUERY CFQUERY NAME=deleteTenant DATASOURCE=#rentDSN# DELETE Tenant WHERE CImproveNo=#caseNo# AND UnitNo='#UnitNo#' /CFQUERY CFSET notice=Tenant info has successfully been deleted! /CFIF ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
-- Original Message -- From: Taco Fleur [EMAIL PROTECTED] PS. there are many sites out there that do not know about this, I used to work for a company called XXX who I told about this problem, but they don't care (or did not believe me). You can completely take over servers with this hole... YYY is another one with the hole, I also told them but they don't care! Funny, people are playing with other peoples personal information and they just don't care Of course not. It'll never happen to them. (or so they think) Scott Scott Brady http://www.scottbrady.net/ ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: all records deleted from table
The items in red is where your problem lies! a simple sql statement via the url could delete all data from your db. Have a look at your webserver log file, do a search for DELETE, if its anywhere in the log file then see if its a SQL statement, if so, thats the hacker, trace its IP address... To solve your probs in the future use #val(yourVariable)# for any integer used in your SQL clause, i.e. ## without the single quotes around it... Just a couple of pieces of advice. First, I'd recommend using CFQUERYPARAM instead of Val in your inline SQL whenever possible. Second, in my experience, people aren't really interested in deleting your data usually. They'd much rather do something less visible but more useful (to them), like install rootkits on your database server. So, looking for DELETE in your web server log files and not finding it, doesn't mean that you haven't been victimized. PS. there are many sites out there that do not know about this, I used to work for a company called RP Data who I told about this problem, but they don't care (or did not believe me). You can completely take over servers with this hole... www.massiveauctions.com is another one with the hole, I also told them but they don't care! Funny, people are playing with other peoples personal information and they just don't care One final piece of advice. In general, you should probably avoid posting information about specific vulnerabilities on specific servers. Someday, someone may be held liable for negligence for doing that sort of thing - I think it's just a matter of time - and you probably don't want to be that person. It's analogous to me telling everyone that Ray Camden leaves his front door unlocked, and oh by the way he lives at ... (sorry for dragging you into this, Ray.) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
I just noticed that the HTML is removed from the message so you wont see the items in red. CItem=#CItem# is where the problem lies. talking about security. I know to: - always use val() with integers in my queries - always filter ANY data submitted, including hidden fields - use regEx to remove any invalid chars from submitted data - use regEx to replace characters like to amp; to lt; to gt; to quot; ' to middot; (have not found the right equivelant to ' yet) when these characters are to be used in submitted data What about UNICODE and MS SQL server, can anyone enlighten me on that one? And if possible point out any possible holes I might have missed? TIA Taco Fleur ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
Your name makes me hungry Taco :) taco's At 09:33 AM 2/5/2003 +1000, you wrote: I just noticed that the HTML is removed from the message so you wont see the items in red. CItem=#CItem# is where the problem lies. talking about security. I know to: - always use val() with integers in my queries - always filter ANY data submitted, including hidden fields - use regEx to remove any invalid chars from submitted data - use regEx to replace characters like to amp; to lt; to gt; to quot; ' to middot; (have not found the right equivelant to ' yet) when these characters are to be used in submitted data What about UNICODE and MS SQL server, can anyone enlighten me on that one? And if possible point out any possible holes I might have missed? TIA Taco Fleur ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
Just a couple of pieces of advice. First, I'd recommend using CFQUERYPARAM instead of Val in your inline SQL whenever possible. Second, in my experience, people aren't really interested in deleting your data usually. They'd much rather do something less visible but more useful (to them), like install rootkits on your database server. So, looking for DELETE in your web server log files and not finding it, doesn't mean that you haven't been victimized. I agree..! Just did not want to go that far, besides using val() in your CFQUERYPARAM still has its advantages, like not getting an error when a non integer is passed to it. One final piece of advice. In general, you should probably avoid posting information about specific vulnerabilities on specific servers. Someday, someone may be held liable for negligence for doing that sort of thing - I think it's just a matter of time - and you probably don't want to be that person. It's analogous to me telling everyone that Ray Camden leaves his front door unlocked, and oh by the way he lives at ... (sorry for dragging you into this, Ray.) I agree, and normally don't do these sort of things, however there is a long history behind this story. And I feel that if websites like these HAVE been given ENOUGH warnings about these security holes and still DON'T take action, and by doing so put the personal data of 50.000 clients at stake including their credit card information, I believe they deserve to be mentioned. I don't think my mentioning their names can make responsible for negligence, maybe if I said go to page xxx and use the following code to hack their site, would be ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
A long time ago, I made a really stupid mistake that results in deletion of all data from a table... almost embarrassed to admit this in public, but it might apply here. There was an admin page with a list of all of the records in the table, along with a link to delete or edit each item. The entire website was an intranet and required authentication.. Anyway, the first time we ran a link checker, it of course hit all of the delete links and cleared out the database. I learned my lesson! Al a1webs.com ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: all records deleted from table
In general, you should probably avoid posting information about specific vulnerabilities on specific servers. Someday, someone may be held liable for negligence for doing that sort of thing - I think it's just a matter of time - and you probably don't want to be that person. I agree, and normally don't do these sort of things, however there is a long history behind this story. And I feel that if websites like these HAVE been given ENOUGH warnings about these security holes and still DON'T take action, and by doing so put the personal data of 50.000 clients at stake including their credit card information, I believe they deserve to be mentioned. I don't think my mentioning their names can make responsible for negligence, maybe if I said go to page xxx and use the following code to hack their site, would be That's the problem with the law - you just can't apply rationality to it. I'm not a lawyer, for what that's worth, but in a civil suit, you're responsible if a lawyer can convince a judge or jury that you're responsible - there's certainly no clear standard, no case law, etc. They may well deserve to be mentioned, in some moral sense, but the law isn't about morals, either. Of course, if you feel strongly enough about it, you may still choose to stand by your beliefs. I just thought it was worth warning you about the potential consequences (you might consider posting such things anonymously, if you feel so inclined). Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: all records deleted from table
I see your point, and I thank you for the warning.. - Original Message - From: Dave Watts [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, February 05, 2003 2:21 PM Subject: RE: all records deleted from table In general, you should probably avoid posting information about specific vulnerabilities on specific servers. Someday, someone may be held liable for negligence for doing that sort of thing - I think it's just a matter of time - and you probably don't want to be that person. I agree, and normally don't do these sort of things, however there is a long history behind this story. And I feel that if websites like these HAVE been given ENOUGH warnings about these security holes and still DON'T take action, and by doing so put the personal data of 50.000 clients at stake including their credit card information, I believe they deserve to be mentioned. I don't think my mentioning their names can make responsible for negligence, maybe if I said go to page xxx and use the following code to hack their site, would be That's the problem with the law - you just can't apply rationality to it. I'm not a lawyer, for what that's worth, but in a civil suit, you're responsible if a lawyer can convince a judge or jury that you're responsible - there's certainly no clear standard, no case law, etc. They may well deserve to be mentioned, in some moral sense, but the law isn't about morals, either. Of course, if you feel strongly enough about it, you may still choose to stand by your beliefs. I just thought it was worth warning you about the potential consequences (you might consider posting such things anonymously, if you feel so inclined). Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
