Call Center Outsourcing

Call Center Outsourcing

One year ago, we received a contract as a PDF file. It was digitally signed. We looked at the document - ignoring the "certificate is not trusted" warning shown by the viewer - and asked ourselfs:

"How do PDF signatures exactly work?"

We are quite familiar with the security of message formats like XML and JSON. But nobody had an idea, how PDFs really work. So we started our research journey.

Today, we are happy to announce our results. In this blog post, we give an overview how PDF signatures work and on top, we reveal three novel attack classes for spoofing a digitally signed PDF document. We present our evaluation of 22 different PDF viewers and show 21 of them to be vulnerable. We additionally evaluated 8 online validation services and found 6 to be vulnerable.

In cooperation with the BSI-CERT, we contacted all vendors, provided proof-of-concept exploits, and helped them to fix the issues and three generic CVEs for each attack class were issued: CVE-2018-16042, CVE-2018-18688, CVE-2018-18689.

Full results are available in the master thesis of Karsten Meyer zu Selhausen, in our security report, and on our website.

Digitally Signed PDFs? Who the Hell uses this?

Maybe you asked yourself, if signed PDFs are important and who uses them.
In fact, you may have already used them.
Have you ever opened an Invoice by companies such as Amazon, Sixt, or Decathlon?
These PDFs are digitally signed and protected against modifications.
In fact, PDF signatures are widely deployed in our world. In 2000, President Bill Clinton enacted a federal law facilitating the use of electronic and digital signatures in interstate and foreign commerce by ensuring the validity and legal effect of contracts. He approved the eSign Act by digitally signing it.
Since 2014, organizations delivering public digital services in an EU member state are required to support digitally signed documents, which are even admissible as evidence in legal proceedings.
In Austria, every governmental authority digitally signs any official document [§19]. In addition, any new law is legally valid after its announcement within a digitally signed PDF.
Several countries like Brazil, Canada, the Russian Federation, and Japan also use and accept digitally signed documents.
According to Adobe Sign, the company processed 8 billion electronic and digital signatures in the 2017 alone.

Crash Course: PDF and PDF Signatures

To understand how to spoof PDF Signatures, we unfortunately need to explain the basics first. So here is a breef overview.

PDF files are ASCII files. You can use a common text editor to open them and read the source code.

PDF header. The header is the first line within a PDF and defines the interpreter version to be used. The provided example uses version PDF 1.7.

PDF body. The body defines the content of the PDF and contains text blocks, fonts, images, and metadata regarding the file itself. The main building blocks within the body are objects. Each object starts with an object number followed by a generation number. The generation number should be incremented if additional changes are made to the object.

In the given example, the Body contains four objects: Catalog, Pages, Page, and stream. The Catalog object is the root object of the PDF file. It defines the document structure and can additionally declare access permissions. The Catalog refers to a Pages object which defines the number of the pages and a reference to each Page object (e.g., text columns). The Page object contains information how to build a single page. In the given example, it only contains a single string object "Hello World!".

Xref table. The Xref table contains information about the position (byte offset) of all PDF objects within the file.

Trailer. After a PDF file is read into memory, it is processed from the end to the beginning. By this means, the Trailer is the first processed content of a PDF file. It contains references to the Catalog and the Xref table.

How do PDF Signatures work?

PDF Signatures rely on a feature of the PDF specification called incremental saving (also known as incremental update), allowing the modification of a PDF file without changing the previous content.

As you can see in the figure on the left side, the original document is the same document as the one described above. By signing the document, an incremental saving is applied and the following content is added: a new Catalog, a Signature object, a new Xref table referencing the new object(s), and a new Trailer. The new Catalog extends the old one by adding a reference to the Signature object. The Signature object (5 0 obj) contains information regarding the applied cryptographic algorithms for hashing and signing the document. It additionally includes a Contents parameter containing a hex-encoded PKCS7 blob, which holds the certificates as well as the signature value created with the private key corresponding to the public key stored in the certificate. The ByteRange parameter defines which bytes of the PDF file are used as the hash input for the signature calculation and defines 2 integer tuples:
a, b : Beginning at byte offset a, the following b bytes are used as the first input for the hash calculation. Typically, a 0 is used to indicate that the beginning of the file is used while a b is the byte offset where the PKCS#7 blob begins.
c, d : Typically, byte offset c is the end of the PKCS#7 blob, while c d points to the last byte range of the PDF file and is used as the second input to the hash calculation.

