ColdFusion, rylands Internet Solutions

home : technologies : coldfusion : generate usernames and passwords

We were presented with a database of 1500 “users” and needed to create unique usernames and passwords for each one. As you can imagine it would take a long time to do this by hand. The obvious solution was to knock up a coldfusion routine to do it for us. Essentially we created two arrays to hold the usernames and passwords, then we did a quick query of the database to get a record count of how many usernames and passwords we needed to generate. Then it was a matter of using a random number function to generate three random numbers for converting into text characters and an additional 3-digit number to end the username. This was repeated to generate a password.
Next came the important step, to check through the previous usernames stored in an array to ascertain whether the one just generated had been previously used. If it had not then the username and password were stored in the array, alternatively if the username had been previously used another one was generated. This was repeated until there were two arrays of usernames and passwords. As you can imagine this script took a substantial period of time to run. The final step of updating the username/password fields for each record was substantially quicker.
If this script is of any use to you then please feel free to use it.

If you have any comments or improvements on this script please feel free to send them to leigh@rylands-internet-solutions.co.uk

username/password script:

The following script should be saved to “passwords.cfm”. NOTE this will take around 8 minutes to run (dependant on the CPU capacity of your server) and will cause your server to appear to grind to a halt. We suggest you run it during a quiet period. Also, we do not offer any warranty for this script and will not accept any responsibility for any damage or loss of information incurred - although we would be interested in hearing of any mishaps so we can redesign if necessary.

Next month we will provide an administrator’s script for generating unique usernames and passwords for new users.
now available >>>

passwords.cfm >>

