Prevention of SQL injection in the idClient header and application fields
import express from 'express';
import mysql from 'mysql';
const app = express();
const port = 3000;
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password',
database: 'mydatabase'
});
app.get('/users', (req, res) => {
const idClient = req.headers['idclient'];
const application = req.headers['application'];
const query = `SELECT * FROM users WHERE idClient = '${idClient}' AND application = '${application}'`;
connection.query(query, (error, results) => {
if (error) {
res.status(500).json({ error: 'Internal Server Error' });
} else {
res.json(results);
}
});
});
app.listen(port, () => {
console.log(`Server is running on port ${port}`);
});
The vulnerability in this code is a SQL Injection vulnerability. It occurs because the code directly concatenates user-controlled input (
idClient and
application) into the SQL query string without proper sanitization or validation.
In the vulnerable code, the
idClient and
application values are obtained from the request headers without any validation. Then, these values are directly used in the SQL query string without any form of parameterization or escaping.
An attacker can exploit this vulnerability by manipulating the
idClient and
application headers to inject malicious SQL statements. For example, an attacker can set the
idClient header to
' OR 1=1 -- which would result in the following query:
SELECT * FROM users WHERE idClient = '' OR 1=1 --' AND application = '...'
This injected SQL statement would always evaluate to true (
1=1), bypassing any intended authentication or authorization checks. The attacker can then retrieve or modify sensitive information in the database.
To mitigate this vulnerability, it is recommended to use parameterized statements or stored procedures. Parameterized statements separate the SQL query logic from the user input, preventing the injection of malicious code.
import express from 'express';
import mysql from 'mysql';
const app = express();
const port = 3000;
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password',
database: 'mydatabase'
});
app.get('/users', (req, res) => {
const idClient = req.headers['idclient'];
const application = req.headers['application'];
const query = 'SELECT * FROM users WHERE idClient = ? AND application = ?';
const values = [idClient, application];
connection.query(query, values, (error, results) => {
if (error) {
res.status(500).json({ error: 'Internal Server Error' });
} else {
res.json(results);
}
});
});
app.listen(port, () => {
console.log(`Server is running on port ${port}`);
});
The fixed code addresses the SQL Injection vulnerability by using parameterized statements to perform database queries. Here's an explanation of the changes made:
1. The code imports the necessary modules, including 'express' for creating the server and 'mysql' for connecting to the MySQL database.
2. A connection to the MySQL database is established using the
mysql.createConnection() method. The connection details, such as the host, user, password, and database name, are provided.
3. The code defines an Express app and sets the port to 3000.
4. In the route handler for the '/users' endpoint, the code retrieves the 'idClient' and 'application' values from the request headers using
req.headers['idclient'] and
req.headers['application'], respectively.
5. The code constructs a SQL query string with placeholders for the 'idClient' and 'application' values. The query string is
'SELECT * FROM users WHERE idClient = ? AND application = ?'.
6. An array of values is created with the 'idClient' and 'application' variables. This array,
values, will be used to replace the placeholders in the query.
7. The
connection.query() method is used to execute the SQL query with the provided values. This method takes the query string, the values array, and a callback function as parameters.
8. In the callback function, the code checks if an error occurred during the query execution. If an error is present, the server responds with a 500 status code and an error message. Otherwise, the server responds with the query results in JSON format.
9. The app listens on the specified port, and a console log message is printed to indicate that the server is running.
By using parameterized statements, the code ensures that the user input (idClient and application) is treated as data rather than executable code. This prevents SQL Injection attacks by escaping special characters and separating the SQL logic from the user input.