need to populate 625 database records with passwords

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

LVL 3
Eric BourlandAsked:
Who is Participating?
 
gdemariaCommented:
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
 
Eric BourlandAuthor Commented:
That sounds too easy. =)

I am working on this.
0
 
_agx_Commented:
(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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
_agx_Commented:
Hit submit too soon ;)

Correction:

 WHERE LEN(LTRIM(RTRIM(userpassword))) = 0
0
 
Eric BourlandAuthor Commented:
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
 
gdemariaCommented:
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
 
Eric BourlandAuthor Commented:
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
 
_agx_Commented:
(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
 
gdemariaCommented:
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
 
_agx_Commented:
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
 
Eric BourlandAuthor Commented:
In column UserPassword I have both empty and NULL values.

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

That makes sense!
0
 
Eric BourlandAuthor Commented:
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
 
Eric BourlandAuthor Commented:
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
 
Eric BourlandAuthor Commented:
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
 
_agx_Commented:
>> <cfloop query="setPassword">

Nope.  You just need to use the right query name in the loop ;-) You changed yours to "getUser".
0
 
Eric BourlandAuthor Commented:
>>>You changed yours to "getUser".

ugh. ~sigh~

.... Wow. That worked. I instantly have new passwords for 626 records.
0
 
Eric BourlandAuthor Commented:
This worked really well. Thank you as always to gdemaria and _agx_.

I hope the remainder of your week is excellent.

Eric
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.