http://www.perlmonks.org?node_id=577139


in reply to Basics: CGI MySQL security

I've written quite a few websites with database access and what has been said here is already is pretty decent advice.

how secure you need to go depends on the level of risk you feel is acceptable to your project.


use placeholders in your database queries

when using the DBI the $variable is replaced with a question mark (?). when making the call to the statement you use then $sth->execute(@BindVars). this will be then treated as a litteral, and not interpreted eg select * from dual;drop table users will be inserted into your database, rather than the statement being executed. this is known as an sql injection attack.


use server side authentication schemes for your users

any method that uses some javascript (or anyother client side) method is exceptionally vulnerable to attack, and this means your application is compromised.

instead generate a token server side (md5 of a username/id/secret string) and send it to the browser. at the same time, store it in your mysql database. next time the user requests a page, grab the token from the cookie and compare it to what is in the database.

as mentioned, make sure your token is non trivally determinable (thats just fancy speak for not easily guessable), and remember, there are a lot smarter people that you and i out there who just love puzzles to break.

dont be afraid to be paranoid (or, assume the worst)

make the assumption that everyone out there is out to hack your website. to this end, read about taint checking. think that every input from a user containts carefully crafted cruft to destroy your data.

check all inputs all the time

different physical hosts

as your level of acceptable risk falls (ie, you're less willing to accept risk), think of seperating your presentation and database layers. having your database on a machine that does not contain your webserver, is a good idea. if your webserver is compromised, its going to be just that much harder to compromise your dbserver, especially if you dissallow all access (besides the database of course) from your webserver (eg, via a firewall)

seperation of power, (or physically paranoid)

consider seperating your business logic with your presentation layer - physically. create a bunch of libraries that go and talk to your database, and put them on another physical host. have your websever talk to your libraries and have your libraries talk to your database. a classic three tier structure. (hint, there is a reason why it is a classic)

now your webserver talks to an application server which talks to a database server. a pretty advanced design, and a whole lot of work to implement, especially for a one man band, however, if your webserver is compromised, then there is no direct access to your business layer, which may contain information on how your database works (and hints on how to access it), and how your business processes work, (intentional subtle corruption of data anyone?)

audit trails/exception handling

log log log log. log your users access, trap your errors and provide fall thru conditions to conditional logic where appropriate.

file system security

make sure all your files have the right permissions, and no more than they should. you could install your webserver into a chrooted gaol/(jail) so if the webserver is hacked, it becomes more difficult to pwn the machine itself.

Database security

create your tables with one user role, and have access via another user (without the ability to drop/create objects)

not a whole lot of practical information per se, but concepts that you should atleast look at and understand while developing your web application.

some ideas are more extreme than others, and there is a whole bunch of stuff i've left out (firewalls, network seperation, non public routable addressing, proxying etc etc).

having said that, using placeholders, using taint mode, setting warnings and strict on in your code will help go a long way to providing a secure environment for your application(s)