Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

need to populate 625 database records with passwords

Posted on 2011-09-19
17
315 Views
Last Modified: 2012-05-12
ColdFusion 9
MS SQL Server 2005

Hi. I need to populate 625 database records with passwords. What's a good way to do this?

Details: I have a data table, CareplannersMembers, with column UserPassword, and 625 records. I need to populate column UserPassword with a password value for each record.

Ben Nadel's solution to generate passwords looks useful:

http://www.bennadel.com/blog/488-Generating-Random-Passwords-In-ColdFusion-Based-On-Sets-Of-Valid-Characters.htm

... but I wonder how I can apply it to 625 data records? I'm very grateful for any ideas.

Happy Monday.

Eric

<cfscript>
/** generatePassword - this function will produce a randomly genereated password
 * that is 8 characters long and includes at least one number, lower case letter,
 * and upper case letter
 *
 * none cfscript code found at http://www.bennadel.com/blog/488-Generating-Random-Passwords-In-ColdFusion-Based-On-Sets-Of-Valid-Characters.htm
 * adapted to cfscript code by Andy Marks
 *
 * @return the generated password
 */
function generatePassword()
{
 var strLowerCaseAlpha = "abcdefghijklmnopqrstuvwxyz";
 var strUpperCaseAlpha = UCase( strLowerCaseAlpha );
 var strNumbers = "0123456789";
 var strOtherChars = "!@##$%&*";
 var strAllValidChars = (strLowerCaseAlpha & strUpperCaseAlpha & strNumbers & strOtherChars);
 var arrPassword = ArrayNew(1);
 var strPassword = "";
 var intChar = 0;
 
 arrPassword[ 1 ] = Mid(strNumbers,RandRange( 1, Len( strNumbers ) ),1);
 arrPassword[ 2 ] = Mid(strLowerCaseAlpha,RandRange( 1, Len( strLowerCaseAlpha ) ),1);
 arrPassword[ 3 ] = Mid(strUpperCaseAlpha,RandRange( 1, Len( strUpperCaseAlpha ) ),1);
 for(intChar = ArrayLen(arrPassword) + 1; intChar LTE 8; intChar = intChar + 1)
 {
 arrPassword[ intChar ] = Mid(strAllValidChars,RandRange( 1, Len( strAllValidChars ) ),1);
 }
 CreateObject( "java", "java.util.Collections" ).Shuffle(arrPassword);
 strPassword = ArrayToList(arrPassword,"");
 return strPassword;
}
</cfscript>

Open in new window

0
Comment
Question by:Eric Bourland
  • 9
  • 5
  • 3
17 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 375 total points
ID: 36562383
Hey Eric,

Sounds like this is something you're just going to do once.  So, I think you should just create a quick CF page to loop through all the records with emtpy passwords and perform an update one at a time.

<cfquery name="myUsers"...
  select user_id from users where userpassword is null
</cfquery>

<cfloop query="myUsers">

   generate password


   update user
   where user_id = myUsers.user_id

</cfloop>
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 36562395
That sounds too easy. =)

I am working on this.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36562811
(no points...)

>> where userpassword is null

As a safety, check the length too. Just in case the field is empty rather than NULL

     WHERE LEN(LTRIM(RTRIM(userpassword)))

0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 52

Expert Comment

by:_agx_
ID: 36562826
Hit submit too soon ;)

Correction:

 WHERE LEN(LTRIM(RTRIM(userpassword))) = 0
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 36562983
Here is what I came up with. I get stuck in the UPDATE statement inside the CFLOOP. I am not sure what value to SET the column UserPassword?
<!--- 
 Filename: SetPassword.cfm
 Created by: Ben Nadel, Andy Marks
 Modified by: gdemaria, _agx_, Eric B, September 2011
 Purpose: This application populates passwords for records in a database
 ColdFusion Version 9
 MS SQL Server 2005
--->


<cfquery name="setPassword" datasource="#application.datasource#">
  select CareplannersUUID
  from CareplannersMembers
  where Userpassword IS 'NULL'

</cfquery>

<cfloop query="setPassword">

<cfscript>
/** generatePassword - this function will produce a randomly generated password
 * that is 8 characters long and includes at least one number, lower case letter,
 * and upper case letter
 *
 * note cfscript code found at http://www.bennadel.com/blog/488-Generating-Random-Passwords-In-ColdFusion-Based-On-Sets-Of-Valid-Characters.htm
 * adapted to cfscript code by Andy Marks
 *
 * @return the generated password
 */