According to the specification, it is recommended to sign the whole file except for the PKCS#7 blob (located in the range between a b and c).

Attacks

During our research, we discovered three novel attack classes on PDF signatures:

Universal Signature Forgery (USF)
Incremental Saving Attack (ISA)
Signature Wrapping Attack (SWA)

In this blog post, we give an overview on the attacks without going into technical details. If you are more interested, just take a look at the sources we summarized for you here.

Universal Signature Forgery (USF)

The main idea of Universal Signature Forgery (USF) is to manipulate the meta information in the signature in such a way that the targeted viewer application opens the PDF file, finds the signature, but is unable to find all necessary data for its validation.

Instead of treating the missing information as an error, it shows that the contained signature is valid. For example, the attacker can manipulate the Contents or ByteRange values within the Signature object. The manipulation of these entries is reasoned by the fact that we either remove the signature value or the information stating which content is signed.
The attack seems trivial, but even very good implementations like Adobe Reader DC preventing all other attacks were susceptible against USF.

Incremental Saving Attack (ISA)

The Incremental Saving Attack (ISA) abuses a legitimate feature of the PDF specification, which allows to update a PDF file by appending the changes. The feature is used, for example, to store PDF annotations, or to add new pages while editing the file.

The main idea of the ISA is to use the same technique for changing elements, such as texts, or whole pages included in the signed PDF file to what the attacker desires.
In other words, an attacker can redefine the document's structure and content using the Body Updates part. The digital signature within the PDF file protects precisely the part of the file defined in the ByteRange. Since the incremental saving appends the Body Updates to the end of the file, it is not part of the defined ByteRange and thus not part of the signature's integrity protection. Summarized, the signature remains valid, while the Body Updates changed the displayed content.
This is not forbidden by the PDF specification, but the signature validation should indicate that the document has been altered after signing.

Signature Wrapping Attack (SWA)

Independently of the PDFs, the main idea behind Signature Wrapping Attacks is to force the verification logic to process different data than the application logic.

In PDF files, SWA targets the signature validation logic by relocating the originally signed content to a different position within the document and inserting new content at the allocated position. The starting point for the attack is the manipulation of the ByteRange value allowing to shift the signed content to different loctions within the file.

On a very technical level, the attacker uses a validly signed document (shown on the left side) and proceeds as follows:

Step 1 (optional): The attacker deletes the padded zero Bytes within the Contents parameter to increase the available space for injecting manipulated objects.
Step 2: The attacker defines a new /ByteRange [a b c* d] by manipulating the c value, which now points to the second signed part placed on a different position within the document.
Step 3: The attacker creates a new Xref table pointing to the new objects. It is essential that the byte offset of the newly inserted Xref table has the same byte offset as the previous Xref table. The position is not changeable since it is refer- enced by the signed Trailer. For this purpose, the attacker can add a padding block (e.g., using whitespaces) before the new Xref table to fill the unused space.
Step 4: The attacker injects malicious objects which are not protected by the signature. There are different injection points for these objects. They can be placed before or after the malicious Xref table. If Step 1 is not executed, it is only possible to place them after the malicious Xref table.
Step 5 (optional): Some PDF viewers need a Trailer after the manipulated Xref table, otherwise they cannot open the PDF file or detect the manipulation and display a warning message. Copying the last Trailer is sufficient to bypass this limitation.
Step 6: The attacker moves the signed content defined by c and d at byte offset c*. Optionally, the moved content can be encapsulated within a stream object. Noteworthy is the fact that the manipulated PDF file does not end with %%EOF after the endstream. The reason why some validators throw a warning that the file was manipulated after signing is because of an %%EOF after the signed one. To bypass this requirement, the PDF file is not correctly closed. However, it will be still processed by any viewer.

Evaluation

In our evaluation, we searched for desktop applications validating digitally signed PDF files. We analyzed the security of their signature validation process against our 3 attack classes. The 22 applications fulfill these requirements. We evaluated the latest versions of the applications on all supported platforms (Windows, MacOS, and Linux).

Authors of this Post

Vladislav Mladenov
Christian Mainka
Karsten Meyer zu Selhausen
Martin Grothe
Jörg Schwenk

Acknowledgements

Many thanks to the CERT-Bund team for the great support during the responsible disclosure.
We also want to acknowledge the teams which reacted to our report and fixed the vulnerable implementations.

References:

