Learn how to prevent SQL injection attacks with real code examples and best practices. Protect your database from SQL vulnerabilities and security threats.
SQL Injection is one of the most common web security vulnerabilities, allowing attackers to execute malicious SQL queries. It occurs when an application incorporates untrusted user input into SQL queries without proper validation or sanitization.
The user input can potentially include a malicious payload that manipulates a database query to allow attackers unauthorized actions:
Access, modify, or delete sensitive data from the database
Bypass authentication mechanisms
Execute administrative operations on the database
In some cases, execute commands on the operating system
This guide covers SQL Injection attacks, examples, prevention methods, and how to test for SQL vulnerabilities using real-world techniques.
One Simple SQL Injection Attack Example
Consider this classic example of a login query:
String query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "';";
An attacker could provide this input for the username:
admin' --
The resulting query becomes:
SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything';
This allows the attacker to log in as the admin user without knowing their password. The -- makes the rest of the query a comment, effectively bypassing the password check.
Real-world Occurrences of SQL Injection
Sony Pictures Data Breach (2011)
The attack on Sony’s network was not just a breach of data but an outright assault on the company’s digital infrastructure.
According to The Washington Post, approximately 77 million PlayStation Network accounts were compromised, revealing personal details of millions of users. The financial repercussions were equally severe, with the breach costing Sony an estimated $170 million.
This incident highlighted the vulnerability of even the most sophisticated digital networks to SQL Injection attacks, underscoring the critical need for robust cybersecurity measures. It served as a wake-up call to the entire digital entertainment industry, emphasizing the importance of safeguarding user data against evolving cyber threats.
TalkTalk Data Breach (2015)
The year 2015 marked a significant event in the history of cyberattacks with the TalkTalk breach. Nearly 157,000 customers of the UK-based telecom company TalkTalk had their personal details compromised, as reported by BBC News. This cyber-attack didn’t just breach customer accounts; it shook the foundation of trust between consumers and digital service providers.
SQL Injection Prevention Methods: How to Fix Your Code
The most efficient way to fix an SQL Injection issue in your code is using Prepared Statements with parameterized queries.
Prepared Statements are precompiled SQL templates that separate the query from the user input, meaning they take user-provided values only after the query structure is already determined. By doing so, they prevent attackers from injecting malicious SQL code, since the parameters are treated strictly as data rather than executable instructions.
Code Samples
Vulnerable Code
String userId = request.getParameter("userId");
String query = "SELECT * FROM users WHERE id = ;
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);
Fixed Code
String userId = request.getParameter("userId");
String query = "SELECT * FROM users WHERE id = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, userId);
ResultSet rs = pstmt.executeQuery();
Fix Explanation
The vulnerable code directly concatenates user input into the SQL query, allowing the query to be manipulated.
The fix uses PreparedStatement with parameterized queries.
Parameters are bound using type-safe methods (setString, setInt, etc.).
The Prepared Statement automatically handles proper escaping and prevents SQL injection.
Vulnerable Code
const userId = req.params.userId;
const query = `SELECT * FROM users WHERE id = '${userId}'`;
connection.query(query, (error, results) => {
if (error) throw error;
console.log(results);
});
Fixed Code
const userId = req.params.userId;
const query = 'SELECT * FROM users WHERE id = ?';
connection.query(query, [userId], (error, results) => {
if (error) throw error;
console.log(results);
});
Fix Explanation
The vulnerable code directly interpolates user input into the SQL query string.
The fix uses parameterized queries with question mark placeholders.
Parameters are passed as an array in the second argument.
The MySQL/Node.js driver automatically handles proper escaping and prevents SQL injection.
Vulnerable Code
user_id = request.args.get('userId')
query = f"SELECT * FROM users WHERE id = '{user_id}'"
cursor.execute(query)
results = cursor.fetchall()
Fixed Code
user_id = request.args.get('userId')
query = "SELECT * FROM users WHERE id = %s"
cursor.execute(query, (user_id,))
results = cursor.fetchall()
Fix Explanation
The vulnerable code uses f-string to interpolate user input directly into the query.
The fix uses parameterized queries with placeholders (%s for MySQL, ? for SQLite).
Parameters are passed as a tuple in the second argument.
The database adapter automatically handles proper escaping and prevents SQL injection.
Vulnerable Code
string userId = Request.QueryString["userId"];
string query = "SELECT * FROM users WHERE id = '" + userId + "'";
SqlCommand command = new SqlCommand(query, connection);
SqlDataReader reader = command.ExecuteReader();
Fixed Code
string userId = Request.QueryString["userId"];
string query = "SELECT * FROM users WHERE id = @userId";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@userId", userId);
SqlDataReader reader = command.ExecuteReader();
Fix Explanation
The vulnerable code concatenates user input directly into the SQL query.
The fix uses parameterized queries with named parameters (@paramName).
Parameters are added using AddWithValue or Add methods.
The SQL Server provider automatically handles proper escaping and prevents SQL injection.
Vulnerable Code
$userId = $_GET['userId'];
$query = "SELECT * FROM users WHERE id = '$userId'";
$result = mysqli_query($connection, $query);
Fixed Code
$userId = $_GET['userId'];
$query = "SELECT * FROM users WHERE id = ?";
$stmt = mysqli_prepare($connection, $query);
mysqli_stmt_bind_param($stmt, "s", $userId);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
Fix Explanation
The vulnerable code directly interpolates user input into the SQL query string.
The fix uses prepared statements with parameter placeholders (?).
Parameters are bound using mysqli_stmt_bind_param with type specifiers.
The MySQL driver automatically handles proper escaping and prevents SQL injection.
Vulnerable Code
string userId = request.getParameter("userId");
string query = "SELECT * FROM users WHERE id = '" + userId + "'";
MYSQL_RES* result = mysql_query(connection, query.c_str());
The vulnerable code directly concatenates user input into the SQL query.
The fix uses prepared statements with parameter placeholders (?).
Parameters are bound using MYSQL_BIND structure and mysql_stmt_bind_param.
The MySQL C API automatically handles proper escaping and prevents SQL injection.
