Security Risks

What is SQL Injection?

SQL injection is the most serious security risk when working with databases. It occurs when unsanitized user input becomes part of an SQL query. An attacker can insert their own SQL commands and thereby:

• Gain unauthorized access to data
• Modify or delete data in the database
• Bypass authentication

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

// An attacker might enter a value like: ' OR '1'='1
// The resulting query would be: SELECT * FROM users WHERE name = '' OR '1'='1'
// Which returns all users

The same applies to Database Explorer:

// ❌ DANGEROUS CODE - vulnerable to SQL injection
$table->where('name = ' . $_GET['name']);
$table->where("name = '$_GET[name]'");

Parameterized Queries

The fundamental defense against SQL injection is parameterized queries. Nette Database offers several ways to use them.

The simplest way is to use question mark placeholders:

// ✅ Secure parameterized query
$database->query('SELECT * FROM users WHERE name = ?', $name);

// ✅ Secure condition in Explorer
$table->where('name = ?', $name);

This applies to all other methods in Database Explorer that allow inserting expressions with question mark placeholders and parameters.

For INSERT, UPDATE commands, or the WHERE clause, we can pass values in an array:

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

// ✅ Secure INSERT in Explorer
$table->insert([
	'name' => $name,
	'email' => $email,
]);

Parameter Value Validation

Parameterized queries are the cornerstone of secure database work. However, the values we insert into them must undergo several levels of checks:

Type Checking

The most important thing is to ensure the correct data type of parameters – this is a necessary condition for the safe use of Nette Database. The database assumes that all input data has the correct data type corresponding to the given column.

For example, if $name in the previous examples were unexpectedly an array instead of a string, Nette Database would try to insert all its elements into the SQL query, leading to an error. Therefore, never use unvalidated data from $_GET, $_POST, or $_COOKIE directly in database queries.

Format Validation

At the second level, we check the format of the data – for example, whether strings are in UTF-8 encoding and their length corresponds to the column definition, or whether numerical values are within the allowed range for the given column data type.

For this level of validation, we can partially rely on the database itself – many databases will reject invalid data. However, behavior can vary; some might silently truncate long strings or clip numbers outside the range.

Domain-Specific Validation

The third level involves logical checks specific to your application. For example, verifying that values from select boxes match the offered options, that numbers are within the expected range (e.g., age 0–150 years), or that mutual dependencies between values make sense.

Recommended Validation Methods

• Use Nette Forms, which automatically ensure proper validation of all inputs.
• Use Presenters and specify data types for parameters in action*() and render*() methods.
• Or implement your own validation layer using standard PHP tools like filter_var().

Safe Work with Columns

In the previous section, we showed how to properly validate parameter values. However, when using arrays in SQL queries, we must pay equal attention to their keys.

// ❌ DANGEROUS CODE - keys in the array are not sanitized
$database->query('INSERT INTO users', $_POST);

For INSERT and UPDATE commands, this is a critical security flaw – an attacker can insert or modify any column in the database. They could, for example, set is_admin = 1 or insert arbitrary data into sensitive columns (the so-called Mass Assignment Vulnerability).

In WHERE conditions, it is even more dangerous because they can contain operators:

// ❌ DANGEROUS CODE - keys in the array are not sanitized
$_POST['salary >'] = 100000;
$database->query('SELECT * FROM users WHERE', $_POST);
// executes the query WHERE (`salary` > 100000)

An attacker can use this approach to systematically discover employee salaries. They might start, for example, with a query for salaries above 100,000, then below 50,000, and by gradually narrowing the range, they can reveal the approximate salaries of all employees. This type of attack is called SQL enumeration.

The where() and whereOr() methods are even much more flexible and support SQL expressions, including operators and functions, in keys and values. This gives an attacker the ability to perform SQL injection:

// ❌ DANGEROUS CODE - attacker can insert their own SQL
$_POST = ['0) UNION SELECT name, salary FROM users WHERE (1'];
$table->where($_POST);
// executes the query WHERE (0) UNION SELECT name, salary FROM users WHERE (1)

This attack terminates the original condition using 0), appends its own SELECT using UNION to obtain sensitive data from the users table, and closes the syntactically correct query using WHERE (1).

Column Whitelist

For safe work with column names, we need a mechanism that ensures the user can only work with allowed columns and cannot add their own. We could try to detect and block dangerous column names (blacklist), but this approach is unreliable – an attacker can always come up with a new way to write a dangerous column name that we didn't anticipate.

Therefore, it is much safer to reverse the logic and define an explicit list of allowed columns (whitelist):

// Columns the user is allowed to modify
$allowedColumns = ['name', 'email', 'active'];

// Remove all unauthorized columns from the input
$filteredData = array_intersect_key($userData, array_flip($allowedColumns));

// ✅ Now safe to use in queries, such as:
$database->query('INSERT INTO users', $filteredData);
$table->update($filteredData);
$table->where($filteredData);

Dynamic Identifiers

For dynamic table and column names, use the ?name placeholder. This ensures proper escaping of identifiers according to the syntax of the given database (e.g., using backticks in MySQL):

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

Important: Use the ?name symbol only for trusted values defined in the application code. For values from the user, use a whitelist again. Otherwise, you expose yourself to security risks:

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