Why is tokens field a json type and how to create a query based on inside values?

[Charrette]

Hello,

I don't understand why is the tokens field a json type, is it really necessary to store multiple tokens?

I'm sure there's a good reason, but I have some troubles because of this, on my previous token auth system (handmade) on my API, I was authenticating my users by the token provided by the clients. With a simple line as:

User.find_by(token: params[:token])

How to make this authentication works with this json objects? In the rails console I found that we can get the last generated token for a user by doing:

User.first.tokens.values.last['token']

But how to get a user based on the provided token? Something like:

User.where("users.tokens.values.last['token'] = #{params[:token]}").first

But of course this does not work

[lynndylanhurley]

I don't understand why is the tokens field a json type, is it really necessary to store multiple tokens?

It's necessary if we want to allow users to authenticate using different clients / devices. Because each token needs to have the client's id stored with it.

But how to get a user based on the provided token?

The stored token values are hashes of the original tokens. You can try something like this:

User.where("users.tokens.values.last['token'] = #{::BCrypt::Password.new(params[:token])}")

But you should also be sending the uid header with each request, so you should just be able to do this instead:

# find the user
u = User.find(params[:uid])

# validate the token, where params[:client] is the ID of the device sending the request
u.valid_token?(params[:token], params[:client])

[riggy]

Stumbled upon this issue when I asked myself the same question about tokens stored in json column. Wouldn't it be more efficient in terms of performance if tokens were stored as separate database entries? Token lookup and token generation/storing would be much faster. Only now I noticed that technically main bottleneck of API requests my application is getting is the exact moment, when token column is updated after request.

[zachfeldman]

@riggy do you have any benchmark data to backup your claim?

[riggy]

@zachfeldman Not yet. It was just a hypothetical question (maybe not phrased as one) based on my general knowledge of how relation databases work. I might lack some knowledge on how lookup is performed on json columns though. I was wondering if there was some solid argument to choose that solution over the other and I was hoping to get one.

But you've posted a valid question - I'll try to come up with some data to verify that one way or the other and get back to you.

[KelseyDH]

You can try something like this:
User.where("users.tokens.values.last['token'] = #{::BCrypt::Password.new(params[:token])}")

External params shouldn't be inserted into a query through string interpolation, as it creates opportunities for SQL injections. You'd probably want to rewrite the query to something like:

User.where("users.tokens.values.last['token'] = (?)", ::BCrypt::Password.new(params[:token]))

[lynndylanhurley]

Wouldn't it be more efficient in terms of performance if tokens were stored as separate database entries? Token lookup and token generation/storing would be much faster.

@riggy:

1. No, it would not be more efficient in terms of performance.
2. Disregarding the performance issue, querying by the token value will create a timing attack vulnerability. It's better to query the user by their id and then check the tokens stored on the user's db record using BCrypt's time-safe comparison.

@KelseyDH:

Thanks for pointing out the SQL injection issue. But I want to make it clear that I was not advocating that anyone take the approach described in that example. This is the correct way to check if a user's token is valid:

# find the user
u = User.find(params[:uid])

# validate the token, where params[:client] is the ID of the device sending the request
u.valid_token?(params[:token], params[:client])

[RailsCod3rFuture]

How do you generate tokens for the jsons field? I've been trying a multitude of approaches, but no luck! Can you give me an example of before_save :ensure_authentication_token. Thanks.