Implementation of proper input validation and the use of parameterized statements or stored procedures to prevent SQL injection attacks.
import express from 'express';
import mysql from 'mysql';
const app = express();
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password',
database: 'mydatabase'
});
app.get('/users', (req, res) => {
const username = req.query.username;
const query = `SELECT * FROM users WHERE username = '${username}'`;
connection.query(query, (error, results) => {
if (error) {
console.error(error);
res.status(500).send('Internal Server Error');
} else {
res.json(results);
}
});
});
app.listen(3000, () => {
console.log('Server is running on port 3000');
});
The vulnerability in this code is a SQL injection vulnerability. It occurs because the code constructs a SQL query string by directly concatenating the user-supplied input (
username) into the query without proper validation or sanitization.
In the code, the
username value is obtained from the request query parameters (
req.query.username). It is then directly used in the SQL query string without any validation or sanitization. This means that an attacker can manipulate the
username parameter to inject arbitrary SQL code into the query.
For example, an attacker can pass a value like
' OR '1'='1' -- as the
username parameter. This would result in the following SQL query being executed:
SELECT * FROM users WHERE username = '' OR '1'='1' --'
The injected SQL code
' OR '1'='1' -- is a common technique used to bypass the authentication mechanism and retrieve all the records from the
users table.
This vulnerability allows an attacker to execute arbitrary SQL statements, potentially leading to unauthorized access, data leakage, or even data manipulation.
To fix this vulnerability, the code should use parameterized statements or prepared statements to ensure that user input is properly sanitized and treated as data rather than executable code.
import express from 'express';
import mysql from 'mysql';
const app = express();
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password',
database: 'mydatabase'
});
app.get('/users', (req, res) => {
const username = req.query.username;
const query = 'SELECT * FROM users WHERE username = ?';
const values = [username];
connection.query(query, values, (error, results) => {
if (error) {
console.error(error);
res.status(500).send('Internal Server Error');
} else {
res.json(results);
}
});
});
app.listen(3000, () => {
console.log('Server is running on port 3000');
});
The fixed code is written in TypeScript and addresses the SQL injection vulnerability by using parameterized statements. Here's an explanation of the code:
1. The code imports the necessary modules, including Express and MySQL.
2. An Express application is created using the
express() function.
3. A MySQL connection is established using the
mysql.createConnection() function. The connection details, such as the host, username, password, and database name, are provided.
4. An endpoint
/users is defined using the
app.get() method. This endpoint handles GET requests and expects a query parameter
username.
5. Inside the request handler function, the
username value is extracted from the request query.
6. A SQL query is defined as a string, selecting all columns from the
users table where the
username column matches the provided value. The query uses a placeholder
? for the value.
7. An array
values is created, containing the
username value.
8. The
connection.query() method is used to execute the SQL query with the provided values. This method takes the query, values, and a callback function as parameters.
9. In the callback function, any error that occurred during the query execution is logged to the console, and an appropriate error response is sent if an error occurred. Otherwise, the query results are sent as a JSON response.
10. The Express application is set to listen on port 3000 using the
app.listen() method. A log message is printed to the console once the server starts.
By using parameterized statements (
? placeholder) and passing the values separately, the fixed code ensures that user input is properly sanitized and prevents SQL injection attacks.