FILE INCLUSION VULNERABILITIES: https://www.offensive-security.com/metasploit-unleashed/file-inclusion-vulnerabilities/
php://: https://www.php.net/manual/en/wrappers.php.php
LFI Cheat Sheet: https://highon.coffee/blog/lfi-cheat-sheet/
File inclusion vulnerability: https://en.wikipedia.org/wiki/File_inclusion_vulnerability
PHP 5.2.0 Release Announcement: https://www.php.net/releases/5_2_0.php

I must confess, I've used Python for quite some time without really learning most of the language. It's my go-to language for modeling embedded systems problems and doing data analysis, but I've picked up the language mostly through googling what I need and reading the abbreviated introductions of Python data science books. It was time to remedy that situation with the first book in this face-off: Effective Python: 59 Specific Ways to Write Better Python by Brett Slatkin. I didn't want a straight learn-a-programming-language book for this exercise because I already knew the basics and just wanted more depth. For the second book, I wanted to explore how machine learning libraries are actually implemented, so I picked up Data Science from Scratch: First Principles with Python by Joel Grus. These books don't seem directly related other than that they both use Python, but they are both books that look into how to use Python to write programs in an idiomatic way. Effective Python focuses more on the idiomatic part, and Data Science from Scratch focuses more on the writing programs part.

VS.

Effective Python

I thought I had learned a decent amount of Python already, but this book shows that Python is much more than list comprehensions and remembering self everywhere inside classes. My prior knowledge on the subjects in the first couple chapters was fifty-fifty at best, and it went down from there. Slatkin packed this book with useful information and advice on how to use Python to its fullest potential, and it is worthwhile for anyone with only basic knowledge of the language to read through it.

The book is split into eight chapters with the title's 59 Python tips grouped into logical topics. The first chapter covers the basic syntax and library functions that anyone who has used the language for more than a few weeks will know, but the advice on how to best use these building blocks is where the book is most helpful. Things like avoiding using start, end, and stride all at once in slices or using enumerate instead of range are good recommendations that will make your Python code much cleaner and more understandable.

Sometimes the advice gets a bit far-fetched, though. For example when recommending to spell out the process of setting default function arguments, Slatkin proposed this method:

def get_first_int(values, key, default=0):
    found = values.get(key, [''])
    if found[0]:
        found = int(found[0])
    else:
        found = default
    return found

Over this possibility using the or operator short-circuit behavior:

def get_first_int(values, key, default=0):
    found = values.get(key, [''])[0]
    return int(found or default)

He claimed that the first was more understandable, but I just found it more verbose. I actually prefer the second version. This example was the exception, though. I agreed and was impressed with nearly all of the rest of his advice.

The second chapter covered all things functions, including how to write generators and enforce keyword-only arguments. The next chapter, logically, moved into classes and inheritance, followed by metaclasses and attributes in the fourth chapter. What I liked about the items in these chapters was that Slatkin assumes the reader already knows the basic syntax so he spends his time describing how to use the more advanced features of Python most effectively. His advice is clear and direct so it's easy to follow and put to use.

Next up is chapter 5 on concurrency and parallelism. This chapter was great for understanding when to use threads, processes, and the other concurrency features of Python. It turns out that threads and processes have unique behavior (beyond processes just being heavier weight threads) because of the global interpreter lock (GIL):

The GIL has an important negative side effect. With programs written in languages like C++ or Java, having multiple threads of execution means your program could utilize multiple CPU cores at the same time. Although Python supports multiple threads of execution, the GIL causes only one of them to make forward progress at a time. This means that when you reach for threads to do parallel computation and speed up your Python programs, you will be sorely disappointed.

If you want to get true parallelism out of Python, you have to use processes or futures. Good to know. Even though this chapter was fairly short, it was full of useful advice like this, and it was possibly the most interesting part of the book.

The next chapter covered built-in modules, and specifically how to use some of the more complex parts of the standard library, like how to define decorators with functools.wraps, how to make some sense of datetime and time zones, and how to get precision right with decimal. Maybe these aren't the most interesting of topics, but they're necessary to get right.

Chapter 7 covers how to structure and document Python modules properly when you're collaborating with the rest of the community. These things probably aren't useful to everyone, but for those programmers working on open source libraries it's helpful to adhere to common conventions. The last chapter wraps up with advice for developing, debugging, and testing production level code. Since Python is a dynamic language with no static type checking, it's imperative to test any code you write. Slatkin relates a story about how one programmer he knew swore off ever using Python again because of a SyntaxError exception that was raised in a running production program, and he had this to say about it:

