JAVASCRIPT
Secure SQL Queries with Parameterized Statements
Learn how to prevent SQL injection attacks in Node.js applications by using parameterized queries with the 'pg' library for PostgreSQL, ensuring data integrity and security.
const { Pool } = require('pg');
const pool = new Pool({
user: 'your_user',
host: 'localhost',
database: 'your_database',
password: 'your_password',
port: 5432,
});
async function getUserById(userId) {
try {
// GOOD: Using parameterized queries to prevent SQL injection
const queryText = 'SELECT * FROM users WHERE id = $1';
const result = await pool.query(queryText, [userId]);
return result.rows[0];
} catch (err) {
console.error('Error executing query', err.stack);
throw err;
}
}
// Example usage
// getUserById(1).then(user => console.log(user)).catch(console.error);
// BAD: Directly embedding user input into the query string
// This is vulnerable to SQL injection if userId is untrusted input.
async function getVulnerableUserById(userId) {
try {
const queryText = `SELECT * FROM users WHERE id = ${userId}`;
const result = await pool.query(queryText);
return result.rows[0];
} catch (err) {
console.error('Error executing vulnerable query', err.stack);
throw err;
}
}
How it works: This snippet demonstrates how to prevent SQL injection attacks in Node.js applications using the 'pg' library for PostgreSQL. The key is to use parameterized queries (prepared statements), where the SQL query string and the user-provided values are sent separately to the database. The database then interprets the query structure first, and only after that inserts the values, preventing malicious input from altering the query logic. The 'pg' library achieves this by using placeholders like '$1' and passing an array of values, ensuring user input is treated purely as data, not executable code.