How to use special characters on the SQL

[nkint]

Hi! I'd like to use special character inside the sql. As special character I refer to https://github.com/WebReflection/dblite#the-paramsarrayobject

I'd like to execute this query:

INSERT OR REPLACE INTO User (user_id, username, group_id, status, credits, active) 
VALUES ( 58,"foo@gmail.com",(SELECT group_id FROM Groups WHERE groupname="subscriber"),1,0,1);

But I got:

.../node_modules/dblite/build/dblite.node.js:672
.../node_modules/dblite/build/dblite.node.js:672
  return escape(paramsObject[key]);
  return escape(paramsObject[key]);
                                  ^
                      ^
TypeError: Cannot read property 'gmail' of null

And I didn't find anything in a quick documentation look.

[WebReflection]

any particular reason you are not passing strings via API so that these will be surely escaped correctly ?

db.query(
  'INSERT OR REPLACE INTO User (user_id, username, group_id, status, credits, active) ' +
  'VALUES ($id, $email, (SELECT group_id FROM Groups WHERE groupname=$groupname), 1, 0, 1)',
  {
    id: 58,
    email: 'foo@gmail.com',
    groupname: 'subscriber'
  }
);

This ensures values are correctly escaped once passed to the sqlite-cli so I strongly recommend to not write manually/hard=coded strings in your queries.

Please let me know if this solves, 'cause right now this seems a bogus to me, thanks.

[nkint]

I have some query that I need to log down. Is there a way to get the complete query before executing it?

[WebReflection]

no because there are escaping rules that might even make your query hard to read.

You probably want to log data you are passing per query though, it's even easier to read.

function query(sql, data) {
  console.log(sql);
  console.log(JSON.stringify(data));
  return db.query(sql, data);
}

query(
  'INSERT OR REPLACE INTO User (user_id, username, group_id, status, credits, active) ' +
  'VALUES ($id, $email, (SELECT group_id FROM Groups WHERE groupname=$groupname), 1, 0, 1)',
  {
    id: 58,
    email: 'foo@gmail.com',
    groupname: 'subscriber'
  }
)

Otherwise you might try to hook into the program.stdin.write logic to see what passes through channels

[nkint]

It's a pity that one can't log the complete query but after the correction advised seems to work great.

Please add to the documentation that manual query creation should be avoided due to special character: it is not clear at a first glance.

[WebReflection]

done https://github.com/WebReflection/dblite#warning

I might include a db.debug boolean flag able to log operations and queries but that would take some times and it's still not a bug but a feature request ;-)

Closing this for now, apologies for any inconvenience.

[nkint]

ok cool! grazie