Searching an array with a regex

[scubasteve1281]

I'm writing a script to parse passwords and give me the ones that are not compliant 
 
So far I have spread sheet that look like this 
 
group         title          username            password
grouptest1  titletest1   usernametest1    bob
grouptest2  titletest2   usernametest2     hjk44
grouptest3  titletest3   usernametest3     #$%RTY%4hgf5
 
 
I'm loading the spread sheet into an array 
Then i want to search the array and write new array containing all the entrees in which pass words do not match the regex 
 
Regex: Password filter that matches the NSA Password filter DLL ENPASFILT.DLL. At least 1 small-case letter At least 1 Capital letter At least 1 digit At least 1 special character Length should be between 8-30 characters. Spaces allowed The sequence of the characters is not important.
 
(?=^.{8,30}$)(?=.*d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%^&*()_+}{"":;'?/>.<,]).*$' 
 
In the code below I don't get the output of the the array after it has been searched.

any help would be appreciated. 

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
#include <String.au3>



 $sFilePath1 = @ScriptDir & "\Test.xlsx"

Main()





Func Main()

     $oExcel = _ExcelBookOpen($sFilePath1)
    If @error = 1 Then
        MsgBox(0, "Error!", "Unable to Create the Excel Object")

    ElseIf @error = 2 Then
        MsgBox(0, "Error!", "File does not exist")

    EndIf

    _ExcelSheetActivate($oExcel,"Sheet1")

     $aArray = _ExcelReadSheetToArray($oExcel,1,1 )

    _ArrayDisplay($aArray, "$aArray")

$regex = '(?=^.{8,30}$)(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%^&amp;*()_+}{&quot;&quot;:;''?/&gt;.&lt;,]).*$'   ; Regex: Password filter that matches the NSA Password filter DLL ENPASFILT.DLL. At least 1 small-case letter At least 1 Capital letter At least 1 digit At least 1 special character Length should be between 8-30 characters. Spaces allowed The sequence of the characters is not important.
$sSearch = StringRegExp($aArray,$regex,3)



$iIndex = _ArraySearch($aArray, $sSearch, 2, 0, 1, 1, 1)
    _ArrayDisplay($sSearch,"$iIndex")




    ;_ExcelSheetActivate($oExcel,"Sheet2")
    ;_ExcelWriteSheetFromArray($oExcel, $aArray, 1, 1, 0, 0) ;0-Base Array parameters
    ;_ExcelColumnDelete($oExcel, 1, 1)
    ;_ExcelRowDelete($oExcel, 1, 1)


EndFunc

[czardas]

Loop through the array with a For loop like this. You have to test each array element you are interested in testing individually.

;

For $i = 1 To UBound($aArray) -1 ; For all rows except the first
    If StringRegExp($aArray[$i][3], $regex) Then ; test regexp on col 4
        ; Do whatever if the pattern matches - index = $i
    EndIf
Next

;

Or start looping at zero to search every row ==> For $i = 0 To Ubound($aArray) -1

Code is just an example.

Looking at your regex, it appears to be corrupted by html. I don't know why. Possibly something to do with the forum software. Perhaps you copied it like that.

Edited November 18, 2013 by czardas

[scubasteve1281]

I did copy the regex that way from http://regexlib.com/

This is the original Autoit flagged The single 'before the question mark, I wasn’t sure how to deal with it "regex is not my strong suite" so I added another single quote and tested against several single passwords and it seem to work fine.

(?=^.{8,30}$)(?=.*d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%^&*()_+}{"":;'?/>.<,]).*$

 

Ok I thought about looping through the array but thought I would be able to get away with just _ArraySearch so much for trying to short cut stuff

Thanks

[scubasteve1281]

Ok I have updated my code to do what I want but now I'm getting an error  it well cycle threw the loop 3 times but on the 4th time it throws this error 

 

38) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

If StringRegExp($aArray[$i], $regex) Then

If StringRegExp(^ ERROR

 

i have updated my spread sheet to this 

group   title   username   password

group1 title1 username1 PAAASSSwww000DDD!

group2 title2 username2 bob

group3 title3 username3 welcome to THE L##T*1

group4 title4 username4 1a*Cemtfrog

 

Code

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
#include <String.au3>



 $sFilePath1 = @ScriptDir & "\test.xlsx"

Main()

Func Main()

     $oExcel = _ExcelBookOpen($sFilePath1)
    If @error = 1 Then
        MsgBox(0, "Error!", "Unable to Create the Excel Object")

    ElseIf @error = 2 Then
        MsgBox(0, "Error!", "File does not exist")

    EndIf

    _ExcelSheetActivate($oExcel,"Sheet1")

     $aArray = _ExcelReadSheetToArray($oExcel,1,1 )


    _ArrayDisplay($aArray, "Loaded array")


$regex = '^(?!.*(.)\1{3})((?=.*[\d])(?=.*[a-z])(?=.*[A-Z])|(?=.*[a-z])(?=.*[A-Z])(?=.*[^\w\d\s])|(?=.*[\d])(?=.*[A-Z])(?=.*[^\w\d\s])|(?=.*[\d])(?=.*[a-z])(?=.*[^\w\d\s])).{7,30}$'   ; Regex: 


For $i = 1 To UBound($aArray) -1 ; For all rows except the first

    If StringRegExp($aArray[$i][4], $regex) Then  ; test regexp on col 4
        
        _ArrayDelete($aArray, $i)
        
    EndIf

 Next
 
    _ExcelSheetActivate($oExcel,"Sheet2")
    _ExcelWriteSheetFromArray($oExcel, $aArray, 1, 1, 0, 0) ;0-Base Array parameters
    ;_ExcelColumnDelete($oExcel, 1, 1)
    ;_ExcelRowDelete($oExcel, 1, 1)

EndFunc

 

[czardas]

Okay without looking at the details of the regexp - a quick answer to your problem. Loop backwards if you are deleting elements from the array. Otherwise the loop count will go beyond the number of elements still remaining after you have deleted a few.

Replace this:

For $i = 1 To UBound($aArray) -1

;

with this:

For $i = UBound($aArray) -1 To 1 Step -1

Edited November 19, 2013 by czardas

[scubasteve1281]

Awesome that works now when I'm looping backwards through the array it looks at the that last elements of the array EX: $array [50] [50]  then cycles till it gets to the first element of the array $array [0] [0]?

thanks again for the help

[czardas]

I'm glad if your happy: but actually the code is only testing the fifth column in the array (you changed the column array index from 3 to 4). I'm not exactly sure if that's what you want.

Starting with array element $array[50] [4] , the loop cycles untill it gets to $array [1] [4]

If the first row contains column header information, then you do not need to test it. This will depend on your excel sheet layout: Use ==> _ArrayDisplay($array) to make sure everything is as you expect it to be. If you wish to test every element of a 2D array you need to do something like this:

;

; For every row (including zero)
For $i = UBound($aArray) -1 To 0 Step -1 ; Must loop backwards because some rows get deleted
    
    ; Place a second loop inside the first loop
    For $j = 0 To UBound($aArray, 2) -1 ; For every column entry - No need to loop backwards here
        
        ; Test regexp on all elements within the row
        If StringRegExp($aArray[$i][$j], $regex) Then
            _ArrayDelete($aArray, $i)
            ExitLoop ; Because the row we are testing no longer exists
        EndIf
    Next
Next

;

With your example this will test ==> group, title, username and password and anything else in all fields of your array. I figured that wasn't quite what you wanted. Do some simple experiments with 2D arrays and get used to them.

---

For any beginners out there, the code above illustrates a method and can be optimized. How?

Edited November 20, 2013 by czardas