In ASP I was able to make a connection with any connection string I want. Now in ColdFusion it seems as though I must use a DSN for my datasources. I've done some research and I haven't found anything else to disprove this.

Can you tell me if it's possible to use a DSN-Less connection in ColdFusion without using the ADODB objects commonly used in ASP?

Thanks, in advance, for anyone who replies.

I've never found a solution to that either. As far as I can tell ColdFusion relies on ODBC (which is a really crappy solution in itself). Now I'm wondering again, even thought I don't code CF much anymore, and I'll bet Byron's the man to answer that

There used to be an option with cfquery in CF5 for a connection string, but that has since been depricated.

In MX you have a couple of options for DB conections.

1. Setup a native connection via the CFADMIN, this is a DSN, but is not a windows DSN, and uses native drivers to connect to the DB. These are not ODBC connections and are faster then the next method, since they are using native drivers.

2. Setup a ODBC DSN in windows and add a ODBC Socket connection in the CFADMIN. This does use ODBC and is slower and not prefered. You also need to make sure the CF ODBC server service is running on the server.

3. The wonderful world of Java and using JDBC will do DSN-less connections, I've never done this, but I found a link you might find useful, I make no guarantees about it since I didn't look that hard, but there are probably a lot of other examples out there.
http://www.geocities.com/empiricallyspeaking/index.html

#2 is sill the best way to go, since they are native drivers and will be the fastest.

I'm using HostMySite as the host and my superiors are telling me I can't put the username and password in the DSN so I have to put that in my page in the CFQUERY.

This is horrible for security because if a page were to error it would show the login information to the database and cause a huge problem to anyone that knew how to exploit it.

My thoughts are that if my host can't allow me to do that I would switch to someone that did. No offense to HMS but I don't like it.

Thanks Josh & Byron

First off the login used to access your db, should have the bare minimum of permissions to your database.

Second, it's very common to create a DSN in the CFADMIN without a user and password and in your cfquery use the dsn + the username and password attributes in the code.

I would say this is what 99% of our customers do or should be doing. If you set the user/pass in the DSN in the CFADMIN then anyone on the shared box can access your DSN.

Sandboxes are a problem as well, and the management is setup a little backwords. Any new dsn's have to be added to all the sandboxes NOT allowed to use the DSN, which from a hosting stand point is backwards. MM is aware of this issue and says they are going to deal with that in the future.

If a CF page errors out, you don't see the source code, so I don't see how having the user/pass in the code would be an issue. You can also encrypt the templates before uploading them to the shared machine as well. Homesite actually has this feature built in, and I would recommennd this as well.

Blackstone the next version of CFMX is going to have sourceless deployments, which will solve this issue all together. Although I don't know the details on how this works yet.

I have no choice on the database user I'm using because of the limitation imposed on my by my superiors. I'm just using what they will give me.

And it does, in fact, show source code when it errors but I now bypass this by catching the errors with CFERROR in the application.cfm and redirecting to an error page.

One day when I am allowed to develop how I want I won't have these problems anymore.

Thanks Byron. You're all over the place on this forum. What languages do you know and what do you use the most?

Here's a list of what I think I know

CF4 to current
Javascript
HTML
ASP
VB
VBScript
Very little .NET ASP/VB varieties
SQL Server
MySQL, somewhat
Starting in on Java and CFMX, someday I hope to do my own classes
Some XML

There's probably some other stuff I've used in the past as well, I'm not thinking of.

We use CF/SQL Server on all of our apps here, and I would say it's my favorite combo, but I really like .NET from the little I've used it, but would like to relearn it using C#. MX and Java is really cool too, so I can see that working into the mix more and more as well.

This works on MX as far as I know. Little bit cumbersome but if you need it you need it.



Hope it helps!
Gerd

http://IntermediaMX.com

I actually prefer that HMS doesn't store the username and password in CFADMIN. I think it is a GREAT security precaution because, as they mentioned before, on a shared hosting enviroment, all other accounts on that server have access to the DSN.

As far as it showing the login information on an error, you are correct, but that's where you SHOULD have error handling in your application and redirect the user to a generic page to prevent this.

Also, you can easily store your username and password in your Application.cfm file and call your username and password from that, instead of putting them directly into your query.

In fact, if you didn't have error handling enabled, but had your login information stored in the Application.cfm, it wouldn't jeapordize your username and password when it displayed the error message.

Instead of showing:



in the error message, it would show:



in the error message, since your username, password, and datasource are stored in the Application.cfm like so:



You could also use the <cfset> tag in the Application file to set these as well.

My $.2

MB

Having user/pass in the admin may be something that changes some time down the line. Currently with Sandboxes it can be a pain because the default is to allow all and ever new DSN would have to be added to all the other sandboxes. MM knows about this and is something they are looking to change.

This works with hostmysite and it never reveals your username and password even on error pages.. This is similar to above post but uses the request scope rather than the application scope.

Put this in application.cfm

<cfset request.DSN = "yourdatasource">
<cfset request.DSNUserName = "yourUserName">
<cfset request.DSNpassword = "yourpassword">

Then your queries....
<cfquery datasource='#request.DSN#' username="#request.DSNUserName#' password="#request.DSNpassword#'>

Works for me...

Gerd I agree entirely with your method: use request scope.

Whilst application variables can be used there is little point. Application variables are best suited for global variables that change on a hourly, daily or weekly basis such as News headlines. Application variables are typically too often used when a simple cfset or request scope will do. Only use them when you have to as there is additional overhead in using them.

A quote from the CFMX docs:

Since I am still relatively new to ColdFusion I thought by logging into the Administration section provided by HMS that making a DSN would only allow my account to use it. I'm assuming now it's making a Windows System DSN instead of some generic CF DSN. Not really relevant anymore.

I later decided to put the connection variables in the application.cfm but I wondered why you suggested to use the Application or Request scopes. Why not just this:



...and just skip the App and Req scopes? I just wonder the difference since the stupid application file runs every load just like a header include.

That was a very interesting method of selecting from a database via it's "C:\filePath".tablename. I forgot about that from a long time ago.

Does anyone have a good reference or resource for using COM objects in CF? I may try to use the ADODB object and would therefore be able to make DSN-less connections. I tried once before to use the CreateObject() function but I didn't want to waste time so I went back to CFQUERY.

Is the native Connection and Querying system of ColdFusion faster than using ADO?

Once a COM object is created must it be destroyed as well or does CF kill it for you? Even if it, then kills it, does it like Classic ASP sometimes not really kill it for you?

cfparam will work just fine, however the variable could then be set by passing it in the url.