Secure Transactions and Transactions for Security

Make sure the right amount of money ends up in the right bank account

One of my post that may later become a book on Secure Code. Also one of my posts on Application Security.

Free Content on Jobs in Cybersecurity | Sign up for the Email List

This blog post is part of a series on secure coding principles that may become a future book like my other blog-book Cybersecurity for Executives in the Age of Cloud. If you want to know when the book gets published, follow me on Medium or Twitter — or both!

In my last post I posed a question I often asked in interviews when working on back-office banking and tax systems:

Let’s say you have a system that makes calls across a network to update the money in a bank account. The network connection times out before you get a response from the system to which you made the request. How do you handle that? 

Possible answers:

* Resend the transaction to make sure it went through.
* Assume it went through and don’t do anything because you don’t want the person to get a double payment.

Or something else?

A transaction is one of those words that means different things in different contexts. A transaction may refer to buying or selling something. When you’re on an e-commerce website, a transaction involves pushing the submit button on the order page. Your credit card gets charged the order amount and in return, you get whatever you ordered.

Transactions in Accounting Systems

A transaction may also occur to transfer money between bank accounts or withdraw money from an ATM. In that case, one bank account needs to be debited and one needs to be credited, in accounting-speak. A debit will increase an asset account or increase a liability account. A credit will do the opposite. When you update an accounting system to record a transaction you generally have to perform two operations: a debit and a credit. If you do not perform both operations your accounts will be out of balance.

When it comes to security, an attacker who can find a way to cause part of a transaction to fail could cause a system to not deduct money from their account when it should and allow them to make multiple transactions that exceed the value the attacker has in their account. For this reason, and to maintain good financial records, you need to ensure that the required operations always all occur, or do not occur at all.

Consider the example I posted at the beginning of this blog post. A financial transaction occurs that adds money to one account and deducts it from another. The attacker finds a way to cause the first operation to succeed and the second to fail. The attacker has two accounts. They repeatedly perform the operation to add money to one of the accounts without deducting it from the other. Now the attacker has more money than before when you add the value of both accounts.

“End of Day” is a term used when performing all the required reconciliations and processes that need to occur at the end of the day to ensure all the accounting records are accurate. When I worked as a snack bar manager at Mt. Rainier National Park in college I had to add up all the money at the end of the day and compare it to the cash register records and deposit it to the office in the Paradise Lodge. Banking systems do something similar.

To find errors such as the one in our attacker scenario, accounting processes involve something called reconciliation. During this process, accounts or bookkeepers will perform calculations to ensure the books balance. In back-office banking systems, they are typically reviewing reports the system produced during end-of-day processing. When performing the reconciliation process for our example, they should see that the debits and credits do not add up and find the error. However, if the attacker has already withdrawn their money it will be too late, so it’s better to prevent the error in the first place.

There are so many ways in which accounting and financial systems may be manipulated that involve transactions. I worked on a complex dividend processing system where account balances needed to be transferred between two companies after calculating and distributing the dividends. How this transfer occurred left room for error in the way the transfer was calculated and processed. I fixed that by creating code that would not allow manipulation of the transaction when it occurred.

Each of the dividend calculations had different codes and ways to process the numbers. The transactions got processed in batches at a time. All that needed to add up and when I obtained this dividend processing system, there were random discrepancies at the end of the day. I fixed that as well.

I also once worked on a tax system that calculated taxes incorrectly every month — to the tune of $300,000! We fixed that as well using solid transaction logic and some other mechanisms I’ll explain in other chapters. When the code was released we were off by $23 and I was upset. How could that be? The financial people at the company said it was inconsequential, but I was sure I did not let anything like that into my code. Turns out another member of my team inserted some code with a rounding error. We fixed it.

I wrote about another type of financial transaction issue in my prior book. I was working on improving the process to transfer funds from other banks when a customer opened an account. An extraneous line of code led to some odd money transfers in the system that I resolved later. I won’t repeat all that here, but understand that financial transactions are tricky! Every line of code needs to be scrutinized.

