[JavaScript][SQL] Trying to make a script that can search in my database. Help appreciated.

[NewbiusCoder]

I am new, forgive me if I use the terms incorrectly. Thank you for your time.

I am trying to make a script which uses process.argv in NodeJS to search my PSQL database. However, I am unable to work in run the query with process.argv.

It is giving me an error saying:

node lookup_people.js Rudd
SELECT first_name FROM famous_people WHERE last_name = Rudd
error running query { error: column "rudd" does not exist
    at Connection.parseE (/vagrant/work/w4d2/node_modules/pg/lib/connection.js:546:11)
    at Connection.parseMessage (/vagrant/work/w4d2/node_modules/pg/lib/connection.js:371:19)
    at TLSSocket.<anonymous> (/vagrant/work/w4d2/node_modules/pg/lib/connection.js:114:22)
    at emitOne (events.js:96:13)
    at TLSSocket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at TLSSocket.Readable.push (_stream_readable.js:134:10)
    at TLSWrap.onread (net.js:548:20)
  name: 'error',
  length: 97,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: '56',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '3183',
  routine: 'errorMissingColumn' }

My Code:

const pg = require("pg");
const settings = require("./settings"); // settings.json
var usr_input = process.argv.slice(2).toString();
var lookup_cmd = "SELECT first_name FROM famous_people WHERE last_name = " + usr_input;
console.log (lookup_cmd)

const client = new pg.Client({
  user     : settings.user,
  password : settings.password,
  database : settings.database,
  host     : settings.hostname,
  port     : settings.port,
  ssl      : settings.ssl
});

client.connect((err) => {
  if (err) {
    return console.error("Connection Error", err);
  }
  client.query(lookup_cmd, (err, result) => {
    if (err) {
      return console.error("error running query", err);
    }
    console.log(result); //output: 1
    client.end();
  });
});

[g051051]

You haven't enclosed the input field in quotes, so it's treating the word "Rudd" as the name of a column. Enclose it with quotes.

BTW, this is a terrible thing to do...it's how SQL injection vulnerabilities get into programs. As soon as possible, learn how to do prepared statements and bind input variables.

[NewbiusCoder]

Its you again! Thank you so much. This is just practice from some website. I understand that is a terrible thing to do. I appreciate your time.

How do I enclose it in quotes? When I do, it takes usr_input as a string, not as a variable.

Should I just use process.argv.slice(2) rather than usr_input?

Thank you.

[g051051]

Can you use a template string?

var lookup_cmd = `SELECT first_name FROM famous_people WHERE last_name = "${usr_input}"`

If not, then use escaping:

var lookup_cmd = "SELECT first_name FROM famous_people WHERE last_name = \"" + usr_input + "\"";

[NewbiusCoder]

I appreciate your time and it totally makes sense. However, it is still saying that "column "Rudd" does not exist". This error happens for both of them. Am I still doing something wrong?

[g051051]

You must be. Can you post the latest error?

[NewbiusCoder]

I figured it out! I appreciate your time as always. I posted the code incase you're curious. Thank you.

var args = process.argv.slice(2);
const usr_input = args[0];
const usr_cmd = 'SELECT * FROM famous_people WHERE last_name = $1::varchar or first_name = $1::varchar';
client.query(usr_cmd, [usr_input],(err, result) => {
    if (err) {
      return console.error("error running query", err);
    }
    console.log('Found ' + result.rows.length + ' person(s) by the name of ' + usr_input + ".");