Quote from: MikesMechanix on June 24, 2011, 05:29:38 PM

Quote from: ikonic on June 21, 2011, 02:50:41 AM

~~Any and all interaction with the database should done using either Stored or Prepared Procedures~~

Prepared statements, yes, stored procs, NO.

SPs never really increase security (unless you are talking about the DA's job security), but they do complicate the design. Therefore, you shouldn't use them "just because". Most apps these days use some form of ORM and a minimal set of sprocs, if any.

In addition to the salt data stored with the hashed password and the validation fields I'm keeping on each row, there's also an additional application-specific salt that exists only in the stored procedures which, of course, have the "WITH ENCRYPTION" flag set. This adds an extra layer of difficulty to password cracking attempts since not all of the salt data will be known to an attacker without first going through SQL's built-in encryption.

I also have validation fields on each row of every table such that inserts or updates made without going through the stored procs will be considered invalid. Every stored proc re-validates every record it touches and locks the account if invalid records are found. There is no way to buy, sell, deposit or withdraw bitcoins without a correct validation field and the validation fields are SHA512 with both stored salt data and additional salt in the encrypted stored procedures.

Stored procs which update this validation number require their own validation in the form of a session key which is a hashed amalgam of both a large random number and browser fingerprint data, such that if the cookie were stolen (a la firesheep) it would still be useless without also faking HTTP headers, IP address etc. These session keys are stored in a manner similar to password hashes and are invalidated at the database level after ten minutes of inactivity. This is also my method for enforcing a ten minute auto-logout on idle: if your session key in the database is null, every page redirects to login.

So never say never... Anything can be used as a tool to increase security, it all depends on how you use it. I chose to enforce a lot of my security and data integrity rules at the database level rather than at the web server or application level. Since SQL resides on a separate server which is not internet accessible, it places much of my infrastructure behind at least one more layer of security.

P.S.: As an added benefit, the offloading of many transaction processing and security tasks to stored procedures also allows me to split the load more evenly between the CPUs of my web server and my SQL server, thus increasing the transaction rate that I can handle with the same hardware.