But I have to wonder, why wasn't the code tested before the program was deployed to production? Type safety isn't everything. You should always test your code, regardless of what language it's written in. However, I'll admit that the big difference between Python and many other languages is that the only way to have any confidence in a Python program is by writing tests. There is no veil of static type checking to make you feel safe.

I would have to agree. Every program needs to be tested because syntax errors should definitely be caught before releasing to production, and type errors are a small subset of all runtime errors that can occur in a program. If I was depending on the compiler to catch all of the bugs in my programs, I would have a heckuva lot more bugs causing problems in production. Not having a compiler to catch certain classes of errors shouldn't be a reason to give up the big productivity benefits of working in a dynamic language like Python.

I thoroughly enjoyed learning how to write better Python programs through the collection of pro tips in this book. Each tip was focused, relevant, and clear, and they all add up to a great advanced level book on Python. Even better, the next time I need to remember how to do concurrency or parallelism or how to write a proper function with keyword arguments, I'll know exactly where to look. If you want to learn how to write Python code the Pythonic way, I'd highly recommend reading through this book.

Data Science from Scratch

I didn't expect to enjoy this book quite as much as I did. I went into it expecting to learn about how to implement the fundamental tools of the trade for data science, and that was indeed what I got out of the book. But I also got a lighthearted, entertaining, and surprisingly easy-to-read tour of the basics of machine learning using Python. Joel Grus has a matter-of-fact writing style and a dry wit that I immediately took to and thoroughly enjoyed. These qualities made a potentially complex and confusing topic much easier to understand, and humorous to boot, like having an excellent tour guide in a museum that can explain medieval culture in detail while cracking jokes about how toilet paper wasn't invented until the 1850s.

Of course, like so many programming books, this book starts off with a primer on the Python language. I skipped this chapter and the next on drawing graphs, since I've had just about enough of language primers by now, especially for languages that I kind of already know. The real "from scratch" parts of the book start with chapter 4 on linear algebra, where Grus establishes the basic functions necessary for doing computations on vectors and matrices. The functions and classes shown throughout the book are well worth typing out in your own Python notebook or project folder and running through an interpreter, since they are constantly being used to build up tooling in later chapters from the more fundamental tools developed in earlier chapters. The progression of development from this chapter on linear algebra all the way to the end was excellent, and it flowed smoothly and logically over the course of the book.

The next few chapters were on statistics, probability, and their use with hypothesis testing and inference. Sometimes Grus glossed over important points here, like when explaining standard deviations he failed to mention that this metric only applies to (or at least applies best to) normal distributions. Distributions that deviate too much from the normal curve will not have meaningful standard deviations. I'm willing to cut him some slack, though, because he is covering things quickly and makes it clear that his goal is to show roughly what all of this stuff looks like in simple Python code, not to make everything rigorous and perfect. For instance, here's his gentle reminder on method in the probability chapter:

One could, were one so inclined, get really deep into the philosophy of what probability theory means. (This is best done over beers.) We won't be doing that.

He finishes up the introductory groundwork with a chapter on gradient descent, which is used extensively in the later machine learning algorithms. Then there are a couple chapters on gathering, cleaning, and munging data. He has some opinions about some API authors choice of data format:

Sometimes an API provider hates you and only provides responses in XML.

And he has some good expectation setting for the beginner data scientist:

After you've identified the questions you're trying to answer and have gotten your hands on some data, you might be tempted to dive in and immediately start building models and getting answers. But you should resist this urge. Your first step should be to explore your data.

Data is never exactly in the form that you need to do what you want to do with it, so while the gathering and the munging is tedious, it's a necessary skill that separates the great data scientist from the merely mediocre. Once we're done learning how to whip our data into shape, it's off to the races, which is great because we're now halfway through this book.

The chapters on machine learning models, starting with chapter 12, are excellent. While Grus does not go into intricate detail on how to make the fastest, most efficient MLMs (machine learning models, not multi-level marketing), that is not the point. His objective is to show as clearly as possible what each of these algorithms looks like and that it is possible to understand how they work when shown in their essence. The models include k-nearest neighbors, naive bayes, linear regression, multiple regression, logistic regression, decision trees, neural networks, and clustering. Each of these models is actually conceptually simple, and the models can be described in dozens of lines of code or less. These implementations may be doggedly slow for large data sets, but they're great for understanding the underlying ideas of each algorithm.

