Should I Specify Username and Password for every Query?

[Kory Gorsky]

I've been using Coldfusion since version 5 and during that time I've always chosen to not include username and password when setting up my datasource in CFAdmin. Instead I include it in all of my queries:

<cfquery datasource="MyDataSource" username="MyUsername" password="MyPassword">

I'm starting to use CF9 and I'm wanting to use the "new query" script method in my components, but I don't want to have to include the username and password every time:

<cfscript>

local.myquery = new query();

local.myquery.setDatasource('MyDataSource');

local.myquery.setUsername('MyUsername');

local.myquery.setPassword('MyPassword');

.....

</cfscript>

I'm wondering if there are any security concerns with defining the Username and Password in my datasource in CFAdmin? I'm also wondering if YOU pre-define them or not?

[Owainnorth · Accepted solution]

Depends on a few things:

Which version are you running? Standard or Enterprise?

If Enterprise, are you using Sandboxing?

It is your own server, or a shared server?

Personally, I don't define them (except for ORM, when I have no choice) for several reasons;

If you're running a shared server with no sandboxing, anyone on the box can use and manipulate your data. I need say no more there.

If you're running Enterprise from within a sandbox that's just splendid, save your password in CFAdmin. However, what if you move to a shared box? What if your boss won't pay for an Enterprise upgrade so you move to Standard? You're either going to leave yourself wide open to a hack or your code isn't going to run. For these reasons, I would never store the details in CFAdmin.

How about creating yourself a little helper function like this, and putting it somewhere useful (include file, Application scope etc):

<cffunction name="newQueryWithDetailsAlreadySetAndACatchyName" access="public" returntype="query">

  <cfscript>

    var q = new query() ;

    q.setDatasource(APPLICATION.DB.getDatasource());

    q.setUsername(APPLICATION.DB.getUsername());

    q.setPassword(APPLICATION.DB.getPassword());

    return q ;

  </cfscript>

</cffunction>

Then rather than the code you posted, you could just do:

<cfscript>

  myQuery = newQueryWithDetailsAlreadySetAndACatchyName() ;

  myQuery....

</cfscript>

That way you get the best of both worlds - don't need to store your data and you don't end up repeating your code.

O.

[Kory Gorsky]

We are using our own server in enterprise mode. I will definitely be utilizing this.

Thanks for your response!