function generatePassword()
{
 var strLowerCaseAlpha = "abcdefghijklmnopqrstuvwxyz";
 var strUpperCaseAlpha = UCase( strLowerCaseAlpha );
 var strNumbers = "0123456789";
 var strOtherChars = "!@##$%&*";
 var strAllValidChars = (strLowerCaseAlpha & strUpperCaseAlpha & strNumbers & strOtherChars);
 var arrPassword = ArrayNew(1);
 var strPassword = "";
 var intChar = 0;
 
arrPassword[ 1 ] = Mid(strNumbers,RandRange( 1, Len( strNumbers ) ),1);
 arrPassword[ 2 ] = Mid(strLowerCaseAlpha,RandRange( 1, Len( strLowerCaseAlpha ) ),1);
 arrPassword[ 3 ] = Mid(strUpperCaseAlpha,RandRange( 1, Len( strUpperCaseAlpha ) ),1);
 for(intChar = ArrayLen(arrPassword) + 1; intChar LTE 8; intChar = intChar + 1)
 {
 arrPassword[ intChar ] = Mid(strAllValidChars,RandRange( 1, Len( strAllValidChars ) ),1);
 }
 CreateObject( "java", "java.util.Collections" ).Shuffle(arrPassword);
 strPassword = ArrayToList(arrPassword,"");
 return strPassword;
}
</cfscript>

UPDATE CareplannersMembers
SET UserPassword = ??? not sure how to integrate output of function generatePassword()
WHERE CareplannersUUID = setPassword.CareplannersUUID
AND LEN(LTRIM(RTRIM(userpassword))) = 0 
</cfloop>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36563049
You don't want to put the function definition inside your loop, you only need to create the function once... you need to call it again and again.   See below, the function is defined at the top, then you call it and place the resulting value into a variable.  Then update the table using that variable.

<cfscript>
/** generatePassword - this function will produce a randomly generated password
 * that is 8 characters long and includes at least one number, lower case letter,
 * and upper case letter
 *
 * note cfscript code found at http://www.bennadel.com/blog/488-Generating-Random-Passwords-In-ColdFusion-Based-On-Sets-Of-Valid-Characters.htm
 * adapted to cfscript code by Andy Marks
 *
 * @return the generated password
 */
function generatePassword()
{
 var strLowerCaseAlpha = "abcdefghijklmnopqrstuvwxyz";
 var strUpperCaseAlpha = UCase( strLowerCaseAlpha );
 var strNumbers = "0123456789";
 var strOtherChars = "!@##$%&*";
 var strAllValidChars = (strLowerCaseAlpha & strUpperCaseAlpha & strNumbers & strOtherChars);
 var arrPassword = ArrayNew(1);
 var strPassword = "";
 var intChar = 0;
 
arrPassword[ 1 ] = Mid(strNumbers,RandRange( 1, Len( strNumbers ) ),1);
 arrPassword[ 2 ] = Mid(strLowerCaseAlpha,RandRange( 1, Len( strLowerCaseAlpha ) ),1);
 arrPassword[ 3 ] = Mid(strUpperCaseAlpha,RandRange( 1, Len( strUpperCaseAlpha ) ),1);
 for(intChar = ArrayLen(arrPassword) + 1; intChar LTE 8; intChar = intChar + 1)
 {
 arrPassword[ intChar ] = Mid(strAllValidChars,RandRange( 1, Len( strAllValidChars ) ),1);
 }
 CreateObject( "java", "java.util.Collections" ).Shuffle(arrPassword);
 strPassword = ArrayToList(arrPassword,"");
 return strPassword;
}
</cfscript>


<!--- 
 Filename: SetPassword.cfm
 Created by: Ben Nadel, Andy Marks
 Modified by: gdemaria, _agx_, Eric B, September 2011
 Purpose: This application populates passwords for records in a database
 ColdFusion Version 9
 MS SQL Server 2005
--->


<cfquery name="getUser" datasource="#application.datasource#">
  select CareplannersUUID
  from CareplannersMembers
  where Userpassword IS NULL
</cfquery>
<cfloop query="setPassword">
  <cfset newPassword = generatePassword()>
  <cfquery name="setPassword" datasource="#application.datasource#">
    UPDATE CareplannersMembers
     SET UserPassword = '#newPassword#'
    WHERE CareplannersUUID = '#getUser.CareplannersUUID#'
  </cfquery>
</cfloop>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 36563164
I was wondering whether the function should get called from inside the loop.

Also, thank you for correcting my syntax here:

where Userpassword IS NULL

I understand that the apostrophes are used only with =, not IS.

I'm working on a revision.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36563197
(no points...)

Also if you're going to use the LEN() check, it should be included in your SELECT.  Otherwise, those records won't get updated.
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 375 total points
ID: 36563274
Eric - unless you're really good about ensuring that your field is NULL rather than empty (there is a difference) you should use agx's where clause instead of the IS NULL clause.

WHERE LEN(LTRIM(RTRIM(userpassword))) = 0

0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 125 total points
ID: 36563325
Actually you'd need both because the length of NULL is NULL rather than 0.

WHERE LEN(LTRIM(RTRIM(userpassword))) = 0
OR        userpassword IS NULL
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 36563340
In column UserPassword I have both empty and NULL values.

>>>WHERE LEN(LTRIM(RTRIM(userpassword))) = 0
OR        userpassword IS NULL

That makes sense!
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 36563427
My result is this.

I added cfqueryparam to the variables in the update statement.

This is the part I did not understand; it makes perfect sense now:
<cfset newPassword = generatePassword()>

