cfquery accepting old passwords

[wolfv]

After users change their passwords, fcqueries accept both new and old
passwords. Other passwords are rejected. How do I inform MacroMedia about
this problem? This is a MX7 security risk we should all be aware of.

I am using ColdFusion MX7 on XP, MS server2000 with SQL Server Authentication.
Attached is a simple example that demonstrates a cfquery accepting both new
and old passwords.

How to run the example:
Set user ?f? password to ?pw1?
Then change the password to ?pw2?
Then run the attached cfqueries

Results:
Both passwords ?pw1? and ?pw2? are accepted.
The invalid password ?pw3? is rejected
Logging out and then logging back in makes no difference
MS Query Analyzer accepts only the new password ?pw2?
After rebooting or timing out, cfquery rejects the old passwords
This only happens in MX7. MX6 does not do this.

Conclusion:
MX7 cfquery is not secure if a user changes password after password is
compromised.


<!--- test old password --->
<cfquery name=?getUser1? datasource=?#application#?
username=?#session.username#? password=?pw1?>
SELECT username FROM USERS
WHERE username=?f?
</cfquery>
Old password: username = <cfoutput># getUser1.username#</cfoutput><br>

<!--- test new password --->
<cfquery name=?getUser2? datasource=?#application#?
username=?#session.username#? password=?pw2?>
SELECT username FROM USERS
WHERE username=?f?
</cfquery>
Old password: username = <cfoutput># getUser2.username#</cfoutput><br>

<!--- test invalid password --->
<cfquery name=?getUser3? datasource=?#application#?
username=?#session.username#? password=?pw3?>
SELECT username FROM USERS
WHERE username=?f?
</cfquery> <!--- Login failed. --->

[Dan Bracuk]

Where is the part where you update the user's password in your db?

[BKBK]

I cannot see anything wrong with the way MX7 handles the passwords. You observe
that behaviour because your code contains an error and a misunderstanding.

