What is SQL Injection?
SQL Injection is a security vulnerability that occurs when
an application places untrusted user input directly into an SQL query.
Instead of being treated only as data, the input may be interpreted as
part of the SQL statement.
SQL Injection can potentially allow attackers to bypass application logic, access unauthorized information, modify data, or perform other unintended database operations.
A Normal SQL Query
Before understanding SQL Injection, consider a normal query that searches for a customer by name.
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
customer_name TEXT NOT NULL,
city TEXT NOT NULL
);
INSERT INTO customers (customer_name, city)
VALUES
('Aarav', 'Delhi'),
('Meera', 'Pune'),
('Kabir', 'Jaipur'),
('Riya', 'Mumbai');
SELECT *
FROM customers
WHERE customer_name = 'Meera';How SQL Injection Happens
The problem occurs when an application creates SQL by directly combining SQL text with user input. For example, an application might construct a query using a username entered into a login form.
DROP TABLE IF EXISTS users;
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
user_role TEXT NOT NULL
);
INSERT INTO users (username, user_role)
VALUES
('aman', 'user'),
('neha', 'editor'),
('rohan', 'admin');
-- Example of the SQL query produced for a normal username:
SELECT *
FROM users
WHERE username = 'aman';Why String Concatenation is Dangerous
If an application directly concatenates user input into SQL, special characters supplied by the user can change the meaning of the query. This is why SQL strings should not be constructed by simply joining untrusted input with SQL syntax.
DROP TABLE IF EXISTS products;
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
category TEXT NOT NULL,
price REAL NOT NULL
);
INSERT INTO products (product_name, category, price)
VALUES
('Wireless Mouse', 'Electronics', 850),
('Notebook', 'Stationery', 180),
('Desk Lamp', 'Office', 1200),
('USB Cable', 'Electronics', 450);
-- Normal application query:
SELECT *
FROM products
WHERE category = 'Electronics';Parameterized Queries
The recommended solution is to use a parameterized query.
With parameterized queries, the SQL structure and user-provided values
are handled separately by the database API.
The following example uses a fixed value so that it can be executed directly in your SQLite editor.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name TEXT NOT NULL,
department TEXT NOT NULL,
salary INTEGER NOT NULL
);
INSERT INTO employees (employee_name, department, salary)
VALUES
('Aditi', 'Engineering', 72000),
('Vikram', 'Design', 58000),
('Sana', 'Engineering', 68000),
('Karan', 'Support', 52000);
-- Safe SQL structure:
SELECT employee_name, department, salary
FROM employees
WHERE department = 'Engineering';Parameterized INSERT Concept
Parameterized statements should also be used when applications insert user-provided information. The SQL command remains separate from the values supplied by the application.
DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
message_id INTEGER PRIMARY KEY AUTOINCREMENT,
author TEXT NOT NULL,
message TEXT NOT NULL
);
-- Working SQLite example:
INSERT INTO messages (author, message)
VALUES ('Maya', 'Welcome to SQL security!');
INSERT INTO messages (author, message)
VALUES ('Arjun', 'Parameterized queries help prevent SQL Injection.');
SELECT *
FROM messages
ORDER BY message_id;Parameterized UPDATE Concept
UPDATE statements should also use parameterized queries when their values originate from users or external applications.
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
account_id INTEGER PRIMARY KEY,
account_name TEXT NOT NULL,
status TEXT NOT NULL
);
INSERT INTO accounts (account_name, status)
VALUES
('North Branch', 'Active'),
('Central Branch', 'Active'),
('South Branch', 'Inactive');
-- Working SQLite UPDATE:
UPDATE accounts
SET status = 'Inactive'
WHERE account_id = 2;
SELECT *
FROM accounts
ORDER BY account_id;Parameterized DELETE Concept
DELETE operations should also be protected from unsafe user input. Applications should bind the value rather than inserting it directly into the SQL string.
DROP TABLE IF EXISTS tasks;
CREATE TABLE tasks (
task_id INTEGER PRIMARY KEY,
task_name TEXT NOT NULL,
task_status TEXT NOT NULL
);
INSERT INTO tasks (task_name, task_status)
VALUES
('Backup database', 'Pending'),
('Update documentation', 'Completed'),
('Check server logs', 'Pending'),
('Review reports', 'Completed');
-- Working SQLite DELETE:
DELETE FROM tasks
WHERE task_id = 2;
SELECT *
FROM tasks
ORDER BY task_id;Input Validation
Input validation can provide an additional layer of protection. For example, an application expecting an age may verify that the value is numeric and within an acceptable range.
DROP TABLE IF EXISTS students;
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name TEXT NOT NULL,
age INTEGER NOT NULL
);
INSERT INTO students (student_name, age)
VALUES
('Riya', 19),
('Dev', 21),
('Tara', 20),
('Kabir', 23);
-- Example of a validated numeric condition:
SELECT *
FROM students
WHERE age >= 20
ORDER BY age;SQL Injection in Login Applications
Login systems are especially important because authentication queries often use information supplied through forms. If those values are inserted directly into SQL, the application may become vulnerable.
DROP TABLE IF EXISTS login_users;
CREATE TABLE login_users (
user_id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
password_hash TEXT NOT NULL
);
INSERT INTO login_users (username, password_hash)
VALUES
('riya', 'hash_101'),
('dev', 'hash_202'),
('tara', 'hash_303');
-- Safe query structure demonstrated with a fixed value:
SELECT user_id, username
FROM login_users
WHERE username = 'riya';Using Parameters with SQLite Applications
SQLite supports parameter binding through programming-language APIs. The exact syntax depends on the language. For example, an application can prepare an SQL statement and then bind a username separately rather than constructing the complete SQL command with string concatenation.
DROP TABLE IF EXISTS products;
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
price REAL NOT NULL
);
INSERT INTO products (product_name, price)
VALUES
('Mechanical Keyboard', 3200),
('USB-C Adapter', 750),
('Monitor Stand', 2100);
-- SQL statement used by an application:
SELECT product_name, price
FROM products
WHERE product_name = 'USB-C Adapter';SQL Injection Prevention Methods
| Method | Purpose | Recommendation |
|---|---|---|
| Parameterized Queries | Separates SQL code from input values | Strongly recommended |
| Prepared Statements | Allows SQL to be prepared separately from values | Strongly recommended |
| Input Validation | Ensures input follows expected rules | Additional protection |
| Least Privilege | Limits what a database account can access | Recommended |
| Error Handling | Prevents unnecessary database details from being exposed | Recommended |
Common SQL Injection Mistakes
- Directly concatenating user input into SQL statements.
- Building SQL queries with string interpolation.
- Relying only on client-side input validation.
- Giving an application database account excessive privileges.
- Exposing detailed database errors to users.
- Failing to use prepared statements or parameterized queries.
Best Practices
- Use parameterized queries for values supplied by users.
- Use prepared statements whenever supported by your database API.
- Validate input according to the application’s requirements.
- Apply the principle of least privilege to database accounts.
- Do not expose sensitive database errors to end users.
- Regularly review application code that builds SQL queries.
SQL Injection occurs when untrusted input can influence the structure of an SQL statement. The most important defense is to use parameterized queries or prepared statements so that user input is handled as data rather than SQL code.
🧪 Test Your SQL Code
Edit the SQL code on the left and click “Run Code” to see the result on the right.
Click “Run Code” to see the result here.