It's worth mentioning that tracking addresses is a lot harder than one might suspect.
Let's go with these assumptions:
* Deposit addresses are UNIQUE (generated by server)
* Withdrawal addresses are NOT UNIQUE (generated by untrusted parties)
What's the rational for not forcing non-uniqueness on the withdrawal addresses?
* If all withdrawal addresses are forced to be unique, I could submit any withdrawal address and if it bounces, I know it's in the database. => Leaks database information.
Okay so we don't force uniqueness. What are the pitfalls?
* Tracking addresses becomes complex, our database now has to allow for multiple records of the same withdraw address. We build a primary key { user, withdraw_address } for each record.
=> deduct the balance before the withdrawal goes into the queue, and make sure that it targets the right user.
What if an attacker attempts to use a deposit address as a withdrawal address?
* This is a more interesting scenario, especially if you combine both deposit and withdrawal addresses in the same table.
Let's take this simple table as our initial state.
Mallory uses the deposit address of Alice as a withdrawal address.
Alice | DEPOSIT | A |
Mallory | WITHDRAW | A |
Alice does a new deposit and the payment handler has a flaw, it assumes the deposit addresses are unique (not true in a combined table!).
SELECT user FROM table WHERE (address = A)
That could result in Mallory being selected as the receiver of the deposited funds by Alice. Simply by failing to verify that the type of the address was indeed a deposit address rather than a withdrawal address.
The correct way of getting the right user for the deposit address would be:
SELECT user FROM table WHERE (address = A AND type= DEPOSIT)
In a combined table (storing both deposit and withdrawal addresses), the primary key is { user, type, address }.
Long story short, do not store deposit addresses and withdrawal addresses in the same table in your database.
The uniqueness deposit vs withdraw addresses does not hold, and they deserve their own tables. A single missing type check in a query can spell disaster.