Transactions in Databases

In software programming, a transaction is a construct that allows you to start executing a particular block of code and then roll back all the actions that the code took if an error occurs.

Transactions are commonly used in database code. Many financial systems use SQL (Structured Query Language) to write database code to move money around in the database when a financial transaction occurs. We used them a lot in back-office programming at the bank where I worked. These types of transactions are generally supported by relational database systems. More about different types of NoSQL database systems in the next section.

Most of the time when you carry out a financial transaction in a database it requires more than one line of code. First, you need to subtract the money from one account. Then you need to write a line of code to add it to another account. Here’s some pseudo-SQL to show you what the code generally looks like to subtract 100 from one account in the database and add it to another:

//subtract money from the sending bank account
update bank_account set balance = balance - 100 
      where account_number = 1234

//add money to the receiving bank account
update bank_account set balance = balance + 100 
      where account_number = 5678

These are two separate lines of code and in this case very simple. Often in a transaction, there will be a number of calculations and transformations of numbers when performing operations like this. In addition, the database could have some sort of problem while running the code and the execution of these two lines of code fails to complete. The database might run the first line and fail to execute the second line.

If that scenario occurs, your accounts are not accurate. You only want the database to update the first account if it can also update the second account. This is where transactions come in. You wrap the code in a transaction, similar to how you would use an error handler. The difference is that when an error occurs you can tell the database to roll back any of the lines it executed before the error occurred.

Here’s an example of what that code might look like.

BEGIN TRY
    BEGIN TRANSACTION 
        //subtract money from the sending bank account
        update bank_account set balance = balance - 100 
        where account_number = 1234

        //add money to the receiving bank account
        update bank_account set balance = balance + 100 
        where account_number = 5678
    COMMIT
END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

Notice that the code has error handlers like those covered in the last blog post. There’s a Try-Catch statement to try to perform the two statements involved in the transaction. If the code has no error, then it will be committed to the database with the COMMIT statement. If an error occurs, the transaction will roll back any lines of code that it executed prior to the error.

This is a very simple transaction example. Let’s say you have a transaction that involves multiple values on one side and one value on the other with no transaction. Let’s say this code gets executed with inline SQL statements in a Java application (i.e. no stored procedure). The Java application pulls the values from a flat file received from a third party. Usually, it would be a CSV file.

function (transfer_amount, fee, customer_account_1,   
    customer_account_2, bank_fee_account)

  //add money to the receiving bank account
  update bank_account set balance = balance + transfer_amount
        where account_number = customer_account1

  //Subtract money sending bank account
  update bank_account set balance = balance - transfer_amount
        where account_number = customer_account2

  //subtract wire transfer fee from sending bank account
  update bank_account set balance = balance - fee
        where account_number = customer_account2

  //Add money sending bank account
  update bank_account set balance = balance + fee
        where account_number = bank_fee_account

Someone creating the CSV file puts invalid values into the “fee” column. The system processes the files and for each line, it calls the function I wrote in pseudo-code above. When the system tries to process the third update statement to subtract the fee from customer_account_2 an error occurs.

In this scenario, the credits and debits are in balance. If the reconciliation process is not specifically looking to see that the fee balance aligns with the number of wire transfers processed by the system, no red flags will indicate to those performing reconciliations that a problem exists. However, the customer got to send a wire transfer without paying a fee.

A transaction would fix that. It would roll back the account transfer if it hit an error deducting the fee:

BEGIN TRY
    BEGIN TRANSACTION 
      //add money to the receiving bank account
      update bank_account set balance = balance + transfer_amount
        where account_number = customer_account1

       //Subtract money sending bank account
       update bank_account set balance = balance - transfer_amount
        where account_number = customer_account2

       //subtract wire transfer fee from sending bank account
       update bank_account set balance = balance - fee
        where account_number = customer_account2

      //Add money sending bank account
      update bank_account set balance = balance + fee
        where account_number = bank_fee_account
    COMMIT
END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

PS: More on data types to come!

