Challenge Objective
You've been asked to help out the Cyber Loss Prevention team from Eagle One Financial. Help the team prevent credit card fraud by scraping a credit card dump that was recently released on the dark web, running the partially available data against the Eagle One Financial Customer database to identify the leaked details of Eagle One Customers, and storing the matching customer details into a CSV. This CSV will enable Eagle One Financial to proactively cancel and re-issue cards for their customers - so upload this CSV to the challenge page to complete the challenge. Challenge
Link: https://pathfinder.automationanywhere.com/challenges/automationanywherelabs-cyberlossprevention.html
Steps Package
Like with our other solution tutorials, we always advocate for the use of steps to map out the major components of your bot's logic. This is especially important for larger/more complex bots. Note that this can easily feel overwhelming to think through ALL of the logic all at once. Consider breaking it up into layers...map out the highest layer general flow of your bot...then get into each of those high-level steps to map out slightly more specific steps that need to occur therein. Starting a bot build with this type of approach is also SUPER helpful in identifying areas where there may be code reusability/opportunities to simplify a process.
Counter Based Loop
In the Bot Games Season 2 Auction Sniper tutorial, we recommended a while loop as a part of the solution tutorial. In this exercise, we should have some idea how many pages we'll need to iterate through based on the page count details showing up on pagination navigation on Ryan's Club. Extract the total number of pages number to guide your bot to know how many pages it would need to loop through. This makes the bot build as dynamic as possible.
For Loop : Each Row of a Data Table
While technically still from the Loop package, this bot build needs a For Loop inside the Counter based loop to iterate through each row of the HTML table of credit card entries...for every page of the credit card dump. Note that the iterator for this loop type is a record...which is essentially the current row worth of data. This data can be accessed from the record datatype be providing the data's index or a column name. Personally, we always stick with index just in case the table header name changes or there is an issue recognizing it has a header row. When referencing data by the index within a record, remember that 0 represents the first column, not 1. If you wanted to get fancy here, you could include things inside the loop like checking to see if the index 0 value of the record contains "Card" so you'd know its a header row and wouldn't bother running it against the DB...though, with a well-written SQL query, this would just be a 0 result return like any other row without a matching customer detail.
Database Package
The biggest thing that may be new to a lot of developers is the use of the Database Package in this exercise. Since we need to check each entry in the Ryan's Club Credit Card dump against the existing customer database, we ultimately need to make a lot of calls to the database. On top of that, we also need to make sure we're writing queries that are efficient and effective at not accidentally missing customers or validating customers that are not actually impacted by making our query too "loose". In order to practice/test with these queries, we strongly recommend downloading SQLiteStudio - which will enable you to select the Customer.db file, and explore/query the DB as you work through to build out the query you want to use. In the video tutorial above, we ultimately landed on using:
Select customer_id,first_name,last_name,card_number,cvv,brand
FROM customer_details,card_details
WHERE customer_details.customer_id = card_details.id and card_number like '<card first digits>%' and expiration = '<expiration from record>' and zip = '<zip from record>'
Where <card first digits>, <expiration from record>, and <zip from record> would be replaced by variables from your bot (don't include the <> either, that was just for communication purposes. A final note on DB Queries: USE SINGLE QUOTES for your String literals. In many programming languages, single vs double quotes don't really matter in most cases...but they are extremely important in SQL. As a helpful rule of thumb:
- [S]ingle quotes are for [S]trings Literals
- (date literals are also strings)
- [D]ouble quotes are for [D]atabase Identifiers
Example: INSERT INTO "USERS" ("LOGIN", "PASSWORD", "DT_BIRTH") VALUES ('EDUARDO', '12345678', '1980-09-06'); Here, USERS, LOGIN, PASSWORD, and DT_BIRTH are all DB identifiers (table or field names), whereas EDUARDO, 12345678, 1980-09-06 are all String literals.
Conclusion
End up with a slightly different solution than we suggested? All good! Part of the fun of learning to build bots is trying out different approaches to see how those changes impact the reliability, accuracy, and ultimately the outcome of your bot. Try out some different queries against the DB to see how that changes the performance, explore the way you're doing looping...could your approach to looping be done more efficiently? There's always room to learn and try new approaches. Enjoy doing this Challenge Page? Check out some of our other RPA Challenge pages to see if you can knock out some of our other bot-building challenges!