Threaded through each of these chapters are examples of how to use each of the statistical and machine learning tools that is being developed. These examples are presented within the context of the tasks given to a new data scientist who is an employee of a budding social media startup for…well…data scientists. I just have to say that it is truly amazing how many VPs a young startup can support, and I feel awfully sorry for this stalwart data scientist fulfilling all of their requests. This silliness definitely keeps the book moving along.

The next few chapters delve a bit deeper into some interesting problems in data science: natural language processing, network analysis (or graph algorithms), and recommender systems. These chapters were just as great as the others, and by now we've built up our data science tooling pretty well from the original basics of linear algebra and statistics. The one thing we haven't really talked about, yet, is databases. That's the topic of the 23rd chapter, where we implement some of the basic operations of SQL in Python in the most naive way possible. Once again it's surprising to see how little code is needed to implement things like SELECT or INNER JOIN as long as we don't give a flying hoot about performance.

Grus wraps things up with an explanation of the great and all-powerfull MapReduce, and shows the basics of how it would be implemented with mapper and reducer functions and the plumbing to string it together. He does not get into how to distribute this implementation to a compute cluster, but that's the topic of other more complicated books. This one's done from scratch so like everything else, it's just the basics. That was all fine with me because the basics are really important, and knowing the basics well can lead you to a much deeper understanding of the more complex concepts much faster than if you were to try to dive into the deep end without knowing the basic strokes. This book provides that foundation, and it does it with flair. I highly recommend giving it a read.

Both Effective Python and Data Science from Scratch were excellent books, and together they could give a programmer a solid foundation in Python and data science as long as they already have some experience in the language. With that being said, Data Science from Scratch will not provide the knowledge on how to use the powerful data analysis and machine learning libraries like numpy, pandas, scikit-learn, and tensorflow. For that, you'll have to look elsewhere, but the advanced, idiomatic Python and fundamental data science principles are well covered between these two books.

Call Center Outsourcing

In this next installment of the miniseries of exploring the monster taming mechanics of Final Fantasy XIII-2, we'll fill out another database table that we need in order to start connecting all of the monster data together. In the last article, we built the core monster table with hundreds of attributes for each of 164 monsters. In the first article, we had identified four other tables that we would need as well, these being abilities, game areas, monster materials, and monster characteristics. The data in these four tables is all related in one way or another to the monsters in the monster table. We'll start with the abilities table, which will end up being three tables because we actually have passive, command, and role abilities. Once the passive abilities table is complete, we'll see how to connect that data in the database so that we can later make inferences on the data.

Finding Monster Abilities

Like for the core monster data, the first thing we need to do is get the data for the monster abilities into a .csv format. We'll start with the passive abilities because this is the largest group of abilities and they have a rank attribute that the role and hidden abilities don't have. Since there are over 200 passive abilities, we're going to want to pull this data out of the FAQ with another script instead of building it up manually. We can use an FSM very similar to the one used for parsing the monster data with a few simplifications because there are not nearly so many attributes for the passive abilities. I'll just throw the script out there, and then we can discuss it:

SECTION_TAG = "PassADe"
ABILITY_SEPARATOR = "........................................"
ABILITY_REGEX = /(\w\S+(?:\s[^\s\(]+)*)\s\((RL|\d)\)-*:\s(\w\S+(?:\s\S+)*)/
ABILITY_EXT_REGEX = /^\s+(\S+(?:\s\S+)*)/

end_abilities = lambda do |line, data|
  return end_abilities, data
end

new_ability = lambda do |line, data|
  props = line.scan(ABILITY_REGEX)
  if props.empty?
    if line.include? ABILITY_SEPARATOR
      return end_abilities, data
    else
      extra_line = ABILITY_EXT_REGEX.match(line)
      data.last["description"] += ' ' + extra_line[1]
      return new_ability, data
    end
  end

  if props.first[1] == "RL"
    props.first[1] = "99"
    props.first[0] += " (RL)"
  end
  data << {"name" => props.first[0], "rank" => props.first[1], "description" => props.first[2]}
  return new_ability, data
end

find_abilities = lambda do |line, data|
  if line.include? ABILITY_SEPARATOR
    return new_ability, data
  end
  return find_abilities, data
end

find_sub_section = lambda do |line, data|
  if line.include? SECTION_TAG
    return find_abilities, data
  end
  return find_sub_section, data
end

section_tag_found = lambda do |line, data|
  if line.include? SECTION_TAG
    return find_sub_section, data
  end
  return section_tag_found, data
end

start = lambda do |line, data|
  if line.include? SECTION_TAG
    return section_tag_found, data
  end
  return start, data
end

next_state = start
data = []
File.foreach("ffiii2_monster_taming_faq.txt") do |line|
  next_state, data = next_state.(line, data)
end

Starting from the bottom, we can see that we kickoff the FSM by looking for the SECTION_TAG, which is "PassADe" in this case. We have to find it three times instead of the two times we looked for the section tag in the monster FSM because the tag appears one extra time in the FAQ in a sub-contents under the main "Monster Infusion" section. Then, we look for the ABILITY_SEPARATOR, which is the same as the MONSTER_SEPARATOR from the previous script, and then go into a loop of matching and storing the ability data in the data list of hash tables. The ability data has a pretty clean format with the possibility of overflowing to a second line. The first couple abilities showcase the variations we have to handle:

Uncapped Damage (RL)---------: Raises the cap on damage to 999,999.
Enhanced Commando (RL)-------: Enhances the Commando Role Bonus by one Bonus
                               Boost.

We have a name that can be multiple words (and by looking further ahead we also see some special characters) followed by a space and a rank in parentheses, a -: separator, and the description, possibly extending to a second line. We want to capture the name, rank, and description, and the rank can be either "RL" (for Red Lock, meaning it can never be removed from the monster) or a number 1-9. From what we've learned of regexes in past posts, it should be clear that the regex

/(\w\S+(?:\s[^\s\(]+)*)\s\((RL|\d)\)-*:\s(\w\S+(?:\s\S+)*)/

is sufficient to match on the first line and capture those three attributes. Looking at the rest of the new_ability state code, we can see that if the line doesn't match the ABILITY_REGEX, it checks if the line is the ABILITY_SEPARATOR and moves to the last state if it is. Otherwise, we know that the line is an extra line of description, so we capture it and add it to the description of the last ability captured. If the line did match the ABILITY_REGEX, then we create a new hash with the values populated from the regex captures and continue in the same state.

Note that if the rank was "RL", we convert that to "99" so that all ranks will be integers in the database. Why not "10?" Well, when an ability is yellow-locked, its rank increases by nine, so making the red-locked abilities have a rank of 10 would conflict. I could have made it 19 or 20, but 99 works just as well and really sets them apart. We also need to add the " (RL)" text back into the name of the ability because it's possible to have red-locked and non-red-locked abilities with the same name, and they are different abilities for all intents and purposes.

Validating and Exporting Monter Abilities

Next, we want to do a little validation check on this data and write it to a .csv file, so here's the code to do that:

PROPER_NAME_REGEX = /^\w.*[\w)!%]$/
NUMBER_REGEX = /^\d\d?$/
FREE_TEXT_REGEX = /^\S+(?:\s\S+)*$/

VALID_ABILITY = {
  "name" => PROPER_NAME_REGEX,
  "rank" => NUMBER_REGEX,
  "description" => FREE_TEXT_REGEX,
}

data.each do |ability|
  VALID_ABILITY.each do |key, regex|
    if ability.key?(key)
      unless ability[key] =~ regex
        puts "Monster ability #{ability["name"]} has invalid property #{key}: #{ability[key]}."
      end
    else
      puts "Monster ability #{ability["name"]} has missing property #{key}."
    end
  end
end

require 'csv'
opts = {headers: data.first.keys, write_headers: true}
CSV.open("monster_abilities.csv", "wb", opts) do |csv|
  data.each { |hash| csv << hash }
end

Since we're building up each hash table directly with name, rank, and description, it's not necessary to make sure that each hash contains all three attributes and no others. We just want to make sure that each attribute value has the right (admittedly loose) format. Then we write it out to the monster_abilities.csv file.

Importing Monster Abilities

Moving right along, we can now import this .csv file into the database with another simple addition to our seed script:

csv_file_path = 'db/monster_abilities.csv'

CSV.foreach(csv_file_path, {headers: true}) do |row|
  Ability.create!(row.to_hash)
  puts "#{row['name']} added!"
end

Before running this script, we need to generate the Ability model and its schema. Since there are only three attributes, we can easily create the migration script in one shot:

$ rails generate model Ability name:string rank:integer description:string

This command will create this migration file:

class CreateAbilities < ActiveRecord::Migration[6.0]
  def change
    create_table :abilities do |t|
      t.string :name
      t.integer :rank
      t.string :description

      t.timestamps
    end
  end
end

Now we can run the migration and then seed the database:

$ rails db:migrate
$ rails db:seed

And we've added our second table to the database with 147 abilities. (Wait, I thought there were over 200 abilities. Good catch. We'll get to that in a bit.) However, we should have a link between this new ability table and the monster table because every monster's many abilities should all be one of the abilities in the ability table.

