How to extract the query string from a SQLite object in nodejs?

Question

How to extract the query string from a SQLite object in nodejs?

For example if we have this:

import sqlite3 from 'sqlite3';
import { open } from 'sqlite';

const db = await open({
  filename: 'test.db',
  driver: sqlite3.Database
});

var ret = await db.get('SELECT * FROM `test` WHERE id=? AND foo=?', [100, 'bar']);

How can we achieve this:

SELECT * FROM `test` WHERE id="100" AND foo="bar"

I expect something like this to happen:

db.getQueryString();

ret has the query result, so what exactly is your problem? are you searching for this https://stackoverflow.com/questions/37776205/retrieve-data-from-sqlite-query-node-js — nbk, May 01 '22 at 16:08
@nbk as I said, I need to the query built by sqlite as string. — Nabi K.A.Z., May 01 '22 at 17:11
that is simplestring replacement and the query does that automaticall, so i don't understand what do want with a string or does it come somehow from the datavase. this is very unclear — nbk, May 01 '22 at 17:22
The C API has [a way to do it](https://sqlite.org/c3ref/expanded_sql.html) but I suspect the node interface doesn't export a fraction of the functionality of the C version. — Shawn, May 02 '22 at 05:21
@nbk Of course, placement is not simple, otherwise we would have simply used `foo="bar"` instead of `"foo=?", ["bar"]`. sqlite has an internal control mechanism to escape some characters to make the query secure, I need the same built-in query to insert into the log files when it fails. — Nabi K.A.Z., May 02 '22 at 09:07

nbk · Answer 1 · 2022-05-02T17:54:34.997

-1

es6 string replacement is supporet by nodesjs.

var my_id = 100
var my_var = 'bar';
var s = `SELECT * FROM `test` WHERE id="${my_id}" AND foo="${my_var}" `;
console.log(s);

But you should prepare your statements to make the secure like https://stackoverflow.com/a/49328621/5193536

Depending if ydo want the strings in the log to be used, for testing, you must add single quotes to the sting above, which the query makes automatically

edited May 02 '22 at 17:54

answered May 02 '22 at 09:50

nbk

45,398
8
30
47

This method does not guarantee that it will not have problems with special characters and does not cover security. – Nabi K.A.Z. May 02 '22 at 17:01
that is why you use the prepared statement and not a sting replacement, but you stated you want to write in a log file, so it is the only way to do it – nbk May 02 '22 at 17:54

How to extract the query string from a SQLite object in nodejs?

1 Answers1