Hi everyone! I'm back with another blog post. Today, I want to share with you a recent experience I had while working on a project that involved storing an array of integer values in a database. I initially used an integer column with an array type and an empty array as the default value. Let me show you the code:
add_column :user, :selected, :integer, array: true, default: []
SELECTED = { "0" => "Blue", "1" => "Green", "2" => "Red" }
However, when my senior reviewed my code, he pointed out that integer data type columns should only be used for storing integer values, not other data types. He suggested that the selected column's data type should be a string. Additionally, he asked me to investigate whether using an array in an integer column was a good practice.
To address his concerns, I turned to the official PostgreSQL documentation. According to the documentation,
PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.
Armed with this information, I explained my findings to my senior. In response, he shared another reference link that outlined the permitted params when using the array type in a database column. Here's what he shared:
params.require(:user).permit(:selected, selected: [])
He warned me that if we submit an array as nil or containing nil values, it would raise an error. The error message stated:
Value for params[:selected] was set to nil, because it was one of [], [null] or [null, null, ...]. Go to http://guides.rubyonrails.org/security.html#unsafe-query-generation for more information.
To understand this error better, I consulted the Rails guides on security.
The guide explained that
Due to the way Active Record interprets parameters in combination with the way that Rack parses query parameters it was possible to issue unexpected database queries with IS NULL where clauses. As a response to that security issue (CVE-2012-2660, CVE-2012-2694 and CVE-2013-0155) deep_munge method was introduced as a solution to keep Rails secure by default.
The deep_munge method replaces some values with nil to prevent unsafe query generation. Here's how the parameters are transformed based on the JSON sent in the request:
| JSON | Parameters |
|---|---|
| { "person": null } | { :person => nil } |
| { "person": [] } | { :person => [] } |
| { "person": [null] } | { :person => [] } |
| { "person": [null, null, ...] } | { :person => [] } |
| { "person": ["foo", null] } | { :person => ["foo"] } |
It is possible to return to old behavior and disable deep_munge configuring your application if you are aware of the risk and know how to handle it:
config.action_dispatch.perform_deep_munge = false
After considering all of this information, my senior agreed that using an array in an integer column was a suitable approach. So, we celebrated our decision to use arrays in integer columns! 🥳
This was a positive experience for me. If something happens that makes me interested or teaches me something new, I will let you friends know.
Top comments (0)