Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

need to populate 625 database records with passwords

Posted on 2011-09-19
17
Medium Priority
?
327 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
[X]
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
  • 9
  • 5
  • 3
17 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 1500 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 1500 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 500 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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 …
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 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