MySQL Where

Let’s dive into how you can leverage Node.js with MySQL, specifically focusing on executing SELECT statements with various filters, using wildcard characters for pattern matching, and the proper way to escape query values to prevent SQL injection and other security risks.

1. Select With a Filter

When querying your MySQL database, it’s common to retrieve a subset of records that match a certain condition. This is achieved using the WHERE clause in a SQL statement. In Node.js, using the mysql module, you can easily filter records based on specific criteria.

Example:

const mysql = require('mysql');

// Create connection
const con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

// Connect to MySQL
con.connect(function(err) {
  if (err) throw err;

  // Select records where the lastname is 'Doe'
  var sql = "SELECT * FROM customers WHERE lastname = 'Doe'";

  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

In this example, we’re selecting all records from the customers table where the lastname column matches ‘Doe’.

2. Wildcard Characters

Wildcard characters are used in SQL to select data that matches a pattern. The most common wildcard characters are % (represents zero, one, or multiple characters) and _ (represents a single character).

Example:

// Select records where the lastname starts with 'Do'
var sql = "SELECT * FROM customers WHERE lastname LIKE 'Do%'";

This query selects all records from the customers table where the lastname starts with ‘Do’. You can also use _ to find names with a specific character at a certain position.

3. Escaping Query Values

To prevent SQL injection and ensure your application’s security, it’s crucial to escape query values. The mysql module in Node.js provides an easy way to escape query values using the mysql.escape() method, or by using ? as a placeholder in your SQL query, which the library automatically escapes.

Example with mysql.escape():

var post = { id: 1, title: 'Hello MySQL' };
var sql = 'SELECT * FROM posts WHERE id = ' + mysql.escape(post.id);

Example with placeholder ?:

var postId = 1;
var sql = 'SELECT * FROM posts WHERE id = ?';
con.query(sql, [postId], function (err, result) {
  if (err) throw err;
  console.log(result);
});

In these examples, mysql.escape() and the placeholder ? are used to safely insert the id into the SQL query, preventing any malicious SQL injection.

By understanding and utilizing these techniques, selecting with a filter, leveraging wildcard characters, and properly escaping query values, you can efficiently and securely interact with your MySQL database from your Node.js applications. Remember, always validate and sanitize user inputs to maintain the security of your applications.