Fresh Blurbs

Enhanced MySQL Transaction Security

A transaction in the database modifies data. Due to its impotance, any extra care and security you can apply to a transaction, can pay back big time in a critical situation. There is one measure which is relatively easy to implement in MySQL and can save you a lot of headache if something goes south.

There are transactions in program logic that developer knows should only ever affect one row. It can be a Delete or Update statement and we can predict that it never spans across multiple rows. If that is a case, it is a good practice to add "Limit 1" at the end of the statement. That way, even if for some reason our delete or update statement's "where" clause gets messed up, we will only damage one row at max per transaction.

Now, let's assume you are building your "where" clause on-the-fly (disclaimer: generally, a security risk in itself). Let's imagine that, for whatever reasons, under some conditions you end up with a generated statement like:

delete from users where user_id is not null

now, compare this to

delete from users where user_id is not null Limit 1


P.S. Bad things happen, ignoring their possibility is just inviting trouble.

comments powered by Disqus