Proper DB Access usgin CFQUERY?

brwright wrote:
> Your worst nightmare - after 18 months of code development with 2 weeks left
> before BETA. My site experienced a SQL Injection Attack 2 days ago. A URL
> pointing to China which trys to call a js script. I am a self taught CF user
> who has coded many sites never thinking it would happen to me. I have already
> read the many threads on using CFPARAM to bind the DB types.

Not cfparam, cfqueryparam.


> So I have to go
> back through hundreds of CFML files to do that. Now that I am paranoid I am
> trying to figure out what else I can do on the SQL 2005 Server side. I have
> always used my db_owner login password in CFQUERY to make the connection, which
> is apparently a no-no. I can't find any best practices online: 1) Should you
> use the username/password in CFQUERY or just specify it in CF Admin? However, I
> thought if you do that, then any call can be made directly to the DB. I heard
> you should store the DB credentials in the application file encoded and then
> call them as a new variable so they're not in plain text in your CFQUERY?

There is no clear consensus on the usage of a u/p in all queries versus
storing the u/p in the CF Administrator. However, hardcoding the u/p in
each cfquery statement is bad. You should create variables for the
username and password and use those variables in your queries. (Just
imagine in how many places you have to change the code if you have a
security policy where you have to change all passwords every 3 months.)


> 2) If
> I setup a new role in SQL 2005, do people use standard or the application role?

Depends. Typically dbreader/dbwriter is used for applications that do
not use stored procedures.


> 3) I fear I will keep getting hit by the SQL injection code until everything is
> fixed. Not knowing the extent of the damage how should I lock down the DB
> tables using Enterprise Manager in SQL 2005?

Always.

Jochem


--
Jochem van Dieten
Adobe Community Expert for ColdFusion