Explain SQL injection attacks and demonstrate how you would prevent them in a full-stack application. What are the different types of SQL injection, and which defense mechanisms are most effective?

Question

Accepted Answer

SQL injection is one of the most dangerous and prevalent vulnerabilities. It occurs when untrusted data is sent to an interpreter as part of a command, allowing attackers to execute unintended SQL commands.

There are several types of SQL injection. Classic, or in-band, SQL injection is where the attacker uses the same communication channel to launch the attack and gather results. For example, adding a single quote to an input field to break out of a query. Blind SQL injection is where the application does not return error messages, but the attacker can still infer information by observing application behavior, such as response times or conditional responses. Out-of-band SQL injection uses different channels, like DNS or HTTP requests, to exfiltrate data when in-band methods are not available.

For prevention, I implement defense in depth with multiple layers. The primary defense is parameterized queries, also called prepared statements. Instead of building SQL strings with concatenation, you use placeholders that the database driver safely handles. In Node.js with PostgreSQL, you would write something like: query with the text "SELECT * FROM users WHERE id = $1" and values as an array containing the user ID. This ensures that user input is always treated as data, never as SQL code.

The second layer is using an ORM, or Object-Relational Mapper. Tools like Prisma, TypeORM, or Sequelize abstract direct SQL and use parameterized queries internally. However, be careful with raw query methods that might bypass these protections.

The third layer is input validation. Even with parameterized queries, validate that inputs match expected formats. If an ID should be a number, reject non-numeric input before it reaches the database layer. Use allowlists rather than denylists; define what is acceptable rather than trying to block what is dangerous.

The fourth layer is the principle of least privilege. Database accounts used by the application should have minimal permissions. A read-only endpoint should use a connection with only SELECT privileges. This limits damage even if injection occurs.

The fifth layer is stored procedures with caution. They can help by pre-defining SQL logic, but they are not a silver bullet. Stored procedures that dynamically build SQL are still vulnerable.

For detection and monitoring, log all database queries and use anomaly detection to identify unusual patterns. Tools like database activity monitoring can alert on suspicious queries. In production, consider a Web Application Firewall, or WAF, as an additional layer, though it should never be your only defense.

The key principle is never trusting user input and always treating it as potentially malicious, regardless of where it comes from.