SQL Injection

SQL injection involves injecting malicious SQL code into a web application to manipulate databases or gain unauthorized access.

How It Works

Consider the following SQL query used to validate a user during login:

queryString = `SELECT * FROM users
  WHERE user_name='${userName}' AND password='${hash(password)}'`

An attacker could manipulate the input by setting userName to admin' --. This alters the queryString to:

SELECT * FROM users WHERE user_name='admin' -- AND password='***'

Here, the comment -- ignores the password check, allowing the attacker to log in as an admin.

Prevention

To prevent SQL injection, always use parameterized queries or an Object-Relational Mapping (ORM) tool. This separates SQL statements from user data, ensuring that input is not treated as executable SQL code.

Using parameterized queries, the query would be written as:

queryString = `SELECT * FROM users
  WHERE user_name=$1 AND password=$2`
client.query(queryString, userName, hash(password))

Libraries like postgres provide a more convenient syntax using tagged template, making it easier to write:

sql`SELECT * FROM users
  WHERE user_name=${userName} AND password=${hash(password)}`