Working with Parameterized Queries

1. Introduction

1.1 Goal of the tutorial

This tutorial aims to teach you how to work with parameterized queries in your web development journey. Parameterized queries are an essential tool for preventing SQL injection, a common web security vulnerability.

1.2 Learning outcomes

By the end of this tutorial, you will learn:
- What parameterized queries are.
- How to use parameterized queries in your web applications.
- Best practices when dealing with parameterized queries.

1.3 Prerequisites

This tutorial assumes that you have basic knowledge of:
- SQL
- A programming language that can interact with a database (we will use JavaScript for our examples).

2. Step-by-Step Guide

Parameterized queries are SQL queries where placeholders are used for values and these values are supplied at execution time. This reduces the risk of SQL injection as the parameters are sent separately from the SQL command.

2.1 Concept explanation

Here is a simple non-parameterized SQL query: SELECT * FROM users WHERE username = 'John'

In a parameterized query, it would look like this: SELECT * FROM users WHERE username = ?

The ? is a placeholder for a parameter that will be defined when the query is run.

2.2 Best practices

• Always use parameterized queries or prepared statements for SQL commands that include user-supplied input.
• Never use string concatenation or interpolation to create SQL commands that include user input.

3. Code Examples

We will use JavaScript and the pg-promise library for PostgreSQL for our examples.

3.1 Basic parameterized query

The following code snippet shows a basic parameterized query that selects a user based on a given username.

const db = require('pg-promise')();

const username = 'John';

db.any('SELECT * FROM users WHERE username = $1', [username])
  .then(data => {
    console.log(data); // prints the user data
  })
  .catch(error => {
    console.log(error); // prints the error
  });

In this code snippet, $1 is a placeholder for the first parameter in the parameters array ([username]). The db.any function executes the SQL command and returns a promise that resolves with the data returned by the query.

3.2 Parameterized query with multiple parameters

The following code snippet shows a parameterized query that selects a user based on a given username and password.

const db = require('pg-promise')();

const username = 'John';
const password = 'password123';

db.any('SELECT * FROM users WHERE username = $1 AND password = $2', [username, password])
  .then(data => {
    console.log(data); // prints the user data
  })
  .catch(error => {
    console.log(error); // prints the error
  });

In this case, $1 and $2 are placeholders for the parameters in the parameters array ([username, password]).

4. Summary

In this tutorial, we learned what parameterized queries are and why they're important for preventing SQL injection. We looked at how to use parameterized queries in JavaScript using the pg-promise library.

5. Practice Exercises

1. Write a parameterized query that inserts a new user into a users table with username, password, and email fields.
2. Write a parameterized query that updates the email of a user in the users table based on their username.

You can use the pg-promise library and the examples above as a guide. Remember to check your code against the best practices we discussed. Happy coding!