SQL Injection¶
What does this mean ?¶
SQL injection occurs when untrusted data becomes SQL syntax. Concatenating a name, query parameter, cookie or imported record into a query can change its meaning. Stored data is not automatically trusted: a value saved safely can become dangerous if concatenated into a later query.
What can happen ?¶
An affected query can expose or modify records beyond the caller's permissions. The database account and enabled database features determine the wider impact. Injection does not require a login form, and a parameterized query still needs authorization and tenant scoping.
Recommendation¶
Use the database driver's parameter API for values, keeping query text constant. Do not replace this with a blacklist of quotes or SQL keywords. Validate length and type for business rules, not as a substitute for binding.
Parameters usually cannot stand for a table name, column name or ASC/DESC. Map those choices to a small set of server-owned constants. Keep database permissions narrow. Stored procedures and ORMs need the same review when they construct raw SQL.
Sample Code¶
These focused lookup examples assume an existing connection and a bounded string name (or nameInput). Each unsafe/safe pair performs the same lookup. Driver placeholder syntax varies; copy the pair for your driver, not just its punctuation. Error handling, result rendering and record authorization belong to the surrounding application.
Python's standard sqlite3 module:
# Unsafe: name changes the query text.
rows = connection.execute(
"SELECT id FROM customers WHERE name = '" + name + "'"
).fetchall()
# Safer: the driver binds name as one value.
rows = connection.execute(
"SELECT id FROM customers WHERE name = ?", (name,)
).fetchall()
Microsoft.Data.SqlClient; connection is an open SqlConnection:
// Unsafe
using var unsafeCommand = new SqlCommand(
"SELECT id FROM customers WHERE name = '" + name + "'", connection);
// Safer: bound type and size match the application's schema.
using var command = new SqlCommand(
"SELECT id FROM customers WHERE name = @name", connection);
command.Parameters.Add("@name", System.Data.SqlDbType.NVarChar, 100).Value = name;
using var reader = command.ExecuteReader();
JDBC with an open java.sql.Connection:
// Unsafe
String sql = "SELECT id FROM customers WHERE name = '" + name + "'";
// Safer
try (var statement = connection.prepareStatement(
"SELECT id FROM customers WHERE name = ?")) {
statement.setString(1, name);
try (var rows = statement.executeQuery()) {
while (rows.next()) {
long id = rows.getLong("id");
// Use id only after applying the application's access policy.
}
}
}
PDO with exceptions enabled; use native prepared statements where the driver supports them:
// Unsafe
$rows = $pdo->query("SELECT id FROM customers WHERE name = '" . $name . "'");
// Safer
$statement = $pdo->prepare('SELECT id FROM customers WHERE name = :name');
$statement->execute(['name' => $name]);
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);
An established mysql2/promise connection; execute uses prepared statements. The same query pattern applies in TypeScript after narrowing request input to a string.
// Unsafe
const [unsafeRows] = await connection.query(
"SELECT id FROM customers WHERE name = '" + nameInput + "'"
);
// Safer
const [rows] = await connection.execute(
'SELECT id FROM customers WHERE name = ?', [nameInput]
);
database/sql with a PostgreSQL driver and an existing db/ctx:
// Unsafe
query := fmt.Sprintf("SELECT id FROM customers WHERE name = '%s'", name)
_ = query
// Safer; PostgreSQL uses numbered placeholders.
rows, err := db.QueryContext(ctx,
"SELECT id FROM customers WHERE name = $1", name)
if err != nil {
return err
}
defer rows.Close()
for rows.Next() {
var id int64
if err := rows.Scan(&id); err != nil {
return err
}
}
return rows.Err()
Rails Active Record:
# Unsafe
customers = Customer.where("name = '#{name}'")
# Safer: Active Record handles the value separately.
customers = Customer.where(name: name)
Regression checks¶
Use an isolated fixture database. Store O'Reilly and verify that it returns exactly its own record without a syntax error. Compare results for empty, Unicode and maximum-length values. Assert that unexpected sort/table choices are rejected before the query. Test that another tenant's valid record remains inaccessible: injection prevention does not establish authorization.