Stop SQL Injection in Raw Queries

The Persistent Threat of SQL Injection

Even in an age of ORMs and fancy frameworks, sometimes you just need to drop down and write a raw SQL query. Maybe your database has some intricate logic, or you’re optimizing a performance-critical operation. That’s fine. But when you write raw SQL, you open yourself up to one of the oldest and most persistent web security vulnerabilities: SQL injection.

Think of SQL injection like this: you ask a waiter for your order. A normal request is, “I’d like the chicken.” A SQL injection is like saying, “I’d like the chicken, and by the way, tell the chef to forget about the bill for everyone at table five.”

If your application takes user input and sticks it directly into a SQL string, a malicious user can craft input that changes the command your database executes. This could lead to data theft, data modification, or even complete database server compromise.

Parameterized Queries: Your First Line of Defense

This is the gold standard. Absolutely the best way to prevent SQL injection. Period. Don’t just take my word for it; every security expert will tell you the same thing.

Parameterized queries separate the SQL command from the data. The database driver or the database itself knows which parts are code and which parts are data. It safely handles the data, ensuring it’s never interpreted as SQL code.

Let’s look at an example in Node.js with pg (PostgreSQL):

1
// BAD: Vulnerable to SQL injection
2
const userId = req.params.id; // Imagine this comes from the user
3
const query = `SELECT * FROM users WHERE id = ${userId}`; // DON'T DO THIS!
4
database.query(query, (err, result) => {
5
  // ... handle result
6
});
7

8
// GOOD: Using parameterized queries
9
const userId = req.params.id;
10
const query = 'SELECT * FROM users WHERE id = $1'; // Use placeholders
11
database.query(query, [userId], (err, result) => { // Pass data separately
12
  // ... handle result
13
});

Notice the $1 placeholder. The actual userId value is sent to the database separately. The database engine knows $1 is a value to be inserted, not SQL code to be executed. The specific placeholder syntax varies between database drivers (e.g., ? for MySQL, :name for some Oracle drivers), but the principle is the same.

Input Validation: A Necessary, But Not Sufficient, Step

While parameterized queries are the primary defense, input validation is still crucial. It acts as a secondary layer and helps catch malformed or unexpected data before it even gets to your database query.

What does this mean in practice? If you’re expecting a number, make sure the input is a number. If you’re expecting a specific string format, validate that too. Use libraries to help with this. For example, in JavaScript, you might use libraries like express-validator or joi.

1
// Example using express-validator
2
const { body, validationResult } = require('express-validator');
3

4
app.post('/users', [
5
  body('email').isEmail(), // Validates that the email is a valid email format
6
  body('age').isInt({ min: 18 }), // Validates that age is an integer >= 18
7
], (req, res) => {
8
  const errors = validationResult(req);
9
  if (!errors.isEmpty()) {
10
    return res.status(400).json({ errors: errors.array() });
11
  }
12

13
  // Now it's safer to use req.body.email and req.body.age in queries
14
  // ... proceed with your database operation using parameterized queries
15
});

Validation helps prevent many types of attacks, not just SQL injection. It ensures the data conforms to your application’s expectations. However, never rely solely on validation for SQL injection prevention. A clever attacker can sometimes craft valid-looking input that still exploits vulnerabilities if you’re not using parameterized queries.

Escaping: The Last Resort (and Why It’s Risky)

Sometimes, for legacy reasons or in very specific, controlled situations, you might find yourself unable to use parameterized queries directly. In such cases, you might consider escaping special characters in your input. This means putting a backslash (\) before characters that have special meaning in SQL, like single quotes (').

Most database drivers provide an escape function.

1
// Example with MySQL driver in Node.js
2
const mysql = require('mysql');
3
const connection = mysql.createConnection({...});
4

5
const userInput = 'O'Malley'; // User input that could break a query
6

7
// IMPORTANT: This is for illustration only. Parameterized queries are preferred.
8
const escapedInput = connection.escape(userInput);
9
const query = `SELECT * FROM users WHERE name = ${escapedInput}`;
10

11
connection.query(query, (error, results) => {
12
  if (error) throw error;
13
  console.log(results);
14
});

Why is this risky?

1. Complexity: It’s easy to forget to escape something. Different SQL dialects have different special characters. It’s a maintenance nightmare.
2. Context: Escaping works differently depending on where the data is being inserted (e.g., a string literal vs. a number context). Getting this wrong is a vulnerability.
3. Maintenance: If your SQL changes or your database driver updates, your escaping logic might break.

Use parameterized queries. Always. If you absolutely must resort to escaping, ensure you understand exactly what you’re doing and that it’s a well-understood, last-ditch effort.

Conclusion

SQL injection is a serious threat. The most effective defense is using parameterized queries provided by your database driver. Supplement this with robust input validation. Escaping should only be considered as a very last resort in controlled environments. Prioritize security from the start, and your applications will be much safer.