How to Prevent SQL Injection in API: Best Practices and Fixes

To prevent SQL injection in your API, always use parameterized queries or prepared statements instead of directly inserting user input into SQL commands. Additionally, validate and sanitize all inputs to avoid malicious data from altering your database queries.

Why This Happens

SQL injection happens when user input is directly added to a SQL query string without proper checks. Attackers can insert harmful SQL code that changes the query's meaning, allowing them to access or damage your database.

javascript

const express = require('express');
const app = express();
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

app.get('/user', (req, res) => {
  const userId = req.query.id;
  const query = `SELECT * FROM users WHERE id = ${userId}`; // Unsafe
  db.all(query, [], (err, rows) => {
    if (err) {
      res.status(500).send('Database error');
      return;
    }
    res.json(rows);
  });
});

Output

If user sends id=1; DROP TABLE users; --, the query becomes:
SELECT * FROM users WHERE id = 1; DROP TABLE users; --
This deletes the users table, causing data loss.

The Fix

Use parameterized queries or prepared statements to separate SQL code from user input. This way, the database treats input only as data, not code, preventing injection attacks.

javascript

const express = require('express');
const app = express();
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

app.get('/user', (req, res) => {
  const userId = req.query.id;
  const query = 'SELECT * FROM users WHERE id = ?';
  db.all(query, [userId], (err, rows) => {
    if (err) {
      res.status(500).send('Database error');
      return;
    }
    res.json(rows);
  });
});

Output

When user sends id=1; DROP TABLE users; --, the database treats it as a string, not code, so no harm occurs. The query safely returns user data or empty results.

Prevention

To avoid SQL injection in the future, always:

• Use parameterized queries or prepared statements for all database access.
• Validate and sanitize user inputs to allow only expected formats (e.g., numbers for IDs).
• Use ORM libraries that handle SQL safely.
• Limit database user permissions to only what is necessary.
• Regularly update your database and libraries to patch security issues.

Related Errors

Other common security issues related to SQL injection include:

• Cross-Site Scripting (XSS): Injecting malicious scripts into web pages.
• Broken Authentication: Weak login systems that allow unauthorized access.
• Improper Error Handling: Revealing sensitive database errors to users.

Fixes involve input validation, secure coding, and proper error management.

Key Takeaways

Always use parameterized queries or prepared statements to separate code from data.

Never directly insert user input into SQL query strings.

Validate and sanitize all user inputs before using them.

Use ORM tools and limit database permissions for extra safety.

Keep your software and dependencies up to date to avoid known vulnerabilities.