Database Transaction Security and the “Halloween Problem.”

By | November 23, 2016
Data Protection

Database Transactional Based Fraud, Transaction Security, and the “Halloween Problem.”

Many database attacks are directed at external interfaces and intended to cause delays in accessing or using data which includes malicious transactions. The main concern with malicious transactions is danger to data integrity and availability.  Currently, there is no practical mechanism that identifies attackers executing malicious transactions.  Ayushi, Sharma, & Bansal (2010) have proposed development of a Database Malicious Transactions Detector (DBMTD) mechanism.

The DBMTD would consist of two different phases: transaction profiling and intrusion detection. Transaction profiling entails identifying authorized transactions as sequences of valid commands. The database transaction would be represented as a direct graph describing the different execution paths (sequences of selects, inserts, updates, and deletes) between the beginning of the transaction and the commit or rollback command. The proposed DBMTD would be designed to run autonomously and include an auditing mechanism. The auditing mechanism would collect information about the commands executed by the database users. Some typical information logged for each command would include; username, session identification, commands entered by the user, and transaction identification. The DBMTD would compare transactions against pre-defined transaction profiles (Ayushi, Sharma, & Bansal, 2010).

The phrase “Halloween Problem” refers to a SQL UPDATE query intended to give a 10% raise to every employee who earned less than $25,000. The problem was that the query kept giving 10% raises until everyone earned at least $25,000. The underlying issue within the “Halloween Problem” is that the SQL language provides a way for users to specify database changes using an UPDATE statement, but the syntax says nothing about how the database engine should perform the changes. White (2013) states that the SQL standard does specify that the result of an UPDATE must be the same as if it had been executed within three non-overlapping phases as follows:

  • A read-only search determines the records to be changed and the new column values
  • Changes are applied to affected records
  • Database consistency constraints are verified

Implementing the three phases in a database engine would avoid the “Halloween Problem” and produce the correct results, but performance could be adversely impacted. An alternative strategy is to process the UPDATE a row at a time which has the advantage of only touching each row once, and generally does not require memory for storage (White, 2013).



Ayushi, Sharma, A., & Bansal, R. (2010). Detection of Malicious Transactions in DBMS. International Journal of Information Technology and Knowledge Management, Volume 2, No. 2(July-December 2010), 675-677. Retrieved April 11, 2016, from

White, P. (2013, February 13). The Halloween Problem – Part 1. Retrieved April 11, 2016, from

Leave a Reply

Your email address will not be published. Required fields are marked *