SQL Reference ยท Dialects and Concepts
SQL injection
What SQL injection is, how string-built queries are exploited, and the defenses that work: parameterized queries, least privilege, validation and safe dynamic SQL.
SQL injection happens when user input becomes part of the SQL text and changes what the statement means. The database cannot tell which part of the text you wrote and which part the user supplied, so it runs all of it. A search box that was meant to supply a value can instead supply extra conditions, extra statements or a comment that cuts off the rest of the query.
It is one of the oldest and most damaging web vulnerabilities: it can leak whole tables, bypass logins, change or delete data and sometimes reach the operating system. It is also one of the easiest to prevent, with one rule: never build SQL by joining strings with input; send the values separately as parameters.
How it works
Suppose an application builds this statement by string concatenation:
-- application code (pseudo): "SELECT * FROM customers WHERE city = '" + input + "'" SELECT * FROM customers WHERE city = 'Lyon';
If the user types Lyon all is well. If the user types ' OR '1'='1, the text becomes:
SELECT * FROM customers WHERE city = '' OR '1'='1';
The condition is always true, so every customer is returned. With a quote, a semicolon and a comment (--), an attacker can stop the original query early and append another one.
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Prevention
1. Parameterized queries (prepared statements)
The statement and the values travel separately. The database parses the statement first, with placeholders, so the values can never change its structure.
// PHP (PDO) $stmt = $pdo->prepare('SELECT * FROM customers WHERE city = :city'); $stmt->execute(['city' => $city]);
# Python cursor.execute("SELECT * FROM customers WHERE city = ?", (city,))
// Java (JDBC) PreparedStatement ps = conn.prepareStatement("SELECT * FROM customers WHERE city = ?"); ps.setString(1, city);
Placeholder styles differ by driver: ?, :name, $1 (PostgreSQL), @name (SQL Server) and %s (some Python drivers).
2. Things you cannot parameterize
Placeholders stand for values. They cannot stand for table names, column names, sort directions or keywords. For those, map user input onto a fixed list in your code:
$allowed = ['name', 'price', 'added_on']; $column = in_array($sort, $allowed, true) ? $sort : 'name'; $sql = "SELECT * FROM products ORDER BY $column"; // safe: from a fixed list
Never pass the raw input through; also never rely on escaping functions as the primary defense.
3. Other layers
- Least privilege. The application's database user should hold only the rights it needs. A read-only page should use a read-only account, and no web account needs
DROPor file access. - Validate input. Check type, length and format (an id must be an integer). Validation catches mistakes but is not a replacement for parameters.
- Stored procedures help only if they use parameters inside; dynamic SQL built by string joining inside a procedure is just as vulnerable.
- ORMs and query builders parameterize by default, but raw fragments (
whereRaw,.raw(), string interpolation) are still dangerous. - Hide errors. Detailed database errors help attackers; log them, and show users a generic message.
- Review
LIKEinput. Parameterize the pattern, and escape%and_if the user must not control wildcards.
Dialect differences
- Placeholder syntax and prepared-statement APIs differ per driver, but every major database supports them.
- Stacked statements (
...; DROP TABLE ...) are rejected by many drivers by default (MySQL's connector and SQLite'sexecuterun one statement), but are accepted by others (SQL Server, PostgreSQL's simple query protocol). Do not rely on this. - Comment syntax used to cut off a query differs:
--and/* */everywhere,#also in MySQL. - Escape functions differ (
mysqli_real_escape_string,quote_literal,QUOTENAME) and each has caveats around character sets; prefer parameters.
Usage notes
- Treat every external value as untrusted: form fields, query strings, headers, cookies, file contents and data read earlier from the database (second-order injection).
- Numbers too.
WHERE id = $idwith$id = '1 OR 1=1'is injectable; cast or bind as an integer. - Test your own code with a quote character in every field. An error or odd result is a warning sign.
- Log and monitor unusual queries, and keep database software patched.
- Do not build "sanitizers" by deleting words like
DROP. They are easy to bypass and break legitimate input such as the name O'Brien.