PostgreSQL REST API Node.js CRUD - String query parameter giving error

Question

I am setting up my first REST API to query a Postgres database I set up. I have two CRUD methods that currently work that query for all rows in a table and for rows where ID = something, respectively.

The problem I'm having occurs when trying to query when the request parameter is a String. Here is the error I'm getting:

error: invalid input syntax for type integer: "NaN"

Here is how I've set up my GET route and endpoint URL:

const getShowByTitle = (request, response) => {
  const title = request.params.title
  pool.query('SELECT * FROM show WHERE title = $1', [title], (error, results) => {
    if (error) {
      throw error
    }
    response.status(200).json(results.rows)
  })
}

app.get('/show/:title', getShowByTitle)

Expected result is that sending a GET request using a show title as a parameter (String) returns a JSON response of just that show.

Any help or direction to some useful resources would be greatly appreciated. Thank you.

aarkerio · Accepted Answer · 2020-01-30 18:14:32Z

2

There are some issues here, first in SQL the name of the tables should be in plural "shows", second you are making the select without quotes, you need something like:

"SELECT * FROM show WHERE title = '$1'"

Third, since the user can use uppercase and down cases you need a more robust way to search for text using LIKE, ILIKE and ~, ~* operators.

https://www.2ndquadrant.com/en/blog/text-search-strategies-in-postgresql/

Fourth and more important, you are not filtering the string and you are at risk of suffering an SQL injection because "UPDATE admins SET password='newsom22th88';" is going to be executed in your database.

edited Jan 30, 2020 at 18:14

answered Jan 30, 2020 at 18:09

aarkerio

2,4043 gold badges23 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

aarkerio Over a year ago

There are a lot of good tutorials about how to protect your API: veracode.com/blog/secure-development/… Just keep in mind that all that is sent to your server can be dangerous. Good luck!

aarkerio Over a year ago

Print the value: console.log("this is the value: >>>", title); use the value directly in the psql console in postgres.

aarkerio Over a year ago

Yeah, in the API case you have a verb GET, POST, PUSH, DELETE, etc and a route and can't be more than one verb-route match, so it looks like the route is already taken. Try with "shows/:title" <-- note the "s"

Einar Longva · Accepted Answer · 2020-01-30 19:28:15Z

0

After some debugging my original code wasn't working because I had multiple verb-route matches. I solved the issue by creating a new unique verb-route match. Below is my complete code for querying using a String. Aarkerio's other points still hold; I need to alter my code to avoid SQL injection as well as create a more robust search.

const getShowByTitle = (request, response) => {
  const title = request.params.title
  pool.query('SELECT * FROM show WHERE title = $1', [title], (error, results) => {
    if (error) {
      throw error
    }
    response.status(200).json(results.rows)
  })
}

answered Jan 30, 2020 at 19:28

Einar Longva

576 bronze badges

Collectives™ on Stack Overflow

PostgreSQL REST API Node.js CRUD - String query parameter giving error

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related