Now you’re all jazzed to use transactions right? Beware that transactions can lock tables so no other write operations can occur while the transaction gets processed. This can lead to performance issues. If a table on an e-commerce website locks the database people may not be able to place orders. On an investment website, such as the one I worked on, people may not be able to make trades.

A database lock can help your security. You can lock tables to prevent other processes from touching them while performing financial calculations. For example, during end-of-day processing and reconciling accounts, locks will prevent anything from changing until that processing is complete. Maybe I’ll write more about database locks in the book but just be aware that performance issues are a consideration when locking tables.

https://docs.microsoft.com/en-us/dynamics365/business-central/admin-view-database-locks

Eventually Consistent

When NoSQL databases first came onto the scene most of them did not support transactions. NoSQL databases aim for better performance and reliability by storing data in a structurally different way. In addition, most of them were eventually consistent. What that means is that when a transaction occurs it will not immediately appear in all the databases that store the related data.

For example, you can have a system that has three database servers supporting it. The three database servers may be replicas of each other to ensure that if one goes down, the application continues to run. You may also want to locate your database near the place where processing occurs to reduce latency.

Sometimes you have logs feeding into databases in three different regions but in the end, they all need to end up in one database. You might have a log appear in the Frankfurt, Germany region of AWS. There are two other databases in the US and Canada. Someone queries the US log server and they don’t immediately see that log record, but eventually, they will when the databases sync up.

That may be ok for a logging system but consider a financial system. You have an account with $1000 in the account in the US. Someone pulls $1000 out of an ATM in Germany. At the same time, someone pulls $1000 out of an ATM in the US. Both transactions go through because the US database hasn’t yet received the update showing that the ATM transaction in Germany took out all the money.

That’s why, when performing transactions, tables get locked. One transaction needs to complete before the next one can start. That’s also why distributed databases are challenging! Locking tables across the globe with potential network issues and latency can cause problems.

By the way, you might want to understand whether Bitcoin is or is not eventually consistent and how that could impact you if you choose to transact in that currency. I’m not going to get into that debate here. You can find many articles and research papers on the topic. Other cryptocurrencies may have similar considerations.

Some NoSQL databases now support transactions. I haven’t tested them out but in theory, they should be able to provide similar transaction functionality that relational databases offer.

Transactions in Application Code

I prefer to perform financial calculations in databases whenever possible. I’ll explain why you should be using stored procedures as well in a future post, for security reasons. I prefer to have transactions written in a single type of code on a single type of platform to minimize the risk of mismatched processing or data types, a topic for a separate chapter. Additionally, relational databases are written with data integrity in mind by some of the smartest people out there. Databases are incredibly performant — and complicated! They are meant to store and process data efficiently. That is their sole purpose.

However, sometimes it just makes more sense to perform a transaction in your application code. We had a particular web design that required two steps of a transaction to occur in such a way that it could not possibly be handled by one stored procedure in the database.

The DBA wanted us to redesign the entire application. I argued that we could use a transaction in the application tier instead. This is a judgment call. Do you have solid processes to ensure someone working on the application tier won’t later remove the transaction code because they don’t understand why it exists?

At any rate, I trusted the person working on the application tier to build the code correctly. When we delivered the system to production it worked great with no errors. The improved user interface made the company a lot of money on new inbound transfers (compared to fax-a-souras-rex, as someone in the transfer department called it.)

A transaction in your application tier code works much the same way as your database code. Here’s an example of a transaction written in C#:

using (IDbTransaction tran = conn.BeginTransaction()) {
    try {
        // your code
        tran.Commit();
    }  catch {
        tran.Rollback();
        throw;
    }
}

Looks pretty similar right? You can use transactions to ensure that your code doesn’t perform three steps of an operation that requires six steps to be accurate because it got an error. In that case, you can catch the error and roll back.

Isolation levels

If you are going to use a transaction in any language, dig into the details of the documentation before you start and make sure you fully understand how they work. Ensure you understand the different properties that affect how your transaction behaves in different scenarios. For example, .NET has different isolation levels that indicate whether data can be read while a transaction is in flight.

