API misbehavior with SQLite

[kii-chan-reloaded]

Describe the bug

In database.go, many SQL calls are made that include NOW(), however NOW() does not exist in SQLite3 (see here and here for documentation and examples respectively). As such, endpoints like /api/me return errors whenever access_tokens are used.

Edit: I did not notice before that the now() function existed, however, from what I can tell, it is not used in this line, which is causing the issue.

Steps to reproduce (if necessary)

1. Create a SQLite3 database
2. Create a user account
3. Log in through API (/api/auth/login)
4. Use the access token to request user data (/api/me)
5. Get 500 error from server (My guess is it is triggered at this line)

Expected behavior

Should have received user data

Application configuration

• Single mode
• SQLite3
• Registration Closed
• Federation enabled

Version or last commit: V0.9.1

Other notes

• The /api/me/posts and /api/me/collections endpoints return 401 errors, but /api/me returns 500.
• (Unrelated to this bug) /api/me/posts and similar should probably support pagination or similar to prevent undue strain on the server / excessive bandwidth usage from users with a large volume of posts.

[thebaer]

Thanks for the report! Yes, this query should use the now() helper function to fix this. Anyone should feel free to patch this -- otherwise I will when I have a chance.

That's also a good point about pagination on the /api/me/posts endpoint. We've discussed it a bit for other endpoints on the forum before, but please feel free to start a new topic there for this endpoint in particular.

[qwazix]

Setting the now() function instead of NOW() indeed makes the /api/me endpoint 500 error to go away but it is replaced by a 401, just like the other endpoints.

It seems that SQLite doesn't like queries with byte arrays and returns an empty result set. If you replace the access token with a textual representation in the database (Token 00000000-0000-0000-0000-000000000000) and then prepare the query with plain access token instead of auth.GetToken(accessToken) the 401 error goes away.

One solution is to store the token in text if when in SQLite mode but this sounds like technical debt to me (code branches both in writes and reads). I suppose that the tokens were stored in blobs for performance reasons so converting to text for both db backends is probably not a good idea?

I can create a PR with the minor now() change and open another issue for the 401.

[thebaer]

Thanks for taking a look at this, @qwazix!

I would say we should avoid changing what data we store and just try to fix the issue with SQLite. Looking at the schema again, we're using TEXT for the token column. Maybe BLOB would be more fitting / fix this issue?

[qwazix]

I checked with TEXT but I'm getting the same behavior. However, changing token = ? to token LIKE ? seems to fix the issue. I also checked with a MySQL installation and it still works.

Please have a look at the PR.

[donnuven]

I've done some testing with these changes and it seems that it does work when using LIKE replacing the = via search query on both ends with SQLite and MySQL installations. Thus far, there is nothing wrong with anything as far as I can see.