1// VULNERABLE — 不要这样做
2$username=$_POST['username'];3$password=$_POST['password'];4$query="SELECT * FROM users WHERE username='$username' AND password='$password'";
SQL Injection is the most dangerous web security vulnerability, existing for over 25 years yet still ranking at the top of the OWASP Top 10 2021 list. A single apostrophe (') entered into a search box or login form can allow an attacker to access your entire database — no password needed, no special account required.
2019Trusted since
B2BData solutions
Data·AIExpertise
Need data solutions for your business?
AlgoData has helped businesses with data engineering, analytics & AI since 2019.
SQL Injection (SQLi) is an attack technique where an attacker injects malicious SQL statements into the input parameters of a web application. When the application does not handle this correctly, the database engine executes these statements as if they were a legitimate part of the query — leading to data leakage, authentication bypass, or data destruction.
In the OWASP Top 10 Web Application Security Risks 2021, SQL Injection falls under A03: Injection — one of the most prevalent and serious security risks. Injection in general (including SQLi, LDAP injection, OS command injection) was found in 94% of applications tested, with an incidence rate of 19% according to OWASP data.
Why is SQLi still prevalent after 25+ years?
SQL Injection was first documented in the late 1990s. After more than 25 years, the natural question is: why hasn't this vulnerability been completely eliminated?
Technical reasons: Many applications — especially legacy systems — still use direct string concatenation when building SQL queries. This is a natural way of writing code for beginners, and without code review or SAST (Static Application Security Testing), it easily slips through.
Human factors: Deadline pressure leads many teams to skip best practices. Additionally, many inherited codebases were written before Prepared Statements became the industry standard.
Wide attack surface: Any point where the application receives data from users (forms, URL parameters, cookies, HTTP headers) and passes it into a SQL query is a potential attack point.
The Attack Mechanism: From Input to Database
To understand SQLi, you need to understand the data flow from when a user enters input to when the database executes the query.
Data flow in a vulnerable application
User enters data into a form (username, password, search query...)
The application concatenates the input directly into a SQL string
The SQL string is sent to the database engine
The database executes all the SQL — including the part the attacker injected
Here is a classic example of vulnerable PHP code:
php
1// VULNERABLE — don't do this
2$username=$_POST['username'];3$password=$_POST['password'];4$query="SELECT * FROM users WHERE username='$username' AND password='$password'";
In a normal case, if the user enters alice and secret123, the query will be:
This is a valid and harmless query. But what happens when an attacker enters admin'-- in the username field?
Attack payload and impact
When an attacker enters admin'-- as the username (and anything as the password):
SQL
1-- Query becomes injected as:
2SELECT*FROMusersWHEREusername='admin'--' AND password='anything'
3-- Everything after -- is commented out → password check bypassed
-- in SQL is the comment symbol (on MySQL you can also use #). The entire AND password='anything' portion is ignored. The database only checks the username, and if the admin account exists, the attacker logs in successfully without knowing the password.
Consequences of a successful attack
Depending on the configuration and privileges of the database user the application uses, an attacker can:
Read sensitive data: entire users table, credit card information, medical records
Bypass authentication: log in as any account, including admin
Modify/delete data: unlimited UPDATE or DELETE
Dump the entire schema: learn the database structure to plan further attacks
Execute operating system commands: on MySQL with INTO OUTFILE, on SQL Server with xp_cmdshell
Types of SQL Injection
Not all SQLi works by the same mechanism. Attackers use different variants depending on how the application processes and displays results.
Classic (In-band) SQLi
This is the most common and direct form. The result of the malicious SQL statement is returned directly in the application response — the same channel as the original request.
Error-based SQLi: The application displays detailed database error messages. The attacker exploits information in error messages to learn the database structure, version, and table names.
UNION-based SQLi: The attacker uses the UNION operator to append results from another SELECT to the original results. This is the most common way to dump data from other tables.
Blind Boolean-based SQLi
When the application does not display query results or error messages directly, an attacker can still exploit it by observing differences in response when a condition is TRUE vs FALSE.
For example: the attacker adds condition AND 1=1 (TRUE) vs AND 1=2 (FALSE) and observes whether the page displays normal results or is empty. From there, information can be inferred bit by bit by asking yes/no questions.
This process is slow but fully automatable with tools like sqlmap.
Blind Time-based SQLi
Similar to Boolean-based, but instead of observing response content, the attacker measures time for the server to return a response. By using SLEEP() (MySQL), WAITFOR DELAY (SQL Server), or pg_sleep() (PostgreSQL) functions, the attacker can infer information based on delays.
For example: IF(1=1, SLEEP(5), 0) — if the server takes 5 seconds to respond, the condition is TRUE.
Out-of-band SQLi
The rarest attack type, depending on the database server's ability to create outbound network connections (DNS queries, HTTP requests). The attacker doesn't need to read the response directly; instead, data is sent out via another channel (e.g., DNS lookup to an attacker-controlled domain).
Example Payloads (Educational)
Important note: The payloads below are provided for educational purposes and security awareness only. Using these techniques to attack systems you do not have permission to access is illegal. Only apply these in test/lab environments that you control.
SQL
1-- Login bypass
2' OR '1'='1 3' OR 1=1--
4admin'--
5 6-- Data dump (UNION-based)
7' UNION SELECT username, password, NULL FROM users--
8 9-- Database version
10'UNIONSELECT@@version,NULL,NULL--
1112-- Time-based blind (MySQL)
13'; SELECT SLEEP(5)--
1415-- Drop table (destructive)
16';DROPTABLEusers--
Explanation of each payload
' OR '1'='1 — Closes the current string with an apostrophe, adds a condition that is always true. Result: the WHERE clause is always TRUE → returns all rows.
admin'-- — Closes the string after the username, uses -- to comment out the rest of the query (usually the password check). Result: password authentication bypass.
UNION SELECT — Appends another SELECT to the original results to read data from another table (must match number of columns and data types).
SLEEP(5) — Used for time-based blind SQLi: measures delay to confirm the vulnerability exists.
DROP TABLE — Destructive data attack. In practice, many DB users don't have DROP permission — this is why the principle of least privilege is important.
Prevention Code: Prepared Statements
Prepared Statements (also called Parameterized Queries) are the most effective and reliable method for preventing SQL Injection. Instead of concatenating input into SQL, Prepared Statements completely separate the SQL structure from the data.
How it works:
The application sends an SQL template (with ? or :name placeholders) to the database
The database compiles and parses the SQL template — at this point, the query structure is fixed
The application sends the actual data separately
The database executes the query with data bound to placeholders — there is no way for data to change the query structure
Here is the correct implementation in popular languages:
php
1// PHP PDO — SAFE
2$pdo=newPDO($dsn,$user,$pass);3$stmt=$pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");4$stmt->execute([$username,$password_hash]);5$user=$stmt->fetch();
Python
1# Python psycopg2 — SAFE2importpsycopg23conn=psycopg2.connect(dsn)4cur=conn.cursor()5cur.execute(6"SELECT * FROM users WHERE username = %s AND password = %s",7(username,password_hash)8)9user=cur.fetchone()
Java
1// Java JDBC — SAFE
2Stringsql="SELECT * FROM users WHERE username = ? AND password = ?";3PreparedStatementstmt=conn.prepareStatement(sql);4stmt.setString(1,username);5stmt.setString(2,passwordHash);6ResultSetrs=stmt.executeQuery();
Why are Prepared Statements safe?
When an attacker enters admin'-- in the username field with a Prepared Statement:
The database already knows the query structure: WHERE username = ? AND password = ?
The value admin'-- is passed in as a string literal, not SQL code
The database treats it as a plain string of characters — including the ' and --
No injection occurs
ORM and SQLAlchemy — Safe or Not?
ORMs (Object-Relational Mappers) like SQLAlchemy, Hibernate, ActiveRecord... provide an abstraction layer over SQL. A common question: "I'm using an ORM, do I need to worry about SQLi?"
The answer: Yes, you still need to worry — if you use raw SQL in the ORM.
Python
1# SQLAlchemy — SAFE (ORM query) 2user=session.query(User).filter(User.username==username).first() 3 4# SQLAlchemy — VULNERABLE (raw f-string) 5result=session.execute(f"SELECT * FROM users WHERE username = '{username}'") 6 7# SQLAlchemy — SAFE (raw SQL with bindparam) 8fromsqlalchemyimporttext 9result=session.execute(10text("SELECT * FROM users WHERE username = :username"),11{"username":username}12)
Analysis
ORM query builder (SAFE): When you use .filter(User.username == username), SQLAlchemy automatically creates a parameterized query behind the scenes. No string concatenation occurs.
Raw f-string (VULNERABLE): This is the most common mistake when a developer wants to write custom SQL. The f-string directly concatenates the username into the SQL string — this is exactly the pattern exploited by SQLi.
text() with named parameters (SAFE): When you need raw SQL, always use SQLAlchemy's text() with :param_name placeholders and pass values via a dictionary. SQLAlchemy will automatically use a parameterized query.
Note on Stored Procedures
Stored Procedures are not automatically safe against SQLi. If the stored procedure internally uses dynamic SQL (EXEC, sp_executesql) with string concatenation, it is still vulnerable. Always check the code inside stored procedures.
WAF, Input Validation, and Why They're Not Enough
Web Application Firewall (WAF)
A WAF works by analyzing HTTP requests and blocking those containing known SQLi patterns (apostrophes, keywords like UNION, SELECT, DROP...). This sounds effective, but there are many bypass techniques:
Encoding bypass:%27 is the URL-encoding of '. Many WAFs don't decode properly before checking.
Comment insertion:UN/**/ION — SQL comments within a keyword. MySQL and some other databases accept this syntax.
Case variation:SeLeCt, uNiOn, sElEcT — WAFs using case-sensitive matching are fooled.
Double encoding:%2527 → decoded to %27 → decoded to '.
Whitespace alternatives: Tab, newline, carriage return can replace spaces in SQL.
Input validation — a double-edged sword
Some teams try to prevent SQLi by stripping or escaping special characters like ', ", ;. The problems:
Breaks legitimate data: Usernames like O'Brien, D'Souza are completely valid but will be blocked.
Not comprehensive enough: There are dozens of ways to encode and obfuscate SQLi payloads.
False sense of security: Developers think they're safe when vulnerabilities still exist.
Defense-in-depth: the right strategy
Effective SQLi prevention requires multiple layers of protection:
Prepared Statements (mandatory): This is the fundamental and irreplaceable measure. No other protection layer is sufficient without Prepared Statements.
Least Privilege DB User: The database account used by the application should only have the minimum necessary permissions (SELECT, INSERT, UPDATE on specific tables). Never use root/sa/admin accounts for the application.
WAF (supplemental): Detects and blocks known attacks, reduces noise in logs. But not the primary solution.
SAST/DAST: Static Application Security Testing to detect vulnerable code patterns during development.
Proper error handling: Don't display stack traces or detailed database error messages to users. Log errors server-side, return generic messages to the client.
Monitoring and alerting: Detect unusual patterns (many attempts with input containing ', unusual response times) for timely response.
Stored Procedures — when they're safe, when they're not
Escaping — only as a last resort when Prepared Statements aren't possible
Input validation — how to do it correctly
Least Privilege recommendations
Notable CVEs Related to SQL Injection
CVE-2011-4505 — Joomla SQL Injection:
An SQLi vulnerability in Joomla CMS affected more than 1.5 million websites worldwide. Attackers could perform unauthenticated SQL Injection through URL parameters, allowing them to read the entire database including admin login credentials. This is one of the most broadly impactful CVEs in CMS history.
Yahoo! Data Breach 2012 — SQL Injection:
In 2012, the hacker group D33Ds Company announced they had stolen 450,000 login credentials (usernames and plaintext passwords) from Yahoo! Voices (formerly Associated Content) via SQL Injection. This breach affected not just Yahoo! but also other services because many users reused the same passwords across multiple accounts.
Lessons from historical attacks:
SQLi doesn't only target small applications — large companies and popular platforms can also be affected.
A single SQLi vulnerability in a library/CMS can impact millions of websites running that platform.
Stolen data is often sold or publicly disclosed — reputational damage is usually greater than direct financial damage.
Summary: SQLi Prevention Checklist
Before deploying any feature that interacts with a database, check:
All SQL queries use Prepared Statements / Parameterized Queries
No f-strings/string concatenation in SQL code
ORM raw queries (if any) use bindparam, not f-strings
Database user has only the minimum necessary permissions
WAF is configured and operational (defense-in-depth)
Code has been SAST-scanned to detect injection patterns
SQL Injection is completely preventable. Unlike many other complex security vulnerabilities, the solution for SQLi is simple and clear: always use Prepared Statements, never concatenate SQL strings from user input. One correct coding habit from the start will completely eliminate this risk.