Associating Monsters' Abilities with the Abilities Table

What we want to do in order to associate each monster's abilities with the abilities table is make each ability in the monster table a reference instead of a string, and point those references at the correct specific abilities in the abilities table. To accomplish this association, we'll need to change both the monster table migration and the monster model part of the seed script.

First, we can change all of the t.string declarations in the monster table migration to t.references. Changing only the abilities that end in "_passive" is sufficient for now because those are the abilities we created in the abilities table. The references for these abilities will actually be foreign keys from the abilities table, so we need to tell Rails that at the end of the migration:

class CreateMonsters < ActiveRecord::Migration[6.0]
  def change
    create_table :monsters do |t|
      t.string :name
      # ... The other monster attributes ...
      t.references :default_passive1
      t.references :default_passive2
      t.references :default_passive3
      t.references :default_passive4
      t.string :default_skill
      t.string :special_notes
      t.references :lv_02_passive
      t.string :lv_02_skill
      # ... The rest of the lv XX abilities ...

      t.timestamps
    end

    add_foreign_key :monsters, :abilities, column: :default_passive1_id, primary_key: :id
    add_foreign_key :monsters, :abilities, column: :default_passive2_id, primary_key: :id
    add_foreign_key :monsters, :abilities, column: :default_passive3_id, primary_key: :id
    add_foreign_key :monsters, :abilities, column: :default_passive4_id, primary_key: :id
    add_foreign_key :monsters, :abilities, column: :lv_02_passive_id, primary_key: :id
    # ... All of the other _passive abilities foreign keys ...
  end
end

I know this seems like tedious busy work, but it's necessary for setting up the database. Some of it can be done with regex replace tools in some editors or generated with a script. I just brute-forced it and got pretty good at changing numbers really rapidly. Be sure to remove lv_xx_passive abilities that don't exist later in the list.

Because we changed this migration, we need to rerun it, but now it references the ability migration so when we rollback the migrations, we need to change the date in the file name on the monster migration to be after the ability migration before we run the migrations forward again. Otherwise, Rails will complain.

$ rails db:rollback STEP=2
$ [rename the monster migration file to a date after the ability migration file]
$ rails db:migrate

We're not quite done with preparing the database, yet, because the Rails models need to know about this association, too. The app/models/monster.rb file needs to know that the passive ability attributes are associated with the Ability model using the belongs_to association:

class Monster < ApplicationRecord
  belongs_to :default_passive1, class_name: 'Ability', optional: true
  belongs_to :default_passive2, class_name: 'Ability', optional: true
  belongs_to :default_passive3, class_name: 'Ability', optional: true
  belongs_to :default_passive4, class_name: 'Ability', optional: true
  belongs_to :lv_02_passive, class_name: 'Ability', optional: true
  # ... The rest of the lv_XX_passive attributes ...
end

It may sound weird that a monster belongs to an ability, but that's the type of association we want where the link points from the monster attribute to the ability. It's the same as if we had an Author model and a Book model, and the book belongs to the author that wrote it. The link would be in the book with the author's ID. That same link direction is what we want with monsters having links to ability IDs, so belongs_to it is.

We also need to add associations to the Ability model so that we can follow links from abilities to monsters. That association is done with has_many, which also seems a bit weird, but oh well:

class Ability < ApplicationRecord
  has_many :default_passive1_monsters, :class_name => 'Monster', :foreign_key => 'default_passive1'
  has_many :default_passive2_monsters, :class_name => 'Monster', :foreign_key => 'default_passive2'
  has_many :default_passive3_monsters, :class_name => 'Monster', :foreign_key => 'default_passive3'
  has_many :default_passive4_monsters, :class_name => 'Monster', :foreign_key => 'default_passive4'
  has_many :lv_02_passive_monsters, :class_name => 'Monster', :foreign_key => 'lv_02_passive'
  # ... The rest of the lv_XX_passive attributes ...
end