<--
first of all we need to know how many records there are -->
<CFIF ISDEFINED("form.update") IS FALSE>
<CFQUERY NAME="howmany" DATASOURCE="members" DBTYPE="ODBC">
select members.memberid
from members
</CFQUERY>
<CFSET NUMBERRECORDS = #HOWMANY.RECORDCOUNT#>
<-- then we need 3 arrays to hold all the info -->
<CFSET USERARRAY = ARRAYNEW(1)>
<CFSET PASSARRAY = ARRAYNEW(1)>
<CFSET IDARRAY = ARRAYNEW(1)>
<CFSET COUNT = 1>
<-- then we load the IDs into an array -->
<CFOUTPUT QUERY="howmany">
<CFSET IDARRAY[#COUNT#] = #MEMBERID#>
<CFSET #COUNT# = #INCREMENTVALUE(COUNT)#>
</CFOUTPUT>
<-- then we loop through creating random usernames and passwords, and then loop through again for each username to make sure it is unique -->
<CFSET COUNT = 1>
<CFLOOP FROM="1" TO="#numberrecords#" INDEX="CurrentRange">
<CFSET UNIQUE = 0>
<CFLOOP CONDITION="#unique# LESS THAN 1">
   <CFSET USERNAMECHAR1=#CHR(RANDRANGE(65, 90))#>
   <CFSET USERNAMECHAR2=#CHR(RANDRANGE(65, 90))#>

   <CFSET USERNAMECHAR3=#CHR(RANDRANGE(65, 90))#>
   <CFSET USERNAMENUMBER=RANDRANGE(100, 999)>
   <CFSET USERNAME = #USERNAMECHAR1#&#USERNAMECHAR2#&#USERNAMECHAR3#&#USERNAMENUMBER#>
   <CFSET PASSWORDCHAR1=#CHR(RANDRANGE(65, 90))#>
   <CFSET PASSWORDCHAR2=#CHR(RANDRANGE(65, 90))#>
   <CFSET PASSWORDCHAR3=#CHR(RANDRANGE(65, 90))#>
   <CFSET PASSWORDNUMBER=RANDRANGE(100, 999)>
<CFSET PASSWORD = #PASSWORDCHAR1#&#PASSWORDCHAR2#&#PASSWORDCHAR3#&#PASSWORDNUMBER#>
   <CFIF #COUNT# GREATER THAN 1>
    <CFSET PREVIOUS = #COUNT#-1>
    <CFSET INNERLOOP = 1>
    <CFLOOP FROM="1" TO="#previous#" INDEX="CurrentRange">
     <CFIF #USERNAME# IS #USERARRAY[INNERLOOP]#>
      <CFSET UNIQUE = 0>
     <CFELSE>
      <CFSET UNIQUE = 1>
     </CFIF>
    </CFLOOP>
   <CFELSE>
    <CFSET UNIQUE = 1>
   </CFIF>
</CFLOOP>
<CFSET USERARRAY[#COUNT#] = #USERNAME#>
<CFSET PASSARRAY[#COUNT#] = #PASSWORD#>
<CFSET #COUNT# = #INCREMENTVALUE(COUNT)#>
</CFLOOP>
</CFIF>
<-- if results are being posted take the posted results into arrays and then do a looping query update to place the usernames and passwords into the database -->
<CFIF ISDEFINED("form.update") IS TRUE>
<CFSET NUMBERRECORDS = '#FORM.NUMBERRECORDS#'>
<CFSET USERARRAY = ARRAYNEW(1)>
<CFSET PASSARRAY = ARRAYNEW(1)>
<CFSET IDARRAY = ARRAYNEW(1)>
<CFSET USERARRAY = '#LISTTOARRAY(FORM.USERNAMES)#'>
<CFSET PASSARRAY = '#LISTTOARRAY(FORM.PASSWORDS)#'>
<CFSET IDARRAY = '#LISTTOARRAY(FORM.MEMBERIDS)#'>
<CFSET COUNTER=1>
<CFLOOP INDEX = "Counter" FROM = "1" TO =#NUMBERRECORDS#>
<CFQUERY NAME="adduserpass" DATASOURCE="members">
UPDATE members
SET username='#userarray[Counter]#',
password='#passarray[Counter]#'
WHERE memberid=#idarray[Counter]#
</CFQUERY>
<CFSET #COUNTER# = #INCREMENTVALUE(COUNTER)#>
</CFLOOP>
</CFIF>
<HTML>
<HEAD>
<TITLE>usernames and passwords generator</TITLE>
</HEAD>
<BODY>
<CFIF ISDEFINED("form.update") IS FALSE>
<H3>username and password generator</H3>
<table>
<FORM ACTION="passwords.cfm" METHOD="POST">
<cfoutput>
<INPUT TYPE="hidden" NAME="numberrecords" VALUE="#numberrecords#">
<INPUT TYPE="hidden" NAME="usernames" VALUE="#ArrayToList(userarray, ",")#">
<INPUT TYPE="hidden" NAME="passwords" VALUE="#ArrayToList(passarray, ",")#">
<INPUT TYPE="hidden" NAME="memberids" VALUE="#ArrayToList(idarray, ",")#">
<INPUT TYPE="hidden" NAME="update" VALUE="yes">
</cfoutput>
<tr><td><DIV ALIGN="center">
<INPUT TYPE="submit" VALUE="update"></DIV></td></tr>
</form></table>
<TABLE>
<TR>
<TD>memberid</TD>
<TD>username</TD>
<TD>password</TD>
</TR>
<CFOUTPUT>
<CFSET COUNT=1>
<CFLOOP FROM="1" TO="#numberrecords#" INDEX="CurrentRange">
<TR>
<TD>#idarray[count]#</TD>
<TD>#userarray[count]#</TD>
<TD>#passarray[count]#</TD>
</TR>
<CFSET #COUNT# = #INCREMENTVALUE(COUNT)#>
</CFLOOP>
</CFOUTPUT>
</TABLE>
<CFELSE>
<CFQUERY NAME="userdetails" DATASOURCE="members" DBTYPE="ODBC">
select members.memberid, members.username, members.password
from members
</CFQUERY>
<H3>the following usernames and passwords have been generated</H3>
<table>
<tr>
<td>memberid</td>
<td>username</td>
<td>password</td>
</tr>
<cfoutput query="userdetails">
<tr>
<td>#memberid#</td>
<td>#username#</td>
<td>#password#</td>
</tr>
</cfoutput>
</table>
</CFIF>
<CFOUTPUT>
start: #timestart#<BR>
finish: #TIMEFORMAT(NOW(), "hh:mm:ss")#
</CFOUTPUT>
</BODY>
</HTML>

coldfusion links