Answer

The most reliable way is to always use parameterized queries or prepared statements, and NOT to form queries through simple string concatenation with user values.

For more complex scenarios (dynamic SQL within procedures), apply special escaping mechanisms and whitelisting (strict filtering) of parameters.

Example:

• At the application level (Python, Java, C#):

cursor.execute('SELECT * FROM users WHERE login = %s', [login])

• Inside SQL (PostgreSQL):

EXECUTE format('SELECT * FROM %I WHERE id = $1', tablename) USING id;

Here %I safely substitutes only table/column names.

Tricky Question

Is the following approach safe when inserting a number into a string?

EXECUTE 'SELECT * FROM users WHERE id = ' || user_input;

NO! Even if you expect a number, an attacker can submit code: 1; DROP TABLE users; — the query will be executed. Therefore, always use placeholders and explicit type checks for input parameters.

Examples of real errors due to lack of knowledge of the topic

---

Story 1

A search form for employees built WHERE conditions through string concatenation on the application side. An attacker entered: ' OR 1=1 --, which outputted data for all employees. Later, a similar approach led to the deletion of the data section. Conclusion: concatenating significant parameters is always a risk.

---

Story 2

In an administrative report selection interface, the name of the table was pulled from a user field and inserted into the SQL query. The user was able to insert an invalid name and gain access to system tables. After that, only hardcoded names were allowed (whitelisting).

---

Story 3

In an e-commerce project, during dynamic sorting, users passed the field name via a GET parameter. A hacker inserted price; DELETE FROM orders; --, resulting in the loss of all orders. Filtering allowed column names and using parameterized queries solved the problem.