Offensive Coding with SQL

Offensive Coding with SQL

Programmers are often advised to code defensively, checking the validity of all parameters before proceeding. In reality, when accessing a database, there is a real advantage in coding offensively, trying to do several things simultaneously.

A good example is a succession of various checks, designed to flag up an exception whenever the criterion required by any of these checks fails to be met. Let's assume that some kind of payment by a credit card has to be processed. There are a number of steps involved. It may be necessary to check that the customer id and card number that have been submitted are valid, and that they are correctly associated one with the other. The card expiration date must also be validated. Finally, the current purchase must not exceed the credit limit for the card. If everything is correct, the debit operation may proceed.

An unskilled developer may write as follows:

    select count(*)
    from customers
    where customer_id = provided_id

and will check the result.

Then the next stage will be something like this:

    select card_num, expiry_date, credit_limit
    from accounts
    where customer_id = provided_id

These returns will be checked against appropriate error codes.

The financial transaction will then proceed.

A skillful developer will do something more like the following (assuming that today( ) is the function that returns the current date):

    update accounts
    set balance = balance - purchased_amount
    where balance >= purchased_amount
      and credit_limit >= purchased_amount
      and expiry_date > today(  )
      and customer_id = provided_id
      and card_num = provided_cardnum

Then the number of rows updated will be checked. If the result is 0, the reason can be determined in a single operation, by executing:

    select c.customer_id, a.card_num, a.expiry_date,
           a.credit_limit, a.balance
    from customers c
         left outer join accounts a
              on a.customer_id = c.customer_id
              and a.card_num = provided_cardnum
    where c.customer_id = provided_id

If the query returns no row, the inference is that the value of customer_id is wrong, if card_num is null the card number is wrong, and so on. But in most cases this query will not even be executed.

Did you notice the use of count(*) in the first piece of novice code? This is a perfect illustration of the misuse of count(*) to perform an existence test.

The essential characteristic of "aggressive coding " is to proceed on the basis of reasonable probabilities. For example, there is little point in checking whether the customer existsif they don't, they won't be in the database in the first place! Assume nothing will fail, and if it does, have mechanisms in place that will address the problem at that point and only that point. Interestingly, this approach is analogous to the "optimistic concurrency control " method adopted in some database systems. Here update conflicts are assumed not to occur, and it is only when they do that control strictures are brought into play. The result is much higher throughput than for systems using pessimistic methods.

Code on a probabilistic basis. Assume the most likely outcome and fall back on exception traps only when strictly necessary.

 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows