SQL Injection Prevention: How to Properly Escape SQL Strings

SQL injection is one of the most critical security vulnerabilities in web applications. It occurs when an attacker injects malicious SQL code into a query through unsanitised user input. Properly escaping SQL strings is a key defence, but the most effective protection is using prepared statements with parameterised queries.

How SQL Injection Works

Consider a login query built by string concatenation:

$sql = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "' AND password = '" . $_POST['password'] . "'";

If an attacker submits a username of admin' --, the query becomes:

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

The -- comments out the password check, allowing the attacker to log in as admin without knowing the password. More dangerous injections can delete tables, read arbitrary data, or execute remote commands.

The Best Defence: Prepared Statements

Prepared statements separate SQL logic from data. The query structure is sent to the database first, and the data is sent separately. The database treats the data as literal values, never as SQL code.

PHP with PDO

$pdo = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
$stmt->execute([
    'username' => $_POST['username'],
    'password' => $_POST['password'],
]);
$user = $stmt->fetch();

Python with sqlite3 / psycopg2

import sqlite3

conn = sqlite3.connect('database.db')
cursor = conn.cursor()

# Use ? placeholders
cursor.execute(
    'SELECT * FROM users WHERE username = ? AND password = ?',
    (username, password)
)

Node.js with mysql2

const mysql = require('mysql2/promise');
const conn = await mysql.createConnection({ /* config */ });

// Use ? placeholders
const [rows] = await conn.execute(
    'SELECT * FROM users WHERE username = ? AND password = ?',
    [username, password]
);

Java with JDBC

Connection conn = DriverManager.getConnection(url, user, pass);
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

When You Must Escape Manually

There are rare cases where prepared statements are not available: dynamic table names, dynamic column names, or building SQL for a legacy system. In these cases, you must escape manually.

Manual Escaping by Language

// PHP with mysqli
$escaped = mysqli_real_escape_string($conn, $userInput);

# Python — manual escaping (avoid if possible)
# Use the database adapter's escape function
from psycopg2 import sql
safe_value = sql.Literal(user_input)

// Node.js — mysql2 provides escaping
const mysql = require('mysql2');
const escaped = mysql.escape(userInput);

What the SQL Escape Tool Does

The SQL Escape Tool on Help2Code escapes strings for use in SQL queries. It is useful when you are debugging, generating SQL scripts, or working in an environment where prepared statements are not available. The tool supports MySQL and PostgreSQL escaping modes.

Common SQL Injection Vectors

Login bypass — using ' OR '1'='1 to bypass authentication.

UNION injection — using UNION SELECT to extract data from other tables.

Blind injection — using conditional queries like IF(SUBSTRING(password,1,1)='a', SLEEP(5), 0) to extract data character by character based on response timing.

Second-order injection — injecting into stored data that is later used unsafely in another query.

Defence Layers

Defence in depth is essential:

1. Prepared statements — always the primary defence
2. Input validation — reject unexpected input formats (e.g., email validation)
3. Least privilege — database user should have minimum required permissions
4. Output encoding — prevent reflected injection in error messages
5. WAF — Web Application Firewall as an additional layer

Conclusion

SQL injection prevention starts and ends with prepared statements. Use them consistently for all database queries. Manual escaping is a fallback for special cases, not a primary defence. The SQL Escape Tool helps with debugging and script generation, but your production code should rely on parameterised queries.