FAQ: Learn Node SQLite - Using Placeholders

This community-built FAQ covers the “Using Placeholders” exercise from the lesson “Learn Node SQLite”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Web Development

Learn Node-SQLite

FAQs on the exercise Using Placeholders

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply (reply) below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

it seems it’s much more convenient and easier to use template literals than placeholders. would be nice to update this lesson

4 Likes

Why aren’t the results logged in the same order that the array is?

5 Likes

It would be nice for this lesson to explain its example code as it currently does not

4 Likes

just what i was thinking…the following was more logical and less code than placeholders.

ids.forEach(x => {
db.get(select * from TemperatureData where id = ${x},…

is there a situation where placeholders would be more effective?

1 Like

P.S. the photo is of an upside-down dog

1 Like

after much testing of my own code i conclude that placeholders are an absolute waste of time and coding…if anyone can disagree, state your reasons:

db.get( `select * from TemperatureData where id = ${x};` , (req, res, next) => {...}

is less code and easier to read than:

db.get( 'select * from TemperatureData where id = $x;', {$x : x} , (req, res, next) => {...}

just be careful with a strings to include the extra speech marks…very important For example:

db.get( `select * from TemperatureData where country= "${x}";` , (req, res, next) => {...}
3 Likes

I was wondering the exact same thing, and seeing that you asked this question three months ago and nobody has replied, I will leave it as a mystery for now (I could not find the answer myself anywhere).

2 Likes

if you re-run the exercise the results appear in different orders, so there is no logic behind the order which leaves me to conclude the following. Using db.all or db.get methods within a loop, makes asynchronous calls to the database we are just seeing printed whatever results are back first. The reason for a changing order of results coming back from an unchanged database are mysterious, but ill put it down to a secret of the interweb :wink:

1 Like

The only reason I have found to be placeholders to be of any use is that placeholders can refer to undefined values without causing errors (unless you have NOT NULL constraints ofc). EG:

db.run(“INSERT INTO table (name, age, description) VALUES ($name, $age, $description)”),
{
$name: “Big Gaz”,
$age: 41,
$description: undefined
},

Sqlite3 will treat the undefined description value as NULL when using placeholders. However, if you were to do the same thing with template literals where description is an undefined value, you get an error as sqlite does not know how to interpret the value:

db.run(INSERT INTO table (name, age, description) VALUES ('${name}', ${age}, ${description})), // Will cause an error!

If you wrap ${description} in quotes sqlite3 will assume the value is the text string ‘undefined’, which does not work either.

If anybody has anything to add or correct in any way please do so, these are just observations my experiments!

1 Like

what about using null?

Works fine with both placeholders and template literals, sqlite understands that null means null.
If you wrap the template literal in quotes however sqlite will interpret the value as the string ‘null’, and not a null value.

1 Like

I find the written tasks not very clear, which is frustrating. I spent too much time figuring out why my results were coming out as undefined and later realised my code in the sql query command part, written as 'db.get(“SELECT * FROM TemperatureData WHERE id_no = $idNo”, was because id_no was incorrect (i made this up for my own understanding). After looking at the solution, it was ‘id’ instead of ‘id_no’. When i made the change, it then worked. They should have mentioned to use id as one of the endpoints!..rant over…

In short, placeholder assigns a single value to a parameter. Template literals can be used to maliciously amend the entire query and potentially allow access to all records.

Full explanation by factoradic can be found in this thread:

https://discuss.codecademy.com/t/are-sql-placeholders-safe/381951

That sure is odd, isn’t it?
I tried ordering the results using ORDER BY id, here’s my code:

ids.forEach(function (id) {
  console.log(id)
  db.get(
    'SELECT * FROM TemperatureData WHERE id = $id ORDER BY id',
    {
      $id: id
    },
    (error, rows) => {
      printQueryResults(rows);
    }
  );
});

Still, different results everytime… anybody knows how to fix this into a nice, structured result?

I think it’s because db.get() gets only one row per request to the db and this operations are asynchronous.
ORDER BY id worked, but there was only one row in the result))
For the particular order we have to use WHERE … IN … construction and change .get() to .all()

const { printQueryResults } = require('./utils');
const sqlite = require('sqlite3');

const db = new sqlite.Database('./db.sqlite');

const ids = [1, 25, 45, 100, 360, 382];
// your code below:
db.all(`SELECT * FROM TemperatureData WHERE id IN (${ids.join(",")})`,  
(error, row) => {printQueryResults(row)});