SQLi demo

data.sql

INSERT INTO user(email, password, name) VALUES
  ('[email protected]', 'dEqeTA', 'Nighel Wouters'),
  ('[email protected]', '6eCHfi', 'Lison Robin'),
  ('[email protected]', 'lPahIc', 'Anton Seppala'),
  ('[email protected]', 'dg9Z8C', 'Célestine Bertrand'),
  ('[email protected]', 'Abcd1234', 'Magnus Hoyer');

INSERT INTO account(user_id, iban) VALUES
  (1, 'NL09INGB8479662646'),
  (2, 'FR7612739000409492429559L94'),
  (3, 'FI0654463539846795'),
  (4, 'BE89798962149285'),
  (5, 'DE36500105172611642679');

db-connection.js

// get the client
const mysql = require('mysql2');
 
// create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'sqlidemo'
});

module.exports = connection;

schema.sql

CREATE TABLE user(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100),
  password VARCHAR(60),
  name VARCHAR(100)
);

CREATE TABLE account(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  iban VARCHAR(34)
);

ALTER TABLE account ADD CONSTRAINT fk_account_user_id FOREIGN KEY(user_id) REFERENCES user(id);

server.js

const express = require('express');
const connection = require('./db-connection');

const app = express();

app.get('/users/:userId/accounts', (req, res) => {
  const { userId } = req.params;
  const sql = `SELECT * FROM account WHERE user_id = ${userId}`;
  connection.query(sql, (err, accounts) => {
    if (err) {
      return res.status(500).json({ error: err.message });
    }
    return res.json(accounts);
  });
});

// Default port: 5000
// Change it by running e.g.: PORT=8080 npm start
const port = process.env.PORT || 5000;
app.listen(port, (err) => {
  if (err) {
    console.error(`ERROR on server startup: ${err.message}`);
  } else {
    console.log(`Server listening on port ${port}`);
  }
});