Single quote in value results in SQLITE_ERROR

[dshepsis]

Problem

When using Keyv#set, an error is thrown if the value contains a string containing a single quote.

For example, if the string is simply a single quote, the error is:

node:internal/process/promises:246
          triggerUncaughtException(err, true /* fromPromise */);
          ^

[Error: SQLITE_ERROR: near "","": syntax error] {
  errno: 1,
  code: 'SQLITE_ERROR'
}

How to reproduce

Run this demonstration code in a directory with Keyv added as a dependency:

const Keyv = require('keyv');

const testDB = new Keyv('sqlite://test.sqlite', { namespace: 'test' });
testDB.on('error', err => console.log('Connection Error', err));

const value = "'";
testDB.set('key', value);

This will result in the above error.

It seems that #210 was supposed to address this issue, but it's either not part of the current release, or failed to fix the issue. Either way, a test case should probably be added for this.

Expected behavior

Values containing single quotes in strings should be serialized normally, like any other values.

Version

4.1.1

[jaytist]

@dshepsis can you pas it like this"\'" or '\''

[dshepsis]

@dshepsis can you pas it like this"\'" or '\''

This doesn't work for a few reasons. Of course, just using a single backslash has no effect because the single quote is already a literal in this case. In other words, the following code produces exactly the same result as the code in the original report:

const Keyv = require('keyv');

const testDB = new Keyv('sqlite://test.sqlite', { namespace: 'test' });
testDB.on('error', err => console.log('Connection Error', err));

const value = "\'";
testDB.set('key', value);

If you instead use a double backslash "\\'", it produces the same error yet again (not really sure why. Maybe backslash isn't a valid escape character in this SQL implementation?). Using a double single quote "''" does actually work, with the saved result having just a single single quote, since a double single quote is a valid escape sequence for a single single quote in this case.

This isn't an acceptable workaround either, because I'm trying to store objects instead of strings. To escape everything would require iterating through every single key and value of the object, recursively, to double-up every single quote. Also, this means that anyone trying to pass in strings/objects which already contain double single quotes (for whatever reason) are having their data silently modified by keyv.

It's also worth mentioning that the key passed into the .set method also can't contain single quotes:

const Keyv = require('keyv');

const testDB = new Keyv('sqlite://test.sqlite', { namespace: 'test' });
testDB.on('error', err => console.log('Connection Error', err));

const value = 'foo';
testDB.set('\'', value);

Results in the error:

node:internal/process/promises:246
          triggerUncaughtException(err, true /* fromPromise */);
          ^

[Error: SQLITE_ERROR: unrecognized token: "{"] {
  errno: 1,
  code: 'SQLITE_ERROR'
}

This can be avoided using double single quotes in the key (both when using .set and .get), but this has the same issues as before.

[dshepsis]

Why was this closed?

[jaredwray]

Why was this closed?

Sorry as this was a mistake and we are looking into it how to test for this scenario and resolve it. :-)

[jaredwray]

@dshepsis - wanted to update that we are working on moving back to sqlite3 which should help resolve this issue.

[jaredwray]

We will have a newer version of sqlite coming in the next week or so that should be based on sqlite3. FYI.

[jaredwray]

@dshepsis - this is being resolved in this pull request: #290

[jaredwray]

This should be resolved with the following version v3.5.0: https://www.npmjs.com/package/@keyv/sqlite