Scripting Queries

Running queries directly in the MySQL interpreter is nice for testing, but ultimately, you want your web service to run these queries in response to client requests. The mysql package on NPM bridges between JavaScript and MySQL, so you set up a new Node.js project with these commands:

mkdir unforget-service
cd unforget-service
npm init
npm install mysql

The mysql driver needs to know the database, user, and password. You could put these in your script, but that seems dangerous, especially if your service is going to be stored in a remote Git repository. Secrets should never be placed in version control. Instead, you make the file credentials.json in your project directory:

{
  "user": "unforget_user",
  "password": "REDACTED",
  "database": "unforget"
}

To keep the file from getting add to version control, you put this line in a file named .gitignore, also in your project directory:

/credentials.json

In your main script, which is probably named index.js if you chose the default when initializing your project, you add this code to read in and deserialize the credentials:

const fs = require('fs');

const json = fs.readFileSync('credentials.json', 'utf8');
const credentials = JSON.parse(json);

The file is read synchronously because nothing else that can be done until the credentials are loaded. Once you have them, you ask the driver to establish a connection with the database:

const fs = require('fs');
const mysql = require('mysql');

const json = fs.readFileSync('credentials.json', 'utf8');
const credentials = JSON.parse(json);

const connection = mysql.createConnection(credentials);
connection.connect(error => {
  if (error) {
    console.error(error);
    process.exit(1);
  }
});

// TODO: issue queries.

connection.end();

Between the connect and end calls, you throw in a SELECT query to see what memories are in the database. The mysql driver expects callbacks rather than promises. You use an arrow function to keep the code tidy:

const selectQuery = 'SELECT * FROM memory';
connection.query(selectQuery, (error, rows) => {
  if (error) {
    console.error(error);
  } else {
    console.log(rows);
  }
});

Try running your script with node index.js. Do you a see list of memories if there are any in your database?

Eventually you will need to assemble a query using data from the client. You could concatenate a query string together, like this:

query = `SELECT * FROM memory WHERE id = ${param}`;

What would happen if a client somehow managed to send "1; DELETE FROM memory;" for param? There goes your precious memories. Building a query by interpolating or concatenating leaves you vulnerable to SQL injections.

You avoid SQL injections by using prepared statements. In a mysql prepared statement, you mark each place where you want to inject a value with ?. You also pass as a parameter an array of values to inject. The first ? is replaced by element 0, the second by element 1, and so on. The driver will ensure that the values are injected safely.

You use a prepared statement to insert a new memory:

const insertQuery = 'INSERT INTO memory(year, month, day, entry) VALUES (?, ?, ?, ?)';
const parameters = [2019, 3, 19, 'I was born.'];
connection.query(insertQuery, parameters, (error, result) => {
  if (error) {
    console.error(error);
  } else {
    console.log(result);
  }
});

Note that the insert command has four ? instances. These correspond in sequence to the four elements of parameters.

Try adding some more memories to your database. Then try querying them with the SELECT command. The output reveals that the memory objects are of type RowDataPacket, which is defined by the mysql driver. You decide to convert these to simpler objects using the map function:

function rowToMemory(row) {
  return {
    id: row.id,
    year: row.year,
    month: row.month,
    day: row.day,
    entry: row.entry,
  };
}

connection.query('SELECT * FROM memory', (error, rows) => {
  if (error) {
    console.error(error);
  } else {
    const memories = rows.map(rowToMemory);
    console.log(memories);
  }
});

Try writing queries to update or delete existing memories.