That was the easy, if mindlessly tedious, part. The next step is trickier. We want to change the db seed script so that when the monsters are created, the passive abilities in the monster table are references to the abilities table, and we want to catch any instances where the ability doesn't exist, meaning there was a typo or an omission. We need to make sure the ability table is populated first in the script, so we have access to those abilities when we're importing the monsters. Then, for each passive ability for each monster we search the ability table for that ability's name, and assign it to the corresponding ability attribute for the monster. That assignment creates the proper reference. If the ability isn't found, we print an error and return so the error can be fixed in the FAQ text file. We'll then have to rerun the ability or monster parser and try the import again. Here's what this process looks like in code, including the ability import:

csv_file_path = 'db/monster_abilities.csv'

CSV.foreach(csv_file_path, {headers: true}) do |row|
  Ability.create!(row.to_hash)
  puts "#{row['name']} added!"
end

csv_file_path = 'db/monsters.csv'

CSV.foreach(csv_file_path, {headers: true}) do |row|
  monster = row.to_hash
  monster.keys.select { |key| key.ends_with? '_passive' }.each do |key|
    if monster[key]
      monster[key] = Ability.find_by(name: monster[key])
      if monster[key].nil?
        puts "ERROR: monster #{monster['name']} #{key} not found!"
        return
      end
      puts "Found #{key} #{monster[key].name}"
    end
  end
  Monster.create!(monster)
  puts "#{row['name']} added!"
end

This code is pretty much written as just described, but note that we check if the monster[key] exists before doing anything with it. Remember that these are optional attributes, so they can be nil for any given monster ability. If we run this script, we'll find right away that Cactuaroni has an ability "Critical: Haste (RL)" that doesn't exist in the ability table. We could add this ability to the FAQ and rerun the parser, but notice that there is an ability "Critical: Haste" already in the table. This issue of the non-red-locked ability existing in the table but not the red-locked version is fairly common, so we could solve the problem with code instead of tediously rerunning the scripts to find all of the instances where the red-locked ability is missing. All we have to do is search for the base ability, copy it, change the rank to 99, and tack " (RL)" onto the name. This process can be done like so:

CSV.foreach(csv_file_path, {headers: true}) do |row|
  monster = row.to_hash
  monster.keys.select { |key| key.include? '_passive' }.each do |key|
    if monster[key]
      if monster[key].ends_with?(' (RL)') && Ability.find_by(name: monster[key]).nil?
        ability_name = monster[key][0..-6]
        puts "Searching for #{key} ability #{ability_name}"
        ability = Ability.find_by(name: ability_name).dup
        ability['name'] = monster[key]
        ability['rank'] = '99'
        ability.save
        puts "Ability #{ability['name']} added!"
      end

      monster[key] = Ability.find_by(name: monster[key])
      if monster[key].nil?
        puts "ERROR: monster #{monster['name']} #{key} not found!"
        return
      end
      puts "Found #{key} #{monster[key].name}"
    end
  end
  Monster.create!(monster)
  puts "#{row['name']} added!"
end

If the monster ability ends with " (RL)" and it's not in the ability table, then we copy the base ability and create the corresponding red-locked ability from it. We've sidestepped a bunch of dreary work with a little bit of targeted code!

Now after running this script to seed the database, we will find a few more errors in the FAQ to fix. Four red-locked abilities are missing, so we'll have to add Perpetual Poison, Resist Damage +05%, Bonus CP, and Feral Speed. I found definitions for these abilities simply by googling them. The Resist Elements +20% ability that Twilight Odin has was also missing. I guessed at the rank of 8 for this one because I couldn't find it. Resist Elements +30% has a rank of 9 and Resist Elements +05% has a rank of 5, so it's most likely rank 7 or 8. Finally, there were three typos: the ability "Auto: Enfire (RL)" should not have the colon, the ability "Auto Haste (RL)" should be hyphenated, and the ability "ATB: Advantage (RL)" should not have the colon. After everything is fixed, we have a complete ability table with 218 abilities, and every passive monster ability is linked correctly to the ability table.

That was quite a lot of work, some of it tedious, but we accomplished a lot. We generated a list of passive abilities from the FAQ, validated that data, imported it into a new table in the database, and linked all of those abilities to the monsters that can learn them in the monster table. This process can be repeated for the much smaller tables that are left to create, and that is what we'll do next time.

Call Center Outsourcing

Monday, April 13, 2020

How To Spoof PDF Signatures