What is SQL injection and how do you prevent it?

What it is. SQL injection happens when an application builds a SQL statement by gluing untrusted input into the query text, for example with string concatenation or formatting. The database cannot tell which part of the text was meant to be code (the SQL) and which part was data (the user's value). Specially crafted input can therefore change the structure of the query. That can let someone read data they should not, alter or delete it, or bypass a login check.

It is in the Injection category of the OWASP Top 10, which also covers related flaws such as OS command injection and LDAP injection.

The root cause is mixing code and data. The fix is to keep them separate.

1. Parameterised queries (prepared statements): the primary defence

# Python DB-API: the driver sends the value separately from the SQL text
cur.execute("SELECT id, name FROM students WHERE roll_no = %s", (roll_no,))

PreparedStatement ps = conn.prepareStatement("SELECT id, name FROM students WHERE roll_no = ?");
ps.setString(1, rollNo);

The SQL structure is fixed before any user data is involved. The value is always treated as a value, whatever characters it contains. ORMs (SQLAlchemy, Hibernate, Django's ORM) parameterise for you, as long as you avoid their raw-SQL string-building features.

2. Allow-list anything that cannot be a parameter. Table names, column names and sort direction cannot be bound as parameters. Map user choices to a fixed set, for example {"name": "name", "cgpa": "cgpa"}, and reject anything else.

3. Defence in depth

• Least privilege: the application's database account gets only the permissions it needs. A reporting page does not need DROP or write access.
• Validate input types and lengths (a roll number should match a pattern).
• Do not show raw database errors to users. Log them on the server instead.
• Stored procedures help only if they do not build dynamic SQL inside.
• A web application firewall can catch some attacks, but it is a safety net, not a fix.

Why escaping and block-lists are weak. Escaping rules differ between databases, character sets and contexts (string, number, identifier), and one missed spot is enough. Blocking keywords breaks legitimate input and can usually be bypassed. Parameterisation removes the problem completely instead of trying to spot "bad" input.

Testing: code review that looks for string-built queries, static analysis (SAST) tools, and authorised security testing of your own applications.