coldfusion queries

Question

Anytime I'm using a query I need to log into the database (as I don't have ODBC setup to do it)

<cfquery name="rsUser" datasource="dbname" username="admin" password="adminpass">
    SELECT * 
    FROM dbo.UsersView
    WHERE UserID = #session.userid#
</cfquery>

the part I don't like is having the username and password visible every time I make a query. I could use a #parameter# but that is only a small improvement. Any other ideas short of setting up the ODBC on the server?

From a DB Admin's stand point you should also check to make sure that each website uses it's own user that only has access to it's own database. That way if someone does manage to hack their way in with SQL Injection or whatever else, they can only do anything with that one website. — Dave Long, Apr 25 '11 at 13:58

score 6 · Accepted Answer · answered Apr 21 '11 at 21:14

If you are using a datasource, you don't need to supply the username and password, they are provided when you set up the datasource. If you don't set up a datasource in the CF Administrator, then you have to user username and password attributes but you'd also have to supply the db server information as well.

So, in short, just pull out your username and password and you should be fine.

Also, it is best practice to use for values passed into your query (in this case, session.userid). cfqueryparam not only helps protect you against security issues like SQL injection attacks, it also tells the the db server to create a prepared statement which will be reused in subsequent calls of the query and thus will increase performance of your queries.

big +1 that you should ALWAYS use cfqueryparam, especially in scopes the end user can modify. — Sean Coyne, Apr 21 '11 at 21:51
fyi.. With CF9, if you set "this.datasource" to be the DSN in your Application.cfc, you dont even need to pass the datasource in the cfquery tag. http://forta.com/blog/index.cfm/2009/6/20/Look-No-Datasource — eapen, Apr 22 '11 at 02:48

score 2 · Answer 2 · answered Apr 22 '11 at 02:16

2

Sometimes people don't like to put their username and password into the CF Admin and there is a simple way around that would be to put your datasource information in the Application.cf(c|m).

If using Application.cfm just do the following somewhere in the Application.cfm

Application.dsn = {
    datasource = 'mydatasource',
    username = 'myusername',
    password = 'mypassword'
}

If using Application.cfc place the same code into your onApplicationStart method. Then in your query just use the following

<cfquery name="myquery" attributeCollection="#Application.dsn#">
     SELECT * FROM mytable
</cfquery>

As you can see this makes your code nice and easy to manage and if your DSN changes you only have to change it in one place.

answered Apr 22 '11 at 02:16

Dave Long

9,569
14
59
89

As the OP pointed out, parameterizing the username and password is only marginally better than putting it in every query. Putting this info in the Administration settings is much safer than putting it in the source code. – Ben Doom Apr 24 '11 at 16:42
I agree that it is only marginally better than putting in every query, but it is still a solution if you don't have access to the CF Admin for whatever reason. – Dave Long Apr 25 '11 at 13:57
My point was that it was mentioned and sismissed by the original poster, so it's not a useful answer. – Ben Doom Apr 25 '11 at 14:01
1

I agree, after seeing that in the original post, I don't think it is useful to the OP, but I do think that it may be useful to others who find this post on Google. – Dave Long Apr 25 '11 at 14:06

coldfusion queries

2 Answers2