Security Risks

Databases often contain sensitive data and allow for dangerous operations. Nette Database provides a number of security features. However, it is crucial to understand the difference between safe and unsafe APIs.

SQL Injection

SQL injection is the most serious security risk when working with databases. It occurs when unchecked user input becomes part of an SQL query. An attacker can inject their own SQL commands, gaining or modifying data in the database.

// ❌ UNSAFE CODE - vulnerable to SQL injection
$database->query("SELECT * FROM users WHERE name = '$_GET[name]'");

// The attacker can input something like: ' OR '1'='1
// The resulting query will be:
// SELECT * FROM users WHERE name = '' OR '1'='1'
// This returns all users!

The same applies to Database Explorer:

// ❌ UNSAFE CODE
$table->where('name = ' . $_GET['name']);
$table->where("name = '$_GET[name]'");

Safe Parameterized Queries

The safe way to insert values into SQL queries is through parameterized queries. Nette Database provides several ways to use them.

Placeholder Question Marks

The simplest method is to use placeholder question marks:

// ✅ Safe parameterized queries
$database->query('SELECT * FROM users WHERE name = ?', $_GET['name']);

// ✅ Safe condition in Explorer
$table->where('name = ?', $_GET['name']);

The same applies to all other methods in Database Explorer that allow inserting expressions with placeholder question marks and parameters.

Values must be of scalar type (string, int, float, bool) or null. If, for example, $_GET['name'] is an array, Nette Database will include all of its elements in the SQL query, which can be undesirable.

Value Arrays

For INSERT, UPDATE, or WHERE clauses, we can use value arrays:

// ✅ Safe INSERT
$database->query('INSERT INTO users', [
	'name' => $_GET['name'],
	'email' => $_GET['email'],
]);

// ✅ Safe UPDATE
$database->query('UPDATE users SET', [
	'name' => $_GET['name'],
	'email' => $_GET['email'],
], 'WHERE id = ?', $_GET['id']);

Nette Database automatically escapes all values passed through parameterized queries. However, we must ensure the correct data type of parameters.

Array Keys Are Not a Safe API

While values in arrays are safe, the same cannot be said for keys:

// ❌ UNSAFE CODE - keys can contain SQL injection
$database->query('INSERT INTO users', $_GET);
$database->query('SELECT * FROM users WHERE', $_GET);
$table->where($_GET);

For INSERT and UPDATE commands, this is a critical security flaw – an attacker could insert or modify any column in the database. For example, they could set is_admin = 1 or insert arbitrary data into sensitive columns.

In WHERE conditions, this is even more dangerous because it allows SQL enumeration – a technique to gradually retrieve information about the database. An attacker could attempt to explore employee salaries by injecting into $_GET like this:

$_GET = ['salary >', 100000];   // starts determining salary ranges

The main problem, however, is that WHERE conditions support SQL expressions in keys:

// Legitimate use of operators in keys
$table->where([
    'age > ?' => 18,
    'ROUND(score, ?) > ?' => [2, 75.5],
]);

// ❌ UNSAFE: attacker can inject their own SQL
$_GET = ['1) UNION SELECT name, salary FROM users WHERE (is_admin = ?' => 1];
$table->where($_GET); // allows the attacker to obtain admin salaries

This is once again SQL injection.

Whitelisting Columns

If you want to allow users to choose columns, always use a whitelist:

// ✅ Safe processing - only allowed columns
$allowedColumns = ['name', 'email', 'active'];
$values = array_intersect_key($_GET, array_flip($allowedColumns));

$database->query('INSERT INTO users', $values);

Dynamic Identifiers

For dynamic table and column names, use the ?name placeholder:

// ✅ Safe use of trusted identifiers
$table = 'users';
$column = 'name';
$database->query('SELECT ?name FROM ?name', $column, $table);

// ❌ UNSAFE - never use user input
$database->query('SELECT ?name FROM users', $_GET['column']);

The ?name symbol should only be used for trusted values defined in the application code. For user-provided values, use a whitelist again.