I think I am ready to run this. Yes, I've backed up my CareplannersMembers table. =)
<!--- 
 Filename: SetPassword.cfm
 Created by: Ben Nadel, Andy Marks
 Modified by: gdemaria, _agx_, Eric B, September 2011
 Purpose: This application populates passwords for records in a database
 ColdFusion Version 9
 MS SQL Server 2005
--->


<!--- cfscript generates passwords --->
<!--- omit lowercase l (ell), lowercase o to remove confusion with numerals 1 and 0 --->

<cfscript>
/** generatePassword - this function will produce a randomly generated password
 * that is 8 characters long and includes at least one number, lower case letter,
 * and upper case letter
 *
 * note cfscript code found at http://www.bennadel.com/blog/488-Generating-Random-Passwords-In-ColdFusion-Based-On-Sets-Of-Valid-Characters.htm
 * adapted to cfscript code by Andy Marks
 *
 * @return the generated password
 */
function generatePassword()
{
 var strLowerCaseAlpha = "abcdefghijkmnpqrstuvwxyz";
 var strUpperCaseAlpha = UCase( strLowerCaseAlpha );
 var strNumbers = "0123456789";
 var strOtherChars = "!@##$%&*";
 var strAllValidChars = (strLowerCaseAlpha & strUpperCaseAlpha & strNumbers & strOtherChars);
 var arrPassword = ArrayNew(1);
 var strPassword = "";
 var intChar = 0;
 
 arrPassword[ 1 ] = Mid(strNumbers,RandRange( 1, Len( strNumbers ) ),1);
 arrPassword[ 2 ] = Mid(strLowerCaseAlpha,RandRange( 1, Len( strLowerCaseAlpha ) ),1);
 arrPassword[ 3 ] = Mid(strUpperCaseAlpha,RandRange( 1, Len( strUpperCaseAlpha ) ),1);
 for(intChar = ArrayLen(arrPassword) + 1; intChar LTE 8; intChar = intChar + 1)
 {
 arrPassword[ intChar ] = Mid(strAllValidChars,RandRange( 1, Len( strAllValidChars ) ),1);
 }
 CreateObject( "java", "java.util.Collections" ).Shuffle(arrPassword);
 strPassword = ArrayToList(arrPassword,"");
 return strPassword;
}
</cfscript>

<!--- query for column UserPassword = 0 or IS NULL --->

<cfquery name="setPassword" datasource="#application.datasource#">
  SELECT CareplannersUUID
  FROM CareplannersMembers
  WHERE LEN(LTRIM(RTRIM(UserPassword))) = 0 
  OR UserPassword IS NULL 
</cfquery>

<cfset newPassword = generatePassword()>
<cfloop query="setPassword">
UPDATE CareplannersMembers
SET UserPassword = <cfqueryparam cfsqltype="cf_sql_varchar" value="#newPassword#">
WHERE CareplannersUUID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#getUser.CareplannersUUID#">
</cfloop>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 36563561
I made corrections in my code above per gd's original post:

<cfquery name="getUser" datasource="#application.datasource#">  select CareplannersUUID  from CareplannersMembers  where Userpassword IS NULL</cfquery><cfloop query="setPassword">  <cfset newPassword = generatePassword()>  <cfquery name="setPassword" datasource="#application.datasource#">    UPDATE CareplannersMembers     SET UserPassword = '#newPassword#'    WHERE CareplannersUUID = '#getUser.CareplannersUUID#'  </cfquery></cfloop>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 36563586
I'm getting an error:

Attribute validation error for tag cfloop. 
The value of the attribute query, which is currently setPassword, is invalid. 
 The error occurred in C:/websites/www.osm-vista.com/SetPassword.cfm: line 58
58 : <cfloop query="setPassword">
59 : <cfset newPassword = generatePassword()>
60 : <cfquery name="setPassword" datasource="#application.datasource#">

Open in new window


I think I need to move query SetPassword to before the CFLOOP?

<cfquery name="getUser" datasource="#application.datasource#">
  SELECT CareplannersUUID
  FROM CareplannersMembers
  WHERE LEN(LTRIM(RTRIM(UserPassword))) = 0 
  OR UserPassword IS NULL 
</cfquery>


<cfloop query="setPassword">
<cfset newPassword = generatePassword()>
<cfquery name="setPassword" datasource="#application.datasource#">
UPDATE CareplannersMembers
SET UserPassword = <cfqueryparam cfsqltype="cf_sql_varchar" value="#newPassword#">
WHERE CareplannersUUID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#getUser.CareplannersUUID#">
</cfquery>
</cfloop>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 36563857
>> <cfloop query="setPassword">

Nope.  You just need to use the right query name in the loop ;-) You changed yours to "getUser".
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 36563866
>>>You changed yours to "getUser".

ugh. ~sigh~

.... Wow. That worked. I instantly have new passwords for 626 records.
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 36563885
This worked really well. Thank you as always to gdemaria and _agx_.

I hope the remainder of your week is excellent.

Eric
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question