SQLi

SQL Injection in Java

Vulnerability: SQL Injection

Vulnerable Code:

javaCopy codeString query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);

Reason for vulnerability: This code directly concatenates user input into the SQL query, which allows an attacker to inject malicious SQL code.

Fixed Code:

javaCopy codeString query = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();

Reason for fix: Using PreparedStatement with parameterized queries prevents SQL injection by treating user input as data, not code.

---

Vulnerable Code

import java.sql.*;

public class UserDao {
    public User getUser(String username, String password) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/users", "root", "password");
        String sql = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        // Process result set and return user
    }
}

Reason for Vulnerability:

This code constructs an SQL query by directly concatenating user input, allowing an attacker to manipulate the query structure.

Fixed Code:

javaCopyimport java.sql.*;

public class UserDao {
    public User getUser(String username, String password) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/users", "root", "password");
        String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, username);
        pstmt.setString(2, password);
        ResultSet rs = pstmt.executeQuery();
        // Process result set and return user
    }
}

Reason for Fix:

The fixed code uses a PreparedStatement with parameterized queries, which separates SQL code from user input, preventing SQL injection attacks.

Java Example

Vulnerable Code:

javaCopy@Repository
public class ProductRepository {
    @PersistenceContext
    private EntityManager entityManager;

    public List<Product> searchProducts(String category) {
        String jpql = "SELECT p FROM Product p WHERE p.category = '" + category + "'";
        return entityManager.createQuery(jpql, Product.class).getResultList();
    }
}

Reason for Vulnerability:

This JPA query is constructed by directly concatenating user input, allowing potential manipulation of the query structure.

Fixed Code:

javaCopy@Repository
public class ProductRepository {
    @PersistenceContext
    private EntityManager entityManager;

    public List<Product> searchProducts(String category) {
        String jpql = "SELECT p FROM Product p WHERE p.category = :category";
        return entityManager.createQuery(jpql, Product.class)
                            .setParameter("category", category)
                            .getResultList();
    }
}

Reason for Fix:

The fixed code uses a parameterized JPQL query, which binds the user input as a parameter, preventing SQL injection attacks in JPA queries.

Python Example

Vulnerable Code:

pythonCopyimport sqlite3

def get_user(username):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute(f"SELECT * FROM users WHERE username = '{username}'")
    return cursor.fetchone()

Reason for Vulnerability:

This code uses string formatting to construct the SQL query, allowing an attacker to inject malicious SQL code.

Fixed Code:

pythonCopyimport sqlite3

def get_user(username):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
    return cursor.fetchone()

Reason for Fix:

The fixed code uses parameterized queries, which treat user input as data rather than part of the SQL command, preventing SQL injection attacks.

---

Vulnerable Code:

pythonCopy codequery = "SELECT * FROM users WHERE username = '%s'" % username
cursor.execute(query)

Reason for vulnerability: User input is directly used in the SQL query, allowing SQL injection.

Fixed Code:

pythonCopy codequery = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (username,))

Reason for fix: Use parameterized queries to prevent SQL injection.

---

NoSQL Injection

Example 1: JavaScript (Node.js with MongoDB)

Vulnerable Code:

javascriptCopy codeapp.post('/login', (req, res) => {
    const username = req.body.username;
    const password = req.body.password;
    db.collection('users').findOne({ username: username, password: password }, (err, user) => {
        if (user) {
            res.send('Login successful');
        } else {
            res.send('Login failed');
        }
    });
});

Reason for vulnerability: User input is directly used in the query, allowing NoSQL injection.

Fixed Code:

javascriptCopy codeapp.post('/login', (req, res) => {
    const username = req.body.username;
    const password = req.body.password;
    db.collection('users').findOne({ username: username, password: hash(password) }, (err, user) => {
        if (user) {
            res.send('Login successful');
        } else {
            res.send('Login failed');
        }
    });
});

Reason for fix: Hash the password before querying the database to prevent injection.

Example 2: Python (Flask with MongoDB)

Vulnerable Code:

pythonCopy [e-mail]('/login', methods=['POST'])
def login():
    username = request.form['username']
    password = request.form['password']
    user = db.users.find_one({'username': username, 'password': password})
    if user:
        return 'Login successful'
    else:
        return 'Login failed'

Reason for vulnerability: User input is directly used in the query, allowing NoSQL injection.

Fixed Code:

pythonCopy [e-mail]('/login', methods=['POST'])
def login():
    username = request.form['username']
    password = hash_password(request.form['password'])
    user = db.users.find_one({'username': username, 'password': password})
    if user:
        return 'Login successful'
    else:
        return 'Login failed'

Reason for fix: Hash the password before querying the database to prevent injection.