SQL injection is a web security problem that happens when user input is treated as part of a database command. This cheat sheet helps students recognize unsafe query construction and understand why string concatenation is dangerous. It also shows the main defenses used in real applications, especially prepared statements and parameterized queries.
These skills matter because databases often store passwords, personal data, grades, orders, and other sensitive information.
Key Facts
- SQL injection happens when untrusted input changes the meaning of a SQL command, such as input ' OR '1'='1 making a login condition always true.
- Unsafe query construction often looks like "SELECT * FROM users WHERE name = '" + userName + "'", because userName can contain SQL code.
- A prepared statement separates the SQL structure from the data, using a placeholder such as SELECT * FROM users WHERE name = ?.
- Parameter binding sends user input as a value, so input like ' OR '1'='1 is treated as text instead of executable SQL.
- Input validation should check allowed formats, lengths, and ranges, such as allowing only numbers for a user_id field.
- Escaping quotes is not a complete defense because it is easy to apply incorrectly and may fail with different databases or encodings.
- Least privilege means the application database account should only have the permissions it needs, such as SELECT and INSERT but not DROP TABLE.
- Safe error handling hides detailed database errors from users while logging technical details for developers.
Vocabulary
- SQL injection
- A security attack where malicious input changes a SQL query so the database runs unintended commands.
- Prepared statement
- A precompiled SQL command that uses placeholders so data values are supplied separately from the query structure.
- Parameterized query
- A database query that uses parameters for user input instead of building the command with string concatenation.
- Input validation
- The process of checking that input matches the expected type, length, format, and allowed values before using it.
- Least privilege
- A security rule that gives each account or program only the minimum permissions needed to do its job.
- Error handling
- The way a program responds to failures, including showing safe messages to users and recording useful details for developers.
Common Mistakes to Avoid
- Building SQL with string concatenation, because attacker input can become part of the command instead of remaining data.
- Relying only on client-side validation, because attackers can bypass browser checks and send requests directly to the server.
- Escaping quotes as the only protection, because escaping can be missed, implemented incorrectly, or fail in unusual database settings.
- Using a database account with administrator permissions, because a successful injection could then read, change, or delete far more data.
- Showing raw database error messages to users, because errors can reveal table names, column names, query structure, and other attack clues.
Practice Questions
- 1 A login query is built as SELECT * FROM users WHERE username = '" + input + "'. What unsafe SQL might be produced if input is ' OR '1'='1?
- 2 Rewrite this unsafe query using a placeholder: SELECT * FROM products WHERE product_id = " + productId.
- 3 A form field for age receives the value 17; DROP TABLE users;. Name two defenses that should prevent this from becoming a successful SQL injection.
- 4 Why do prepared statements protect against SQL injection better than simply checking whether the input contains the word SELECT?
Understanding SQL Injection Prevention Reference
When an application sends a command to a database, the database first reads its grammar. Words such as SELECT, WHERE, and ORDER BY have roles in that grammar. Quotation marks, spaces, operators, and parentheses help divide the command into tokens.
A safe design fixes this grammatical shape before a visitor supplied value arrives. The database then receives the value in a separate slot.
This makes a placeholder more than a shorter way to write a query. It creates a firm boundary between instructions and data at the database interface.
Prepared statements often follow several steps. The application defines the command structure, the database checks that structure, then the application supplies values for each placeholder. Good database libraries can bind each value with an appropriate type, such as an integer, date, or text value.
Type binding reduces mistakes caused by manually adding quote marks or converting values into text too early. A failed conversion should produce a controlled application response. It should not cause the program to guess at a value or retry using a hand-built SQL string.
Some parts of a database command cannot normally be supplied as parameters. Table names, column names, and sort directions are common examples. A search page might allow sorting by name or date, but it should choose from a small fixed list written by the developer.
It should not insert an arbitrary submitted word into the command. This approach is called allowlisting. It is safer than trying to identify every bad character because new combinations can be missed.
The same idea applies to filters, report options, and page sizes. A page size can be limited to a sensible whole number range before it is used.
Validation, permissions, error handling, and testing form extra layers around parameter binding. Validation checks whether data makes sense for the task. A number may have the right format but still be an invalid account number for the current user.
The application must check access rules before showing or changing a record. Its database account should have limited rights, so a programming mistake has a smaller effect. Development logs need enough detail to trace failures, including a request identifier and the operation that failed.
They should avoid recording passwords, session tokens, or full private records. Users should receive a simple failure message instead of internal database details. Students can practise by testing empty input, very long input, unusual punctuation, wrong data types, and attempts to select records owned by another user.