Databases have similar concepts.

You may want to block people from reading data while a transaction is in progress to prevent the retrieval of partially updated information. In our bank account example, one account may be debited but the other not yet credited and someone viewing that data might think an error occurred. In other cases, like perhaps viewing scores on a video game, it’s no big deal if the data is slightly off while a transaction is processing. These design issues can get very tricky and once again, require a bit of software engineering to think through the potential issues and tradeoffs and get it right.

Transactions and networks

At the beginning of my post, I posed a question. You are sending a transaction across the Internet to add money to another bank account and your request times out. You never got the response, but there is a possibility that the other system got and processed the data. Or maybe they didn’t.

Let’s say you are using a transaction in your SQL stored procedure and it rolled back due to a system timeout. You haven’t reduced any money in your local records for the bank account sending the money. But what if the other system got the request and processed it prior to some issue that caused the timeout?

The proper answer to my question is that you can’t know whether or not the transaction occurred successfully until you can query the records in the other system. You should flag the transaction failure with a code of some kind indicating that you got a network timeout. Then query the other system to determine if you should reprocess the system or not.

Handling transactions can be very tricky and requires logical thinking to make sure an unexpected error does not lead to unbalanced accounts — or worse, loss of funds.

Bitcoin and Double-Spend

When I first researched bitcoin I skimmed a book on the topic. I see many reasons why cryptocurrencies are risky, but one in particular that caught my eye was “double spend.” Of course, everyone who wants you to believe in cryptocurrency and invest in Bitcoin will tell you that although it may be technically possible the risk is very low, and that it has never happened. Don’t worry.

After working on banking systems where the numbers either add up, or they don’t, I decided Bitcoin was not for me based on that possibility alone. Even if you think the possibility is so rare and very unlikely, it may one of those Black Swan events people take for granted. A Black Swan event is a highly improbable event that can cause a lot of damage. You can read about that and other faulty logic related to risk decisions in this book and the second book I liked even better.

If you don’t think it’s a problem, suit yourself. I choose not to participate. If at some point the risk of using a bank exceeds the risk posed by the potential double spend and the other risks that may cause monetary loss, I reserve the right to change my mind. But having worked on banking systems, I trust them more at this point. The fees are there to support the salaries of people such as myself who keep those systems secure, and ensure transaction have the intended outcome.

Next Steps

• Review your code for places where transactions require multiple lines of code to execute together for an accurate outcome.
• Insert transactions as needed for accurate calculations.
• Understand isolation levels and performance impact of table locks.
• Review where you are using consistent and eventually consistent systems and how they may affect transactions and security.
• Ensure that you handle transactions properly when network and system errors produce ambiguous results.
• Test your application logic for errors due to lack of transactions — or hire a penetration tester such as myself with experience in this area to do it for you!

Follow for updates.

Teri Radichel | © 2nd Sight Lab 2022

About Teri Radichel:
~~~~~~~~~~~~~~~~~~~~
⭐️ Author: Cybersecurity Books
⭐️ Presentations: Presentations by Teri Radichel
⭐️ Recognition: SANS Award, AWS Security Hero, IANS Faculty
⭐️ Certifications: SANS ~ GSE 240
⭐️ Education: BA Business, Master of Software Engineering, Master of Infosec
⭐️ Company: Penetration Tests, Assessments, Phone Consulting ~ 2nd Sight Lab

Need Help With Cybersecurity, Cloud, or Application Security?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
🔒 Request a penetration test or security assessment
🔒 Schedule a consulting call
🔒 Cybersecurity Speaker for Presentation

Follow for more stories like this:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
❤️ Sign Up my Medium Email List
❤️ Twitter: @teriradichel
❤️ LinkedIn: https://www.linkedin.com/in/teriradichel
❤️ Mastodon: @teriradichel@infosec.exchange
❤️ Facebook: 2nd Sight Lab
❤️ YouTube: @2ndsightlab