ColdFusion Securing Databases (part 1)

Most ColdFusion applications I'm come across tend to use a single datasource or if they use more than one the same user credentials are used. As well as causing possible performance and scalability issues this can be a security risk.

It's quite easy to restrict what SQL statements a datasource will run with the ColdFusion administrator.

Why would you restrict the SQL statements a datasource can run? To stop SQL injection attacks and make your application more secure.

Consider this code used to check if a user name and password is correct from a login form:

If the value of form.password was "'xxx';insert into users (name,password) values ('hacker','password')" a new login would be created, and via this method, called a SQL injection attack, someone could access your application without a password by creating their own login. (The exact way to run multiple statements depends on the database.) Even worse it would be possible to stop the application from working with something like "'xxx';drop table users".

If you look at a datasource in the ColdFusion administrator you'll notice a "Show Advanced Settings" button. If you click on this you can see that you can restrict the SQL operations that the datasource can run.

Straight away you can see that some SQL statements should be disallowed. Most applications don't need create, drop, alter, grant or revoke and these should be disabled. You would also want to disable stored procedures if you application doesn't use them.

You can then disable update, insert and delete by partitioning your application and creating multiple datasources.

Let take a hypothetical web site that displays a simple poll. Users of the site select answers to a question (the poll) and those answers are saved in the database. Administrators of the system can enter new questions and look at the results of previous polls.

Rather than using one datasource for the entire web site we can use two, one for the users of the system and one for the administrators.

The user datasource only needs to run select statements (display polls) and insert statements (save answers). Update and delete can be disabled for the user datasource.

The administrators need to run select statements (view answers), insert statements (new questions) and update statements (edit existing questions). Delete can be disabled for the administrator datasource.

If you do this is will be harder for SQL injection attacks or other malicious code to be run and alter your database.

Returning to the login issue above. In this case you would create a datasource that is only used on the login page and that can only run select statements. That way it would be impossible to alter the database via the login form no matter the values of name or password.

Of course you should always validate user input and use cfqueryparam to hinder SQL injection attacks.

In part 2 I'll look at securing the application further by assigning permissions to the database tables.