# SQLi

#### SQL Injection in Java

**Vulnerability:** SQL Injection

**Vulnerable Code:**

{% code overflow="wrap" %}

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

{% endcode %}

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

**Fixed Code:**

{% code overflow="wrap" %}

```java
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();
```

{% endcode %}

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

***

#### Vulnerable Code

```java
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:

```java
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:

```java
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:

```java
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:

```python
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:

```python
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:**

```python
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:**

```python
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:**

```javascript
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:**

```javascript
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:**

```python
pythonCopy code@app.route('/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:**

```python
pythonCopy code@app.route('/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.