Solved

need to populate 625 database records with passwords

Posted on 2011-09-19
17
307 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now