First, the error. The username attribute of the cfquery tag is the one, and
only one ,
username that you have registered for the
datasourcename(datasource=?#application#?)
in the Coldfusion Administrator. As such it should not be a session variable.
Since the
datasourcename is at least of Application scope, so too are the other cfquery
attributes,
username and password. They usually remain fixed throughout the Application's
lifetime.
The session code you use might sometimes work, but it is in general not
correct. That
might explain the inconsistencies you notice when you reboot or when sessions
time out.

An example to illustrate. When a client's session expires you might wish to
store some
closing information in the database, for example, by running an insert-query
in
Application.cfc's onSessionEnd method. Your code would not be able to do that
when
session.username becomes undefined at the end of a session.

Second, the misunderstanding. You seem to confuse the login credentials of the
user
and those of the datasource. The username/password attributes in the cfquery
tag
are used by Coldfusion to log on to the database server. They have nothing to
do
with the user who is currently logged in or with the contents of the USERS
table.
To get the username/password combination for the user, apply code like

<cfloginuser name="user_name" password="user_password" roles="user_role">

or

<cfquery name=?getUser1? datasource=?datasourceName? username=?DSN_username?
password=?DSN_password?>
SELECT uname, pword FROM USERS
WHERE uname=?f?
</cfquery>

[wolfv]

I changed the password via MS Enterprise Manager or the following cfquery.
Either way, the old password is accepted. Since the above example, I have
replaced all application and session variables with string literals.

The following example is a very simple demonstration of MX7 cfquery accepting
an old password.


<!--- change password from 'pw1' to 'pw2' --->
<cfquery datasource="demo" username="f" password="pw1">
sp_password 'pw','pw2'
</cfquery>

<!--- old password is accepted --->
<cfquery name="getUser1" datasource="demo" username="f" password="pw1">
SELECT username FROM USERS WHERE username='f'
</cfquery>
Old password: username = <cfoutput>#getUser1.username#</cfoutput><br>

<!--- new password is accepted --->
<cfquery name="getUser2" datasource="demo" username="f" password="pw2">
SELECT username FROM USERS WHERE username='f'
</cfquery>
new password: username = <cfoutput>#getUser2.username#</cfoutput><br>

<!--- invalid password is rejected --->
<cfquery name="getUser3" datasource="demo" username="f" password="pw3">
SELECT username FROM USERS WHERE username='f'
</cfquery>

[BKBK]

sp_password 'pw','pw2' contains an error. It should be sp_password 'pw1','pw2'.

[wolfv]

BKBK
Your right. the first cfquery should read
sp_password 'pw1','pw2'

Thanks for pointing that out.

[wolfv]

Sorry about the typo in the previous example. The corrected version is below.

This simple example demonstrates cfquery accepting an old password.


<!--- change password from 'pw1' to 'pw2' --->
<cfquery datasource="demo" username="f" password="pw1">
sp_password 'pw1','pw2'
</cfquery>

<!--- old password is accepted --->
<cfquery name="getUser1" datasource="demo" username="f" password="pw1">
SELECT username FROM USERS WHERE username='f'
</cfquery>
Old password: username = <cfoutput>#getUser1.username#</cfoutput><br>

<!--- new password is accepted --->
<cfquery name="getUser2" datasource="demo" username="f" password="pw2">
SELECT username FROM USERS WHERE username='f'
</cfquery>
new password: username = <cfoutput>#getUser2.username#</cfoutput><br>

<!--- invalid password is rejected --->
<cfquery name="getUser3" datasource="demo" username="f" password="pw3">
SELECT username FROM USERS WHERE username='f'
</cfquery>

[BKBK]

<!--- change password from 'pw1' to 'pw2' --->
<cfquery datasource="demo" username="f" password="pw1">
sp_password 'pw1','pw2'
</cfquery>
We're already on to a bad start here. The tag to run stored procedures is
<cfstoredproc>, not <cfquery>.

This simple example demonstrates cfquery accepting an old password.
No. It demonstrates the MS SQL server accepting an old password, as Coldfusion
uses the password to log on to the MS SQL server.

[wolfv]

After running the above example, Query Analyzer accepted the new password but
not the old password. Which leads me to suspect it?s a problem with cfquery,
possibly only with Server2000.

Thank you for testing it on your system BKBK.

sp_password is a Transact-SQL stored procedure. I tried it in cfstoredproc
tags but got the error ?The page cannot be displayed.?

<!--- change password from 'pw1' to 'pw2' --->
<cfstoredproc procedure="sp_password" datasource="demo" username="f"
password="pw1">
<cfprocparam value="pw1" cfsqltype="cf_sql_varchar">
<cfprocparam value="pw2" cfsqltype="cf_sql_varchar">
<!--- <cfprocparam value="f" cfsqltype="cf_sql_varchar"> --->
</cfstoredproc >

[BKBK]

After running the above example, Query Analyzer accepted the new password but not the old password. Which leads me to suspect it?s a problem with cfquery
Hmmm... more complicated than I had thought.

[Mountain Lover]

it sounds to me like connection pooling. see
[url]http://www.talkingtree.com/blog/index.cfm/2005/3/14/ConnPooling1[/url] for a
good explanation

HTH,
Tim
--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[BKBK]

Thanks, Mountain Lover.

It sometimes takes someone else lighting the torch. Seeing your "connection
pooling" sparked the aha moment. Everything immediately made sense.

By default, a single request may only make one connection to a database at a
time. However, a request can have, available to it, a connection pool of, say,
5 connections, each pool being identified by the distinct combination of
username/password, database server and database.

Wolfv's page amounts to one Coldfusion request. It will therefore only make
one connection to the database at a time. In fact, the page opens within a
connection pool that uses the original username/password combination. The
first two queries run within this pool.

However, when Coldfusion encounters a new username/password, the request
disconnects from the database and then re-connects. The new connection belongs
to a new connection pool, created to distinguish any new set of connections
from the original. The third query runs within this pool.

[wolfv]

Thanks Newsgroup User. That explains a lot.

I unchecked ?Maintain Connection? and now my application does not accept any
old passwords. Problem solved.

But there is still something I don?t understand. My little demonstration
above, still accepts the old password. Go figure.

[BKBK]

> My little demonstration above, still accepts the old password.
It does not . The word "old" is the source of the confusion. You switch
connection pools, and your demonstration simply accepts the password of the
current pool.